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: Chris Leonard <s_p_a_m_chris_at_hotmail.com>
Date: Thu, 29 Apr 2004 17:11:29 -0500
Message-ID: <l4fkc.49$2b2.35772@news.uswest.net>


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

> 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 ...
Received on Thu Apr 29 2004 - 17:11:29 CDT

Original text of this message

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