Display Tables and Corresponding Information on IndicesThis is a featured page

set lines 110 pages 500
col tblo format a10 hea 'Owner'
col tbln format a25 hea 'Table'
col indx format a30 hea 'Index'
col ityp format a3 hea 'Typ'
col comp format a1 hea 'C|O|M|P'
col logg format a1 hea 'L|O|G|G'
col part format a1 hea 'P|A|R|T'
col safe format a1 hea 'S|A|F|E'
col temp format a1 hea 'T|E|M|P'
col uniq format a1 hea 'U|N|I|Q'
break on tblo on tbln

select table_owner tblo, table_name tbln, index_name indx,
decode(INDEX_TYPE,'NORMAL','NML','BITMAP','BMP','IOT - TOP','IOT','DOMAIN','DOM','LOB','LOB',
'FUNCTION-BASED BITMAP','FBB','FUNCTION-BASED NORMAL','FBN',INDEX_TYPE) ityp,
decode(COMPRESSION, 'ENABLED', 'Y','DISABLED','N','COMPRESSION') comp,
decode(LOGGING,'YES', 'Y', 'NO','N', LOGGING) logg,
decode(PARTITIONED,'YES', 'Y', 'NO','N', PARTITIONED) part,
decode(STATUS,'VALID', 'Y', 'INVALID','N', STATUS) safe,
decode(TEMPORARY,'Y', 'Y', 'N','N', TEMPORARY) temp,
decode(UNIQUENESS, 'UNIQUE','Y','NONUNIQUE', 'N', UNIQUENESS) uniq
from all_indexes where not TABLESPACE_NAME in ('SYS','SYSAUX')
order by table_owner, table_name, index_name;
....................................................................... C L P S T U
....................................................................... O O A A E N
....................................................................... M G R F M I
Owner......Table.....................Index..........................Typ P G T E P Q
---------- ------------------------- ------------------------------ --- - - - - - -
HR.........COUNTRIES.................COUNTRY_C_ID_PK................IOT N Y N Y N Y
...........DEPARTMENTS...............DEPT_ID_PK.....................NML N Y N Y N Y
.....................................DEPT_LOCATION_IX...............NML N Y N Y N N
...........EMPLOYEES.................EMP_DEPARTMENT_IX..............NML N Y N Y N N
.....................................EMP_EMAIL_UK...................NML N Y N Y N Y
.....................................EMP_EMP_ID_PK..................NML N Y N Y N Y
.....................................EMP_JOB_IX.....................NML N Y N Y N N
.....................................EMP_MANAGER_IX.................NML N Y N Y N N
.....................................EMP_NAME_IX....................NML N Y N Y N N
...........JOBS......................JOB_ID_PK......................NML N Y N Y N Y
...........JOB_HISTORY...............JHIST_DEPARTMENT_IX............NML N Y N Y N N
.....................................JHIST_EMPLOYEE_IX..............NML N Y N Y N N
.....................................JHIST_EMP_ID_ST_DATE_PK........NML N Y N Y N Y
.....................................JHIST_JOB_IX...................NML N Y N Y N N
...........LOCATIONS.................LOC_CITY_IX....................NML N Y N Y N N
.....................................LOC_COUNTRY_IX.................NML N Y N Y N N
.....................................LOC_ID_PK......................NML N Y N Y N Y
.....................................LOC_STATE_PROVINCE_IX..........NML N Y N Y N N
...........REGIONS...................REG_ID_PK......................NML N Y N Y N Y
PM.........ONLINE_MEDIA..............ONLINEMEDIA_PK.................NML N Y N Y N Y
...........PRINT_MEDIA...............PRINTMEDIA_PK..................NML N Y N Y N Y
.....................................SYS_C003010....................NML N Y N Y N Y
SH.........CAL_MONTH_SALES_MV........I_SNAP$_CAL_MONTH_SALES_MV.....FBN N Y N Y N Y
...........CHANNELS..................CHAN_PK........................NML N Y N Y N Y
...........COUNTRIES.................COUNTRY_PK.....................NML N Y N Y N Y
...........CUSTOMERS.................CUSTOMERS_GENDER_BIX...........BMP N N N Y N N
...

Columns.Explained:

TYP........=.Type: FUNCTION-BASED BITMAP (FBB) or IOT – TOP (IOT) or DOMAIN (DOM) or
.................. FUNCTION-BASED NORMAL (FBN) or BITMAP (BMP) or NORMAL (NML) or LOB (LOB)
COMP.......= Compression: Yes (Y) or No (N)
LOGG.......= Logging: Yes (Y) or No (N)
PART.......= Partitioned: Yes (Y) or No (N)
SAFE.......= Status: Valid (Y) or Invalid (N)
TEMP.......= Temporary: Yes (Y) or No (N)
UNIQ.......= Uniqueness: Unique (Y) or Nonunique (N)



olapdba
olapdba 		Latest page update: made by olapdba , Jan 10 2008, 10:31 AM EST (about this update About This Update olapdba Edited by olapdba


view changes
- complete history)
Keyword tags: None
More Info: links to this page

Threads for this page

There are no threads for this page.  Be the first to start a new thread.