RE: Renaming a Tablespace in 8i & 9i

From: Kerber, Andrew W. <Andrew.Kerber_at_umb.com>
Date: Tue, 28 Aug 2007 08:03:43 -0500
Message-ID: <D40740337A3B524FA81DB598D2D7EBB3097A8998@x6009a.umb.corp.umb.com>

That's pretty simple.

Create your new tablespace.

Generate and run the ddl from the command below:

Select

'alter table ||owner||'.'||table_name||' move tablespace personal;'

from dba_tables where tablespace_name='USERS';

then do this one:

select

'alter index '||owner||'.'||index_name||' rebuild tablespace
'||decode(tablespace_name,'USERS','PERSONAL',tablespace_name)||';'

from dba_indexes where tablespace_name='USERS' or status='UNUSABLE';

Then run utlrpt.sql from $ORACLE_HOME/rdbms/admin

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Godwin vincent Sent: Monday, August 27, 2007 3:38 PM
To: oracle-l_at_freelists.org
Subject: Re: Renaming a Tablespace in 8i & 9i

Hi all,

I am working on renaming a tablespace. I am working on Oracle versions 8i & 9i (HP-UX) and would like to request your help in this regard. I have an idea as what process needs to be followed,

Create new tablespace
Move all objects in the old tablespace to the new tablespace
Drop the old tablespace.

The main step here is implementing the 2nd process, that is moving objects. I have tables, indexes, and other objects stored in the same tablepsace (USERS). Suppose, I want to rename the USERS tablespace to PERSONAL. How can i move all the objects (tables, indexes, views, materialized views, packages, procedures, etc) from the old tablespace to new tablespace? For tables, we can issue "Alter table <table_name> move tablespace <tablespace_name>" but how can i move all other objects like indexes, views, etc.., which reside in this tablespace?

Any information will be of great help.

Thank you,

Godwin.

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 28 2007 - 08:03:43 CDT