Re: ORA-01555 when trying to DROP TABLESPACE

On Feb 7, 12:58 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> 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...

> 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.

> Brian Peasland

Hi Brian,

Sure enough - increasing the UNDO_RETENTION to 600 allowed the tablespace to be dropped without errors. I think the problem is I misunderstood the way the UNDO_RETENTION works. I thought that this was the minimum amount of time in seconds that the undo data would be retained before it could be re-used.

However, based on the results of this test, it looks to me like we need to anticipate how long our longer running query will be and set the UNDO_RETENTION to slightly higher than that value. Is this correct?

Would this not cause several old undo pages to be retained for 600 seconds even though we did not need them after (say as an example) 12 seconds?

Appreciate the help. Now I have to go update the Metalink call...

Steve Received on Wed Feb 07 2007 - 13:23:26 CST