CODEWITHSUNDEEP
oracle-databasesqlplusdbms-metadata
parita porwal
parita porwal

Reputation: 672

Get complete ddl for index in oracle

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.

Upvotes: 2

Views: 16585

Answers (2)

BobC
BobC

Reputation: 4416

In SQLplus, set these before running the procedure.

set long 100000
set longchunksize 100000

Upvotes: 3

Brian
Brian

Reputation: 13571

Oracle has a DBMS_METADATA package to retrieve and customize the DDL returned. Default settings for all indexes SQL:

select DBMS_METADATA.GET_DDL('INDEX', index_name) 
from all_indexes 
where owner in (USER, 'USER_OTHER_THAN_LOGGED_IN_USER');

A pl/sql block example:

set serveroutput on 
DECLARE
   V_DDL CLOB;
BEGIN
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
   V_DDL := DBMS_METADATA.GET_DDL('VIEW', 'A_VIEW_NAME');
   DBMS_OUTPUT.PUT_LINE(V_DDL);
END;

Upvotes: 1

Related Questions