Saturday, May 17, 2014

Get Create table structure in Oracle

select dbms_metadata.get_ddl('TABLE','YOUR TABLE NAME','YOUR USER NAME') from dual

e.g.
select dbms_metadata.get_ddl ('TABLE','STAFF','SCOTT') from dual


output:
CREATE TABLE "SCOTT"."MASTER_STUDENT" ( "CO_SCH_ID" VARCHAR2(20), "OBJECTIVE" VARCHAR2(50), "CONTENT_COMMENTS" VARCHAR2(50), "MAT_COMMENTS" VARCHAR2(50), "PRESENTATIONS" VARCHAR2(50), "ARRANGEMENTS" VARCHAR2(50), "PHYSICAL" VARCHAR2(50), "RATING" FLOAT(126), "DELIVERY_COURSE" VARCHAR2(1000), "INFRASTRUCTURE" VARCHAR2(1000), "IDEA" VARCHAR2(1000), "TRAINEE_ID" VARCHAR2(35) NOT NULL ENABLE, "IOBJ" NUMBER(2,0) DEFAULT 0, "ICONTENT" NUMBER(2,0) DEFAULT 0, "IMATERIAL" NUMBER(2,0) DEFAULT 0, "IPRESENTATION" NUMBER(2,0) DEFAULT 0, "IARRANGEMENT" NUMBER(2,0) DEFAULT 0, "NEWTOPICSUGGEST" VARCHAR2(1000), "TEMPLATEID" NUMBER(1,0) DEFAULT 1, CONSTRAINT "CHECK_RATING" CHECK (rating between 0 and 10) ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 53477376 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MYTBLSPACE"

No comments:

Post a Comment

Followers