Extract Index Definition from Oracle

[Volker Borowski]

Hi,

how do I extract the exact "CREATE INDEX" Statement for a function based index ?

I tried this:

select substr ( INDEX_NAME||':'||COLUMN_NAME,1,60),DESCEND
from DBA_IND_COLUMNS where INDEX_NAME like '&index_like_value%'
order by INDEX_NAME,COLUMN_POSITION;

Which runs like:

SQL> select substr ( INDEX_NAME||':'||COLUMN_NAME,1,60),DESCEND
from DBA_IND_COLUMNS where INDEX_NAME like '&index_like_value%'
order by INDEX_NAME,COLUMN_POSITION;
Enter value for index_like_value: /BIC/AY04_WP_I00%
old 2: from DBA_IND_COLUMNS where INDEX_NAME like '&index_like_value%'
new 2: from DBA_IND_COLUMNS where INDEX_NAME like '/BIC/AY04_WP_I00%%'

/BIC/AY04_WP_I0004:SOURSYSTEM ASC
/BIC/AY04_WP_I0004:/BIC/Y04GRANST ASC
/BIC/AY04_WP_I0004:/BIC/Y04HESYS ASC
/BIC/AY04_WP_I0004:/B20/S_C43CLACC ASC
/BIC/AY04_WP_I0004:/B20/S_C41FINST ASC
/BIC/AY04_WP_I00~0:SOURSYSTEM ASC
/BIC/AY04_WP_I00~0:/B20/S_C30POS ASC
/BIC/AY04_WP_I00~0:/BIC/AO_VER_VF ASC
/BIC/AY04_WP_I00~0:/BIC/AO_SYS_VF ASC
/BIC/AY04_WP_I00~0:/BIC/AO_VAL_VF ASC
/BIC/AY04_WP_I00~ZV8:SOURSYSTEM ASC
/BIC/AY04_WP_I00~ZV8:/BIC/AO_PACKNO ASC
/BIC/AY04_WP_I00~ZV8:/B20/S_C41FINST ASC
/BIC/AY04_WP_I00~ZV8:SYS_NC00029$ DESC
/BIC/AY04_WP_I00~ZV9:SOURSYSTEM ASC
/BIC/AY04_WP_I00~ZV9:/BIC/AO_PACKNO ASC
/BIC/AY04_WP_I00~ZV9:SYS_NC00029$ DESC

17 rows selected.

Elapsed: 00:00:00.16
SQL>


Now I like to identify my reverse ordered columns by name, i.E "SYS_NC00029$"

I scaned a lot of DBA_ Tables, but did not find it yet ?

Volker

[Peter Godron]

Volker,
unless a script is absolutely required, you could use TOAD.

[Volker Borowski]

Peter,

Toad will surely work, but I am not permitted to install it right here.

Got a (not so good) solution meanwhile:
Doing an export of the table with
rows=n
indexes=y
constraints=n
grants=n
triggers=n
gives the complete CREATE INDEX statement in the dump, allthough it is a bit garbled.

Since I do not like to use EXP all the time in production, still a rabit to win for the dictionary table that contains the required information.

Volker

[Peter Godron]

Volker,
I had another snoop and found:
http://oracle.ittoolbox.com/code/archives.asp?d=2117&a=s&i=10

Notes:
Check what your utl_file_dir is.
Customize the select by replacing schema_name and adding your index_name

I had to run as sys
connect / as sysdba
@c1.sql
exec CREATE_SQL_FOR_INDICES

Produced a script like:
CREATE UNIQUE INDEX APPLY$_ERROR_HANDLER_UNQ ON APPLY$_ERROR_HANDLER(RESOLUTION_ID) TABLESPACE SYSTEM;

[Volker Borowski]

Peter,

thanks, but this script is way too poor.

It does not care about
- multi-column-indexes
-- order of multi-column index-columns
- ascending/descending columns

I searched half of google before I asked here. Lots of stuff out there, but also a lot of faulty or at least incomplete stuff.

Volker

[Patti Johnson · Accepted solution]

Try the dbms_metadata procedure.
For example.
create table pdj_test(fld number );
create index pdj_test_f1 on pdj_test (to_char(fld));



select dbms_metadata.GET_DDL('INDEX','PDJ_TEST_F1','SYSTEM') from dual;

---- Handles function based indexes. ---

CREATE INDEX "SYSTEM"."PDJ_TEST_F1" ON "SYSTEM"."PDJ_TEST" (TO_CHAR("FLD"))
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

[Volker Borowski]

Patti,

that pretty much seems to nail it perfectly!
Will try out tomorrow morning !

Good night
Volker

[Leon Allen]

Also....

exp system/manager indexfile=myindexes.txt owner = scott

This will produce a better formatted output file containing all the create index statements.

Cheers!

[Volker Borowski]

Excellent Solution by Patti !
Closing thread.
Volker