表や索引、その他オブジェクトを再作成するために、DDLとしてデータを取り出す方法についてご紹介します。
また、取り出したDDLからオブジェクトが再作成できることを確認します。
【Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1)】
概要
DBMS_METADATAパッケージには、メタデータを取り出す機能とXMLを送信する機能があります。
1. 構文
構文は下記となります。
select DBMS_METADATA.GET_DDL( '[object_type]', '[name]', '[schema]') from dual;
・[object_type]には、対象のオブジェクトの種類(TABLEやINDEX, TABLESPACE等)を入れます。
※[object_type]の一覧は、「3.おまけ -指定できるオブジェクトの種類-」を参考下さい。
・[name]には、対象のオブジェクトの名前を入れます。
・[schema]には、対象のオブジェクトを所有しているスキーマ(ユーザ)名を入れます。
※DBMS_METADATAを実行するユーザと同じ場合は不要
2. 実行例
以下に実行例を示します。
今回は、[object_type]が'TABLE、[name]がEMP、[schema]がSCOTTのDDLを取り出します。簡単に言いますと、SCOTTユーザのEMP表のDDLを取り出します。
SQL> set pagesize 1000
SQL> set long 20000000
SQL> select DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE S
TATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
※"set long"と "set pagesize"も実行してください。
"set long"は出力する文字数を指定します。デフォルトでは、DDLが途中で途切れてしまいます。
また、"set pagesize"が無いと出力結果が見辛くなります。
▼再作成
上記で確認したDDLを用いて、再作成できることを確認します。
※DDLの結果は変なところで改行されていたりするため、適宜編集ください。
SQL> drop table scott.emp;
表が削除されました。
SQL> CREATE TABLE "SCOTT"."EMP"
("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS";
表が作成されました。
SQL> desc scott.emp
名前
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
3. おまけ -指定できるオブジェクトの種類-
object_typeで指定できるオブジェクトの種類の一覧は下記になります。
詳細は、下記のマニュアルを参照して下さい
【Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1)】
表94-12 DBMS_METADATA: オブジェクト・タイプ
AQ_QUEUE
AQ_QUEUE_TABLE
AQ_TRANSFORM
ASSOCIATION
AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
CONTEXT
DATABASE_EXPORT
DB_LINK
DEFAULT_ROLE
DIMENSION
DIRECTORY
FGA_POLICY
FUNCTION
INDEX_STATISTICS
INDEX
INDEXTYPE
JAVA_SOURCE
JOB
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
REFRESH_GROUP
RESOURCE_COST
RLS_CONTEXT
RLS_GROUP
RLS_POLICY
RMGR_CONSUMER_GROUP
RMGR_INTITIAL_CONSUMER_GROUP
RMGR_PLAN
RMGR_PLAN_DIRECTIVE
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SCHEMA_EXPORT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLE_DATA
TABLE_EXPORT
TABLE_STATISTICS
TABLESPACE
TABLESPACE_QUOTA
TRANSPORTABLE_EXPORT
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW
XMLSCHEMA
XS_USER
XS_ROLE
XS_ROLESET
XS_ROLE_GRANT
XS_SECURITY_CLASS
XS_DATA_SECURITY
XS_ACL
XS_ACL_PARAM
XS_NAMESPACE
以上になります。