CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
JohnSortley24
JohnSortley24

Reputation: 41

Can I export all my table definitions in a database in Oracle SQL Developer

So I created a database in Oracle SQL Developer and wrote up all the table definitions by hand using Notepad ++ and just did a bulk create tables statement. Silly me forgot to save this file so now I have all the definitions on SQL developer but none locally.

I have made some major changes to the design however there are some definitions that I can still use that are on there.

Is there a way I can export all the tables in a database to a file? I know I can grab the definitions individually however the time constraint would be massive. This way I don't have to go about manually writing it all again. Any searches that I conduct looking for an answer are just giving answers on how to select all tables in a database and not how to export table definitions.

Just to note, I had a look in my temp data for Notepad ++ however the file is not there anymore.

Upvotes: 2

Views: 3852

Answers (2)

Littlefoot
Littlefoot

Reputation: 142958

Sure, you can - using the DBMS_METADATA package and its GET_DDL function.

For example - in which I'm doing it in SCOTT's schema for two tables only - it would be like this:

SQL> set pagesize 0
SQL> set linesize 200
SQL> set long 20000
SQL> set longchunksize 20000
SQL> set feedback off
SQL> set verify off
SQL> set trimspool on
SQL>
SQL> select dbms_metadata.get_ddl ('TABLE', table_name, 'SCOTT')
  2  from user_tables
  3  where table_name in ('EMP', 'DEPT');

  CREATE TABLE "SCOTT"."DEPT"
   (    "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 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SQL>

You've noticed some SET commands at the beginning - I ran it in SQL*Plus, but they work in SQL Developer as well.

Upvotes: 2

thatjeffsmith
thatjeffsmith

Reputation: 22457

Tools, Database Export

Uncheck 'data'

Pick your output options, you want one file for everything or one file for each object.

enter image description here

Then pick your schema and objects - if you pick 'nothing' it will dump out the entire schema by default.

Upvotes: 2

Related Questions