Oracle

AWR

[パフォーマンス]-[SQL]-[SQLチューニング・アドバイザ]
　「履歴SQL(AWR)」から負荷の高いSQLの情報を照会可能
SQLの検索は正直使いづらいので、SQL実行できる場合はSQL文を用意した方がいい

-- AWRレポート取得
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('SQL ID', NULL, NULL, 'ALL'));

-- スナップショット取得
SELECT DISTINCT
    SQL.SNAP_ID
,   SNAP.BEGIN_INTERVAL_TIME
,   SQL.SQL_ID
,   SQL.PLAN_HASH_VALUE
FROM
    SYS.WRH$_SQLSTAT  SQL
,   SYS.WRM$_SNAPSHOT SNAP
WHERE
    SQL.SNAP_ID = SNAP.SNAP_ID
AND SQL.SQL_ID = 'SQL ID'
ORDER BY
    SQL.SNAP_ID
/

統計情報

--統計情報の収集
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS;
END;
/

BEGIN
--テーブル単位の収集
DBMS_STATS.GATHER_TABLE_STATS
(
	OWNNAME      => 'TEST'
,	TABNAME      => 'TBLNAME'
,	METHOD_OPT   => 'FOR ALL INDEXED'
,	CASCADE      => TRUE
);
END;
/

BEGIN
--スキーマ単位の収集
DBMS_STATS.GATHER_SCHEMA_STATS
(
    OWNNAME => 'TEST'
,   OPTIONS => 'GATHER'
);
END;
/

-- 統計情報最終取得日取得
SELECT TABLE_NAME, LAST_ANALYZED FROM USER_TABLES  WHERE TABLE_NAME = 'テーブル名'
UNION ALL
SELECT INDEX_NAME, LAST_ANALYZED FROM USER_INDEXES WHERE INDEX_NAME = 'インデックス名'
/

-- 統計情報未取得のテーブル、インデックス
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES  WHERE OWNER in ( 'TEST' ) AND LAST_ANALYZED IS NULL
UNION ALL
SELECT INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER in ( 'TEST' ) AND LAST_ANALYZED IS NULL
/

-- 統計ロック情報
SELECT * FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED IS NOT NULL ORDER BY OWNER, TABLE_NAME;

-- ジョブスケジューラー
SELECT * FROM DBA_AUTOTASK_CLIENT;
SELECT * FROM DBA_SCHEDULER_WINDOWS;

オブジェクト

オブジェクト一覧

spo D:\Object一覧.spo

conn aaa/aaa@aaa

SELECT
    A.OWNER                                             "ユーザー"
,   A.OBJECT_TYPE                                       "オブジェクトタイプ"
,   A.OBJECT_NAME                                       "オブジェクト名"
,   SUBSTRB( RTRIM( B.COMMENTS ),1, 80 )                "コメント"
,   C.BYTES                                             "サイズ"
,   C.TABLESPACE_NAME                                   "テーブルスペース"
,   A.STATUS                                            "状態"
,   TO_CHAR( A.CREATED,'YYYY/MM/DD HH24:MI:SS' )        "作成日付"
,   TO_CHAR( A.LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS' )  "更新日付"
FROM
    ALL_OBJECTS      A
,   DBA_TAB_COMMENTS B
,   DBA_SEGMENTS     C
WHERE
    A.OWNER                 IN ( '対象スキーマ－' )
AND A.OBJECT_TYPE           IN ( 'VIEW','PROCEDURE','FUNCTION','PACKAGE BODY' )
--AND   C.TABLESPACE_NAME   NOT IN( '' )
AND A.OWNER                 = B.OWNER(+)
AND A.OBJECT_TYPE           = B.TABLE_TYPE(+)
AND A.OBJECT_NAME           = B.TABLE_NAME(+)
AND A.OWNER                 = C.OWNER(+)
AND A.OBJECT_TYPE           = C.SEGMENT_TYPE(+)
AND A.OBJECT_NAME           = C.SEGMENT_NAME(+)
ORDER BY
    A.OWNER
,   C.TABLESPACE_NAME
,   A.OBJECT_TYPE
,   A.OBJECT_NAME
/

spo off

テーブルインデックス一覧

-- テーブル・インデックス関連付け
SELECT
    A.OWNER                     "ユーザー"
,   E.OBJECT_NAME               "テーブル名"
,   A.OBJECT_NAME               "インデックス名"
,   MAX( C.TABLESPACE_NAME )    "TBLテーブルスペース"
,   MAX( F.TABLESPACE_NAME )    "IDXテーブルスペース"
,   MAX( C.BYTES )              "サイズ"
FROM
    ALL_OBJECTS      A
,   DBA_SEGMENTS     C
,   DBA_IND_COLUMNS  D
,   ALL_OBJECTS      E
,   DBA_SEGMENTS     F
WHERE
    A.OWNER         IN ( '対象スキーマ－' )
AND A.OBJECT_TYPE   = 'INDEX'
AND A.OWNER         = C.OWNER(+)
AND A.OBJECT_TYPE   = C.SEGMENT_TYPE(+)
AND A.OBJECT_NAME   = C.SEGMENT_NAME(+)
AND A.OWNER         = D.INDEX_OWNER(+)
AND A.OBJECT_NAME   = D.INDEX_NAME(+)
AND D.TABLE_OWNER   = E.OWNER(+)
AND D.TABLE_NAME    = E.OBJECT_NAME(+)
AND E.OBJECT_TYPE   = 'TABLE'
AND E.OWNER         = F.OWNER(+)
AND E.OBJECT_TYPE   = F.SEGMENT_TYPE(+)
AND E.OBJECT_NAME   = F.SEGMENT_NAME(+)
GROUP BY
    A.OWNER
,   E.OBJECT_NAME
,   A.OBJECT_NAME
ORDER BY
    A.OWNER
,   E.OBJECT_NAME
,   A.OBJECT_NAME
/

オブジェクト権限一覧

-- ディレクトリオブジェクト権限一覧
SELECT
    * 
FROM 
    DBA_TAB_PRIVS
WHERE
    GRANTEE     IN ('') 
AND OWNER   NOT IN ('')
ORDER BY
    OWNER
,   TABLE_NAME
,   PRIVILEGE
/

リコンパイル

SELECT
    CASE OBJECT_TYPE
        WHEN 'PACKAGE BODY' THEN 'ALTER PACKAGE '               || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;'
                            ELSE 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
    END
FROM
    ALL_OBJECTS
WHERE
    STATUS = 'INVALID'
ORDER BY
    OBJECT_TYPE
,   OWNER
,   OBJECT_NAME
/

オブジェクト一括削除

SET SERVEROUTPUT ON
BEGIN
    FOR REC_OBJ IN
    (
        SELECT
            A.OWNER
        ,   A.OBJECT_TYPE
        ,   A.OBJECT_NAME
        ,   B.MASTER
        FROM
            ALL_OBJECTS    A
        ,   ALL_MVIEW_LOGS B
        WHERE
            A.OBJECT_NAME = B.OBJECT_TABLE(+)
        AND A.OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'SEQUENCE', 'SYNONYM', 'MATERIALIZED VIEW' )
        AND A.OWNER IN ('')
        AND (   NOT EXISTS( SELECT 'X' FROM ALL_MVIEWS C WHERE A.OBJECT_NAME = C.MVIEW_NAME AND A.OBJECT_TYPE = 'TABLE' )
            OR  A.OBJECT_TYPE = 'MATERIALIZED VIEW' )
        ORDER BY
            A.OWNER
        ,   A.OBJECT_TYPE
        ,   NVL( B.MASTER, A.OBJECT_NAME )
        ,   B.MASTER NULLS LAST
    )
    LOOP
        IF REC_OBJ.OBJECT_TYPE = 'TABLE' THEN
            IF REC_OBJ.MASTER IS NULL THEN
                 -- 通常テーブル
                 DBMS_OUTPUT.PUT_LINE( 'DROP ' || REC_OBJ.OBJECT_TYPE || ' ' || REC_OBJ.OWNER || '.' || REC_OBJ.OBJECT_NAME || ' CASCADE CONSTRAINTS PURGE;' );
            ELSE
                 -- マテビューログ
                 DBMS_OUTPUT.PUT_LINE( 'DROP MATERIALIZED VIEW LOG ON ' || REC_OBJ.OWNER || '.' || REC_OBJ.MASTER || ';' );
            END IF
        ELSE
            -- 通常テーブル
            DBMS_OUTPUT.PUT_LINE( 'DROP ' || REC_OBJ.OBJECT_TYPE || ' ' || REC_OBJ.OWNER || '.' || REC_OBJ.OBJECT_NAME || ';' ); 
        END IF;
    END LOOP;

    -- リフレッシュグループ
    DBMS_OUTPUT.PUT_LINE( 'BEGIN' ); 

    FOR REC_OBJ IN
    (
        SELECT
            ROWNER
        ,   RNAME
        FROM
            ALL_REFRESH
        WHERE
            ROWNER IN ('')
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE( 'DBMS_REFRESH.DESTROY( NAME => ''' || REC_OBJ.ROWNER || '.' || REC_OBJ.RNAME || ''');' );
    END LOOP;

    DBMS_OUTPUT.PUT_LINE( 'END;' ); 
    DBMS_OUTPUT.PUT_LINE( '/' ); 
END;
/

表領域

表領域チェック

SELECT
        TABLESPACE_NAME
,       TO_CHAR( "SIZE(MB)"     , '9,999,999' ) "SIZE(MB)"
,       TO_CHAR( "USED(MB)"     , '9,999,999' ) "USED(MB)"
,       TO_CHAR( "FREE(MB)"     , '9,999,999' ) "FREE(MB)"
,       TO_CHAR( "MAX_SIZE(MB)" , '9,999,999' ) "MAX_SIZE(MB)"
,       TO_CHAR( "USE_RATE(%)"  , '990.00'    ) "USE_RATE(%)"
,       TO_CHAR( "MAX_RATE(%)"  , '990.00'    ) "MAX_RATE(%)"
,       CASE
                WHEN "MAX_RATE(%)" > 98 THEN '■■■警告'
                WHEN "MAX_RATE(%)" > 94 THEN '■■警告'
                WHEN "MAX_RATE(%)" > 90 THEN '□注意'
        END "CHK"
FROM
        (
                SELECT
                        A.TABLESPACE_NAME
                ,       A.TOTAL_BYTES                                    / 1024 / 1024                            "SIZE(MB)"
                ,       ( A.TOTAL_BYTES - NVL( B.FREE_TOTAL_BYTES, 0 ) ) / 1024 / 1024                            "USED(MB)"
                ,       NVL( B.FREE_TOTAL_BYTES, 0 )                     / 1024 / 1024                            "FREE(MB)"
                ,       A.TOTAL_MAXBYTES                                 / 1024 / 1024                            "MAX_SIZE(MB)"
                ,       DECODE( A.TOTAL_BYTES   , 0, 0, ROUND( ( A.TOTAL_BYTES - NVL( B.FREE_TOTAL_BYTES, 0 ) ) / A.TOTAL_BYTES    * 100, 2 ) )     "USE_RATE(%)"
                ,       DECODE( A.TOTAL_MAXBYTES, 0, 0, ROUND( ( A.TOTAL_BYTES - NVL( B.FREE_TOTAL_BYTES, 0 ) ) / A.TOTAL_MAXBYTES * 100, 2 ) )     "MAX_RATE(%)"
                FROM
                        (
                                SELECT
                                        TABLESPACE_NAME
                                ,       SUM( NVL( BYTES   , 0 ) ) "TOTAL_BYTES"
                                ,       SUM( NVL( MAXBYTES, 0 ) ) "TOTAL_MAXBYTES"
                                FROM
                                        DBA_DATA_FILES
                                GROUP BY
                                        TABLESPACE_NAME
                        ) A
                ,       (
                                SELECT
                                        TABLESPACE_NAME
                                ,       SUM( NVL( BYTES, 0 ) ) "FREE_TOTAL_BYTES"
                                FROM
                                        DBA_FREE_SPACE
                                GROUP BY
                                        TABLESPACE_NAME
                        ) B
                WHERE
                        A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
        ) A
ORDER BY
        "MAX_RATE(%)" DESC
/

-- 表領域のブロックチェック
SELECT
    E.SEGMENT_NAME
,   E.TABLESPACE_NAME
,   E.EXTENT_ID
,   E.BLOCK_ID
,   E.BLOCKS
FROM
    DBA_EXTENTS E
,   DBA_DATA_FILES  F
WHERE
    E.FILE_ID   = F.FILE_ID
--AND   E.SEGMENT_TYPE  = 'TABLE'
--AND   E.OWNER     = ''
AND F.FILE_NAME = 'F:\APP\ORADATA\DBNAME\TS_TBL.DBF'
ORDER BY
    E.BLOCK_ID DESC
/

テーブルスペース圧縮 ONLINE状態で実行可能（効果：低）

-- 行移動を有効にする
ALTER TABLE TEST.TBL_TEST ENABLE ROW MOVEMENT;
-- テーブルを圧縮する
ALTER TABLE TEST.TBL_TEST SHRINK SPACE CASCADE;
-- 行移動を無効にする
ALTER TABLE TEST.TBL_TEST DISABLE ROW MOVEMENT;
-- テーブルスペース移動、インデックスがあるテーブルの場合は注意が必要（特にプライマリーキーは注意）
ALTER TABLE TBL_TEST MOVE TABLESPACE TS_TBL2;

-- インデックス再構築
ALTER INDEX IDX1_TBL_TEST REBUILD;
ALTER INDEX IDX2_TBL_TEST REBUILD;
-- 一時表領域の縮小
ALTER TABLESPACE TEMP_TEST SHRINK SPACE KEEP 4G;
-- 表領域の書き込み可/不可の変更
-- 読取り専用表領域のデータにアクセスする際のパフォーマンスを向上させるため、表領域を読取り専用にする直前に
-- 表領域内の表のブロックすべてにアクセスする問合せを発行することをお薦めします。
-- 各表に対してSELECT COUNT (*)などの単純な問合せを実行しておくと
-- それ以降、表領域のデータ・ブロックに最も効率的にアクセスできるようになります。
-- これによって、最後にブロックを変更したトランザクションの状態をデータベースが確認する必要がなくなるからです。
SELECT COUNT(*) FROM <テーブル名>;
ALTER TABLESPACE <表領域名> READ ONLY;
ALTER TABLESPACE <表領域名> READ WRITE;

一時表領域の再作成

-- 【手順1】 空きのあるディスク(フォルダ)に一時表領域を作成する
ALTER TABLESPACE TEMP_EBOM ADD TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST_AFTER_DELETE.DBF' SIZE 128M AUTOEXTEND OFF

-- 【手順2】 ディスク圧迫している一時表領域のデータファイルを削除するためオフラインにする
ALTER DATABASE TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST.DBF' OFFLINE
--ALTER DATABASE TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST.DBF' ONLINE

-- 【手順3】 手順2でオフラインにしたデータファイルを削除する
ALTER DATABASE TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST.DBF' DROP INCLUDING DATAFILES

-- 削除できない場合はアプリが使用している可能性があるので下記SQLでプロセスを確認し
-- 対象のプロセスを終了させてから再度削除を試みる。
SELECT
    S.USERNAME
,   S.OSUSER
,   S.MACHINE
,   S.TERMINAL
,   S.PROGRAM
FROM
    SYS.V_$SESSION S
,   SYS.V_$PROCESS P
WHERE
    S.PADDR = P.ADDR
/

-- 【手順4】 削除した一時表領域のデータファイルを再作成する
ALTER TABLESPACE TEMP_TEST ADD TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST.DBF' SIZE 2048M AUTOEXTEND OFF

-- 【手順5】 手順1で作成したデータファイルを削除するためオフラインにする
ALTER DATABASE TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST_AFTER_DELETE.DBF' OFFLINE

-- 【手順6】 手順5でオフラインにしたデータファイルを削除する
ALTER DATABASE TEMPFILE 'E:\APP\ORADATA\DBNAME\TEMP_TEST_AFTER_DELETE.DBF' DROP INCLUDING DATAFILES

データファイルの移動

 -- 表領域をオフラインにする
ALTER TABLESPACE TS_TBL OFFLINE;

 -- データファイルを移動先へコピーする
OSのコマンドなどでデータファイルをコピーします。

 -- データファイルを変更する
ALTER TABLESPACE TS_TBL RENAME DATAFILE 'F:\APP\ORADATA\DBNAME\TS_TBL.ORA' TO 'E:\APP\ORADATA\DBNAME\TS_TBL.ORA';

 -- 表領域をオンラインにする
ALTER TABLESPACE TS_TBL ONLINE;

圧縮表の作成

ディスク容量が圧迫してきたら検討

-- テーブルスペースをデフォルトで圧縮表とする
-- （うろ覚えだが、テーブルスペースに対しては設定出来なかったかも）
CREATE TABLESPACE <表領域名>
DATAFILE '........'
DEFAULT COMPRESS FOR OLTP
/

-- 新規テーブル作成時に圧縮表のオプションを指定
CREATE TABLE <テーブル名> (column1,column2,..)
COMPRESS FOR OLTP
/

-- 既存テーブルを圧縮表に変更する場合
--   新規レコードだけではなく、既存レコードも圧縮する場合
--   索引のRebuildが必要
ALTER TABLE TBL_TEST MOVE TABLESPACE TS_TBL_TEMP;
ALTER TABLE TBL_TEST MOVE TABLESPACE TS_TBL COMPRESS FOR BASIC;
ALTER INDEX IDX1_TBL_TEST REBUILD;
ALTER INDEX IDX2_TBL_TEST REBUILD;

SELECT
    TABLE_NAME
,   COMPRESSION
,   COMPRESS_FOR
FROM
    USER_TABLES
WHERE
    COMPRESSION = 'ENABLED'
ORDER BY
    TABLE_NAME
/

SELECT
    TABLE_OWNER
,   TABLE_NAME
,   PARTITION_NAME
,   COMPRESSION
,   COMPRESS_FOR
FROM
    DBA_TAB_PARTITIONS
WHERE
    COMPRESSION = 'ENABLED'
ORDER BY
    TABLE_OWNER
,   TABLE_NAME
,   PARTITION_NAME
/

マテビュー

マテビューログテーブル作成

マテビューログを作成すると下記３オブジェクトが生成される。（設定によっては作成されないオブジェクトもあり）

オブジェクト名	タイプ	説明
MLOG$_[テーブル名]	TABLE	差分格納テーブル
I_MLOG$_[テーブル名]	INDEX	MLOG$_[テーブル名] テーブルに対するインデックス
RUPD$_[テーブル名]	GLOBAL TEMPORARY TABLE	差分格納一時テーブル

-- マテビューログテーブル削除
DROP MATERIALIZED VIEW LOG ON [テーブル]

-- マテビューログテーブル作成
CREATE MATERIALIZED VIEW LOG ON [テーブル]
TABLESPACE [表領域]
NOLOGGING
WITH ROWID SEQUENCE
(
  [項目]
, [項目]
)
INCLUDING NEW VALUES
PURGE REPEAT INTERVAL '7' DAY
/

-- インデックスの表領域を移動
ALTER INDEX I_MLOG$_[テーブル名] REBUILD TABLESPACE [表領域]
/

-- マテビューログテーブル一覧
SELECT * FROM SYS.ALL_MVIEW_LOGS ORDER BY LOG_OWNER, MASTER
/

集計関数を使用していない場合 WITH PRIMARY KEY ROWID
集計関数を使用している場合（UPDATEでリフレッシュが必要な場合も対象） WITH ROWID, SEQUENCE (項目) INCLUDING NEW VALUES マテビュー内で参照している項目をすべて追加。但し、PRIMARY KEYの項目は追加不要（追加するとエラーになる）

マテビュー

内部結合、外部結合の書き方は、ORACLE独自の書き方で記述しないとマテビュー化できない

-- マテビュー一覧
SELECT * FROM SYS.ALL_MVIEWS ORDER BY OWNER, MVIEW_NAME
/

-- リフレッシュ設定変更：リフレッシュグループを使用する場合
ALTER MATERIALIZED VIEW [マテビュー]
REFRESH FAST ON DEMAND
/

-- リフレッシュ設定変更：リフレッシュグループを使用しない場合
ALTER MATERIALIZED VIEW [マテビュー]
REFRESH FAST
START WITH ROUND(SYSDATE) + 11/24
NEXT SYSDATE + 1 / 24 / 60 * 20
/

-- マテビュー設定チェック
DECLARE
        msg_array SYS.ExplainMVArrayType;
BEGIN
        DBMS_MVIEW.EXPLAIN_MVIEW( 'マテビュー名', msg_array );
        DBMS_OUTPUT.ENABLE;

        FOR IDX IN 1..msg_array.COUNT LOOP

                DBMS_OUTPUT.PUT_LINE
                (
                        msg_array( IDX ).capability_name  || ' ' ||
                        msg_array( IDX ).possible         || ' ' ||
                        msg_array( IDX ).related_num      || ' ' ||
                        msg_array( IDX ).related_text     || ' ' ||
                        msg_array( IDX ).msgno            || ' ' ||
                        msg_array( IDX ).msgtxt
                );
        END LOOP;
END;
/

下記４項目の設定を確認

REFRESH_FAST
REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ANY_DML

-- リフレッシュグループ一覧
SELECT * FROM SYS.ALL_REFRESH ORDER BY ROWNER, RNAME
/
-- リフレッシュグループ内のオブジェクト一覧
SELECT * FROM SYS.ALL_REFRESH_CHILDREN ORDER BY OWNER, NAME
/
-- インスタンス内で現在実行中のジョブ
SELECT * FROM DBA_JOBS_RUNNING
/

-- リフレッシュグループ削除
BEGIN
DBMS_REFRESH.DESTROY( NAME => 'リフレッシュグループ' );
END;
/

-- リフレッシュグループ作成
BEGIN
DBMS_REFRESH.MAKE
( 
    NAME      => 'リフレッシュグループ' 
,   LIST      => 'マテビューの名前をカンマ区切りで指定'
,   NEXT_DATE => TO_DATE( '202501010101' 'YYYYMMDDHH24MI' )
,   INTERVAL  => 'SYSDATE + 1 / 24 / 60 * 20'
);
END;
/

-- リフレッシュ間隔の設定変更
BEGIN
DBMS_REFRESH.CHANGE
( 
    NAME      => 'リフレッシュグループ' 
,   NEXT_DATE => TO_DATE( '202501010101' 'YYYYMMDDHH24MI' )
,   INTERVAL  => 'SYSDATE + 1 / 24 / 60 * 20'
);
END;
/


-- リフレッシュグループ手動リフレッシュ
BEGIN
DBMS_REFRESH.REFRESH( NAME => 'リフレッシュグループ' );
END;
/

-- マテビュー手動リフレッシュ (f:高速、c:完全)
BEGIN
DBMS_MVIEW.REFRESH( LIST => 'マテビュー', METHOD => 'f' );
END;
/

パーティション

非パーティション表をパーティション表へ変換

パーティション化のキー項目は、NOT NULL 制約が必要。
データがある状態でパーティション化を実施すると時間がかかるので、データが空の状態でパーティション化してからデータをインポートした方がよい
ローカルインデックス、グローバルインデックスの違いは理解しておく

LISTパーティション表

SET SERVEROUTPUT ON
DECLARE
    WK_SQL_TEXT1    VARCHAR2(30000);
    WK_SQL_TEXT2    VARCHAR2(30000);
    C_KEY           VARCHAR2(30)    := 'パーティション化キー項目';
    C_CRLF          VARCHAR2(2)     := CHR(10);
--  C_CRLF          VARCHAR2(2)     := CHR(13) || CHR(10);
BEGIN
    FOR REC_TBL IN
    (
        SELECT
            A.OWNER
        ,   A.TABLE_NAME        "TBL_NM"
        ,   A.TABLESPACE_NAME   "TBL_SPC_NM"
        FROM
            SYS.ALL_TABLES      A
        ,   ( SELECT OWNER, TABLE_NAME FROM SYS.ALL_TAB_COLUMNS WHERE COLUMN_NAME = C_KEY GROUP BY OWNER, TABLE_NAME ) B
        WHERE
            A.OWNER                     IN ('対象スキーマ－')
        AND A.TABLE_NAME                IN ('対象テーブル')
        AND A.PARTITIONED               = 'NO'
        AND A.OWNER                     = B.OWNER(+)
        AND A.TABLE_NAME                = B.TABLE_NAME(+)
        AND NVL2( B.OWNER, '〇', '■' ) = '〇'      -- パーティション化キーを含むテーブルを対象とする
        ORDER BY
            A.OWNER
        ,   A.TABLE_NAME
    )
    LOOP
        WK_SQL_TEXT2 := '';
        
        FOR REC_IDX IN
        (
            SELECT
                A.INDEX_NAME            "IDX_NM"
            ,   A.TABLESPACE_NAME       "IDX_SPC_NM"
            ,   CASE WHEN B.INDEX_OWNER IS NOT NULL THEN 'LOCAL' THEN 'GLOBAL' END "IDX_TYPE"
            FROM
                SYS.ALL_INDEXES     A
            ,   ( SELECT INDEX_OWNER, INDEX_NAME FROM SYS.ALL_IND_COLUMNS WHERE COLUMN_NAME = C_KEY GROUP BY INDEX_OWNER, INDEX_NAME ) B
            WHERE
                A.OWNER         = REC_TBL.OWNER
            AND A.TABLE_NAME    = REC_TBL.TBL_NM
            AND A.OWNER         = B.INDEX_OWNER(+)
            AND A.INDEX_NAME    = B.INDEX_NAME(+)
            ORDER BY
                A.OWNER
            ,   A.INDEX_NAME
        )
        LOOP
        
            IF TRIM( WK_SQL_TEXT2 ) IS NULL THEN
                WK_SQL_TEXT2 := 'UPDATE INDEXES (' || C_CRLF ;
            END IF;
        
            -- LOCALインデックス
            IF REC_IDX.IDX_TYPE = 'LOCAL' THEN
            
                WK_SQL_TEXT2 := WK_SQL_TEXT2 || '   ' || REC_IDX.IDX_NM || ' ' || REC_IDX.IDX_TYPE || C_CRLF ||
                                '(' || C_CRLF ||
                                '   PARTITION ' || REC_TBL.TBL_NM || '_A       TABLESPACE ' || REC_IDX.IDX_SPC_NM || C_CRLF ||
                                ',  PARTITION ' || REC_TBL.TBL_NM || '_B       TABLESPACE ' || REC_IDX.IDX_SPC_NM || C_CRLF ||
                                ',  PARTITION ' || REC_TBL.TBL_NM || '_DEFAULT TABLESPACE ' || REC_IDX.IDX_SPC_NM || C_CRLF ||
                                '),' || C_CRLF ;
            
            -- GLOBALインデックス
            ELSE
                WK_SQL_TEXT2 := WK_SQL_TEXT2 || '   ' || REC_IDX.IDX_NM || ' ' || REC_IDX.IDX_TYPE || ' TABLESPACE ' || REC_IDX.IDX_SPC_NM || ',' || C_CRLF ;
            END IF;
        
        END LOOP;
        
        -- 末尾のカンマ＋改行を')'に置換
        IF LENGTHB( WK_SQL_TEXT2 ) > 0 THEN
            WK_SQL_TEXT2 := SUBSTRB( WK_SQL_TEXT2, 1, LENGTHB( WK_SQL_TEXT2 ) - LENGTHB( ',' || C_CRLF ) ) || C_CRLF || ')' ;
        END IF;
        
        -- パーティション表への変更ALTER文作成
        WK_SQL_TEXT1 := 'ALTER TABLE ' || REC_TBL.TBL_NM || ' MODIFY' || C_CRLF ||
                        'PARTITION BY LIST( ' || C_KEY || ' )' || C_CRLF ||
                        '(' || C_CRLF ||
                        '   PARTITION ' || REC_TBL.TBL_NM || '_A       VALUES (''A'')   TABLESPACE ' || REC_TBL.TBL_SPC_NM || C_CRLF ||
                        ',  PARTITION ' || REC_TBL.TBL_NM || '_B       VALUES (''B'')   TABLESPACE ' || REC_TBL.TBL_SPC_NM || C_CRLF ||
                        ',  PARTITION ' || REC_TBL.TBL_NM || '_DEFAULT VALUES (DEFAULT) TABLESPACE ' || REC_TBL.TBL_SPC_NM || C_CRLF ||
                        ')'             || C_CRLF ||
                        WK_SQL_TEXT2    || C_CRLF ||
                        'ONLINE'        || C_CRLF ||
                        '/'             || C_CRLF ;
        
        -- SQL分出力
        DBMS_OUTPUT.PUT_LINE( WK_SQL_TEXT1 || C_CRLF || C_CRLF );
        
    END LOOP;

END;
/

パーティション化確認

-- DBAビューは、データベース内のすべてのパーティション表に関するパーティション化情報を表示します。
SELECT * FROM SYS.DBA_PART_TABLES WHERE OWNER = '' ORDER BY OWNER, TABLE_NAME;

-- パーティション・レベルのパーティション化情報、パーティションの記憶域パラメータ
-- およびDBMS_STATパッケージまたはANALYZE文により生成されたパーティション統計
SELECT * FROM SYS.DBA_TAB_PARTITIONS WHERE TABLE_OWNER = '' ORDER BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME;

-- サブパーティション・レベルのパーティション化情報、サブパーティションの記憶域パラメータ
-- およびDBMS_STATパッケージまたはANALYZE文により生成されたパーティション統計
SELECT * FROM SYS.DBA_TAB_SUBPARTITIONS WHERE TABLE_OWNER = '' ORDER BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME;

-- パーティション表のパーティション化キー列
SELECT * FROM SYS.DBA_PART_KEY_COLUMNS WHERE OWNER = '' ORDER BY OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION;

-- コンポジット・パーティション表（およびコンポジット・パーティション表のローカル索引）のサブパーティション化キー列
SELECT * FROM SYS.DBA_SUBPART_KEY_COLUMNS WHERE OWNER = '' ORDER BY OWNER, NAME, OBJECT_TYPE, COLUMN_NAME;

-- 表のパーティションに関する列統計およびヒストグラム情報
SELECT * FROM SYS.DBA_PART_COL_STATISTICS WHERE OWNER = '' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME, COLUMN_NAME;

-- 表のサブパーティションに関する列統計およびヒストグラム情報
SELECT * FROM SYS.DBA_SUBPART_COL_STATISTICS WHERE OWNER = '' ORDER BY OWNER, TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME;

-- 表パーティションのヒストグラムに関するヒストグラム・データ（各ヒストグラムのエンドポイント）
SELECT * FROM SYS.DBA_PART_HISTOGRAMS WHERE OWNER = '' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME, COLUMN_NAME;

-- 表サブパーティションのヒストグラムに関するヒストグラム・データ（各ヒストグラムのエンドポイント）
SELECT * FROM SYS.DBA_SUBPART_HISTOGRAMS WHERE OWNER = '' ORDER BY OWNER, TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME;

-- パーティション索引のパーティション化情報
SELECT * FROM SYS.DBA_PART_INDEXES WHERE OWNER = '' ORDER BY OWNER, TABLE_NAME, INDEX_NAME;

-- 索引パーティションのパーティション・レベルのパーティション化情報、パーティションの記憶域パラメータ
-- DBMS_STATSパッケージまたはANALYZE文により収集された統計情報
SELECT * FROM SYS.DBA_IND_PARTITIONS WHERE INDEX_OWNER = '' ORDER BY INDEX_OWNER, INDEX_NAME, PARTITION_NAME;

-- 索引サブパーティションのパーティション・レベルのパーティション化情報、パーティションの記憶域パラメータ
-- DBMS_STATSパッケージまたはANALYZE文により収集された統計情報
SELECT * FROM SYS.DBA_IND_SUBPARTITIONS WHERE INDEX_OWNER = '' ORDER BY INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME;

-- 既存のサブパーティション・テンプレートの情報
SELECT * FROM SYS.DBA_SUBPARTITION_TEMPLATES WHERE USER_NAME = '' ORDER BY USER_NAME, TABLE_NAME, SUBPARTITION_NAME;

ディレクトリオブジェクト

-- ディレクトリオブジェクト一覧
SELECT * FROM DBA_DIRECTORIES ORDER BY OWNER, DIRECTORY_NAME;
-- ディレクトリオブジェクト権限一覧
SELECT * FROM DBA_TAB_PRIVS WHERE TYPE = 'DIRECTORY' ORDER BY OWNER, TABLE_NAME, PRIVILEGE;

トリガー

-- トリガー一覧
SELECT OWNER, TRIGGER_NAME, STATUS FROM ALL_TRIGGERS WHERE OWNER NOT IN ('') ORDER BY OWNER, TRIGGER_NAME;

-- トリガー無効化スクリプト生成
SELECT 'ALTER TRIGGER ' || OWNER || '.' || TRIGGER_NAME || ' DISABLE;' FROM ALL_TRIGGERS
WHERE OWNER IN ('') AND STATUS = 'ENABLED'
ORDER BY TRIGGER_NAME
/

-- トリガー有効化スクリプト生成
SELECT 'ALTER TRIGGER ' || OWNER || '.' || TRIGGER_NAME || ' ENABLE;' FROM ALL_TRIGGERS
WHERE OWNER IN ('') AND STATUS = 'ENABLED'
ORDER BY TRIGGER_NAME
/

シーケンス

-- シーケンスー一覧
SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER NOT IN ('') ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME;

DB-LINK

-- DB-LINK一覧
SELECT * FROM DBA_DB_LINKS ORDER BY OWNER, DB_LINK;

sqlplus

glogin設定

--
SET TIME ON

-- SQL*Plus の 標準出力の表示時に行末の空白を出力しない
SET TRIMOUT ON

-- SQL*Plus の SPOOL 命令によるファイル書き出し時に行末の空白を出力しない
SET TRIMSPOOL ON

-- 問い合わせの結果レコード件数メッセージ、DDL の実行時の応答メッセージや PL/SQL の実行時の応答メッセージを表示する
SET FEEDBACK ON

-- 列と列の間の区切り文字を設定する
SET COLSEP '|'

-- 検索結果のヘッダを表示する
SET HEADING ON

--
SET HEADSEP '|'
SET UNDERLINE '-'

-- １ページの行数を設定する
SET PAGESIZE 50000

-- １行の長さを設定する(バイト数)
SET LINESIZE 32767

-- 数値の表示桁数を設定する
--SET NUMWIDTH 100

-- スクリプト実行によるコマンドの結果出力を表示する
SET TERMOUT ON

-- DBMS_OUTPUT による PL/SQL の標準出力を表示する
SET SERVEROUTPUT ON

-- 置換変数に設定する前後の状態を表示する
SET VERIFY ON

-- NULL を別の文字列に変換する
--SET NULL '代替文字列'

-- CSVフォーマットで出力する(12c以降)
--SET MARKUP CSV ON QUOTE ON

-- 行のラップ表示（折り返し） OFFにすると切り捨てられる模様
--SET WRAP OFF

-- ed時に開くエディターの実行パス
define _editor="D:\****\*****.exe"

-- スクリプトを実行して SELECTの結果だけを表示したり SPOOL させたい場合に使用する。
SET ECHO ON

-- 日付フォーマットの変更
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Oracle

AWR

統計情報

オブジェクト

オブジェクト一覧

テーブルインデックス一覧

オブジェクト権限一覧

リコンパイル

オブジェクト一括削除

表領域

表領域チェック

テーブルスペース圧縮 ONLINE状態で実行可能（効果：低）

一時表領域の再作成

データファイルの移動

圧縮表の作成

マテビュー

マテビューログテーブル作成

マテビュー

パーティション

非パーティション表をパーティション表へ変換

パーティション化確認

ディレクトリオブジェクト

トリガー

シーケンス

DB-LINK

sqlplus

glogin設定

Home

環境設定

ページ別機能説明

メモ

Clone this wiki locally

Oracle

AWR

統計情報

オブジェクト

オブジェクト一覧

テーブルインデックス一覧

オブジェクト権限一覧

リコンパイル

オブジェクト一括削除

表領域

表領域チェック

テーブルスペース圧縮 ONLINE状態で実行可能（効果：低）

一時表領域の再作成

データファイルの移動

圧縮表の作成

マテビュー

マテビューログテーブル作成

マテビュー

パーティション

非パーティション表をパーティション表へ変換

パーティション化確認

ディレクトリオブジェクト

トリガー

シーケンス

DB-LINK

sqlplus

glogin設定

Home

環境設定

ページ別 機能説明

メモ

Clone this wiki locally

ページ別機能説明