Databases Reference
In-Depth Information
Recovering a Table into a Different Name
Problem
You want to recover a table, but a table with the same name already exists in the database.
Solution
Suppose you want to recover a table called ACCOUNTS but there is already a table with that same name. Thus, you
want to recover the table under a new name: ACCOUNTS_NEW. Ensure the prerequisites explained in Recipe 13-22.
As explained in that recipe, use the
recover table
command, but with a little addition: the
remap table
clause.
1.
Connect to RMAN:
$ rman
RMAN> connect target "/ as sysdba"
Recover the table but with
remap table
clause that creates a table with a different
name—ACCOUNTS_NEW:
2.
RMAN> recover table arup.accounts
2> until scn 1799975
3> auxiliary destination '+DG1'
4> remap table arup.accounts:accounts_new;
The remap clause at the end causes the table ACCOUNTS to be restored with the name ACCOUNTS_NEW.
How It Works
Recall from Recipe 13-22 that the recover command creates a temporary instance and recovers just the tablespaces
that are required for the table. The operation then exports the table and imports it into the main database. The
remap
table
clause injects the REMAP_TABLE option in the final import process to import the table into a new name. The
remap table
clause in the
recover
command does the trick of restoring the table in the new name.
Here is the syntax of the remap table clause:
remap table <Owner>.<OldTableName>:<NewTableName>
If you want to recover a partition of the table:
remap table <Owner>.<OldTableName>:<PartitionName>:<NewTableName>
So, had you wanted to recover a partition P1 of table ACCOUNTS to a new table called ACCOUNTS_P1_NEW, you
would have used the following clause instead:
remap table arup.accounts:p1:accounts_p1_new;
When you recover a partition, it always goes to a separate table.