Re: Using DBMS_METADATA/DBMS_REDFINITION to move all objects to another TS
Date: Thu, 20 Jun 2013 14:49:53 -0500 (CDT)
Message-ID: <bd9b3f4cfc321867e08b005508cfeafe.squirrel_at_society.servebeer.com>
>> Interesting, I ran into something very similar doing a project for a
>> client. I ended up pulling back the metadata and using the replace
>> function on the metadata to generate the new name. Just remember that you
>> have to be very specific about the string that is replaced or you can have
>> problems.
Here's what I'm testing:
v_ddl_clob := TRIM(TRANSLATE(REGEXP_REPLACE(v_ddl(i).ddlText,'" ON "ORIGTS"."','_RED" ON "ORIGTS"."'),CHR(10),' ')); v_ddl_clob := REGEXP_REPLACE(v_ddl_clob,'" \("','_RED" ("');
...where "v_ddl" is of the "sys.ku$_ddls" record type that DBMS_METADATA.FETCH_DDL returns, and the "ddlText" element is the CLOB of the DDL for the index.
The first REGEXP_REPLACE adds "_RED" (for redefined) on to the index name. This combination appears to be unique, given the TS name stuck in there. The TRIM/TRANSLATE compacts the statement -- yes, "PRETTY" is also false on the transform.
The second adds "_RED" to the index's table name, which a previous call to DBMS_METADATA was able to easily remap. Do I need to use REGEXP_REPLACE? Probably not. But it's flexibility is there should I hit an edge case.
This has worked in the dozen different tables I've tried. On to the other 3400!
Thanks all for the feedback and help!
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 20 2013 - 21:49:53 CEST