Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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 



 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks