Re: Renaming a Tablespace in 8i & 9i
- From: "Tom Pall" <oracle.list@xxxxxxxxx>
- To: godwin.ror@xxxxxxxxx
- Date: Mon, 27 Aug 2007 16:54:58 -0500
This doesn't sound like a fun project. First off, you can look for objects
in dba_objects or if even sys.obj$ to find what exists in the tablespace.
Not materialized views are going to be in the SYSTEM tablespace. If you
followed standard practice, real indexes (as opposed to IOTs) will be in
another tablespace. They will get dropped once you drop the underlying
tables. And they'll become invalid during the move. You're going to have
to develop not only a list of what's in the tablespace but a list of
dependencies. Hmm. Was it 8i or 9i where the dependencies view disappeared
and you had to run utrlrp.sql to recompile invalid objects after an
upgrade? It's been a while.
Anyway, your biggest problem will be the need to re-validate/re-compile and
then the loss of dependent objects, IMO.
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.
>
Other related posts:
