使用dbms_metadata.get_ddl查看对象的定义语句。

当我们想要查看某个表或者是表空间的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

  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

  '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

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")