Re: Using DBMS_METADATA/DBMS_REDFINITION to move all objects to another TS

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Thu, 20 Jun 2013 13:00:23 -0700
Message-ID: <51C35F57.3080309_at_oracle.com>

Question: why are you moving data from your production database to another database? Could you use Partitioning + Compression + read-only tablespaces etc to accomplish the same goals? -KJ

*Kevin Jernigan* 	(650) 607-0392 (o)
*Senior Director Product Management* 	(415) 710-8828 (m)
kevin.jernigan_at_oracle.com <mailto:kevin.jernigan_at_oracle.com>
*Advanced Compression - ACO*: 	*Information Lifecycle Management* - ILM
   Advanced Row Compression 	*Temporal database* (Total Recall etc)
   Advanced LOB Compression 	*SecureFiles*
   Advanced LOB Deduplication 	*Database File System* - DBFS
   RMAN Backup Compression 	*Direct NFS Client* - dNFS
   Data Pump Export Compression 	*CloneDB*
   Data Guard Redo Network Transport Compression 	*Database Resource 
Manager* - DBRM
   Flashback Data Archive History Table Optimization 	*Continuous Query 
Notification* - CQN
*Hybrid Columnar Compression* - HCC 	*Index Organized Tables* - IOT
*Database Smart Flash Cache* 	*OISP*

On 6/20/2013 9:26 AM, Rich Jesse wrote:
> Howdy,
>
> In 11.2.0.3, we're archiving a good portion of data from Production ERP DB
> to Production Archive DB. All tables are in one tablespace, all indexes in
> another, as designed by 3rd party ERP. In order to recover the 100s of GB
> of disk post-archive, my plan is to:
>
> 1) Create new TS big enough to handle all tables and indexes.
> 2) Use DBMS_METADATA to create new tables and indexes in new TS
> 3) Use DBMS_REDEFINITION to online move data to new TS
> 4) Drop/recreate old TS
> 5) Use DBMS_METADATA to create new tables and indexes in original TSs
> 6) Use DBMS_REDEFINITION to online move data to original TSs
> 7) Drop new TS
>
> The reason I need to use DBMS_METADATA is for the requirement to move the
> tables and indexes to a new TS. DBMS_REDEFINITION will not do that.
>
> The problem I'm having is how to remap each index name to a new name in
> DBMS_METADATA. Basically, I'm trying to use this to grab the remapped DDL
> for each index on a given table:
>
> v_idx_handle := DBMS_METADATA.OPEN('INDEX');
> DBMS_METADATA.SET_FILTER(v_idx_handle, 'BASE_OBJECT_SCHEMA', v_schema);
> DBMS_METADATA.SET_FILTER(v_idx_handle, 'BASE_OBJECT_NAME', v_source_table);
> v_ddl_xform := DBMS_METADATA.ADD_TRANSFORM(v_idx_handle,'MODIFY');
> DBMS_METADATA.SET_REMAP_PARAM(v_ddl_xform, 'REMAP_TABLESPACE',
> v_source_idx_ts, v_dest_ts);
> --DBMS_METADATA.SET_REMAP_PARAM(v_ddl_xform, 'REMAP_NAME', ???, ???||'_NEW');
> v_ddl_xform := DBMS_METADATA.ADD_TRANSFORM(v_idx_handle,'DDL');
> LOOP
> v_ddl := DBMS_METADATA.FETCH_DDL(v_idx_handle);
> ...
>
> But how does one remap the name in the transform (commented out) when it's
> not known until it's DDL is fetched? Or am I looking at some regexp in the
> post-FETCH_DDL?
>
> Thanks!
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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

Received on Thu Jun 20 2013 - 22:00:23 CEST