Re: Renaming a Tablespace in 8i & 9i

Tom,

In the spirit of collaboration, there are a few points that I'd like to clarify from your post.

1. Neither dba_objects nor sys.obj$ contain a tablespace column. To find the tablespace in which a segment or segment partition is stored, we look in dba_segments. If you insist upon using the SYS schema, you can look in sys.seg$.
2. Views (assuming this is what you mean by "not materialized views") are logically defined objects, but not segments, and thus don't take up space anywhere, except as a definition in sys.view$. I suppose the right thing to tell the OP is simply that he doesn't have to do anything with views since they are not segments and don't consume space.
3. It is in no way currently standard practice to separate indexes from tables. That old chestnut is a relic of the pre-RAID JBOD days of enterprise computing. Even back then, the rationale for index segregation was based on an fundamental misunderstanding of how blocks are read for index lookups, and a misguided belief that fine-tuning "head movement" would improve access times. This belief never took into account the reality on a multi-user system of many processes performing all kinds of writes and reads to blocks all over the place all the time.
4. While it is true that the indexes for the tables the OP moves will become invalid after the move, the answer to this is certainly not to use "the dependencies view" (I assume you are referring to DBA_DEPENDENCIES), which does not provide a mapping of index to table. To rebuild indexes, the OP would simply find all indexes for a given table from dba_indexes (predicating by table_owner and table_name), then for each index, run ALTER INDEX <owner.index> REBUILD [TABLESPACE <tablespace>] [ONLINE] [PARALLEL] etc...
5. The OP certainly should run utlrp.sql after a table move, in case there are any packages, procedures, views, etc, that become invalid as a result of performing the move DDL. However, this does not require creating a list of dependencies of any kind, and does not really constitute a "problem."
6. Running utlrp.sql has been a standard documented part of upgrade and patchset apply procedures for as long as I can remember, all the way through version 11g.

In a nutshell, for the OP to accomplish his goal, he simply would:

• Find a list of tables and their respective indexes that are stored in the USERS tablespace by querying DBA_SEGMENTS and DBA_INDEXES.
• Write (or generate with SQL) a script that for each table:
  1. Moves the table to the new tablespace
  2. Rebuilds the indexes for that table
• Recompile invalid objects like packages and procedures using utlrp.sql or your own script.

This procedure will also move the table segments that comprise materialized views and materialized view logs.

The outage time can be reduced by using NOLOGGING and PARALLEL for the table and index rebuilds. The only caveat is to remember to set the tables and indexes to LOGGING and NOPARALLEL after the rebuilds. The OP should also gather segment statistics with DBMS_STATS after the move, as segment sizes, high water mark, and data density may change dramatically.

Regards,

Jeremiah Wilton
ORA-600 Consulting
http://www.ora-600.net

Tom Pall wrote:
> 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_at_gmail.com
> <mailto:godwin.ror_at_gmail.com>> wrote:
>
> 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?

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