CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Yogamurthy
Yogamurthy

Reputation: 998

Getting the create table command used in oracle 11g

How can I get the create table command which is used to create a table in oracle 11g so that I can copy the command and run it in another database? Please help.

Upvotes: 0

Views: 1409

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Please use the below Query

select dbms_metadata.get_ddl('TABLE','YOUR_TABLE_NAME','YOUR_SCHEMA_NAME') from dual;

if you use SQL Developer, select the table name , and right click to choose Open Declaration and then Click SQL tab on the window that opens!

enter image description here

Upvotes: 1

Dba
Dba

Reputation: 6639

Try to spool the output of the below query,

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;

Like,

set pagesize 0
set long 90000
set feedback off
set echo off 

spool schema.sql 

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u WHERE TABLE_NAME = '<your_table>';

spool off;

Reference: http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm

Upvotes: 1

user330315
user330315

Reputation: 

select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME_GOES_HERE')
from dual;

Upvotes: 3

Related Questions