Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Startup Trigger Can't Do DDL ...

Re: Startup Trigger Can't Do DDL ...

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 30 Apr 2004 09:27:31 +1000
Message-ID: <40918f69$0$14771$afc38c87@news.optusnet.com.au>


Domenic wrote:

> Somebody out there please try this ...
>
> create sequence sys.test_seq start with 1 nocache;
>
> create trigger drop_seq
> after startup on database
> declare
> pragma autonomous_transaction;
> begin
> execute immediate 'drop sequence sys.test_seq';
> end;
> /
>
> connect / as sysdba
> shutdown immediate
> startup
>
> <the sequence is still there>
>
> is this a bug??
>
> I've also had random problems with execute immediate when it is
> surrounded by other cursors. Anyone else seen this?
>
> What I am trying to do is have the database startup drop and recreate
> sequences to eliminate gaps caused by shutdown aborts or system
> crashes -- ie when the sequences are cached this happens.
>
> Thanks in advance for all replies ...

I won't comment on the particular problem, but I will say that you're chasing clouds trying to eliminate gaps in sequences, and I wouldn't be bothering if I were you. Oracle has never suggested, much less guaranteed, that sequences will be gapless. The only guarantee it's ever offered is that they won't repeat numbers. So you are trying to design something about sequences which Oracle themselves have said is a no-no.

And never mind the loss of numbers when an instance crashes: what about the loss of numbers that will happen when somebody rolls back their transaction (or suffers a blue screen of death in mid-transaction and has PMON roll it back for them)? Or when the junior DBA wanting to know what the last sequence number issued was happens to select from currval?

Gaps are an inevitable feature of sequences, cached or non-cached. And if your application or business rules demand no gaps, then you ought to use a different mechanism altogether.

Regards
HJR Received on Thu Apr 29 2004 - 18:27:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US