| Doubts abt user data [message #433812] |
Fri, 04 December 2009 06:28  |
sekharsomu
Messages: 72
Registered: December 2008
|
Member |
|
|
A particular db user called xxxx in the database has the data in the system datafile and users datafile, and currently users is the default tablespace
Q1) if i export XXXX, will all the objects in the users datafile & system datafile will get exported????
Q2) After exporting the user, if i drop XXXX user with cascade option, will alll the objects in th Esystem datafile as well as users datafile will get dropped?????
|
|
|
|
| Re: Doubts abt user data [message #433814 is a reply to message #433812] |
Fri, 04 December 2009 06:40  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) Yes - all the objects will get exported. This means that the user you import to will need to have a quote on the System tablespace or the imported objects will not be created.
2) The objects owner by that user in the System tablespace will be dropped when the user is dropped.
Your best bet is to move the objects into the correct tablespace.
|
|
|
|
| Re: Doubts abt user data [message #433822 is a reply to message #433814] |
Fri, 04 December 2009 07:21 |
sekharsomu
Messages: 72
Registered: December 2008
|
Member |
|
|
JRowbottom wrote on Fri, 04 December 2009 06:401) Yes - all the objects will get exported. This means that the user you import to will need to have a quote on the System tablespace or the imported objects will not be created.
is there any change i can do in the source database so that once the export is successful and when i try to import it into destination database it doesn't ask me for system table space
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Doubts abt user data [message #433978 is a reply to message #433812] |
Sun, 06 December 2009 02:17 |
nets_edge@cox.net
Messages: 17
Registered: December 2009
Location: home, Arizona
|
Junior Member |
|
|
If you
drop user xxxx cascade;
Only xxxx's objects in the system/sysaux/any other tablespace will be dropped. (It will not mess with objects owned by sys, system, etc, just xxxx's.)
Also, depending on your version,(10g) it may move those objects to the 'recyclebin', so the space may not be released until its actually needed. To prevent that (recyclebin activity) from occurring, you may issue:
alter session recyclebin off; ## pretty sure thats the syntax.
drop user xxxx cascade;
When dropping objects (tables, indexes, etc) there is an option you can add to the drop statement, (along the lines of norecycle purge) but I am not sure of the exact syntax.
Peace
Nets Edge
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
