OraFAQ Forum: Server Administration » Solaris 9, 10g - move all object from one tablespace to another

Home » RDBMS Server » Server Administration » Solaris 9, 10g - move all object from one tablespace to another (

) 1 Vote

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

Solaris 9, 10g - move all object from one tablespace to another [message #144877]

Fri, 28 October 2005 12:42

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

Does anyone have a script on Solaris to move all objects (data only in this one) from one tablespace(mount point full) to another(more space left)?

Then take the tablespace offline, then drop it?

Thanks

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144889 is a reply to message #144877]

Fri, 28 October 2005 13:03

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

alter table table_name move tablespace new_tablespace;

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144893 is a reply to message #144877]

Fri, 28 October 2005 13:14

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

so to create a script I would:

1) spool move-objects
2) set linesize 100
3) set pagesize 0
4) select 'alter table ' || table_name || ' move tablespace DATA_61;' from dba_all_tables where tablespace_name = 'DATA_01'
order by table_name;
commit;
exit
5) edit the spooled file to take out the select and commit statements.
6) run the spooled file
7) take tablespace DATA_01 offline
Cool

remove tablespace DATA_01
9) delete the supporting datafiles
10) do a hot backup

Is that about it?

[Updated on: Fri, 28 October 2005 13:15]

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144895 is a reply to message #144893]

Fri, 28 October 2005 13:20

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Why are you commiting here?
Make no difference to nothing.

7) take tablespace DATA_01 offline
Cool remove tablespace DATA_01
9) delete the supporting datafiles

I can never recomend those without understanding your system.
Once you move the tables to another tablespace, the tablespace is free of some space.
Why are you deleting them?

>>10) do a hot backup
Very fine.
You are supposed to do this first!.
first backup, do your business and then backup again.

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144896 is a reply to message #144877]

Fri, 28 October 2005 13:24

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

Actually I was only going to relocate some of the tables to another tablespace because I am running out of space on the mountpoint.

I usually do a commit after eveything out of habit.

I just did a hot backup.

I do not want to delete ANY data, just move where it lives.

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144900 is a reply to message #144896]

Fri, 28 October 2005 13:32

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> I do not want to delete ANY data, just move where it lives.
You are about to delete the datafiles.
Apply caution.
Or you can just relocate the those datafiles.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dfiles.htm#8765
or after moving the tables ( Be aware that, you need to move the indexes too) You can just shrink the datafile.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dfiles.htm#7459

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144901 is a reply to message #144877]

Fri, 28 October 2005 13:41

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

Thank you so much! I will investigate both your links.
Neil.

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144918 is a reply to message #144877]

Fri, 28 October 2005 18:25

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

One little gotya is that moving the tables invalidates the primary keys and indexes. They have to be dropped and recreated.

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144923 is a reply to message #144877]

Fri, 28 October 2005 19:16

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

Does anyone have an example of a alter table shrink statement?

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #144925 is a reply to message #144923]

Fri, 28 October 2005 21:11

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you mean compress the table?
If so read it is a little different ( and most effective in a read-only kind of table).So First read about it, before using it.

Report message to a moderator

Re: Solaris 9, 10g - move all object from one tablespace to another [message #145337 is a reply to message #144900]

Wed, 02 November 2005 10:11

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

Mahesh Rajendran wrote on Fri, 28 October 2005 13:32

You read my mind.

In your case, I would go with the relocation of the datafiles in the new mount point (where there are tons of extra space than before).

1. take backup
2. backup control file to trace
3. take tablespace offline (normal) - follow link Mahesh stated
4. relocate datafiles ..(all steps carefully)
5. bring tablespace online
6. backup control file to trace, again
7. take backup, again

this will be faster than moving object at individual level. Cool

Report message to a moderator

Previous Topic:	forcing a user to type password when he tries to connect "conn /as sysdba"
Next Topic:	How does one rename a database in detail

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 12:42:32 CST 2025