Tablespace Problem [message #49769] |
Fri, 08 February 2002 03:15 |
Milind S Deobhankar
Messages: 33 Registered: January 2002
|
Member |
|
|
I had one user abc whose default tablespace is System i had imported the full user abc. But now I want to move the data to another tablespace xyz. Both are on different oracle servers.
Follwing steps i had carried out:
1) From serverno1 I had taken the full dump of the user abc, having default tablespace System.
2) I had created user abc in serverno2 and assign default tablespace xyz to it.
3) The problem is then when i imports the dump the table which were already there in the serverno1 uses the System tablespace and the tables which i create new are using xyz table space.
So please can any body help how to move the table and its data from System tablespace to XYZ tablespace for the abc user only.
If furthur clarification is required then please mail me back.
Thanks in advance.
|
|
|
Re: Tablespace Problem [message #49771 is a reply to message #49769] |
Fri, 08 February 2002 04:05 |
Sanjay Bajracharya
Messages: 279 Registered: October 2001 Location: Florida
|
Senior Member |
|
|
What you are doing is OK. Here are some points.
1. Make sure that user abc DOES NOT have any access to tablespace system in Server2. Default tablespace is xyz, but user can have access to other ts also.
2. Import the table logging in as user abc itself. You may have to grant extra role to abc for it to be able to do an import. DO NOT do the import as sys or system.
That should pretty much take care of it.
|
|
|
Re: Tablespace Problem [message #49774 is a reply to message #49769] |
Fri, 08 February 2002 06:01 |
Grant
Messages: 578 Registered: January 2002
|
Senior Member |
|
|
Do not give RESOURCE it gives UNLIMITED TABLESPACE system privs. Grant CONNECT role. Then grant CREATE PROCEDURE, CREATE TRIGGER, and CREATE TYPE. This gives the additional system privs that RESOURCE gives on top of CONNECT without giving UNLIMITED TABLESPACE. Make their default tablespace xyz and grant quota to it. Then import.
|
|
|