Re: List table's indexes !

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 18 Jan 2000 22:17:38 -0000
Message-ID: <948234038.26332.0.nnrp-04.9e984b29@news.demon.co.uk>

Your script is showing its age, Thomas ;)

8.1.5 (if not sooner) allows up to 32 columns in an index definition. (Not that I would suggest that anyone is likely to use that many).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Thomas Kyte wrote in message ...
>A copy of this was sent to Pascal-Eric Servais <servpas_at_cognicase.ca>
>(if that email address didn't require changing)
>On Tue, 18 Jan 2000 20:56:12 GMT, you wrote:
>
>>Hi !
>>I am a little green with Oracle DB and working on a "rush" project !
>>(same as usual ;)
>>
>>I just can't figure out how to list all the indexes that are
>>associated to an Oracle table with SQL*Plus.
>>Is anybody could help about that.
>>
>>BTW : If you are aware of any link to good online manuals, like some
>>"Getting started with Oracle", I'd appreciate very much.
>>
>>Thanks !
>>
>>Pascal-Eric.
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Before you buy.
>
>
>this script might help you out. save it to a file called 'desc'. Then you
can
>do something like:
>
>
>ops$tkyte_at_8i> @desc emp
>Datatypes for Table emp
>
> Data Data
>Column Name Type Length
>Nullable
>------------------------------ ------------------------------ --------- ---
-
>EMPNO NUMBER (4,0) not
null
>ENAME VARCHAR2 10
null
>JOB VARCHAR2 9
null
>MGR NUMBER (4,0)
null
>HIREDATE DATE 7
null
>SAL NUMBER (7,2)
null
>COMM NUMBER (7,2)
null
>DEPTNO NUMBER (2,0)
null
>CATEGORY VARCHAR2 30
null
>
>
>Indexes on emp
>
>Index Is
>Name Unique Indexed Columns
>------------------------------ ------ -----------------------------------
>EMP_IDX1 No SAL
>EMP_PK Yes EMPNO
>
>
>
>
>--------------------------------- script

desc.sql ---------------------------

>
>set verify off
>set pagesize 9999
>set feedback off
>
>variable owner varchar2(30)
>variable tname varchar2(30)
>begin
> :owner := USER;
> :tname := upper('&1');
>end;
>/
>
>Prompt Datatypes for Table &1
>
>column data_type format a30
>column column_name heading "Column Name"
>column data_type heading "Data|Type"
>column data_length heading "Data|Length"
>column nullable heading "Nullable"
>
>select column_name,
> data_type,
> substr(
> decode( data_type, 'NUMBER',
> decode( data_precision, NULL, NULL,
> '('||data_precision||','||data_scale||')' ), data_length),
> 1,11) data_length,
> decode( nullable, 'Y', 'null', 'not null' ) nullable
>from all_tab_columns
>where owner = :owner
> and table_name = :tname
>order by column_id
>/
>
>
>prompt
>prompt
>Prompt Indexes on &1
>column index_name heading "Index|Name"
>column Uniqueness heading "Is|Unique" format a6
>column columns heading "Indexed Columns" format a35 word_wrapped
>
>select substr(a.index_name,1,30) index_name,
> decode(a.uniqueness,'UNIQUE','Yes','No') uniqueness,
>max(decode( b.column_position, 1, substr(b.column_name,1,30), NULL )) ||
>max(decode( b.column_position, 2, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 3, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 4, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 5, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 6, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 7, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 8, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 9, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 10, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 11, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 12, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 13, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 14, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 15, ', '||substr(b.column_name,1,30),
NULL )) ||
>max(decode( b.column_position, 16, ', '||substr(b.column_name,1,30),
NULL ))
>columns
>from all_indexes a, all_ind_columns b
>where a.owner = :owner
>and a.table_name = :tname
>and b.table_name = a.table_name
>and b.table_owner = a.owner
>and a.index_name = b.index_name
>group by substr(a.index_name,1,30), a.uniqueness
>/
>
>--------------------------------- eof ---------------------------
>
>
>--
>See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
>Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
>Thomas Kyte tkyte_at_us.oracle.com
>Oracle Service Industries Reston, VA USA
>
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Tue Jan 18 2000 - 16:17:38 CST