Re: Performance issue with dbms_metadata.get_ddl
- From: Brent Day <coloradodba@xxxxxxxxx>
- To: ecandrietta@xxxxxxxxx
- Date: Tue, 16 Oct 2012 09:48:28 -0600
In the future I would recommend you provide more information about your
environment, some sample output/timings and what you consider too slow.
Without this information you may not get a response.
I took your code and ran it in a fairly large development system and for my
test schema it generated 287 rows in 6 seconds.
Thans,
Brent
On Tue, Oct 16, 2012 at 8:08 AM, Eriovaldo Andrietta
<ecandrietta@xxxxxxxxx>wrote:
> 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
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
