Performance issue with dbms_metadata.get_ddl
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Oct 2012 11:08:07 -0300
Hi Friends,
I am running the pl/sql block below and it is too slow.
This query returns only 266 lines.
SELECT index_name
FROM user_indexes
WHERE index_type = 'DOMAIN'
Any idea ?
-- start of script
DECLARE
vddl VARCHAR2(4000);
vobjname VARCHAR2(30);
vsql VARCHAR2(400);
vstring VARCHAR2(4000);
vwhat VARCHAR2(200);
vinterval VARCHAR2(4000);
BEGIN
DELETE FROM TMP_EXP_TBD_SSG;
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,
'PRETTY', TRUE );
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,
'SQLTERMINATOR', true );
FOR l IN (SELECT index_name
FROM user_indexes
WHERE index_type = 'DOMAIN')
LOOP
SELECT dbms_metadata.get_ddl('INDEX', l.index_name)
INTO vddl
FROM dual;
INSERT INTO TMP_EXP_TBD_SSG (TYPE, STATMENT)
VALUES (1, VDDL);
COMMIT;
END LOOP;
END;
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
