Hello Everyone,
I have a problem in dropping rollback segments and tablespaces. I'm
running Oracle V6.0.33.1.1 on Ultrix 4.3. Recently I want to do a
complete export & import to eliminate data fragmentation.
After I recreate the database, I create a rollback segment SYS_AUX in
the system tablespace so that I can create other tablespace. Then I
edit the init.ora file to include SYS_AUX and restart ORACLE nomount.
create rollback segment system_aux tablespace system
storage (initial 1M next 1M pctincrease 0 maxextents 15);
rem edit init.ora to add system_aux and restart Oracle
Then I create a tablespace TEMPROLLBACK. I want to exclude any non-sys
rollback activity in the system tablespace so that the SYSTEM rollback
won't grow in size during import and fill up my system tablespace. So I
create a TEMPROLL rollback segment, edit init.ora to exclude SYS_AUX and
include TEMPROLL.
create tablespace temprollback datafile '/oracle/temproll.bck' size
11M reuse;
create rollback segment temprollback tablespace temprollback
storage (initial 2M next 2M pctincrease 0 minextents 5);
rem edit init.ora to add temprollback and restart oracle
Then I restart Oracle and do my import. Everything went fine. After
restarting the database normally. I start to do some clean up job. I
shutdown ORACLE, edit init.ora to exclude TEMPROLLBACK, include RS1, RS2,
RS3 & RS4 which are my original rollaback segments before export/import.
I restart Oracle normally. I check that TEMPROLL is available by querying
sys.dba_rollback_segs and drop it. I then drop the TEMPROLLBACK
tablespace including contents. All command executed without any error.
When I check on the tablespaces, I'm in TROUBLE !!!
SQL> select * from sys.dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE STATUS
----------- ------------ ---------
SYSTEM 10240 10240 1
99 50 ONLINE
TEMPROLLBACK 10240 10240 1
99 50 INVALID
PROD 10240 10240 1
99 10 ONLINE
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE STATUS
----------- ------------ ---------
INDX 10240 10240 1
99 10 ONLINE
TEMP 10240 10240 1
99 10 ONLINE
ROLL 10240 10240 1
99 50 ONLINE
6 rows selected.
SQL> select * from sys.dba_data_files;
FILE_NAME
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
/usr/oracle/dbs/istest_system.dbf
1 SYSTEM 33554432 16384 AVAILABLE
/usr/oracle/dbs/PROD.dbf
3 PROD 25165824 12288 AVAILABLE
/usr/oracle/dbs/INDX.dbf
4 INDX 14680064 7168 AVAILABLE
FILE_NAME
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
/usr/oracle/dbs/TEMP.dbf
5 TEMP 3145728 1536 AVAILABLE
/usr/oracle/dbs/ROLL.dbf
