| |
 |
|
ss
Oracle Tips by Burleson |
Chapter 8 Oracle Index
Internal Scripting
rem
COLUMN owner FORMAT a8 HEADING 'Index|Owner'
COLUMN index_name FORMAT a27 HEADING 'Index'
COLUMN index_type FORMAT a6 HEADING 'Type|Index'
COLUMN table_owner FORMAT a8 HEADING 'Table|Owner'
COLUMN table_name FORMAT a24 HEADING 'Table Name'
COLUMN table_type FORMAT a10 HEADING 'Table|Type'
COLUMN uniqueness FORMAT a1 HEADING 'U|n|i|q|u|e'
COLUMN tablespace_name FORMAT a13 HEADING 'Tablespace'
COLUMN column_name FORMAT a25 HEADING 'Col. Name'
SET PAGES 58 LINES 130 FEEDBACK OFF VERIFY OFF
BREAK ON owner
ttitle 'Expandeded Index Report'
SPOOL ind_exp.lis
SELECT
a.owner,
a.index_name,
a.index_type,
a.table_owner,
a.table_name,
a.table_type,
DECODE (a.uniqueness,
'UNIQUE', 'U','NONUNIQUE','N') uniqueness,
a.tablespace_name,
b.column_name
FROM
dba_indexes a, dba_ind_columns b
SEE CODE DEPOT FOR FULL SCRIPT
ORDER BY
owner, index_type;
SPOOL OFF
Here is a sample listing.
Index Type Table Table
u
Owner
Index Index Owner Table Name Type e
Tablespace Col Name
------ ----------------------- ------ ------ ------------- ----- -
---------- -------
SYSTEM
PK_TEST_IOT IOT - SYSTEM TEST_IOT TABLE U RAW_DATA
TEST1
TOP
SYS_IL0000001562C00035$ LOB SYSTEM DEF$_AQCALL TABLE U
SYSTEM
SYS_IL0000001571C00035$ LOB SYSTEM DEF$_AQERROR TABLE U
SYSTEM
SYS_IL0000001588C00005$ LOB SYSTEM DEF$_LOB
TABLE U SYSTEM
SYS_IL0000001597C00002$ LOB SYSTEM DEF$_TEMP$LOB
TABLE U SYSTEM
|
