RMAN
Using remap_tablespace to Import Objects to a Different Tablespace
Being a DBA, there are plenty of times when you have to move
the objects around the tablespaces to get them reorganized. This
can be done in many ways; for example, using the alter table move
command or the dbms_redefinition package. One of the easy ways to
do so is to the use the remap_tablespace switch. As the name
suggests, this option moves the object from the source tablespace
to the target tablespace. To see this in action, make a table and
explicitly create some fragmentation in it.
test@orcl112> create table t as
select * from dba_objects;
table created.
test@orcl112> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL procedure successfully completed.
test@orcl112> select sum(bytes) , sum(blocks) from dba_extents
where segment_name='t';
SUM(BYTES) SUM(BLOCKS)
---------- -----------
9568256 1168
1 row selected.
test@orcl112> delete t;
72615 rows deleted.
test@orcl112> commit;
commit complete.
test@orcl112> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL procedure successfully completed.
test@orcl112> select sum(bytes) , sum(blocks) from dba_extents
where segment_name='t';
SUM(BYTES) SUM(BLOCKS)
---------- -----------
9568256 1168
1 row selected.
So you can see that even after the complete delete, the table
does not release the used space and it is reported again.
Check which tablespace the object belongs at the moment.
test@orcl112> select table_name,
tablespace_name from dba_tables where table_name='t' and
owner='test';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T USERS
1 row selected.
This shows that it is the users tablespace at the moment.
Though not good but for the sake of this demonstration, move the
table to the system tablespace using Data Pump. Export the table
first:
E:\>expdp
test/test directory=dp dumpfile=exptab.dmp tables=T
Now use the import command and the remap_tablespaceswitch to move the object into a different tablespace.
Also, since the table is already there in the schema, you have to
use the switch table_exists_action with its value replace. Check
it out in action:
E:\>impdp
test/test directory=dp dumpfile=exptab.dmp table_exists_action=replace
remap_tablespace=users:system
Check the tablespace of the object to see whether it has
changed or not:
SQL>
select
tablespace_name, table_name
from
dba_tables
where
table_name='t' and owner='test';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYSTEM T
Using table_exists_action Parameter
When importing a table, Data Pump skips the job if the object
already exists in the imported schema. However, by using the
table_exists_action parameter
with its available values, it is possible to bypass skipping the
table import. This parameter accepts the following values:
-
skip: Is
the default value and used to skip importing the table if it
already exists
-
append:
Appends the data to an already existing table
-
truncate:
Truncates the already existing table and imports fresh data
-
replace:
Drops already an existing table and creates a new one with fresh
data
The next example demonstrates the usage of the above values.
For this, create a table with one row:
SQL>
create
table tbl_test (id number);
Table created.
SQL>
insert into
tbl_test values(1);
1 row created.
SQL>
commit;
Commit complete.
SQL>
Now export the table:
expdp usr1/usr1
directory=dp_dir dumpfile=test.dmp tables=tbl_test
Using the skip value:
skip is the default value and it will just abort the process by
saying that the table already exists.
impdp usr1/usr1 directory=dp_dir
dumpfile=test.dmp table_exists_action=skip
ora-39151: table "usr1"."tbl_test" exists. All dependent
metadata and data will be skipped due to table_exists_action of
skip
Using the append value:
C:\>impdp usr1/usr1 directory=dp_dir
dumpfile=test.dmp table_exists_action=append
ORA-39152: Table "usr1"."tbl_test" exists. Data will be appended
to existing table but all dependent metadata will be skipped due
to table_exists_action of append
Now check the table. Since import used the append value for the
table_exists_action parameter, there should be two rows:
SQL>
select * from
tbl_test;
ID
----------
1
1
Using truncate and replace values:
Both values give the same result; however, the first just
truncates the table, where the second value drops and recreates
it. It is shown here in one example:
C:\>impdp usr1/usr1 directory=dp_dir
dumpfile=test.dmp table_exists_action=truncate
ora-39153: table "usr1"."tbl_test" exists and has been
truncated. Data will be loaded but all dependent metadata will
be skipped due to table_exists_action of truncate
C:\>impdp
usr1/usr1 directory=dp_dir dumpfile=test.dmp table_exists_action=replace
The replace value will not give an informative message and just
drops and recreates the table with new values.
