|
|
|
|
|
|
|
|
Re: execute procedure after database startup. [message #429623 is a reply to message #429610] |
Wed, 04 November 2009 15:14   |
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 #429761 is a reply to message #429646] |
Thu, 05 November 2009 12:54   |
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 #429769 is a reply to message #429592] |
Thu, 05 November 2009 13:38   |
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   |
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>
|
|
|
|
|
|
|
|