Re: ORA-01555 when trying to DROP TABLESPACE

steven_nospam at Yahoo! Canada wrote:
> 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
>

When dropping the tablespace, UNDO is not generated for every object in the ts. You are most likely getting the ORA-1555 error because the UNDO_RETENTION parameter is too low. Consider raising this to a higher value and try the DROP TABLESPACE operation again.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown