Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Startup Trigger Can't Do DDL ...
There is a thread in metalink about this, although it is about 1.5 years
old. Basically it looks like the only DDL supported in a startup trigger at
that time was table operations and ALTER or COMPILE statements. A suggested
workaround was to use dbms_job to schedule a one-time job to do the DDL.
-- Cheers, Chris ___________________________________ Chris Leonard, The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com MCSE, MCDBA, OCP, CIW ___________________________________ "Domenic" <domenicg_at_hotmail.com> wrote in message news:c7e08a19.0404291359.45aed24_at_posting.google.com...Received on Thu Apr 29 2004 - 17:11:29 CDT
> 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 ...