History startup & shutdown DB

1. create table to store history startup – shutdown DB

CREATE TABLE WI.TMP_DATABASE_ACTIVITY
(
USER_NAME      VARCHAR2(20 BYTE),
USER_TIME        DATE,
DESCRIPTION  VARCHAR2(30 BYTE)
)
TABLESPACE TBLSPC_01;

2. create trigger after startup

CREATE OR REPLACE TRIGGER WI.TRIG_STARTUP
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO TMP_DATABASE_ACTIVITY
VALUES (USER, SYSDATE, ‘STARTUP’);
END;
/

3. create trigger before shutdown

CREATE OR REPLACE TRIGGER WI.TRIG_SHUTDOWN
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO TMP_DATABASE_ACTIVITY
VALUES (USER, SYSDATE, ‘SHUTDOWN’);
END;
/

4. to see the history

SELECT * FROM TMP_DATABASE_ACTIVITY ORDER BY USER_TIME;