当我们想要查看某个表或者是表空间的DDL的时候，可以利用dbms_metadata.get_ddl这个包来查看。
dbms_metadata包中的get_ddl函数详细参数
GET_DDL函数返回创建对象的原数据的DDL语句，详细参数如下
-- object_type ---需要返回原数据的DDL语句的对象类型
-- name --- 对象名称
-- schema ---对象所在的Schema，默认为当前用户所在所Schema
-- version ---对象原数据的版本
-- model ---原数据的类型默认为ORACLE
-- transform. - XSL-T transform. to be applied.
-- RETURNS: 对象的原数据默认以CLOB类型返回
dbms_metadata包中的get_ddl函数定义
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;
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET LONG 999999
查看表的DDL 语句。
yang@rac1>select dbms_metadata.get_ddl('TABLE','YANGTAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','YANGTAB')
---------------------------------------------------------------------------
CREATE TABLE "YANG"."YANGTAB"
( "ID" NUMBER,
"NAME" VARCHAR2(15),
PRIMARY KEY ("ID")
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)
TABLESPACE "USERS" 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"
查看表空间的DDL 语句。
sys@rac1>select dbms_metadata.get_ddl('TABLESPACE', 'USERS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
---------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'D:\ORACLE\ORADATA\ORACL\USERS01.DBF' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
'D:\ORACLE\ORADATA\ORACL\USERS02.DBF' SIZE 943718400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'D:\ORACLE\ORADATA\ORACL\USERS01.DBF' RESIZE 347340800
sys@rac1>select dbms_metadata.get_ddl('TABLESPACE', 'EXAMPLE') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','EXAMPLE')
---------------------------------------------------------------------------
CREATE TABLESPACE "EXAMPLE" DATAFILE
'D:\ORACLE\ORADATA\ORACL\EXAMPLE01.DBF' SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'D:\ORACLE\ORADATA\ORACL\EXAMPLE01.DBF' RESIZE 314572800
查看 用户的定义语句。
sys@rac1>select dbms_metadata.get_ddl('USER', 'YANG') FROM DUAL;
DBMS_METADATA.GET_DDL('USER','YANG')
---------------------------------------------------------------------------
CREATE USER "YANG" IDENTIFIED BY VALUES 'S:269264CD30B5AC166D2C9882AB88F
DA20'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
查看 索引的定义语句：
yang@rac1>select index_name ,table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C0010473 YANG_B
SYS_C0010476 YANG_A
SYS_C0010278 YANGTAB2
SYS_C0010277 YANGTAB
SYS_C0010286 MV_YANGTAB2
SYS_C0010285 MV_YANGTAB
ID_SEQNO DA_TEST
yang@rac1>select dbms_metadata.get_ddl('INDEX','ID_SEQNO') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','ID_SEQNO')
--------------------------------------------------------------------------------
CREATE INDEX "YANG"."ID_SEQNO" ON "YANG"."DA_TEST" ("SEQNO")
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)
TABLESPACE "USERS"
yang@rac1>select dbms_metadata.get_ddl('INDEX','SYS_C0010285') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','SYS_C0010285')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "YANG"."SYS_C0010285" ON "YANG"."MV_YANGTAB" ("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)
TABLESPACE "USERS"