SQLメモ（Oracle） - 闘うITエンジニアの覚え書き

Oracleメモ - SQLメモ †

【DDLを取得する】

SELECT DBMS_METADATA.GET_DDL('TABLE', 'テーブル名') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW', 'ビュー名') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', '索引名') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'トリガー名') FROM DUAL;
など

【テーブルの列定義を確認する】

SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = 'テーブル名';

【テーブルについている索引を確認する】

SELECT * FROM USER_INDEXES_COLUMNS WHERE TABLE_NAME = 'テーブル名';
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'テーブル名' ORDER BY COLUMN_POSITION;

【文字コードから文字を取得】

SELECT CHR(13) FROM DUAL; -- CR取得
SELECT CHR(10) FROM DUAL; -- LF取得
SELECT CHR(13) || CHR(10) FROM DUAL; -- CRLF取得
/* UTL_FILEで PUTLINE でなく PUT を使用して改行コードを出力する場合は注意が必要！
   UTL_FILEはプラットフォームに依存する改行を出力するための文字としてLFを使用する様子。
   ファイルをRAWモードで開いていない限り、必ず末尾に改行が付いてしまうらしい。。
 */

【表領域のフルパスと自動拡張の設定値の確認】

SELECT TABLESPACE_NAME,
       FILE_NAME,
       AUTOEXTENSIBLE
  FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME = '表領域名';

【表領域の空き容量の確認】

SELECT SUM(BYTES) / 1024 || 'Kbytes' AS 空き領域サイズ
  FROM DBA_FREE_SPACE 
 WHERE TABLESPACE_NAME = '表領域名';

【連続する空き領域の結合】

ALTER TABLESPACE APP COALESCE;

【インデックス再構築】

ALTER INDEX インデックス名 REBUILD ONLINE;

【インデックス再構築方法（別のテーブルスペースに作成）】

ALTER INDEX インデックス名 REBUILD TABLESPACE テーブルスペース名;

【データベース・バッファ・キャッシュを取得する】

SET SERVEROUTPUT ON;
    DECLARE
      d_gets NUMBER;
      c_gets NUMBER;
      p_reads NUMBER;
      result NUMBER;
    BEGIN
      SELECT VALUE INTO d_gets FROM V$SYSSTAT
        WHERE NAME = 'db block gets';
      SELECT VALUE INTO c_gets FROM V$SYSSTAT 
        WHERE NAME = 'consistent gets';
      SELECT VALUE INTO p_reads FROM V$SYSSTAT 
        WHERE NAME = 'physical reads';
      result := 
        ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100;
      DBMS_OUTPUT.PUT_LINE(
        'データベース・バッファキャッシュヒット率->' ||
        result || '%');
    END;
    /

　※一般的に、このヒット率が90％以上を維持できるようなデータベース・バッファ・キャッシュ・サイズを
　　作成すべきとされている。
　※ヒット率が90％に満たない場合は、初期化パラメータファイルのデータベース・バッファ・キャッシュのサイズを変更する。

【バッファ・キャッシュの空き比率を調べる】

SET SERVEROUTPUT ON;
    DECLARE
      cnt_total NUMBER;
      cnt_free NUMBER;
      result NUMBER;
      message VARCHAR2(256);
    BEGIN
      SELECT COUNT(*) INTO cnt_total FROM X$BH;
      SELECT COUNT(*) INTO cnt_free FROM X$BH 
        WHERE STATE = 0;
      result := ROUND((cnt_free / cnt_total), 3) * 100;
      SELECT DECODE(SIGN(result), 1,
          'バッファキャッシュ余裕有り',
          'バッファキャッシュ余裕なし') INTO message
        FROM DUAL;
      DBMS_OUTPUT.PUT_LINE('バッファ空き比率->' || 
          result || '%');
      DBMS_OUTPUT.PUT_LINE(message);
    END;
    /

【共有プールのメモリサイズ情報を取得】

SET SERVEROUTPUT ON;
    DECLARE
      sum_db_object_cache NUMBER;
      sum_s_sqlarea NUMBER;
      sum_u_sqlarea NUMBER;
      now_parameter NUMBER;
      result NUMBER;
    BEGIN
      SELECT SUM(SHARABLE_MEM) INTO sum_db_object_cache 
        FROM V$DB_OBJECT_CACHE;
      SELECT SUM(SHARABLE_MEM) INTO sum_s_sqlarea 
        FROM V$SQLAREA;
      SELECT SUM(250 * USERS_OPENING) INTO sum_u_sqlarea 
        FROM V$SQLAREA;
      result := 
        sum_db_object_cache + sum_s_sqlarea + sum_u_sqlarea;
      SELECT value INTO now_parameter 
        FROM V$PARAMETER WHERE NAME = 'shared_pool_size';
      DBMS_OUTPUT.PUT_LINE(
        'パラメータSHARED_POOL_SIZE->' || now_parameter);
      DBMS_OUTPUT.PUT_LINE(
        '現在最低限必要なSHARED_POOL_SIZE->' || result);
      DBMS_OUTPUT.PUT_LINE(
        '適正なSHARED_POOL_SIZE(×1.3)->' || result * 1.3);
    END;
    /

【ライブラリ・キャッシュ・ヒット率取得】

SELECT SUM(PINS) AS キャッシュヒット合計,
       SUM(RELOADS) AS キャッシュミス合計,
       ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2) || '%' AS ライブラリキャッシュヒット率
    FROM V$LIBRARYCACHE;

【ディクショナリヒット率の算出】

SELECT SUM(GETS) AS キャッシュヒット合計,
       SUM(GETMISSES) AS キャッシュミス合計,
       ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2) || '%' AS ディクショナリヒット率
    FROM V$ROWCACHE;

　※ヒット率は95％以上が望ましいとされています。

【初期化パラメータを参照する】

SELECT NAME, VALUE FROM V$PARAMETER ORDER by NAME;

【DBの文字コードを調べる】

select dump(col_name,1016) from table_name where rownum <= 1;
/* col_name、table_nameは存在するものであれば何でもいい。
   col_nameはもちろん全角文字が入ってる列がよい。と思う。*/

【参照制約設定用のSQLを作るSQL】

SET DEFINE ON
SET LINESIZE 200
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
   USER_NAME   VARCHAR2(20) DEFAULT 'ユーザID';
   CURSOR DISABLE_CSR IS  
          SELECT 'ALTER TABLE ' || A.TABLE_NAME || 
                 ' DISABLE CONSTRAINT "'  ||
                 A.CONSTRAINT_NAME || '";' "SQLTXT"
            FROM ALL_CONSTRAINTS A,
                 ALL_CONSTRAINTS B
           WHERE B.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
             AND A.CONSTRAINT_TYPE   = 'R'
             AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
          UNION
          SELECT 'ALTER TABLE ' || C.TABLE_NAME || 
                 ' DISABLE CONSTRAINT "'  ||
                 C.CONSTRAINT_NAME || '";'
            FROM ALL_CONSTRAINTS C
           WHERE C.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
             AND C.CONSTRAINT_TYPE   = 'R'
        ORDER BY 1 ASC;
   CURSOR ENABLE_CSR IS  
          SELECT 'ALTER TABLE ' || A.TABLE_NAME || 
                 ' ENABLE VALIDATE CONSTRAINT "'  ||
                 A.CONSTRAINT_NAME || '";' "SQLTXT"
            FROM ALL_CONSTRAINTS A,
                 ALL_CONSTRAINTS B
           WHERE B.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
             AND A.CONSTRAINT_TYPE   = 'R'
             AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
          UNION
          SELECT 'ALTER TABLE ' || C.TABLE_NAME || 
                 ' ENABLE VALIDATE CONSTRAINT "'  ||
                 C.CONSTRAINT_NAME || '";'
            FROM ALL_CONSTRAINTS C
           WHERE C.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = USER_NAME)
             AND C.CONSTRAINT_TYPE   = 'R'
        ORDER BY 1 ASC;
BEGIN
   DBMS_OUTPUT.PUT_LINE('-- 参照制約無効化用ＳＱＬ -- '); 
   FOR DISABLE_SQL IN DISABLE_CSR LOOP 
      DBMS_OUTPUT.PUT_LINE(DISABLE_SQL."SQLTXT"); 
   END LOOP; 
   DBMS_OUTPUT.PUT_LINE('-- 参照制約復活用ＳＱＬ -- '); 
   FOR ENABLE_SQL IN ENABLE_CSR LOOP 
      DBMS_OUTPUT.PUT_LINE(ENABLE_SQL."SQLTXT"); 
   END LOOP; 
END;
/

【ユーザ権限の確認】

select 'USER_ROLE_PRIVS' "PRIVILEGES",GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE,OS_GRANTED from USER_ROLE_PRIVS;
select 'USER_SYS_PRIVS' "PRIVILEGES",PRIVILEGE,ADMIN_OPTION from USER_SYS_PRIVS;
select OWNER || ' の ' || TABLE_NAME || ' を ' || PRIVILEGE || ' 可能' from USER_TAB_PRIVS where USER not like 'SYS%' and OWNER like 'SYS%';

【AUTOトレースのON】

SET AUTOTRACE TRACEONLY

【実行計画の表示幅を変更】

COLUMN plan_plus_exp format a60

【実行計画の調査】

-- PLAN_TABLEを一旦削除
delete from plan_table;

-- 対象SQLの実行計画の取得
explain plan
set statement_id = '調査1' for 
select * from ・・・・・・;

-- 表示設定
SET PAGESIZE 50
SET LINESIZE 200
COLUMN "QUERY PLAN" FORMAT A50
COLUMN OBJECT_NEME  FORMAT A30

-- 簡易表示
SELECT LPAD(' ',2*(LEVEL-1))||OPERATION||' '||OPTIONS "QUERY PLAN" ,OBJECT_NAME
  FROM PLAN_TABLE 
 START WITH ID = 0
 CONNECT BY PRIOR ID = PARENT_ID
   AND STATEMENT_ID = '&STATEMENT_ID';

-- ちょっと詳細表示
set markup html preformat on
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

【統計情報の取得】

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME       => 'USERID'
    ,TABNAME      => 'テーブル名'
    ,METHOD_OPT   => 'FOR ALL INDEXED'  -- 索引列のカラム統計を取る
    ,CASCADE      => TRUE               -- 関連する索引の統計も取る
  );
END;
/

【統計情報の削除】

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS('USERID','テーブル名');
END;
/

【ロックセッション抽出】

/* ロックをかけているプログラム、ユーザの表示 */
SELECT SADDR,SID,SERIAL#,USERNAME,PROGRAM
  FROM V$SESSION
 WHERE SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX','TM'));

/* 問題のSQLを表示 */
SELECT A.SQL_TEXT,A.ADDRESS
  FROM V$SQLAREA A,V$SESSION B
 WHERE A.ADDRESS = B.SQL_ADDRESS
   AND B.SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX','TM'));

/* ロックをかけているセッションの情報を表示 */
SELECT a.SID sid,
            a.USERNAME username,
            a.SERIAL# serialno,
            b.TYPE type,
            a.PROGRAM program,
            TO_CHAR(b.CTIME/60,'999990.9') lock_time,
            c.SQL_TEXT SQL
     FROM V$SESSION a,
          V$LOCK b,
          V$SQLAREA c
     WHERE a.SID = b.SID
       AND b.TYPE IN ('TX','TM')
       AND a.SQL_ADDRESS = c. ADDRESS;

/*  ロックのため待ちが発生しているセッション情報 */
SELECT a.USERNAME username,
            a.PROGRAM program,
            a.SERIAL# serialno,
            a.SID sid,
            b.TYPE type,
            TO_CHAR(b.CTIME/60,'999990.9') lock_time
     FROM V$SESSION a,
          V$LOCK b
     WHERE a.SID = b.SID
       AND b.TYPE = 'TM'
       AND b.SID = (SELECT SID FROM V$LOCK c WHERE c.TYPE = 'TX' AND c.REQUEST > 0);

/* 問題のセッションをKILL ※SYSDBA権限が必要 */
alter system kill session 'sid,serial'

oracleセッションID取得 †

select userenv('SESSIONID') from dual

SQLID取得(v$session) †

SQL文取得(v$sqltext) †

SQLコスト取得(v$sqlarea) †