Oracle Technologies Blog

By ASKM

exporting objects to different schema with different tablespace

Posted by Srikrishna Murthy Annam on August 22, 2010

Assume that we have some objects in a schema TEST1 with default tablespace TS1. Suppose if there is an application running on this database which requires these objects to be in two different schemas.
Say the second schema is TEST2 with default tablespace TS2.
Now we have to move some objects from TEST1 schema to TEST2 schema.

Consider the following syntax ..

SQL>alter table TEST1.TABLE1 rename to TEST2.TABLE1; ==> Syntax error.
sql>alter table TEST1.TABLE1 move tablespace TS2; ==> This moves the object to tablespace TS2, but still in schema TEST1.

Does export and import work ?   Lets try ….

$exp system/manager file=TABLE1.dmp tables=TEST1.TABLE1 log=exp_TABLE1.log
$imp system/manager file=TABLE1.dmp fromuser=TEST1 touser=TEST2 tables=TABLE1 log=imp_TABLE1.log

This should actually move the table TABLE1 from TEST1 to TEST2.
But if you observe the table TABLE1 after the import it is in schema TEST2 which is ok but tablespace is still TS1. WHY ?
The user TEST2 has default tablespace TS2, so when we import the table it should be created in TS2 tablespace. Why is it created in TS1?

Then what is the solution to move an object to TEST2 with tablespace TS2. !!!!!!!

Follow the following steps to move the objects to different schema with different tablespace.

SQL>alter user TEST2 quota 0 on TS1 quota unlimited on TS2;
SQL>revoke unlimited tablespace from TEST2;

$imp system/manager file=TABLE1.dmp fromuser=TEST1 touser=TEST2 tables=TABLE1 log=imp_TABLE1.log

Then verify the object schema and tablespace. It should be TEST2 with TS2.

Hope it helps ….

— SRI


Advertisements

3 Responses to “exporting objects to different schema with different tablespace”

  1. Hi Srikrishna ,

    I tried the above method but unsuccessful i’m getting the below error

    Export file created by EXPORT:V11.02.00 via conventional path
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    . importing TEST1’s objects into TEST2
    . . importing table “TEST1”
    IMP-00058: ORACLE error 1536 encountered
    ORA-01536: space quota exceeded for tablespace ‘TEST1’
    Import terminated successfully with warnings.

    regards
    amit

  2. learnwithme11g said

    Hi Amit,
    Please give me output for
    select username,default_tablespace from dba_users where username in (‘TEST1′,’TEST2’);
    select property_value from database_properties where property_name = ‘default_permanent_tablespace’;

    Thanks
    SRI

  3. amitnkrocks said

    SQL> select username,default_tablespace from dba_users where username in (‘TEST1′,’TEST2’);

    USERNAME DEFAULT_TABLESPACE
    —————————— ——————————
    TEST2 TEST2
    TEST1 TEST1

    SQL> select property_value from database_properties where property_name = ‘default_permanent_tablespace’;

    no rows selected

    both the users have connect and resource roles….

    regards

    Amit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: