Hi,
Beside Alter log file, is there any other way to know the database startup and shutdown time?
Thanks and Regards
Printable View
Hi,
Beside Alter log file, is there any other way to know the database startup and shutdown time?
Thanks and Regards
well in single instance database you can check startup time in v$instance
dont think shutfown is recorded anywhere else
if you want to record all that in a table write database evnets triggers for startup and shutdown
Hi Pando,
Long time back hearing from you. Hope doing well.
Can you pls send me a sample of 'EVENT TRIGGER' and guide me little bit, what excatly I have to capture.
Thanks
one example of event triggers
Code:
create table event_log
(log_date varchar2(36),
event varchar2(36),
username varchar2(36),
owner varchar2(36),
object_name varchar2(36),
object_type varchar2(36),
dbname varchar2(36),
sql_text varchar2(2000)
);
create or replace trigger truncate_trg
after truncate on database
declare
l_sql_stmt varchar2(2000);
begin
select sql_text
into l_sql_stmt
from v$sql a, v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value;
insert into event_log
values(sysdate,
ora_sysevent,
ora_login_user,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
ora_database_name);
end;
/
create or replace trigger alter_user_trg
after alter on database
declare
l_sql_stmt varchar2(2000);
l_sid number;
begin
if (ora_dict_obj_type='USER')
then
select sql_text, sid
into l_sql_stmt, l_sid
from v$sql a, v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and audsid = userenv('SESSIONID');
insert into event_log
values(sysdate,
ora_sysevent,
ora_login_user,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
ora_database_name,
l_sid||' ##### '||l_sql_stmt);
end if;
end;
/