Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Extract Index Definition from Oracle

SOLVED
Go to solution
Volker Borowski
Honored Contributor

Extract Index Definition from Oracle

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
8 REPLIES
Peter Godron
Honored Contributor

Re: Extract Index Definition from Oracle

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

Re: Extract Index Definition from Oracle

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
Honored Contributor

Re: Extract Index Definition from Oracle

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
Honored Contributor

Re: Extract Index Definition from Oracle

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
Respected Contributor
Solution

Re: Extract Index Definition from Oracle

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
Honored Contributor

Re: Extract Index Definition from Oracle

Patti,

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

Good night
Volker
Leon Allen
Regular Advisor

Re: Extract Index Definition from Oracle

Also....

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

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

Cheers!

Time's fun when your having flys (ancient frog saying)
Volker Borowski
Honored Contributor

Re: Extract Index Definition from Oracle

Excellent Solution by Patti !
Closing thread.
Volker