SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

dbms_metadata.get_ddl giving additional rows

MSKDec 18 2009 — edited Dec 21 2009

Hi
I am trying to save some index definitions before dropping them.
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name, u.index_owner)
from dba_ind_columns u
where index_name like 'XXDL%'
and table_name not like 'XXDL%';

but I get multiple records for each index defintion. Something like this

CREATE UNIQUE INDEX "PA"."XXDL_PA_TASKS_U3" ON "PA"."PA_TASKS" ("PROJECT_ID",
"TASK_ID")
PCTFREE 5 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 2129920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX"

CREATE UNIQUE INDEX "PA"."XXDL_PA_TASKS_U3" ON "PA"."PA_TASKS" ("PROJECT_ID",
"TASK_ID")
PCTFREE 5 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 2129920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX"

CREATE INDEX "PSP"."XXDL_PSP_ENC_LINES_N1" ON "PSP"."PSP_ENC_LINES" ("ASSIGNME
NT_ID", "PERSON_ID", "ENC_CONTROL_ID")
PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 9707520 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX"

CREATE INDEX "PSP"."XXDL_PSP_ENC_LINES_N1" ON "PSP"."PSP_ENC_LINES" ("ASSIGNME
NT_ID", "PERSON_ID", "ENC_CONTROL_ID")
PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 9707520 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX"

CREATE INDEX "PSP"."XXDL_PSP_ENC_LINES_N1" ON "PSP"."PSP_ENC_LINES" ("ASSIGNME
NT_ID", "PERSON_ID", "ENC_CONTROL_ID")
PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 9707520 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX"

What I am missing here? I want to just get one record for each index and spool the output

Thank you
MSK

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 18 2010

Added on Dec 18 2009

4 comments

6,763 views