ORA-01555 when trying to DROP TABLESPACE

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: 7 Feb 2007 07:36:51 -0800
Message-ID: <1170862611.153966.127480@v45g2000cwv.googlegroups.com>

ORACLE 9.2.0.6.0 (Enterprise Ed.)
AIX 5.2
DATA TABLESPACE = 9GB We have a test environment that once every week or two, we drop the existing DATA tablespace and then refresh it by taking an exp/imp using TRANSPORT_TABLESPACE=Y.

Our old 8i setup was using 3.5GB of rollback segments and the routine was working great. When we switched to 9i recently, we were told that UNDO tablespace using AUM was the way to go. So we ran a CREATE DATABASE with these pertinent settings:

UNDO TABLESPACE esinde01undo
DATAFILE '/oradata03/e01/undo/esinde01undo01' SIZE 2000M AUTOEXTEND OFF ALTER TABLESPACE esinde01undo
ADD DATAFILE '/oradata03/e01/undo/esinde01undo02' SIZE 2000M AUTOEXTEND OFF;

undo_management = AUTO
undo_tablespace = esinde01undo
undo_retention  = 30

Setting of 30 for retention was based on the MAXQUERYLEN from
$UNDOSTAT, which initial tests showed a value of 25.

So now that we have 4GB of UNDO space (more reserved than when we had the rollback segments), we have been getting the following errors when we try to drop the tablespace:

DROP TABLESPACE ESINDL01DATA INCLUDING CONTENTS *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$"
too small

The following was posted in the alert log:

Wed Feb 7 09:20:38 2007
ORA-01555 caused by SQL statement below (Query Duration=45 sec, SCN: 0x0000.20a3
dec9):
Wed Feb 7 09:20:38 2007
select u.name, o.name from obj$ o,user$ u, (select obj# objnum# from tab$ w
here ts#=:1 and bitand(property,64+512+8192)=0 union all select dist
inct t.obj# objnum# from tabpart$ tp,tab$ t where tp.ts#=:1

and          t.ob
j#=tp.bo# and          bitand(property,64+512+8192)=0 union all

select distin
ct t.obj# objnum# from tabcompart$ tcp,tabsubpart$ tsp,tab$ t where tsp.ts#=:
1 and tsp.pobj#=tcp.obj# and t.obj#=tcp.bo# and bitand(propert
y,64+512+8192)=0 union all select t.obj# objnum# from ind$ i,tab$ t where
i.ts#=:1 and i.type#=4 and t.obj#=i.bo# and bitand(t.property, 8192)=0 u
nion all select distinct t.obj# objnum# from indpart$ ip,ind$ i,tab
$ t whe

re ip.ts#=:1 and i.type#=4 and i.obj#=ip.bo# and t.obj#=i.bo#
and bitand(t.property,8192)=0 union all select distinct t.obj# objnum# fro
m indcompart$ icp,indsubpart$ isp,ind$ i,tab$ t where isp.ts#=:1 and i.type#=
4 and isp.pobj#=icp.obj# and i.obj#=icp. ORA-604 signalled during: DROP TABLESPACE ESINDL01DATA INCLUDING CONTENTS... Does anyone see something that needs to be adjusted, other than possibly adding more undo space? There must be a way to drop the entire 9GB of DATA contents without requiring 9GB of undo space I hope!

Also, not sure why this worked in my RBS configuration with less space but now complains when there is more. Any help is appreciated while we wait for a Metalink response.

Steve Received on Wed Feb 07 2007 - 09:36:51 CST