Oracle Servererror Login Trail.

SQL> CREATE TABLE LOG_FAILED_TRAIL
  2  (
  3    USER_NAME           VARCHAR2(200),
  4    ERROR_MESSAGE   VARCHAR2(300),
  5    LOGON_TIME          DATE                           DEFAULT SYSDATE,
  6    HOST_OR_IP          VARCHAR2(100 BYTE)             DEFAULT SYS_CONTEXT('USERENV', 'IP_ADDRESS')
  7  );
Table created.
SQL> CREATE OR REPLACE TRIGGER LOGON_FAILURES_AUDIT
  2     AFTER SERVERERROR
  3     ON DATABASE
  4    DECLARE
  5    V_DB_USER VARCHAR2(300);
  6    V_ERROR_CODE VARCHAR2(100);
  7    V_ERROR_DESC VARCHAR2(200);
  8    V_HOST_OR_IP VARCHAR2(100);
  9    PROCEDURE SP_ERROR_TRACE IS
 10    BEGIN
 11      IF (IS_SERVERERROR(1017)) THEN
 12      V_ERROR_CODE:='1017';
 13      V_ERROR_DESC:='Invalid username/password.';
 14       ELSIF (IS_SERVERERROR(1005)) THEN
 15      V_ERROR_CODE:='1005';
 16      V_ERROR_DESC:='Null password given.';
 17           ELSIF (IS_SERVERERROR(1004)) THEN
 18      V_ERROR_CODE:='1004';
 19       V_ERROR_DESC:='Default username/password.';
 20           ELSIF (IS_SERVERERROR(1035)) THEN
 21      V_ERROR_CODE:='1035';
 22       V_ERROR_DESC:='ORACLE only available to users with RESTRICTED SESSION privilege.';
 23           ELSIF (IS_SERVERERROR(1045)) THEN
 24      V_ERROR_CODE:='1045';
 25       V_ERROR_DESC:='User lacks CREATE SESSION privilege.';
 26        ELSE
 27      V_ERROR_CODE:='0000';
 28      V_ERROR_DESC:='Invalid Error.';
 29      END IF;
 30    END;
 31
 32  BEGIN
 33      V_DB_USER:= SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY');
 34      V_HOST_OR_IP:=SYS_CONTEXT('USERENV', 'IP_ADDRESS');
 35      SP_ERROR_TRACE;
 36     IF V_ERROR_CODE IS NOT NULL
 37     THEN
 38       BEGIN
 39        INSERT INTO LOG_FAILED_TRAIL (USER_NAME, ERROR_MESSAGE,HOST_OR_IP)
 40        VALUES (V_DB_USER, V_ERROR_CODE||'-'||V_ERROR_DESC,V_HOST_OR_IP);
 41       EXCEPTION
 42              WHEN OTHERS THEN
 43              NULL;
 44       END;
 45     END IF;
 46  END;
 47  /
Trigger created.
SQL> CONN TEST/TEST;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> SELECT USER_NAME, ERROR_MESSAGE
  2  FROM LOG_FAILED_TRAIL;
USER_NAME  ERROR_MESSAGE
-------------- ----------------------------------------------------------------
TEST    1017-Invalid username/password.
SQL>

Find Out Oracle Instance Start Time.

SQL> SELECT (SYSDATE-STARTUP_TIME)*24*60*60 INSTANCE_START_MINUTES FROM V$INSTANCE;
INSTANCE_START_MINUTES
----------------------
                 11849
SQL>

Find and Monitoring Oracle Alert Log Using External Table.

SQL> show parameter background
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orcl/orcl/trace
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@DB ~]$ ls  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/al*
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

[oracle@DB ~]$

CREATE DIRECTORY ALERT_LOG AS '/u01/app/oracle/diag/rdbms/orcl/orcl/trace/';
 CREATE TABLE
    ALERT_LOG ( MESSAGE VARCHAR2(3000) )
 ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ALERT_LOG
    ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE
    )
    LOCATION('alert_orcl.log')
 )
 REJECT LIMIT 1000;

SELECT ROWNUM , MESSAGE
FROM ALERT_LOG
ORDER BY 1 DESC

DROP and TRUNCATE Restriction in Oracle database.

SQL> CREATE TABLE OBJECT_EVENT_ALLOWED
  2  (OBJECT_NAME VARCHAR2(50),
  3   DROP_ALLOWED CHAR(1),
  4   TRUNCATE_ALLOWED CHAR(1),
  5   DELETE_ALLOWED CHAR(1)
  6   );
Table created.
SQL> CREATE TABLE ORA_EVENT_LOG
  2  (
  3    OBJECT_NAME     VARCHAR2(100 BYTE),
  4    EVENT      VARCHAR2(50 BYTE),
  5    OWNER      VARCHAR2(50 BYTE),
  6    MACHINE    VARCHAR2(100 BYTE) DEFAULT SYS_CONTEXT ('USERENV', 'HOST'),
  7    IPADRES    VARCHAR2(100 BYTE)  DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
  8    TIMESTAMP  DATE DEFAULT SYSDATE
  9  );
Table created.
SQL> CREATE TABLE TEST_TRUNCATE
  2  (
  3    DATA1  VARCHAR2(20 BYTE)
  4  );
Table created.
SQL> INSERT INTO OBJECT_EVENT_ALLOWED
  2     (OBJECT_NAME, DROP_ALLOWED, TRUNCATE_ALLOWED, DELETE_ALLOWED)
  3   VALUES
  4     ('TEST_TRUNCATE', 'Y', 'Y', 'Y');
1 row created.
SQL> COMMIT;
Commit complete.

SQL> CREATE OR REPLACE TRIGGER DTR_EVENT_CHECK_STORE
  2     BEFORE DROP OR TRUNCATE ON SCHEMA
  3  DECLARE
  4     V_OBJNAME   VARCHAR2 (50);
  5     V_OBJECT_TYPE VARCHAR2(30);
  6     V_EVENT     VARCHAR2 (30);
  7     V_OBJECT_COUNT NUMBER(5);
  8  BEGIN
  9     SELECT ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, ORA_SYSEVENT
 10       INTO V_OBJNAME, V_OBJECT_TYPE, V_EVENT
 11       FROM DUAL;
 12
 13       IF V_EVENT IN ('DROP','TRUNCATE') AND V_OBJECT_TYPE IN ('TABLE') THEN
 14          IF V_EVENT='DROP' THEN
 15              BEGIN
 16                  SELECT COUNT(OBJECT_NAME)
 17                  INTO V_OBJECT_COUNT
 18                  FROM OBJECT_EVENT_ALLOWED
 19                  WHERE UPPER(OBJECT_NAME)=UPPER(V_OBJNAME)
 20                  AND DROP_ALLOWED='Y';
 21              END;
 22          END IF;
 23
 24          IF V_EVENT='TRUNCATE' THEN
 25              BEGIN
 26                  SELECT COUNT(OBJECT_NAME)
 27                  INTO V_OBJECT_COUNT
 28                  FROM OBJECT_EVENT_ALLOWED
 29                  WHERE UPPER(OBJECT_NAME)=UPPER(V_OBJNAME)
 30                  AND TRUNCATE_ALLOWED='Y';
 31              END;
 32          END IF;
 33
 34           IF V_OBJECT_COUNT=0 THEN
 35                  RAISE_APPLICATION_ERROR(-20099, V_EVENT||' Not allowed in '||V_OBJNAME|| ' '||V_OBJECT_TYPE);
 36             ELSE
 37                  BEGIN
 38                      INSERT INTO ORA_EVENT_LOG( OBJECT_NAME, EVENT, OWNER) VALUES (V_OBJNAME, ORA_SYSEVENT, USER);
 39                    EXCEPTION
 40                          WHEN OTHERS THEN
 41                          NULL;
 42                  END;
 43           END IF;
 44
 45       END IF;
 46
 47  END DTR_EVENT_CHECK_STORE;
 48  /
Trigger created.
SQL> INSERT INTO OBJECT_EVENT_ALLOWED
  2     (OBJECT_NAME, DROP_ALLOWED, TRUNCATE_ALLOWED, DELETE_ALLOWED)
  3   VALUES
  4     ('TEST_TRUNCATE', 'Y', 'Y', 'Y');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> TRUNCATE TABLE TEST_TRUNCATE;
Table truncated.
SQL> DELETE FROM OBJECT_EVENT_ALLOWED;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> TRUNCATE TABLE TEST_TRUNCATE;
TRUNCATE TABLE TEST_TRUNCATE
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TRUNCATE Not allowed in TEST_TRUNCATE TABLE
ORA-06512: at line 33

SQL>

Finding INSERT, UPDATE, DELETE rows in table from oracle database.

EXECUTE DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

This procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS views are updated immediately, without waiting for the Oracle database to flush them periodically.


  SELECT TABLE_OWNER,
         TABLE_NAME,
         INSERTS,
         UPDATES,
         DELETES,
         TRUNCATED,
         (CASE WHEN DROP_SEGMENTS = 1 THEN 'YES' ELSE 'NO' END) DROPED,
         TIMESTAMP
    FROM DBA_TAB_MODIFICATIONS
   WHERE TABLE_OWNER = USER
ORDER BY TABLE_NAME