Re: Export & import problems

In article <3bbi19$1id_at_hk.super.net> shciosea_at_hk.super.net (Mr John Shaller) writes:
>From: shciosea_at_hk.super.net (Mr John Shaller)
>Subject: Export & import problems
>Date: 28 Nov 1994 03:15:53 GMT

>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
> 6 ROLL 8388608 4096 AVAILABLE
>
>SQL> drop tablespace temprollback
>*
>ERROR at line 1:
>ORA-00959: tablespace 'TEMPROLLBACK' does not exist
>
>I have tried this in two ULTRIX machine and get the same error. In one
>case, I can make the TEMPROLLBACK tablespace back to normal by adding a
>tablespace file to it. It becomes online again. But when I drop it again.
>It becomes INVALID again. On another machine, I cannot even add
>tablespace file to it.
>
>So far the system is running OK. But I really want to get rid of this
>entry. or more importantly WHY it would happen?
>

You can't get rid of the entry, you have to live with it. You can delete the physical file but that's it. This is normal behavior so don't worry about it.

An unsupported trick is to delete from the OBJ$ table but I wouldn't do it on a production database and Oracle won't help you if it messes things up.

Lawrence..... Received on Mon Nov 28 1994 - 17:56:44 CET