Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, leon.

Asked: August 07, 2020 - 4:59 am UTC

Answered by: Chris Saxon - Last updated: August 07, 2020 - 3:37 pm UTC

Category: SQL - Version: 19c

Viewed 100+ times

You Asked

Hi Tom,

How to get DDL scripts of table partition and index partition in oracle.

Thanks,
Leon.

and we said...

If you're using SQLcl/SQL Developer, use the DDL command:

create table t (
  c1 int
) partition by range ( c1 ) 
  interval ( 10 ) (
    partition p0 values less than ( 1 )
  );
create index i 
  on t ( c1 ) 
  local;
  
ddl t

  CREATE TABLE "CHRIS"."T" 
   ( "C1" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  PARTITION BY RANGE ("C1") INTERVAL (10) 
 (PARTITION "P0"  VALUES LESS THAN (1) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;

  CREATE INDEX "CHRIS"."I" ON "CHRIS"."T" ("C1") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
 (PARTITION "P0" NOCOMPRESS 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;



For more on this see:

https://www.thatjeffsmith.com/archive/2016/05/configuring-your-generated-ddl-in-sql-developer-and-sqlcl/

You can also use DBMS_metadata.get_ddl to get the scripts.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.