Oracle DDL从数据库导出

如何从Oracle数据库中仅导出数据库结构？

取决于你想要什么。

对于单个对象或仅几个对象，请使用DBMS_METADATA，例如

ops$tkyte@ORA920.US.ORACLE.COM> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE,
         CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
          REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
复制

对于整个数据库或类似数据库，您可以使用datapump导出定义并将其获取到sql脚本中

C:\>expdp userid=scott/tiger@db18_pdb1 dumpfile=scott_ddl.dmp directory=TEMP content_type=metadata_only
LRM-00101: unknown parameter name 'content_type'


C:\>expdp userid=scott/tiger@db18_pdb1 dumpfile=scott_ddl.dmp directory=TEMP content=metadata_only

Export: Release 18.0.0.0.0 - Production on Thu May 30 13:07:10 2019
Version 18.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  userid=scott/********@db18_pdb1 dumpfile=scott_ddl.dmp directory=TEMP content=metadata_only
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\SCOTT_DDL.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 30 13:07:51 2019 elapsed 0 00:00:33

C:\>impdp userid=scott/tiger@db18_pdb1 dumpfile=scott_ddl.dmp directory=TEMP sqlfile=scott_ddl.sql

Import: Release 18.0.0.0.0 - Production on Thu May 30 13:08:22 2019
Version 18.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_SQL_FILE_FULL_01":  userid=scott/********@db18_pdb1 dumpfile=scott_ddl.dmp directory=TEMP sqlfile=scott_ddl.sql
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at Thu May 30 13:08:27 2019 elapsed 0 00:00:04

复制