Performance issue with dbms_metadata.get_ddl

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Tue, 16 Oct 2012 11:08:07 -0300
Message-ID: <CAJdDhaO3M0M5dcUwMVxm8iyudB+aAZandrSYQ2M72S+p+O+sQw_at_mail.gmail.com>

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; /

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 16 2012 - 16:08:07 CEST