Có rất nhiều công cụ mà bạn có thể sử dụng để lấy các tập lệnh DDL cho các đối tượng khác nhau trong cơ sở dữ liệu. Sau đây là một ví dụ để generate toàn bộ DDL của một schema, chúng ta sẽ sử dụng tham số SQLFILE để lấy script DDL:
SQL> create or replace
2 procedure dmpfile_to_script(
3 p_dumpfile varchar2,
4 p_dumpdir varchar2,
5 p_jobname varchar2,
6 p_schema varchar2,
7 p_new_schema varchar2 default null) is
8
9 l_jobid number;
10 l_job_state varchar2(30);
11 l_sts ku$_Status;
12
13 --
14 -- list of object types we'd allow from the dump
15 --
16 l_obj_nt sys.odcivarchar2list :=
17 sys.odcivarchar2list(
18 'ANALYTIC_VIEW',
19 'ATTRIBUTE_DIMENSION',
20 'CLUSTER',
21 'DIMENSION',
22 'FUNCTION',
23 'HIERARCHY',
24 'MATERIALIZED_VIEW',
25 'PACKAGE',
26 'PROCEDURE',
27 'REFRESH_GROUP',
28 'SEQUENCE',
29 'TABLE',
30 'TYPE',
31 'VIEW');
32
33 l_obj_types varchar2(4000);
34 begin
35 l_jobid := dbms_datapump.open(
36 operation => 'SQL_FILE',
37 job_mode => 'SCHEMA',
38 job_name => upper(p_jobname)
39 );
40
41 dbms_datapump.add_file(
42 handle => l_jobid,
43 filename => p_dumpfile,
44 directory => p_dumpdir);
45
46 dbms_datapump.add_file(
47 handle => l_jobid,
48 filename => p_dumpfile||'.log',
49 directory => p_dumpdir,
50 filetype => dbms_datapump.ku$_file_type_log_file);
51
52 dbms_datapump.add_file(
53 handle => l_jobid,
54 filename => p_dumpfile||'.sql',
55 directory => p_dumpdir,
56 filetype => dbms_datapump.ku$_file_type_sql_file);
57
58 -- just in case they give us multiple schemas or a full
59
60 dbms_datapump.metadata_filter(
61 handle => l_jobid,
62 name => 'SCHEMA_EXPR',
63 value => '= '''||p_schema||'''');
64
65 dbms_datapump.metadata_remap(l_jobid,
66 'REMAP_SCHEMA',
67 p_schema,
68 p_new_schema);
69
70 -- don't need storage params
71
72 dbms_datapump.metadata_transform(
73 handle => l_jobid,
74 name => 'STORAGE',
75 value => 0);
76
77 -- don't need tablespace
78
79 dbms_datapump.metadata_transform(
80 handle => l_jobid,
81 name => 'SEGMENT_ATTRIBUTES',
82 value => 0);
83
84 -- filter the list of valid object types we'll permit
85
86 l_obj_types := 'IN ('''||l_obj_nt(1)||'''';
87 for i in 2 .. l_obj_nt.count
88 loop
89 l_obj_types := l_obj_types || ','''||l_obj_nt(i)||'''';
90 end loop;
91 l_obj_types := l_obj_types || ')';
92
93 dbms_datapump.metadata_filter(
94 handle => l_jobid,
95 name => 'INCLUDE_PATH_EXPR',
96 value => l_obj_types
97 );
98
99 dbms_datapump.start_job(l_jobid);
100
101 dbms_datapump.wait_for_job(
102 handle => l_jobid,
103 job_state => l_job_state);
104
105 dbms_datapump.detach(l_jobid);
106 end;
107 /
Procedure created.
SQL>
SQL> begin
2 dmpfile_to_script(
3 p_dumpfile =>'scott.dmp',
4 p_dumpdir =>'TEMP',
5 p_jobname =>'SCOTT_DP',
6 p_schema =>'SCOTT',
7 p_new_schema =>'SCOTT2'
8 );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> host cat c:\temp\scott.dmp.sql
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT2"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) DEFAULT COLLATION "USING_NLS_COMP" ;
CREATE TABLE "SCOTT2"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE) COLLATE "USING_NLS_COMP",
"LOC" VARCHAR2(13 BYTE) COLLATE "USING_NLS_COMP"
) DEFAULT COLLATION "USING_NLS_COMP" ;
CREATE TABLE "SCOTT2"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10 BYTE) COLLATE "USING_NLS_COMP",
"JOB" VARCHAR2(9 BYTE) COLLATE "USING_NLS_COMP",
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) DEFAULT COLLATION "USING_NLS_COMP" ;
CREATE TABLE "SCOTT2"."BONUS"
( "ENAME" VARCHAR2(10 BYTE) COLLATE "USING_NLS_COMP",
"JOB" VARCHAR2(9 BYTE) COLLATE "USING_NLS_COMP",
"SAL" NUMBER,
"COMM" NUMBER
) DEFAULT COLLATION "USING_NLS_COMP" ;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT2"."DEPT" ADD CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPTNO")
USING INDEX ENABLE;
ALTER TABLE "SCOTT2"."EMP" ADD CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT2"."EMP" ADD CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT2"."DEPT" ("DEPTNO") ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER
-- fixup virtual columns...
-- done fixup virtual columns
...
...