ablog

不器用で落着きのない技術者のメモ

Oracle Database から DDL 文を生成する SQL スクリプト

Oracle

Oracle Database から DDL 文を生成する SQL スクリプトを作った。
用途は export&import してデータを移行したいけど、表領域の構成を変えたい場合など。
この SQL スクリプトを使ってDDL 文を生成して、表領域の指定を変更して DDL を実行し、ignore=y オプションをつけて import してやれば、テーブルやインデックスを作成する表領域を変更できます。

define owner_name = &owner_name
define object_type = &object_type

set echo off
set termout off
set pagesize 0
set head off
set feed off
set verify off
set linesize 10000
set trimout on
set trimspool on

col spool_file_name new_value spool_file_name format a100
select 'create_'||lower('&&object_type')||'_'||lower('&&owner_name')||'.sql' spool_file_name from dual;
spool &spool_file_name

select dbms_lob.substr(dbms_metadata.get_ddl(upper('&&object_type'), object_name, owner),3999,1)||'/'
		from all_objects
	where owner = upper('&&owner_name') 
		and object_type = upper('&&object_type');

spool off
exit


実行してみるとこんな感じ。

  • CREATE TABLE文を生成する。
$ sqlplus -s system/manager @create_ddl.sql
Enter value for owner_name: scott
Enter value for object_type: table
  • CREATE INDEX文を生成する。
$ sqlplus -s system/manager @create_ddl.sql
Enter value for owner_name: scott
Enter value for object_type: index
  • テーブルを作成する表領域を変更する。
$ perl -i -ple 's/TABLESPACE\s+\"[a-zA-Z_]+\"/TABLESPACE \"TBS_USERS\"/gi' create_table_scott.sql
  • インデックスを作成する表領域を変更する。
$ perl -i -ple 's/TABLESPACE\s+\"[a-zA-Z_]+\"/TABLESPACE \"TBS_IX_USERS\"/gi' create_index_scott.sql
  • DDL文を実行してテーブルを作成する。
$ sqlplus system/manager @create_table_scott.sql <<EOF
exit
EOF
  • DDL文を実行してインデックスを作成する。
$ sqlplus system/manager @create_index_scott.sql <<EOF
exit
EOF

関連