Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

Asked: May 29, 2019 - 11:56 am UTC

Last updated: July 02, 2019 - 4:25 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

How to export only database structure from Oracle database?

and Connor said...

Depends on what you want.

For a single object, or just a few, use DBMS_METADATA, eg

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)


For the entire database or similar, you can use datapump to export the definitions and get them into an sql script

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


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dar, June 18, 2019 - 3:48 pm UTC


I use the following command in oracle 12c to import single table but I also check some table also imported which i not mention in my script so what is the reason behind this situation.

impdp username/password TABLES=gen_dep_sal DIRECTORY=BACKUP_IMP_EXP DUMPFILE=backup01012019.dmp LOGFILE=impdp18062019.log
Connor McDonald
June 23, 2019 - 6:47 pm UTC

Can you show us the entire log file

A reader, June 26, 2019 - 7:04 am UTC

can you share mail address?
so I could share.
Connor McDonald
June 26, 2019 - 9:33 pm UTC

asktom_us@oracle.com

Please put the question ID (9540972700346733641) in the subject line

David, June 27, 2019 - 8:15 am UTC

When you use DBMS_METADATA, don't forget to do
set long 1000000

if you use SQL*Plus to see the entire output of the function.
Connor McDonald
July 02, 2019 - 4:25 am UTC

Agreed.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.