Oracle PL/SQL Tutorial/System Tables Data Dictionary/all tables

Материал из SQL эксперт
Перейти к: навигация, поиск

Get information on all the tables you have access to using all_tables

   <source lang="sql">

SQL> SQL> desc all_tables;

Name                             Null?    Type
OWNER                            NOT NULL VARCHAR2(30)
TABLE_NAME                       NOT NULL VARCHAR2(30)
TABLESPACE_NAME                           VARCHAR2(30)
CLUSTER_NAME                              VARCHAR2(30)
IOT_NAME                                  VARCHAR2(30)
STATUS                                    VARCHAR2(8)
PCT_FREE                                  NUMBER
PCT_USED                                  NUMBER
INI_TRANS                                 NUMBER
MAX_TRANS                                 NUMBER
INITIAL_EXTENT                            NUMBER
NEXT_EXTENT                               NUMBER
MIN_EXTENTS                               NUMBER
MAX_EXTENTS                               NUMBER
PCT_INCREASE                              NUMBER
FREELISTS                                 NUMBER
FREELIST_GROUPS                           NUMBER
LOGGING                                   VARCHAR2(3)
BACKED_UP                                 VARCHAR2(1)
NUM_ROWS                                  NUMBER
BLOCKS                                    NUMBER
EMPTY_BLOCKS                              NUMBER
AVG_SPACE                                 NUMBER
CHAIN_CNT                                 NUMBER
AVG_ROW_LEN                               NUMBER
AVG_SPACE_FREELIST_BLOCKS                 NUMBER
NUM_FREELIST_BLOCKS                       NUMBER
DEGREE                                    VARCHAR2(10)
INSTANCES                                 VARCHAR2(10)
CACHE                                     VARCHAR2(5)
TABLE_LOCK                                VARCHAR2(8)
SAMPLE_SIZE                               NUMBER
LAST_ANALYZED                             DATE
PARTITIONED                               VARCHAR2(3)
IOT_TYPE                                  VARCHAR2(12)
TEMPORARY                                 VARCHAR2(1)
SECONDARY                                 VARCHAR2(1)
NESTED                                    VARCHAR2(3)
BUFFER_POOL                               VARCHAR2(7)
ROW_MOVEMENT                              VARCHAR2(8)
GLOBAL_STATS                              VARCHAR2(3)
USER_STATS                                VARCHAR2(3)
DURATION                                  VARCHAR2(15)
SKIP_CORRUPT                              VARCHAR2(8)
MONITORING                                VARCHAR2(3)
CLUSTER_OWNER                             VARCHAR2(30)
DEPENDENCIES                              VARCHAR2(8)
COMPRESSION                               VARCHAR2(8)
DROPPED                                   VARCHAR2(3)

SQL> SQL> --select * from all_tables;</source>


Query the all_tables

   <source lang="sql">

SQL> SQL> desc all_tables;

Name                                     Null?    Type
-----------------------------------------
OWNER                                    NOT NULL VARCHAR2(30)
TABLE_NAME                               NOT NULL VARCHAR2(30)
TABLESPACE_NAME                                   VARCHAR2(30)
CLUSTER_NAME                                      VARCHAR2(30)
IOT_NAME                                          VARCHAR2(30)
STATUS                                            VARCHAR2(8)
PCT_FREE                                          NUMBER
PCT_USED                                          NUMBER
INI_TRANS                                         NUMBER
MAX_TRANS                                         NUMBER
INITIAL_EXTENT                                    NUMBER
NEXT_EXTENT                                       NUMBER
MIN_EXTENTS                                       NUMBER
MAX_EXTENTS                                       NUMBER
PCT_INCREASE                                      NUMBER
FREELISTS                                         NUMBER
FREELIST_GROUPS                                   NUMBER
LOGGING                                           VARCHAR2(3)
BACKED_UP                                         VARCHAR2(1)
NUM_ROWS                                          NUMBER
BLOCKS                                            NUMBER
EMPTY_BLOCKS                                      NUMBER
AVG_SPACE                                         NUMBER
CHAIN_CNT                                         NUMBER
AVG_ROW_LEN                                       NUMBER
AVG_SPACE_FREELIST_BLOCKS                         NUMBER
NUM_FREELIST_BLOCKS                               NUMBER
DEGREE                                            VARCHAR2(10)
INSTANCES                                         VARCHAR2(10)
CACHE                                             VARCHAR2(5)
TABLE_LOCK                                        VARCHAR2(8)
SAMPLE_SIZE                                       NUMBER
LAST_ANALYZED                                     DATE
PARTITIONED                                       VARCHAR2(3)
IOT_TYPE                                          VARCHAR2(12)
TEMPORARY                                         VARCHAR2(1)
SECONDARY                                         VARCHAR2(1)
NESTED                                            VARCHAR2(3)
BUFFER_POOL                                       VARCHAR2(7)
ROW_MOVEMENT                                      VARCHAR2(8)
GLOBAL_STATS                                      VARCHAR2(3)
USER_STATS                                        VARCHAR2(3)
DURATION                                          VARCHAR2(15)
SKIP_CORRUPT                                      VARCHAR2(8)
MONITORING                                        VARCHAR2(3)
CLUSTER_OWNER                                     VARCHAR2(30)
DEPENDENCIES                                      VARCHAR2(8)
COMPRESSION                                       VARCHAR2(8)
DROPPED                                           VARCHAR2(3)

SQL> SQL> select owner, table_name

 2  from all_tables
 3  where rownum < 30
 4  order by owner, table_name ;

OWNER TABLE_NAME


------------------------------

SYS ATTRCOL$ SYS BOOTSTRAP$ SYS CCOL$ SYS CDEF$ SYS CLU$ SYS COL$ SYS COLTYPE$ SYS CON$ SYS FET$ SYS FILE$ SYS ICOLDEP$ SYS LIBRARY$ SYS LOB$ SYS NTAB$ SYS OBJ$ SYS OPQTYPE$ SYS PROXY_DATA$ SYS PROXY_ROLE_DATA$ SYS REFCON$ SYS SEG$ SYS SUBCOLTYPE$ SYS TAB$ SYS TS$ SYS TSQ$ SYS TYPE_MISC$ SYS UET$ SYS UNDO$ SYS USER$ SYS VIEWTRCOL$ 29 rows selected.</source>