使用DBMS_METADATA.GET_DDL获取对象的DDL脚本

最新推荐文章于 2024-09-03 09:22:10 发布

转载最新推荐文章于 2024-09-03 09:22:10 发布 · 235 阅读

文章标签：

#数据库

通过DBMS_METADATA.GET_DDL包在SQLPLUS中获取数据库对象的DDL脚本，包括表、索引、视图、存储过程、函数等。

--GET_DDL: Return the metadata for a single object as DDL.

-- This interface is meant for casual browsing (e.g., from SQLPlus)

-- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.

-- 参数 :

-- object_type - The type of object to be retrieved.

-- name - Name of the object. 表名或索引名, procedure名等等

-- schema - Schema containing the object. Defaults to

-- the caller's schema.

-- version - The version of the objects' metadata.

-- model - The object model for the metadata.

-- transform. - XSL-T transform. to be applied.

-- RETURNS: Metadata for the object transformed to DDL as a CLOB.

FUNCTION 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.得到一个表或索引的ddl语句

SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

2.得到一个用户下的所有表，索引，存储过程的ddl

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

3.得到所有表空间的ddl语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

4.得到所有创建用户的ddl

SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;

================================================================================

9i 中可以利用DBMS_METADATA.GET_DDL包得到数据库的对象的ddl脚本。如下（SQLPLUS中执行）：

a. 获取单个的建表、视图和建索引的语法

set pagesize 0
set long 90000
set feedback off
set echo off
spool DEPT.sql
select dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual;
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_NAME','SCOTT') from dual;
spool off;

b.获取一个SCHEMA下的所有建表、视图和建索引的语法，以scott为例：

set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('VIEW',u.VIEW_name) FROM USER_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
spool off;

c. 获取某个SCHEMA的建全部存储过程的语法

set pagesize 0
set long 90000
set feedback off
set echo off
spool procedures.sql
select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) from user_objects u where object_type = 'PROCEDURE';
spool off;

d.    获取某个SCHEMA的建全部函数的语法

set pagesize 0
set long 90000
set feedback off
set echo off
spool function.sql
select   DBMS_METADATA.GET_DDL('FUNCTION',u.object_name) from     user_objects u where     object_type = 'FUNCTION';
spool off;

------------------------------------------------

例子 :

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> spool schema.sql
SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EDIBTO850_PO1PID','DFMS') from dual;

SQL> spool off;
CREATE TABLE "DFMS"."EDIBTO850_PO1PID"
   (    "CONTROLNUMBER" VARCHAR2(40) NOT NULL ENABLE,
        "BEG03" VARCHAR2(20) NOT NULL ENABLE,
        "PO101" VARCHAR2(20) NOT NULL ENABLE,
        "SEQNO" NUMBER(*,0) NOT NULL ENABLE,
        "PO109" VARCHAR2(20) DEFAULT (''),
        "PID01" CHAR(1) DEFAULT (''),
        "PID02" VARCHAR2(3) DEFAULT (''),
        "PID05" VARCHAR2(200) DEFAULT ('') NOT NULL ENABLE,
        "PID08" CHAR(1) DEFAULT (''),
        "LASTEDITDT" DATE DEFAULT (sysdate),
        "CREATE_TIME" DATE DEFAULT sysdate,
        "TIEGROUP" VARCHAR2(35),
         CONSTRAINT "EDIBTO850_PO1PID_PK" PRIMARY KEY ("CONTROLNUMBER", "BEG03", "PO101
", "SEQNO", "PID05")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 104857600 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SN_IDX" ENABLE
   ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2"
PARTITION BY RANGE ("CREATE_TIME")
(PARTITION "P200907" VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201001" VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201007" VALUES LESS THAN (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201101" VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201107" VALUES LESS THAN (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201201" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201207" VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201301" VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201307" VALUES LESS THAN (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201401" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201407" VALUES LESS THAN (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "P201501" VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PART_D_TS2" ) ENABLE ROW MOVEMENT