Home » RDBMS Server » Server Administration » Tables need to move from one Tablespace to another (11g, 11.2.0.1, windows server2008)
Tables need to move from one Tablespace to another [message #602387] Tue, 03 December 2013 23:19 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

Developer user has create some tables and assigned them to USERS tablespace. Now, we need to move it to its respective owners's tablespaces.

Kindly provide me the steps to change tablespace. Also let me know what are all do I need to execute (like rebuilt indexes etc).

Waiting for your kind response...

Regards,
Ishika
Re: Tables need to move from one Tablespace to another [message #602389 is a reply to message #602387] Tue, 03 December 2013 23:24 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
from OTN i got the scripts -

SQL> ALTER TABLE <TABLE NAME> MOVE TABLESPACE <TABLESPACE NAME>;

select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';

While doing such activities, application team will get effected?

Regards,
Ishika
Re: Tables need to move from one Tablespace to another [message #602391 is a reply to message #602389] Tue, 03 December 2013 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
> Now, we need to move it to its respective owners's tablespaces.

tablespace does not have any owner.
Re: Tables need to move from one Tablespace to another [message #602398 is a reply to message #602389] Wed, 04 December 2013 01:13 Go to previous messageGo to next message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
ishika_20 wrote on Wed, 04 December 2013 05:24
from OTN i got the scripts -

SQL> ALTER TABLE <TABLE NAME> MOVE TABLESPACE <TABLESPACE NAME>;

select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';

While doing such activities, application team will get effected?

Regards,
Ishika
The table will be locked against DML during the move until you rebuild the indexes.
Re: Tables need to move from one Tablespace to another [message #602399 is a reply to message #602398] Wed, 04 December 2013 01:35 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear John,

Really Appreciate your feedback.

Can you please send me some links to study about the locking of tables during the movements.
Previous Topic: Quota assigned to user
Next Topic: How to use Oracle Swap (Extended Buffer Cache Support)?
Goto Forum:
  


Current Time: Tue Oct 21 19:20:56 CDT 2014

Total time taken to generate the page: 0.14336 seconds