OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » moving an user

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

moving an user [message #309818]

Sat, 29 March 2008 01:19

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,

Report message to a moderator

Re: moving an user [message #309820 is a reply to message #309818]

Sat, 29 March 2008 01:27

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

Report message to a moderator

Re: moving an user [message #309827 is a reply to message #309818]

Sat, 29 March 2008 01:39

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,

Report message to a moderator

Re: moving an user [message #309830 is a reply to message #309827]

Sat, 29 March 2008 01:43

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;

Report message to a moderator

Re: moving an user [message #309831 is a reply to message #309818]

Sat, 29 March 2008 01:50

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

and what about previous existing invalid indexes?

regards,

Report message to a moderator

Re: moving an user [message #309832 is a reply to message #309831]

Sat, 29 March 2008 01:52

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

...and what about previous existing invalid indexes?...
I said ALL invalid indexes.

Report message to a moderator

Re: moving an user [message #309833 is a reply to message #309818]

Sat, 29 March 2008 01:56

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

thanks for your valuable informations.

regards,

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 16 22:14:15 CDT 2024