語法如下,會傳回Clob型態的值 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;
範例如下:
單一Table的DDL set heading off; set echo off; Set pages 999; set long 90000; spool c:\ddl.txt select dbms_metadata.get_ddl('TABLE','TMP_CCC','TEST_USER') from dual; spool off;
取出當前使用者的整個Schmea的Table DDL set pagesize 0 set long 90000 set feedback off set echo off spool c:\schema.sql SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u; spool off;
取出所有的Tablespace的DDL SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;
--查Session在Temporary的實際使用量
select vs.sid,vs.serial#,vp.spid,vs.module,vs.action,vtu.username,sum(vtu.blocks)*8/1024
from V$TEMPSEG_USAGE vtu,v$session vs,v$process vp
where vtu.session_num=vs.serial#
and vtu.session_addr=vs.saddr