Re: Renaming a Tablespace in 8i & 9i
- From: "Alex Gorbachev" <ag@xxxxxxxxxxxx>
- To: godwin.ror@xxxxxxxxx
- Date: Tue, 28 Aug 2007 23:53:12 -0400
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
