查看表定义
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');