DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Database startup and shutdown time

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    Database startup and shutdown time

    Hi,

    Beside Alter log file, is there any other way to know the database startup and shutdown time?

    Thanks and Regards
    sumit

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Dec 2001
    Posts
    203
    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
    sumit

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width