Generate SQL Create Table/View Queries for Existing Tables/Views

Sometimes, it is useful to keep a copy of the DDL statements, i.e., create table/view, for all or some of the tables/views so that we can recreate them. Function dbms_metadata.get_ddl() can be used here.

SQL> select dbms_metadata.get_ddl('VIEW','V_6K_OBS') from dual;

DBMS_METADATA.GET_DDL('VIEW','V_6K_OBS')

CREATE OR REPLACE FORCE VIEW "BDM"."V_6K_OBS" ("TABLE_NAME", "COLUMN_NAME", " DATA_TYPE", "DATA_TYPE_MOD", "DATA_TYPE_OWNER", "DATA_LENGTH", "DATA_PRECISION", "DATA_SCALE", "NULLABLE", "COLUMN_ID", "DEFAULT_LENGTH", "DATA_DEFAULT", "NUM_D ISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY", "NUM_NULLS", "NUM_BUCKETS", "LAS T_ANALYZED", "SAMPLE_SIZE", "CHARACTER_SET_NAME", "CHAR_COL_DECL_LENGTH", "GLOBA L_STATS", "USER_STATS", "AVG_COL_LEN", "CHAR_LENGTH", "CHAR_USED", "V80_FMT_IMAG E", "DATA_UPGRADED", "HISTOGRAM") AS select "TABLE_NAME","COLUMN_NAME","DATA_ TYPE","DATA_TYPE_MOD","DATA_TYPE_OWNER","DATA_LENGTH","DATA_PRECISION","DATA_SCA LE","NULLABLE","COLUMN_ID","DEFAULT_LENGTH","DATA_DEFAULT","NUM_DISTINCT","LOW_V ALUE","HIGH_VALUE","DENSITY","NUM_NULLS","NUM_BUCKETS","LAST_ANALYZED","SAMPLE_S IZE","CHARACTER_SET_NAME","CHAR_COL_DECL_LENGTH","GLOBAL_STATS","USER_STATS","AV G_COL_LEN","CHAR_LENGTH","CHAR_USED","V80_FMT_IMAGE","DATA_UPGRADED","HISTOGRAM" from user_tab_columns where rownum <=6000

SQL> select dbms_metadata.get_ddl('TABLE','MV_UNIVAR_STS') from dual;

DBMS_METADATA.GET_DDL('TABLE','MV_UNIVAR_STS')

CREATE TABLE "BDM"."MV_UNIVAR_STS" ( "FILENAME" VARCHAR2(32), "C" NUMBE R, "TOT" NUMBER, "TOT_DIS" NUMBER, "MI_VAL" VARCHAR2(512), "MX_VAL" VARC HAR2(512) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MA XTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "BDM"