Home » SQL & PL/SQL » SQL & PL/SQL » execute procedure after database startup. (oracle 9i)
execute procedure after database startup. [message #429592] Wed, 04 November 2009 12:48 Go to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
Hi All,
Is there any way to execute a packaged procedure when database is startup. I tried by system event triggers but these have limitations.
Re: execute procedure after database startup. [message #429596 is a reply to message #429592] Wed, 04 November 2009 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>I tried by system event triggers but these have limitations.
Exactly what needs to be done that you were prevented from doing?
Re: execute procedure after database startup. [message #429597 is a reply to message #429596] Wed, 04 November 2009 13:04 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
Packaged procedure inserts rows into a table based on data in another table. System event trigger do not allow queries&DML...so it failed.
Re: execute procedure after database startup. [message #429601 is a reply to message #429597] Wed, 04 November 2009 13:18 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>System event trigger do not allow queries&DML...

I am unaware of any such restriction.
Post URL where this limitation is documented.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#CHDHFBBG
Re: execute procedure after database startup. [message #429606 is a reply to message #429601] Wed, 04 November 2009 13:41 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
in the same like it is mentioned in table 9-3...

Re: execute procedure after database startup. [message #429608 is a reply to message #429592] Wed, 04 November 2009 13:44 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
any one...
please suggest me...
Re: execute procedure after database startup. [message #429609 is a reply to message #429606] Wed, 04 November 2009 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
I do believe you are mistaken.

Re: execute procedure after database startup. [message #429610 is a reply to message #429609] Wed, 04 November 2009 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
12:02:17 SQL> truncate table dbadmin.audit_table;

Table truncated.

12:02:37 SQL> select count(*) from dbadmin.audit_table;

  COUNT(*)
----------
	 0

12:02:51 SQL> @startup_trigger
12:03:11 SQL> CREATE OR REPLACE TRIGGER STARTUP_DB
12:03:11   2  AFTER STARTUP
12:03:11   3  ON DATABASE
12:03:11   4  BEGIN
12:03:11   5  	 INSERT INTO DBADMIN.AUDIT_TABLE SELECT OWNER, TABLE_NAME, NUM_ROWS, SYSDATE FROM DBA_TABLES;
12:03:11   6  END STARTUP_DB;
12:03:11   7  /

Trigger created.

12:03:11 SQL> shudown immediate
SP2-0734: unknown command beginning "shudown im..." - rest of line ignored.
12:03:23 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:03:35 SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size		    1267764 bytes
Variable Size		  201328588 bytes
Database Buffers	  192937984 bytes
Redo Buffers		    7118848 bytes
Database mounted.
Database opened.
12:03:47 SQL> select count(*) from dbadmin.audit_table;

  COUNT(*)
----------
      1584

12:04:04 SQL> 

Re: execute procedure after database startup. [message #429623 is a reply to message #429610] Wed, 04 November 2009 15:14 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
create or replace trigger start_up_db
  after startup on database 
  
begin
   insert into log_script_status_dba    (log_script_status_id,script_name,status,log_comment,last_changed_by,last_changed_on)
  values
    (11, 'start_up_db', 'success', 'null', 'dba', sysdate);
end;


i tried the above as sysdba...
but no row inserted into the log table.
could you please tell me what might be the reason?

[Updated on: Wed, 04 November 2009 15:15]

Report message to a moderator

Re: execute procedure after database startup. [message #429625 is a reply to message #429623] Wed, 04 November 2009 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>could you please tell me what might be the reason?
The most likely cause is PEBKAC.

Not from the lack of details which do not exist in your post.


Did the trigger really get created successfully?
Who owns the table?
Did you actually restart the DB?

[Updated on: Wed, 04 November 2009 16:59]

Report message to a moderator

Re: execute procedure after database startup. [message #429646 is a reply to message #429623] Wed, 04 November 2009 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who owns log_script_status_dba?
Use SQL*Plus and copy and paste a session like BaclkSwan did.

Regards
Michel

[Updated on: Wed, 04 November 2009 23:46]

Report message to a moderator

Re: execute procedure after database startup. [message #429761 is a reply to message #429646] Thu, 05 November 2009 12:54 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select * from log_script_status_dba;

no rows selected

SQL> edit;
Wrote file afiedt.buf

  1  create or replace trigger start_up_db
  2    after startup on database
  3  begin
  4    insert into log_script_status_dba (log_script_status_id,script_name,status,log_comment,last_changed_by,last_changed_on)
  5    values (11, 'start_up_db', 'success', 'null', 'dba', sysdate);
  6* end;
  7  /

Trigger created.

SQL> select * from log_script_status_dba;

no rows selected

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select * from log_script_status_dba;

no rows selected

SQL>


Dears,
Table log_script_status_dba is owned by sys.

Re: execute procedure after database startup. [message #429763 is a reply to message #429761] Thu, 05 November 2009 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never use SYS for your objects.
Retry with another owner.

What is the definition of the table?

Regards
Michel

[Updated on: Thu, 05 November 2009 13:08]

Report message to a moderator

Re: execute procedure after database startup. [message #429764 is a reply to message #429761] Thu, 05 November 2009 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
post results from following SQL

select value from v$parameter where name = '_system_trig_enabled';


Re: execute procedure after database startup. [message #429768 is a reply to message #429764] Thu, 05 November 2009 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch but can't be seen with v$parameter.
select ksppstvl from x$ksppi x, x$ksppsv z
where x.ksppinm = '_system_trig_enabled'
  and z.indx = x.indx
  and x.inst_id = USERENV('Instance')
  and z.inst_id = USERENV('Instance')
/

Regards
Michel
Re: execute procedure after database startup. [message #429769 is a reply to message #429592] Thu, 05 November 2009 13:38 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
Hi Michel,
I tried by defining table with another user.

Connected.
SQL> select * from sateesh.log_script_status;

no rows selected

SQL> edit;
Wrote file afiedt.buf

  1  create or replace trigger start_up_db
  2    after startup on database
  3  begin
  4    insert into sateesh.log_script_status
  5      (log_script_status_id,
  6       script_name,
  7       status,
  8       log_comment,
  9       last_changed_by,
 10       last_changed_on)
 11    values
 12      (11, 'start_up_db', 'success', 'null', 'sateesh', sysdate);
 13* end;
SQL> /

Trigger created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>  select * from sateesh.log_script_status;

no rows selected

SQL> desc sateesh.log_script_status;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 LOG_SCRIPT_STATUS_ID                      NOT NULL NUMBER
 SCRIPT_NAME                               NOT NULL VARCHAR2(10)
 STATUS                                    NOT NULL VARCHAR2(10)
 LOG_COMMENT                               NOT NULL VARCHAR2(100)
 LAST_CHANGED_BY                           NOT NULL VARCHAR2(10)
 LAST_CHANGED_ON                           NOT NULL DATE

SQL>


Am I missing something...
Re: execute procedure after database startup. [message #429770 is a reply to message #429592] Thu, 05 November 2009 13:44 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member

SQL> select ksppstvl from x$ksppi x, x$ksppsv z
  2  where x.ksppinm = '_system_trig_enabled'
  3    and z.indx = x.indx
  4    and x.inst_id = USERENV('Instance')
  5    and z.inst_id = USERENV('Instance');

KSPPSTVL
-------------------------------------------------------------------------------

TRUE

SQL>
Re: execute procedure after database startup. [message #429771 is a reply to message #429769] Thu, 05 November 2009 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SCRIPT_NAME                               NOT NULL VARCHAR2(10)

SQL> select length('start_up_db') from dual;
LENGTH('START_UP_DB')
---------------------
                   11

1 row selected.

Regards
Michel
Re: execute procedure after database startup. [message #429772 is a reply to message #429592] Thu, 05 November 2009 14:04 Go to previous messageGo to next message
dsora
Messages: 24
Registered: May 2009
Junior Member
oh..
thank you Michel
Re: execute procedure after database startup. [message #429773 is a reply to message #429771] Thu, 05 November 2009 14:11 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir,

One concern here, as we can see that
SCRIPT_NAME                               NOT NULL VARCHAR2(10)

why there was no error while passing the value start_up_db?

Thanks
Ved
Re: execute procedure after database startup. [message #429774 is a reply to message #429773] Thu, 05 November 2009 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because all errors in database triggers executed by SYS are ignored.
Otherwise how could you fix it?

Regards
Michel
Re: execute procedure after database startup. [message #429776 is a reply to message #429774] Thu, 05 November 2009 14:22 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks.Got it... Smile

[Updated on: Thu, 05 November 2009 14:22]

Report message to a moderator

Previous Topic: commiting the records with limit
Next Topic: External table
Goto Forum:
  


Current Time: Sun Sep 25 11:10:47 CDT 2016

Total time taken to generate the page: 0.31336 seconds