How to get Oracle create table statement in SQL*Plus

Question

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?

cletus's answer will help you get the DDL you want, but in regard to SQL Developer not showing the table (I assume you are referring to Oracle SQL Developer), are you logging in as the owner of the table? What happens if you type SELECT table_name FROM user_tables in SQL Developer? — Jeffrey Kemp, Jun 04 '09 at 04:50
It shows all the tables, including that one. I have 84 tables in this database, when I do a select table_name from user_tables, and only 83 show in the list of tables. This is a relational database, but it's a copy of a heirarachical database, and it gets regenerated each day. I think SQL Developer gets lost, or can't quite keep up sometimes. — thursdaysgeek, Jun 08 '09 at 16:02

score 35 · Accepted Answer · answered Jun 02 '09 at 00:26

35

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;

answered Jun 02 '09 at 00:26

cletus

616,129
168
910
942

it worked! There is no need for ";" in the set section. – dave Mar 04 '22 at 21:39

score 5 · Answer 2 · edited Jan 04 '16 at 12:39

Same as above but generic script found here gen_create_table_script.sql

-- #############################################################################################
--
-- %Purpose: Generate 'CREATE TABLE' Script for an existing Table in the database
--
-- Use:      SYSTEM, SYS or user having SELECT ANY TABLE  system privilege
--
-- #############################################################################################
--
set serveroutput on size 200000
set echo off
set feedback off
set verify off
set showmode off
--
ACCEPT l_user CHAR PROMPT  'Username: '
ACCEPT l_table CHAR PROMPT 'Tablename: '
--
DECLARE
 CURSOR TabCur IS
 SELECT table_name,owner,tablespace_name,
        initial_extent,next_extent,
        pct_used,pct_free,pct_increase,degree
   FROM sys.dba_tables
  WHERE owner=upper('&&l_user')
    AND table_name=UPPER('&&l_table');
--
 CURSOR ColCur(TableName varchar2) IS
 SELECT column_name col1,
        DECODE (data_type,
                'LONG',       'LONG   ',
                'LONG RAW',   'LONG RAW  ',
                'RAW',        'RAW  ',
                'DATE',       'DATE   ',
                'CHAR',       'CHAR' || '(' || data_length || ') ',
                'VARCHAR2',   'VARCHAR2' || '(' || data_length || ') ',
                'NUMBER',     'NUMBER' ||
                DECODE (NVL(data_precision,0),0, ' ',' (' || data_precision ||
                DECODE (NVL(data_scale, 0),0, ') ',',' || DATA_SCALE || ') '))) ||
        DECODE (NULLABLE,'N', 'NOT NULL','  ') col2
   FROM sys.dba_tab_columns
  WHERE table_name=TableName
    AND owner=UPPER('&&l_user')
 ORDER BY column_id;
--
 ColCount    NUMBER(5);
 MaxCol      NUMBER(5);
 FillSpace   NUMBER(5);
 ColLen      NUMBER(5);
--
BEGIN
 MaxCol:=0;
 --
 FOR TabRec in TabCur LOOP
    SELECT MAX(column_id) INTO MaxCol FROM sys.dba_tab_columns
     WHERE table_name=TabRec.table_name
       AND owner=TabRec.owner;
    --
    dbms_output.put_line('CREATE TABLE '||TabRec.table_name);
    dbms_output.put_line('( ');
    --
    ColCount:=0;
    FOR ColRec in ColCur(TabRec.table_name) LOOP
      ColLen:=length(ColRec.col1);
      FillSpace:=40 - ColLen;
      dbms_output.put(ColRec.col1);
      --
      FOR i in 1..FillSpace LOOP
         dbms_output.put(' ');
      END LOOP;
      --
      dbms_output.put(ColRec.col2);
      ColCount:=ColCount+1;
      --
      IF (ColCount < MaxCol) THEN
         dbms_output.put_line(',');
      ELSE
         dbms_output.put_line(')');
      END IF;
    END LOOP;
    --
    dbms_output.put_line('TABLESPACE '||TabRec.tablespace_name);
    dbms_output.put_line('PCTFREE '||TabRec.pct_free);
    dbms_output.put_line('PCTUSED '||TabRec.pct_used);
    dbms_output.put_line('STORAGE ( ');
    dbms_output.put_line('  INITIAL     '||TabRec.initial_extent);
    dbms_output.put_line('  NEXT        '||TabRec.next_extent);
    dbms_output.put_line('  PCTINCREASE '||TabRec.pct_increase);
    dbms_output.put_line(' )');
    dbms_output.put_line('PARALLEL '||TabRec.degree);
    dbms_output.put_line('/');
 END LOOP;
END;
/

Thanks for the script, it works great and does exactly what I need! — DaveN59, Feb 04 '13 at 21:11

score 1 · Answer 3 · answered Feb 03 '22 at 22:37

If you are using oracle SQLcl command-line client, the simplest way is to use the ddl built-in command. For example

ddl ownername.tablename

You will get the complete definition for that table. Warning: it could be very long based on your target table.

How to get Oracle create table statement in SQL*Plus

3 Answers3

Linked

Related