Re: Using DBMS_METADATA/DBMS_REDFINITION to move all objects to another TS
- From: Kevin Jernigan <kevin.jernigan@xxxxxxxxxx>
- To: rjoralist2@xxxxxxxxxxxxxxxxxxxxx
- Date: Thu, 20 Jun 2013 13:00:23 -0700
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@xxxxxxxxxx <mailto:kevin.jernigan@xxxxxxxxxx>
*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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts: