DBMS_MEDATA.get_ddl查看表，索引，表空间 定义

查看表定义SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT1','BOSWLL')FROM DUAL; DBMS_METADATA.GET_DDL('TABLE',--------------------------------------------------------------------------------   CREATE TABLE "BOSWLL"."DEPT1"   ("DEPTNO" NUMBER(2,0),"DNAME" VARCHAR2(14),"LOC" VARCHAR2(13)   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE  TABLESPACE "NNC_DATA01" 查看索引定义SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT')FROM DUAL; DBMS_METADATA.GET_DDL('INDEX',--------------------------------------------------------------------------------   CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE  TABLESPACE "NNC_INDEX4"查看视图定义SQL>  SELECT dbms_metadata.get_ddl('VIEW', 'V_EMP') FROM DUAL; DBMS_METADATA.GET_DDL('VIEW','--------------------------------------------------------------------------------   CREATE OR REPLACE FORCE VIEW "BOSWLL"."V_EMP" ("EMPNO", "ENAME", "JOB", "MGR",  SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"fROM  EMP查看主键SQL> SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_DEPT','SCOTT')FROM DUAL; DBMS_METADATA.GET_DDL('CONSTRA--------------------------------------------------------------------------------   ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE  TABLESPACE "NNC_INDEX4"  ENABLE查看外键ALTER TABLE EMP ADD  constraints EMP_DEPTNO FOREIGN KEY  (deptno)  REFERENCES dept(deptno)SQL> SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_DEPTNO','BOSWLL')FROM DUAL;  DBMS_METADATA.GET_DDL('REF_CON--------------------------------------------------------------------------------   ALTER TABLE "BOSWLL"."EMP" ADD CONSTRAINT "EMP_DEPTNO" FOREIGN KEY ("DEPTNO")  REFERENCES "BOSWLL"."DEPT" ("DEPTNO") ENABLE查看表空间SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;  SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;SQL>  DBMS_METADATA.GET_DDL('TABLESP--------------------------------------------------------------------------------   CREATE TABLESPACE "SYSAUX" DATAFILE  'F:\ORADATA\BOSTEST\SYSAUX01.DBF' SIZE 629145600  LOGGING ONLINE PERMANENT BLOCKSIZE 8192  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEM   ALTER DATABASE DATAFILE  'F:\ORADATA\BOSTEST\SYSAUX01.DBF' RESIZE 6002049024查看用户定义：SQL> select DBMS_METADATA.GET_DDL('USER','BOSWLL')   from dual  ; DBMS_METADATA.GET_DDL('USER','--------------------------------------------------------------------------------    CREATE USER "BOSWLL" IDENTIFIED BY VALUES 'S:D5A3C01466F22EBD39CEF66D37D83BB1      DEFAULT TABLESPACE "NNC_DATA01"      TEMPORARY TABLESPACE "TEMP" 获取某个用户的用户表EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)  FROM USER_ALL_TABLES u   WHERE u.nested='NO'   AND (u.iot_type is null or u.iot_type='IOT');EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');