【工具&命令】获取数据库对象的定义（DDL）

     SQL> set pages 0
     SQL> set longchunksize 3000
     SQL> set long 2000000000
     SQL> select dbms_metadata.get_ddl('<对象类型>','<对象名>','<对象SCHEMA>') from dual;

    --表定义
     SQL> set pages 0
     SQL> set longchunksize 3000
     SQL> set long 2000000000
     SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

    --索引定义
     SQL> select dbms_metadata.get_ddl('INDEX','EMP_IND','SCOTT') from dual;

    --用户视图定义
     SQL> select dbms_metadata.get_ddl('VIEW','EMP_VIEW','SCOTT') from dual;

    Home / Oracle Database Online Documentation Library, 12c Release 1 (12.1.0.2)
    Database PL/SQL Packages and Types Reference
    https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66885
    >GET_xxx Functions

    --创建DUMP导出路径
    create or replace directory test_dir as '<DIRECTORY>';

    --使用expdp的content=metadata_only仅导出metadata的DUMP
    expdp <LOGIN>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> content=metadata_only dumpfile=<DUMP_NAME>.dmp


    --使用impdp的SQLFILE从DUMP导出DDL
    impdp <LOGIN>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp SQLFILE=<SQLFILE_NAME>.sql FULL=y

    expdp system/oracle DIRECTORY=test_dir content=metadata_only dumpfile=ddl.dmp SCHEMAS=SCOTT
    impdp system/oracle DIRECTORY=test_dir SQLFILE=allsql.sql dumpfile=ddl.dmp FULL=y
    

    set pagesize 0
    select view_name, view_definition from
    v$fixed_view_definition where view_name =upper('gv$session');

 set long 100000
 select text from dba_views where view_name='UNIFIED_AUDIT_TRAIL';