Wednesday, January 30, 2013

Using dbms_metadata.get_ddl to get DDL but not all source provided

Today I tried to get structure of aud$ table using dbms_metadata.get_ddl. But result shows only a few lines not entire table.


SQL> select dbms_metadata.get_ddl('TABLE', 'AUD$') from dual;  
 DBMS_METADATA.GET_DDL('TABLE','AUD$')  
 -------------------------------------------------------------  
  CREATE TABLE "SYS"."AUD$"  
   (  "SESSIONID" NUMBER NOT NULL ENABLE,  
     "ENTRYI  
 SQL>  



But to get all source I used to_char function and it provide me all source.


 SQL> select   
 to_char(dbms_metadata.get_ddl('TABLE', 'AUD$'))   
 from dual;  
  CREATE TABLE "SYS"."AUD$"  
   (  "SESSIONID" NUMBER NOT NULL ENABLE,  
     "ENTRYID" NUMBER NOT NULL ENABLE,  
     "STATEMENT" NUMBER NOT NULL ENABLE,  
     "TIMESTAMP#" DATE,  
     "USERID" VARCHAR2(30),  
     "USERHOST" VARCHAR2(128),  
     "TERMINAL" VARCHAR2(255),  
     "ACTION#" NUMBER NOT NULL ENABLE,  
     "RETURNCODE" NUMBER NOT NULL ENABLE,  
     "OBJ$CREATOR" VARCHAR2(30),  
     "OBJ$NAME" VARCHAR2(128),  
     "AUTH$PRIVILEGES" VARCHAR2(16),  
     "AUTH$GRANTEE" VARCHAR2(30),  
     "NEW$OWNER" VARCHAR2(30),  
     "NEW$NAME" VARCHAR2(128),  
     "SES$ACTIONS" VARCHAR2(19),  
     "SES$TID" NUMBER,  
     "LOGOFF$LREAD" NUMBER,  
     "LOGOFF$PREAD" NUMBER,  
     "LOGOFF$LWRITE" NUMBER,  
     "LOGOFF$DEAD" NUMBER,  
     "LOGOFF$TIME" DATE,  
     "COMMENT$TEXT" VARCHAR2(4000),  
     "CLIENTID" VARCHAR2(64),  
     "SPARE1" VARCHAR2(255),  
     "SPARE2" NUMBER,  
     "OBJ$LABEL" RAW(255),  
     "SES$LABEL" RAW(255),  
     "PRIV$USED" NUMBER,  
     "SESSIONCPU" NUMBER,  
     "NTIMESTAMP#" TIMESTAMP (6),  
     "PROXY$SID" NUMBER,  
     "USER$GUID" VARCHAR2(32),  
     "INSTANCE#" NUMBER,  
     "PROCESS#" VARCHAR2(16),  
     "XID" RAW(8),  
     "AUDITID" VARCHAR2(64),  
     "SCN" NUMBER,  
     "DBID" NUMBER,  
     "SQLBIND" CLOB,  
     "SQLTEXT" CLOB,  
     "OBJ$EDITION" VARCHAR2(30)  
   ) 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 DEFAULT   
 CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM"  
 LOB ("SQLBIND") STORE AS   
 BASICFILE (TABLESPACE "SYSTEM"   
 ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  
 NOCACHE LOGGING  
 STORAGE(INITIAL 65536 NEXT 1048576   
 MINEXTENTS 1 MAXEXTENTS 2147483645  
  0 FREELISTS 1 FREELIST GROUPS 1   
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT   
 CELL_FLASH_CACHE DEFAULT))  
 LOB ("SQLTEXT") STORE AS BASICFILE (TABLESPACE "SYSTEM"   
 ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  
 NOCACHE 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)  
 )  
 SQL>  

No comments:

Post a Comment

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...