Home » RDBMS Server » Server Administration » Determine Index Definition
Determine Index Definition [message #59295] Mon, 10 November 2003 14:12 Go to next message
Latesha Williams
Messages: 3
Registered: January 2003
Junior Member
How can I determine the columns and SQL query used to generate an index? When I right-click an index in OEM and select SHOW SQL, nothing is displayed.
Re: Determine Index Definition [message #59305 is a reply to message #59295] Tue, 11 November 2003 01:58 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
in 9i try something like this ... for older versions there other motheds.

mag@mutation_mutation > set long 500000000000000
mag@mutation_mutation >  SELECT DBMS_METADATA.GET_DDL('INDEX',I.INDEX_NAME) from user_indexes I
  2   where INDEX_NAME='DOCTOR_DOCTORID_PK'
  3  /

DBMS_METADATA.GET_DDL('INDEX',I.INDEX_NAME)
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "MAG"."DOCTOR_DOCTORID_PK" ON "MAG"."DOCTOR" ("DOCTORID",
"DOCTORDATE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"

Previous Topic: sga &redolog
Next Topic: How to set nls_date_format in oracle9i
Goto Forum:
  


Current Time: Fri Sep 20 15:38:16 CDT 2024