A recent forums question prompted me to write this post as it may be useful to others.
The scenario is that you have 2 (or more) schemas who all share the same tablespace xx in the 'source' database. When this is loaded into another database however we want to keep the objects for schema xx in tablespace xx but for schema xy we want to load them into xy (i.e. move them out of xx). With a default remap_tablespace this is not possible as all objects get remapped - there is not an 'apply just to this schema' option - well at least not without running the import multiple times.
So how can we do this?
Here is a simple walk through example:
First up we create users xx and xy who have all their objects in the same xx tablespace.
SYS@DB>create user xx identified by xx default tablespace xx;
User created.
SYS@DB>alter user xx quota unlimited on xx;
User altered.
SYS@DB>grant create table to xx;
Grant succeeded.
SYS@DB>create user xy identified by xy default tablespace xx;
User created.
SYS@DB>alter user xx quota unlimited on xx;
User altered.
SYS@DB>grant create table to xx;
Grant succeeded.
SYS@DB>create table xx.tab1(col1 number);
Table created.
SYS@DB>create table xy.tab1(col1 number);
Table created.
SYS@DB>
A simple query shows us that both tables for both schemas are in tablespace xx.
SYS@DB>select table_name,tablespace_name from dba_tables where owner in ('XX','XY');
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TAB1 XX
TAB1 XX
Now we unload everything for those schemas
expdp / schemas=xx,xy reuse_dumpfiles=y
Export: Release 11.2.0.3.0 - Production on Mon Oct 20 17:51:10 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_08": /******** schemas=xx,xy reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "XX"."TAB1" 0 KB 0 rows
. . exported "XY"."TAB1" 0 KB 0 rows
Master table "OPS$ORACLE"."SYS_EXPORT_SCHEMA_08" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_SCHEMA_08 is:
/oracle/11.2.0.3.0.DB/rdbms/log/expdat.dmp
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_08" successfully completed at 17:51:57
Now in our destination database we pre-create the users and give them different default tablespaces (with appropriate quota)
SYS@DB>create user xx identified by xx default tablespace xx;
User created.
SYS@DB>grant create table to xx;
Grant succeeded.
SYS@DB>alter user xx quota unlimited on xx;
User altered.
SYS@DB>create user xy identified by xy default tablespace xy;
User created.
SYS@DB>grant create table to xy;
Grant succeeded.
SYS@DB>alter user xy quota unlimited on xy;
User altered.
Now comes the trick - the transform option specified here allows us to strip off the tablespace clause and let the objects get created in the users default tablespace
impdp / schemas=xx,xy exclude=USER transform=segment_attributes:n
Import: Release 11.2.0.3.0 - Production on Mon Oct 20 17:54:10 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "OPS$ORACLE"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_SCHEMA_01": /******** schemas=xx,xy exclude=USER transform=segment_attributes:n
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "XX"."TAB1" 0 KB 0 rows
. . imported "XY"."TAB1" 0 KB 0 rows
Job "OPS$ORACLE"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:54:11
Now a quick query shows us the objects have moved
SYS@DB>select table_name,tablespace_name from dba_tables where owner in ('XX','XY');
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TAB1 XX
TAB1 XY
Useful sometimes i guess - mainly if you have a lot of schemas to move from source to destination and you want to do this.
Comments
Post a Comment