Home » RDBMS Server » Server Administration » moving an user
moving an user [message #309818] Sat, 29 March 2008 01:19 Go to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear all,

I want to know the best way to move an user from system tablespace to new tablespace(oracle 9i).

regards,
Re: moving an user [message #309820 is a reply to message #309818] Sat, 29 March 2008 01:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1. Change user default tablespace specification.
alter user USERNAME default tablespace TABLESPACE_NAME;

2. move all objects from system tablespace to new assign tablespace which previously created.
alter table TABLENAME move tablespace TABLESPACE_NAME
+
rebuild all indexes.

Regards
Taj
Re: moving an user [message #309827 is a reply to message #309818] Sat, 29 March 2008 01:39 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
may you clarify it more:-

1.moving all objects
and
2.rebuilding all indexes.

regards,
Re: moving an user [message #309830 is a reply to message #309827] Sat, 29 March 2008 01:43 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

When ever you move table all associate index will invalid so that you need to rebuild all indexes.

Simple First move all tables to new tbs. and rebuild all associate indexes.
like
alter index INDEX_NAME rebuild tablespace NEW_TABLESPACE_NAME;
Re: moving an user [message #309831 is a reply to message #309818] Sat, 29 March 2008 01:50 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
and what about previous existing invalid indexes?

regards,
Re: moving an user [message #309832 is a reply to message #309831] Sat, 29 March 2008 01:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

...and what about previous existing invalid indexes?...
I said ALL invalid indexes.
Re: moving an user [message #309833 is a reply to message #309818] Sat, 29 March 2008 01:56 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
thanks for your valuable informations.

regards,
Previous Topic: standby database goes down.
Next Topic: Returning free space from index to the tbsp
Goto Forum:
  


Current Time: Thu Dec 08 06:31:38 CST 2016

Total time taken to generate the page: 0.07779 seconds