You probably know already that you can describe a table’s column structure with the ‘describe’ or ‘desc’ keywords:
SQL> describe dba_tables
SQL> desc dba_tables
But what if you don’t know the name of the table you want to describe? When you’re working with user data this isn’t typically an issue, but when you’re working with the data dictionary (DBA_) and performance views (v$_) you might not always know the name of the table you want to query. For example, I wanted to know how many paritions were in a particular table but DBA_TABLES doesn’t have that kind of information. You can actually query DICT or DICTIONARY to find out. DICT/DICTIONARY is like DBA_TABLES but for the data dictionary tables and performance views. If you describe it:
SQL> desc dict
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
you can see that it stores the table name and a short description. So when I want to find out about partitions “hm, the table name probably has partition in it…”
SQL> select table_name from dict where table_name like '%PARTITION%' order by 1;
TABLE_NAME
------------------------------
ALL_IND_PARTITIONS
ALL_IND_SUBPARTITIONS
ALL_LOB_PARTITIONS
ALL_LOB_SUBPARTITIONS
ALL_MVIEW_DETAIL_PARTITION
ALL_MVIEW_DETAIL_SUBPARTITION
ALL_SUBPARTITION_TEMPLATES
ALL_TAB_PARTITIONS
ALL_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
TABLE_NAME
------------------------------
DBA_LOB_PARTITIONS
DBA_LOB_SUBPARTITIONS
DBA_MVIEW_DETAIL_PARTITION
DBA_MVIEW_DETAIL_SUBPARTITION
DBA_SUBPARTITION_TEMPLATES
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
USER_LOB_PARTITIONS
USER_LOB_SUBPARTITIONS
TABLE_NAME
------------------------------
USER_MVIEW_DETAIL_PARTITION
USER_MVIEW_DETAIL_SUBPARTITION
USER_SUBPARTITION_TEMPLATES
USER_TAB_PARTITIONS
USER_TAB_SUBPARTITIONS
27 rows selected.
One of the table_name values returned is DBA_TAB_PARTITIONS. Now I can describe that table:
SQL> desc dba_tab_partitions
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(18)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
And find out the number of partitions in my table:
SQL> select count(*) from dba_tab_partitions where table_owner='CHARLOTTE' and table_name='EVENT';
COUNT(*)
----------
38
Oracle Bits 