How to Get Oracle Create Table Statement in SQL*Plus

How to get Oracle create table statement in SQL*Plus

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;

`show create table` equivalent in oracle sql

If you are asking about SQL*Plus commands (show create table table_name doesn't appear to be a SQL statement), you can use the desc command

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

If you really want a SQL statement, you can use the dbms_metadata package

  1  select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' )
  2*   from dual
SQL> /

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 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  ALTER INDEX "SCOTT"."PK_EMP"  UNUSABLE ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) 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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  CACHE

Depending on the tool you are using, you may need to run set long 10000 first, that tells SQL*Plus to display the first 10,000 bytes of any LOB that is selected. If your DDL is longer, set a larger value.

how to get select statement query which was used to create table in oracle

If you're lucky one of these statements will show the DDL used to generate the table:

select *
from gv$sql
where lower(sql_fulltext) like '%create table suppliers%';

select *
from dba_hist_sqltext
where lower(sql_text) like '%create table%';

I used the word lucky because GV$SQL will usually only have results for a few hours or days, until the data is purged from the shared pool. DBA_HIST_SQLTEXT will only help if you have AWR enabled, the statement was run in the last X days that AWR is configured to hold data (the default is 8), the statement was run after the last snapshot collection (by default it happens every hour), and the statement ran long enough for AWR to think it's worth saving.

And for each table Oracle does not always store the full SQL. For security reasons, DDL statements are often truncated in the data dictionary. Don't be surprised if the text suddenly cuts off after the first N characters.

And depending on how the SQL is called the case and space may be different. Use lower and lots of wildcards to increase the chance of finding the statement.

Get table definition in oracle sql plus

Check the function: DBMS_METADATA.GET_DDL

http://psoug.org/reference/dbms_metadata.html

Oracle : which SQL command to get all details about a table?

I do something similar. I read those things from a SQL Server over OPENQUERY statements directly from Oracle DBs and save results into SQL Server tables to allow analysis of historic comparison schema information and changes.

So what you have to do with the resultsets of the following queries is to store them (regulary) somehow and add some kind of unique / primary key or timestamp to it, in order to distinguish between your different scans.

Leaving away the SQL Server specific code stuff, those are the basic oracle sql queries I use so far:

--Tables
SELECT table_name, owner, Tablespace_name, Num_Rows 
FROM all_tables WHERE tablespace_name is not NULL 
AND owner not in ('SYS', 'SYSTEM') 
ORDER BY  owner, table_name;

--Columns
SLECT OWNER, TABLE_NAME, Column_name, Data_type, data_length, data_precision, NULLABLE, character_Set_Name  
From all_tab_cols 
where USER_GENERATED = 'YES' 
AND owner not in ('SYS', 'SYSTEM');

--Indexes
select Owner, index_name, table_name, uniqueness,BLEVEL,STATUS from ALL_INDEXES
WHERE owner not in ('SYS', 'SYSTEM') 

--Constraints
select owner, constraint_name, constraint_type, table_name, search_condition, status, index_name, index_owner 
From all_constraints 
WHERE generated = 'USER NAME'
AND owner not in ('SYS', 'SYSTEM') 

--Role Previleges
select grantee, granted_role, admin_option, delegate_option, default_role, common
From DBA_ROLE_PRIVS

--Sys Privileges
select grantee, privilege, admin_option, common
From DBA_SYS_PRIVS 

generate create table from existing table

If you are making use of the SQL Developer you can right click the table that you want to generate a script for.

From there select Quick DDL and then click on Save To File. This will then generate the create table script to an external sql file. You can do this for multiple tables as well by selecting more than one table at a time.

Hope this helps!!

---

Related Topics