Describe tables with object columns fully

Another useful option in sqlplus is to be able to 'recursively' describe tables where one of the columns is a type. The default for this option is 1 (i.e. don't recursively do anything). Describing this system table for instance just shows SUBSCRIBERS is of type "SYS.AQ$_SUBSCRIBERS" - but we'd then need to go any find the definition of this.

USER@DB>desc system.AQ$_QUEUES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID                                       NOT NULL RAW(16)
 EVENTID                                   NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TABLE_OBJNO                               NOT NULL NUMBER
 USAGE                                     NOT NULL NUMBER
 ENABLE_FLAG                               NOT NULL NUMBER
 MAX_RETRIES                                        NUMBER
 RETRY_DELAY                                        NUMBER
 PROPERTIES                                         NUMBER
 RET_TIME                                           NUMBER
 QUEUE_COMMENT                                      VARCHAR2(2000)
 SUBSCRIBERS                                        SYS.AQ$_SUBSCRIBERS
 MEMORY_THRESHOLD                                   NUMBER
 SERVICE_NAME                                       VARCHAR2(64)
 NETWORK_NAME                                       VARCHAR2(256)





We can increase the default to anything up to the max of 50/all.

USER@DB>set describe depth all




Now when we descibe the table we see the extra details (NAME/ADDRESS/PROTOCOL).

USER@DB>desc system.AQ$_QUEUES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OID                                       NOT NULL RAW(16)
 EVENTID                                   NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TABLE_OBJNO                               NOT NULL NUMBER
 USAGE                                     NOT NULL NUMBER
 ENABLE_FLAG                               NOT NULL NUMBER
 MAX_RETRIES                                        NUMBER
 RETRY_DELAY                                        NUMBER
 PROPERTIES                                         NUMBER
 RET_TIME                                           NUMBER
 QUEUE_COMMENT                                      VARCHAR2(2000)
 SUBSCRIBERS                                        SYS.AQ$_SUBSCRIBERS
   NAME                                             VARCHAR2(30)
   ADDRESS                                          VARCHAR2(1024)
   PROTOCOL                                         NUMBER
 MEMORY_THRESHOLD                                   NUMBER
 SERVICE_NAME                                       VARCHAR2(64)
 NETWORK_NAME                                       VARCHAR2(256)





This seems to work to at least 4 levels down from tables we have in our databases.