Re: Renaming a Tablespace in 8i & 9i

Alright, so here is "unusual" method.

If your old and new tablespace names are of the same length then you can use the following method. However, it will probably make you database unsupported if you tell that to Oracle support analyst. ;-) Otherwise, it should be not possible to reveal that later.

First of all perform export using transportable tablespaces and drop them. Then you need to use binary/hex editor (something like bvi or bsed) and replace tablespace name with the new one. Note that it must be the same length or dump file would be corrupted with change in length. Next, you just do import with new tablespace names in import parameter file. Oracle won't check tablespace name during import - just DBID, file_id. When you make tablespaces read write, it will flush datafile headers with new ones. Until then headers are intact including old dbid, file id and tablespace names.

As I said you are probably out of support at that point but I have used this method for couple years on 9i on HPUX. Several times a week, dozens of tablespaces were imported/renamed into the database and then dropped in a week and so on for many many weeks. Databases are still kicking AFAIK.
Not sure about 8i but it should work.

Oh... did I mention to take backup before that? ;-)

On 8/27/07, Godwin vincent <godwin.ror_at_gmail.com> wrote:
> 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,
>
> 1. Create new tablespace
> 2. Move all objects in the old tablespace to the new tablespace
> 3. 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.

-- 
Alex Gorbachev, Oracle DBA Brewer, The Pythian Group
http://www.pythian.com/blogs/author/alex http://www.oracloid.com
BAAG party - www.BattleAgainstAnyGuess.com
--
http://www.freelists.org/webpage/oracle-l