Oracle 如何查询一个对象的完整定义语句

来源：互联网发布：京瓷1025网络扫描编辑：程序博客网时间：2024/05/16 00:52

语法：

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;

示例：select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')--------------------------------------------------------------------------------  CREATE TABLE "SCOTT"."EMP"   (    "EMPNO" NUMBER(4,0),        "ENAME" VARCHAR2(10),--设置LONG和pagesize后能完全显示SQL> SET LONG 2000000SQL> SET PAGESIZE 0SQL> 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"