RE: Renaming a Tablespace in 8i & 9i

Hi Vincent,  

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?  

on 9i : dbms_redefinition or

-Indexes: you could rebuild in the new tbs via: "alter index
<index_name> tablespace <new_tbs> rebuild online " .

sample script to rebuild indexes which reside in the USERS tbs:  

set heading off

set feedback off

set pages 0

set wrap off

set termout off

conn / as sysdba;

spool move_indexes

select 'alter index '|| owner || '.'|| index_name || ' tablespace NEW_TBS rebuild online ; '

from dba_indexes di where di.tablespace_name = 'USERS' ;

quit;  

$ sqlplus /nolog @movei.sql

$ more move_indexes.lst

.

.

.

(see the generated content to check)  

-Views, packages, procedures, triggers, types, sequences, dblinks .
their source/definition and object code reside in the dictionary, in the system tablespace.  

-Materialized views: I don't know, i don't worked with them yet.
 

Any information will be of great help.  

Thank you,

Godwin.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 27 2007 - 16:45:37 CDT