How to get Oracle create table statement in SQL*Plus

sql oracle sqlplus
thursdaysgeek picture thursdaysgeek · Jun 2, 2009 · Viewed 95.6k times · Source

I have a table that exists in an Oracle database, but doesn't show on my list of tables in the tool SQL Developer. However, if I go to SQL*Plus, and do a 

select table_name from user_tables;

I get the table listed. If I type

desc snp_clearinghouse;

it shows me the fields. I'd like to get the create statement, because I need to add a field. I can modify the table to add the field, but I still need the create statement to put into our source control. What pl/sql statement is used to get the create statement for a table?

Answer

cletus picture cletus · Jun 2, 2009

From Get table and index DDL the easy way:

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

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

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

Related questions

How do I spool to a CSV formatted file using SQLPLUS?

I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS. How do I do it?

Read More
Escaping ampersand character in SQL string

I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm …

Read More
how to increase sqlplus column output length?

I have some queries to find out the ddl of some objects from a schema. The result columns I am getting are truncated in the middle of the queries. How can I increase the width of the column? I tried …

Read More