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');