Get complete ddl for index in oracle

oracle sqlplus dbms-metadata

parita porwal · Feb 10, 2017 · Viewed 7.5k times · Source

I am using oracle 11g/12c. I want to get ddl of indexes in my database. For this I used the query -

SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000091971C00001$$','CCEEXPERTS') FROM dual

Here 'SYS_IL0000091971C00001$$' is my index name and 'CCEEXPERTS' is my owner name.

From this I get the ddl -

 CREATE UNIQUE INDEX "CCEEXPERTS"."SYS_IL0000091971C00001$$" ON "CCEEXPERTS"."DATABLOB" (

And my actual ddl is -

CREATE UNIQUE INDEX "CCEEXPERTS"."SYS_IL0000091971C00001$$" ON "CCEEXPERTS"."DATABLOB" (
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  PARALLEL (DEGREE 0 INSTANCES 0) ;

In actual ddl after "CCEEXPERTS"."DATABLOB" ( , next line character and from their the ddl is truncted.

How can I get the complete ddl? Please help me...

Thanks in advance.

Answer

In SQLplus, set these before running the procedure.

set long 100000
set longchunksize 100000

Get complete ddl for index in oracle

Answer

Related questions