RE: Renaming a Tablespace in 8i & 9i
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <godwin.ror@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 27 Aug 2007 23:45:37 +0200
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.
Other related posts:
