ごくまれに使うことがあるOracleに関することのまとめ。
1. データ・ディクショナリ
(公式)『Oracle Databaseリファレンス』※英文のみ 19c
https://docs.oracle.com/pls/topic/lookup?ctx=en/database/oracle/oracle-database/19/cncpt&id=GUID-8865F65B-EF6D-44A5-B0A1-3179EFF0C36A
接頭辞の意味
「DBA_」：すべてのオブジェクト
「ALL_」：ユーザーが権限を持つオブジェクト
「USER_」：ユーザーによって所有されているオブジェクト
1-1. すべてのデータ・ディクショナリ
SELECT * FROM DICTIONARY;
1-2. 表領域（論理的なデータ領域）
SELECT * FROM DBA_TABLESPACES;
1-3. データファイル（物理ファイル）
SELECT * FROM DBA_DATA_FILES;
1-4. 表領域の使用可能なエクステント(extents)
SELECT * FROM DBA_FREE_SPACE;
説明：https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_FREE_SPACE.html#GUID-0FFCB990-4013-4D8A-A2FC-CF4B38DB7CC8
参考：11gR2のDBA_FREE_SPAC(日本語)
https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_3194.htm#i1621545
1-4-a. 表領域の空き容量を確認する
SELECT
files.TABLESPACE_NAME
,MIN(files.BYTES)/1024/1024 as FileSize_MB
,ROUND(MIN(files.BYTES)/1024/1024-SUM(free.BYTES)/1024/1014,2) as Used_MB
,ROUND(SUM(free.BYTES)/1024/1024,2) as FreeSpace_MB
,ROUND(
(MIN(files.BYTES)/1024/1024-SUM(free.BYTES)/1024/1024)/
(MIN(files.BYTES)/1024/1024) * 100
, 2)
as UseRate
FROM DBA_DATA_FILES files, DBA_FREE_SPACE free
WHERE files.FILE_ID = free.FILE_ID
GROUP BY files.TABLESPACE_NAME;
1-5. オブジェクト一覧
SELECT * FROM USER_OBJECTS;
— オブジェクトタイプ、オブジェクト名でソート
SELECT * FROM USER_OBJECTS ORDER BY OBJECT_TYPE,OBJECT_NAME;
オブジェクトタイプ
TABLE, INDEX, VIEW, FUNCTION, PROCEDURE, TRIGGER, PACKAGE, PACKAGE BODY, SEQUENCE, SYNONYM, DATABASE LINK
※CONTEXTはこのテーブルでは確認できない。
1-6. コンテキスト一覧
SELECT * FROM ALL_CONTEXT;
※USER_CONTEXTは存在しない。
EXPORTコマンド（スキーマ指定）で対象とならない。
1-7. ユーザ一覧
— 現行のユーザが参照できるデータベースのユーザーをすべて表示
SELECT * FROM ALL_USERS ORDER BY USERNAME;
–データベース内のユーザをすべて表示(詳細）
SELECT * FROM DBA_USERS ORDER BY USERNAME;
2. ストレージ関連用語
| 用語 | 概要 |
|---|---|
| 初期化パラメータファイル | PFILE。ファイル名：init.ora 値の変更を反映するには、インスタンスの再起動が必要 |
| 制御ファイル | 各データファイルの場所、データベースの物理情報を保持。 初期化パラメータに格納先が定義されている。 |
| REDOログファイル | データに対するすべての変更を記録する。 |
| SYSTEM表領域 | データディクショナリを保持 |
| SYSAUX表領域 | SYSTEMの補助的な情報を保持 |
| TEMP(一時)表領域 | ソート処理を実行するための領域 |
| UNDO表領域 | トランザクション領域 |
| Users表領域 | ユーザーが作成するOracleデータベースアカウントによって 作成されるオブジェクトの表領域 |
表領域の常識①：表と索引は別の表領域にする。
3. ユーザー作成・オブジェクト作成
3-1. ユーザー作成(とスキーマ作成）と権限付与
CREATE USER username IDENTIFIED BY “password” DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp ;
| パラメータ | 説明 |
|---|---|
| IDENTIFIED BY | ログイン時のパスワード |
| DEFAULT TABLESPACE | ユーザーのデフォルトの表領域（未指定の場合SYSTEM表領域となる。） |
| TEMPORARY TABLESPACE | 一時表領域（未指定の場合SYSTEM表領域となる。） |
| PROFILE | プロファイルの指定(CERATE PROFILE) |
※パラメータは他にもあります。
3-2. 権限の付与
GRANT UNLIMITED TABLESPACE TO username ;
GRANT 権限名 TO ユーザー名
3-2-a. 権限の確認
— システム権限
SELECT * FROM USER_SYS_PRIVS;
— オブジェクト権限
SELECT * FROM USER_TAB_PRIVS;
権限について、ここがわかりやすい。
OracleMaster講座(9i) : 第４章 オブジェクト権限（補足）
http://rina.jpn.ph/~rance/om/oracle/hosoku04.html
3-3. テーブル作成
CREATE TABLE TEST_TABLE_1 (
COL_VAR_1 VARCHAR2(256) NOT NULL,
COL_CHAR_1 CHAR(10) DEFAULT ‘0000000000’,
COL_NUM_1 NUMBER(4, 0),
COL_BLOB_1 BLOB,
CONSTRAINT P_KEY_TEST_TABLE_1 PRIMARY KEY(COL_VAR_1, COL_CHAR_1)
);
3-3-a. テーブル一覧、テーブルのカラム一覧
–テーブル一覧
SELECT * FROM USER_TABLES ORDER BY TABLE_NAME;
–テーブルのカラム一覧
SELECT * FROM USER_TAB_COLUMNS ORDER BY TABLE_NAME, COLUMN_ID;
3-4. コンテキスト作成からDBMS_SESSION.SET_CONTEXまで
— コンテキスト作成
create CONTEXT test_contxt USING test_contxt_pkg;
— パッケージ作成
create or replace PACKAGE test_contxt_pkg IS
PROCEDURE t_proc( value IN VARCHAR2);
END;
— パッケージ本体作成
create or replace PACKAGE BODY test_contxt_pkg IS
PROCEDURE test_proc( value IN VARCHAR2) IS
BEGIN
DBMS_SESSION.SET_CONTEXT(‘test_contxt’, ‘test_type’, value);
END;
END;
— プロシージャ実行
EXECUTE test_contxt_pkg.t_proc(‘AdminUser’);
— コンテキストの値を取得
SELECT SYS_CONTEXT(‘test_contxt’, ‘test_type’) user_type FROM DUAL;
— 出力：”AdminUser”
4. エクスポート・インポート
4-1. expdp
expdp user/password@service_name schemas=xxx directory=EXP_DIR dumpfile=EXPDP_DATA.DMP logfile=expdp.log
オプションの参考（データ・ポンプ・エクスポート）
https://docs.oracle.com/cd/E16338_01/server.112/b56303/dp_export.htm
4-1-a. ディレクトリ作成
CREATE [OR REPLACE] DIRECTORY dir_name AS ‘C:\work\oradir’;
–ディレクトリの確認
SELECT * FROM ALL_DIRECTORIES;
4-2. impdp
impdp user/password@service_name schemas=xxx directory=IMP_DIR table_exists_action=replace dumpfile=EXPDP_DATA.DMP logfile=impdp.log
–table_exists_action
— truncate：truncateしてからインポート
— replace：drop createしてからインポート
※インポート先に同一シーケンスが存在する場合、シーケンスの値は更新されない。
5. 処理時間の確認
5-1. V$SQL
— 処理時間がかかっている上位5件を取得
SELECT * FROM (
SELECT * FROM V$SQL ORDER BY ELAPSED_TIME DESC
)
WHERE ROWNUM <= 5
ORDER BY ELAPSED_TIME DESC;
ELAPSED_TIME：処理経過時間（マイクロ秒：100万分の1秒）
V$SQLのマニュアル
つづく。。
コメント