export from 1 user & import to 2nd user in same db

Hi All

I am exporting from 1 user (user1) to another user(user2) in the
same database.
user1 uses tablespace (ts1) & user2 uses tablespace (ts2).
The probem i am facing is
after importing from user2 all objects are created in ts1 and not in
ts2 as I want it.

the options i used
1)
export from user1 (using user1 login)
import to user2(using user2 login)

2)
i tried taking export from system login
exp owner=user1
then imp touser=user2 from system login

In both options the tablespace used for the new data objects is ts1
and not ts2. How do i force the import into ts2

Regards
Sushant

The object definition in the export file will include the tablespace details, hence even when you change users the tablespace from the original DDL still exists so the object will be created there.

Remove any quota on the user2 user from the ts1 tablespace.

Code:

---

ALTER USER USER2 QUOTA 0K ON TS1;

---

HTH

Hi


I havent given any quota to user2 on tablespace1.

How do i exclusively abstain user2 from using ts1

Regards
Sushant

If you have granted RESOURCE or UNLIMITED TABLESPACE to a user then the user will be able to write to any tablespace, if you need to be selective (as in your example) you will need to revoke these privileges and assign the quotas manually.

Another way to perform the task would be to take the ts1 tablespace offline during the import, however this would mean that your user1 user would have no access to thier data.

HTH

Hi Jovery

Thanks for your prompt responses
WHenever we create users we specify

create user user2 identified by pwd default tablespace user2
temporary tablespace temp;

alter user user2 quota unlimited on ts2;

even with this can user2 still use ts1

Regards
Sushant

Works on mine:

Code:

---

SQL> create user test1
  2  identified by test1
  3  default tablespace users
  4  temporary tablespace temp;

User created.

SQL> alter user test1 quota unlimited on users;

User altered.

SQL> grant connect to test1;

Grant succeeded.

SQL> grant create table to test1;

Grant succeeded.

SQL> connect test1/test1@zoidberg
Connected.
SQL> create table tab_a (col_1 number)
  2  tablespace diags;
create table tab_a (col_1 number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'DIAGS'


SQL>  create table tab_a (col_1 number)
  2  tablespace users;

Table created.

---

If u are following instructions from Mr.jovery when the tablespace ts1 is taken offline he can't access any thing from this tablespace.

Taken an export of scotts objects (in users tablespace), revoke quota from test1 and define new default tablespace

Code:

---

SQL> alter user test1 quota 0k on users;

User altered.

SQL> alter user test1 quota unlimited on diags;

User altered.

SQL> alter user test1 default tablespace diags;

User altered.

---

Perform the import

Code:

---

$ imp userid=test1/test1 file=scott.dmp fromuser=scott touser=test1

Import: Release 9.2.0.3.0 - Production on Mon May 12 12:11:15 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and UTF8 NCHAR character set
. . importing table          "BONUS"          0 rows imported
. . importing table          "DEPT"          4 rows imported
. . importing table          "DUMMY"          1 rows imported
. . importing table          "EMP"        14 rows imported
. . importing table          "SALGRADE"          5 rows imported
Import terminated successfully without warnings.

---

See the objects as test1

Code:

---

SQL> connect test1/test1@zoidberg
Connected.

  1* select table_name, tablespace_name from user_tables
SQL> /

TABLE_NAME                    TABLESPACE_NAME
------------------------------ ------------------------------
BONUS                          DIAGS
DEPT                          DIAGS
DUMMY                          DIAGS
EMP                            DIAGS
SALGRADE                      DIAGS

---

Regards

Hi jovery

That fine. but now i get an error objects were exported by user1 not by you.(exp from user1 using user1 & imp into user2 using user2)

if i try to take an export from user2 it says you require dba privileges
to take an export from another user.

If i give the dba privilege it gets the privilege to write on ts1 .


Can you see a solution

It sounds like your trying to export user1's objects using user2, this is not the way to do it.

You perform the export as the owner of the objects and then perform the import as the new user with the FROMUSER= TOUSER= parameters.

The two users in my example do not have any DBA privileges.

Regards

Thanks Jovery

Your solution worked.

Its started working now. The only thing to change was to create the new tablespace after user1 's export from user1.

How does the timing of creating the tablespace after export of user1 objects affect exp-imp.

Previously i was doing the same thing but uisng an existing tablespace.

Regards
Sushant

The time of creation of the tablespace is irrelevant (both of the tablespaces in my example have existed since the creation of the database)

I can only assume you may have had a syntax error in your original trial.

Glad it works okay now. :)