Oracle INDEXのDDLをファイル出力する

sqlplusを使ってインデックスのDDLをファイル出力します。（こちらはインデックスのみです。テーブルのDDL出力はこちらです。）

手順１　SQLファイル（get_index_ddl.sql）を作成する。

まずは、適当なフォルダに get_index_ddl.sql という名前のSQLファイルを作成します。記載する内容は以下の通り。

SET LIN 20000
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
SET FEEDBACK OFF
SET ECHO OFF
SET TERMOUT OFF
SET TRIMS ON
SET VERIFY OFF

--DDLの終わりにセミコロン
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
-- PK情報はalter tableで表現
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
-- STORAGE属性を省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );
-- --セグメント属性省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
-- 表領域を省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE );
-- スキーマ名省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE);
--出力ファイル名変数定義
-- 『@』はスプールファイル名に使えないようなので、『_』に置き換える
col spoolout_file_name new_value spool_file_name format a50
select replace('./INDEX_'||'&1'||'.'||'&2'||'.sql','@','_') spoolout_file_name 
from dual
;
spool &spool_file_name

select dbms_metadata.get_ddl('INDEX',t.INDEX_NAME,t.OWNER )
from dba_indexes t
left outer join DBA_CONSTRAINTS c
on t.OWNER =c.INDEX_OWNER and t.INDEX_NAME = c.INDEX_NAME
where t.OWNER= '&1' and t.TABLE_NAME ='&2'
--PKを除外したい場合以下のコメント行を有効にする
--and c.CONSTRAINT_TYPE is null
;
spool off;
exit

手順２．実際にsqlplusを使いDDLを出力する。

SQL*plusをサイレントモード（-sオプション）で動作させ上記作成したSQLファイルを@で指定し、出力対象のテーブル名を指定します。
以下はSOONIスキーマが持つテーブル名「MST_ADDRESS」に作成されているインデックスのDDLを出力する例

sqlplus -s system/pass@vm013/orau8 @get_index_ddl.sql SOONI MST_ADDRESS

するとSQL*Plus実行フォルダに以下DDLファイルが作成されます。
INDEX_SOONI.MST_ADDRESS.sql

以下はLinux環境での実行結果ですがWindwos環境でも同様に可能です

$ sqlplus -s system/pass@vm013/orau8 @get_index_ddl.sql SOONI MST_ADDRESS
$ cat ./INDEX_SOONI.MST_ADDRESS.sql

  CREATE UNIQUE INDEX "PK_MST_ADDRESS" ON "MST_ADDRESS" ("OAZA_TOWN_CHOME_CODE")
  ;


  CREATE INDEX "IDX_MST_ADDRESS_01" ON "MST_ADDRESS" ("CITY_NAME")
  ;


  CREATE INDEX "IDX_MST_ADDRESS_04" ON "MST_ADDRESS" ("PREFECTURES_CODE", "PREFECTURES_NAME" DESC)
  ;


  CREATE INDEX "IDX_MST_ADDRESS_03" ON "MST_ADDRESS" (TO_CHAR("LATITUDE"), TO_CHAR("LONGITUDE"), "PREFECTURES_NAME" DESC)
  ;


  CREATE INDEX "IDX_MST_ADDRESS_02" ON "MST_ADDRESS" ("OAZA_TOWN_CHOME_NAME" DESC)
  ;

$

以上で終了です。