get schema DDL

This document explains how to get DDL for all indexes in a specific schema using dbms_metadata in a single SQL statement.

SOLUTION

Connect as the schema user for which the index DDLs are to be fetched and execute the following code:
 

SET LONG 2000000
SET PAGESIZE 0
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’STORAGE’,false);
SELECT DBMS_METADATA.GET_DDL(‘INDEX’,u.index_name)
FROM USER_INDEXES u ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’DEFAULT’);

For example:
 

SET LONG 2000000
SET PAGESIZE 0
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’STORAGE’,false);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL(‘INDEX’,u.index_name)FROM USER_INDEXES u ;

< … result appear here … >
CREATE INDEX “SCOTT”.”TEST_PK” ON “SCOTT”.”TEST” (“COL”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE “USERS”

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’DEFAULT’);

——————————————-index 换schema名—————

How to generate a DDL statement for an object using DBMS_METADATA.GET_DDL function, but without having the schema owner prefix in the returned DDL statement?

SOLUTION

Use the DBMS_METADATA.ADD_TRANSFORM in ‘MODIFY’ mode, then pass the handle to the procedure

DBMS_METADATA.SET_REMAP_PARAM (
transform_handle IN NUMBER,
name IN VARCHAR2,
old_value IN VARCHAR2,
new_value IN VARCHAR2,
object_type IN VARCHAR2 DEFAULT NULL);

Providing REMAP_SCHEMA as NAME argument, the name of the owner of the object as OLD_VALUE argument and a blank string as NEW_VALUE argument.
Then use DBMS_METADATA.ADD_TRANSFORM in ‘DDL’ mode and finally filter the object as in the below example:

set pages 1000
set long 1000
var h number
var ddl_handle number
var modify_handle number
exec :h := DBMS_METADATA.OPEN(‘TABLE’)
exec :modify_handle := DBMS_METADATA.ADD_TRANSFORM(:h,’MODIFY’)
exec DBMS_METADATA.SET_REMAP_PARAM(:modify_handle,’REMAP_SCHEMA’,’SCOTT’,”)
exec :ddl_handle := DBMS_METADATA.ADD_TRANSFORM(:h,’DDL’)
exec DBMS_METADATA.SET_FILTER(:h, ‘NAME’,’EMP’)
select DBMS_METADATA.fetch_clob(:h) from dual;
print :h

Above returns:

DBMS_METADATA.FETCH_CLOB(:H)
——————————————————————————–
CREATE TABLE “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
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS” ENABLE,
CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “DEPT” (“DEPTNO”) ENABLE
) 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)
TABLESPACE “USERS”

Reference: ‘PLSQL Packages and Types Reference‘ guide, chapter ‘DBMS_METADATA’

DBMS_METADATA.GET_DDL
Oracle数据库有DBMS_METADATA包，它提供了一种从数据库字典中检索元数据的方法。

Get DDL ( Create Script ) DBMS_METADATA.GET_DDL
DBMS_METADATA.GET_DDL 的语法如下。

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT ‘COMPATIBLE’,
model           IN VARCHAR2 DEFAULT ‘ORACLE’,
transform       IN VARCHAR2 DEFAULT ‘DDL’)
RETURN CLOB;
1.
2.
3.
4.
5.
6.
7.
8.
DBMS_METADATA – User Create Script
可以按如下方式获取任何用户（Schema）的 DDL（创建脚本）

select dbms_metadata.get_ddl(‘USER’, du.username) AS DDL_SCRIPT
from dba_users du
where du.username = ‘TYPE_USER_NAME’
union all
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’, dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = ‘TYPE_USER_NAME’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’, drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = ‘TYPE_USER_NAME’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’, dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = ‘TYPE_USER_NAME’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’, dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = ‘TYPE_USER_NAME’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘DEFAULT_ROLE’, drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = ‘TYPE_USER_NAME’
and drp.default_role = ‘YES’
and rownum = 1;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
GET_DDL
For example: You can get DDL of SCOTT user as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl(‘USER’, du.username) AS DDL_SCRIPT
from dba_users du
where du.username = ‘SCOTT’
union all
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’, dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = ‘SCOTT’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’, drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = ‘SCOTT’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’, dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = ‘SCOTT’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’, dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = ‘SCOTT’
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘DEFAULT_ROLE’, drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = ‘SCOTT’
and drp.default_role = ‘YES’
and rownum = 1;

CREATE USER “SCOTT” IDENTIFIED BY VALUES ‘S:BE2891F45D57E8E7897914A39E21F4B3D7F9F23D72E8E0E65DF1F51C2262;435E3EB39C74D939’ DEFAULT TABLESPACE “USERS” TEMPORARY TABLESPACE “TEMP”

   GRANT “CONNECT” TO “SCOTT”
   GRANT “RESOURCE” TO “SCOTT”
   GRANT “DBA” TO “SCOTT”

   GRANT CREATE TABLE TO “SCOTT”
   GRANT UNLIMITED TABLESPACE TO “SCOTT”

   GRANT SELECT ON “SYS”.”DBA_USERS” TO “SCOTT”
   GRANT INSERT ON “SYS”.”TEST402″ TO “SCOTT”
   GRANT UPDATE ON “SYS”.”TEST402″ TO “SCOTT”
   GRANT SELECT ON “SYS”.”TEST402″ TO “SCOTT”
   GRANT DELETE ON “SYS”.”TEST402″ TO “SCOTT”

   ALTER USER “SCOTT” DEFAULT ROLE ALL;

SQL>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
Get DDL of a Table ( Table Create Script )
可以按如下方式获取表的创建脚本

select dbms_metadata.get_ddl( ‘TABLE’, ‘TABLE_NAME’,’SCHEMA_NAME’ ) from dual;
1.
For example; You can get SCOTT.EMP table create script as follows.

select dbms_metadata.get_ddl(‘TABLE’, ‘EMP’,’SCOTT’) from dual;
1.
SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl(‘TABLE’, ‘EMP’,’SCOTT’) from dual;

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”
  PARALLEL

SQL>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
还可以按如下方式获取所有表或任何用户模型下表的创建脚本。

SELECT DBMS_METADATA.get_ddl (‘TABLE’, table_name, owner)
FROM dba_tables
WHERE owner = UPPER(‘&amp;1′);
1.
2.
3.
Index Create Script
You can get DDL ( Create Script ) of any index as follows.

select dbms_metadata.get_ddl( ‘INDEX’, ‘INDEX_NAME’,’SCHEMA_NAME’ ) from dual;
1.
For example; You can get HR.EMP_DEPARTMENT_IX index create script as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl(‘INDEX’,’EMP_DEPARTMENT_IX’,’HR’) from dual;

CREATE INDEX “HR”.”EMP_DEPARTMENT_IX” ON “HR”.”EMPLOYEES” (“DEPARTMENT_ID”)
  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”;

SQL>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
You can get all indexes get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL(‘INDEX’, INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE =’NORMAL’;
1.
Tablespace Create Script
You can generate all tablespaces get ddl ( create script ) as follows.

set head off echo off
select ‘select dbms_metadata.get_ddl(”TABLESPACE”,”’
|| tablespace_name || ”’) from dual;’ from dba_tablespaces;
1.
2.
3.
You can get the Users tablespace get ddl ( create script ) as follows.

SQL> select dbms_metadata.get_ddl(‘TABLESPACE’,’USERS’) from dual;

CREATE TABLESPACE “USERS” DATAFILE
  ‘/u01/app/oracle/oradata/orcl/users01.dbf’ SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  ‘/u01/app/oracle/oradata/orcl/users01.dbf’ RESIZE 85196800;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
DBMS_METADATA.GET_DDL VIEW, FUNCTION,PACKAGE
You can get all views get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = ‘VIEW’ AND OWNER=’USER_NAME’;
1.
You can get all functions, procedures get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL(‘PROCEDURE’,U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = ‘PROCEDURE’ AND OWNER=’USER_NAME’;
SELECT DBMS_METADATA.GET_DDL(‘FUNCTION’,U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = ‘FUNCTION’AND OWNER=’USER_NAME’;
1.
2.
You can get any package’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL(‘PACKAGE’,’OBJECT_NAME’,’SCHEMA_NAME’) FROM DUAL;
1.
You can get any package’s body get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL(‘PACKAGE_BODY’,’OBJECT_NAME’,’SCHEMA_NAME’) FROM DUAL;
1.
You can get any constraint’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL(‘REF_CONSTRAINT’,’OBJECT_NAME’,’SCHEMA_NAME’) from dual;
1.
You can get any user’s system grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’SCHEMA_NAME’) from dual;
1.
You can get any user’s role grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SCHEMA_NAME’) from dual;
1.
上述sql脚本，可根据实际需求进行调整，还有部分其他用法此处未完全罗列，使用者可利用搜索引擎自行检索。
 

代码007普通

打赏 收藏 海报 链接