Home » RDBMS Server » Server Utilities » Doubts abt user data (oracle 10g on solaris)
Doubts abt user data [message #433812] Fri, 04 December 2009 06:28 Go to next message
sekharsomu
Messages: 69
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
JRowbottom wrote on Fri, 04 December 2009 06:40
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.


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 #433830 is a reply to message #433822] Fri, 04 December 2009 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not allocate quota in system tablespace for your user nor grant it dba or resource roles or unlimited tablespace privilege.

Regards
Michel
Re: Doubts abt user data [message #433842 is a reply to message #433830] Fri, 04 December 2009 08:36 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
THANKS for the solution'
one more doubt
once the export is done to the database can i change the roles and other tablespace privilages
Re: Doubts abt user data [message #433845 is a reply to message #433842] Fri, 04 December 2009 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not for the export only for the import.
All objects that import cannot allocate in original tablespace (due to lack of quota or privilege) will go to user default tablespace.

Regards
Michel
Re: Doubts abt user data [message #433846 is a reply to message #433812] Fri, 04 December 2009 08:44 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
yeah i understood it before hand i was also just mentioning the import case
anyway thanks for getting into detail and thank u very much for a fast reply

[Updated on: Fri, 04 December 2009 08:45]

Report message to a moderator

Re: Doubts abt user data [message #433978 is a reply to message #433812] Sun, 06 December 2009 02:17 Go to previous message
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


Previous Topic: unwanted trigger created when importing
Next Topic: space problem when impoting
Goto Forum:
  


Current Time: Sun Sep 25 16:17:23 CDT 2016

Total time taken to generate the page: 0.09192 seconds