Learn Database Concepts

If you have configured IDLE_TIME inr your user profile. IDLE_TIME  Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit Lets say a session has been idle for 10 Minutes. Session will continue to show as idle even after the idle_time for that user,as specified in that user's profile, has expired. When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. After this, the session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session. Reference this oracle Document for more information. Once the oracle session is changed to SNIPED status, we can kill that se...

ENABLE AUDITING IN ORACLE DATABASE SERVER SETUP FOR DB AUDITING Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command. SQL> SHOW PARAMETER AUDIT NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN                                ...

ENABLE AUDITING IN ORACLE DATABASE SERVER SETUP FOR DB AUDITING Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command. SQL> SHOW PARAMETER AUDIT NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN                                ...

Inserting Data with DML Error Logging: When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature. To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time. DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements. -------------------------------------------------------- --  DDL for Table ATEST1 ---------------------...

-- Simple trigger to audit to audit basic schema changes : --- CREATE TABLE TO STORE AUDIT DATA CREATE TABLE DDL_AUDIT_LOG (   STAMP DATE , USERNAME VARCHAR2(30 BYTE) , OSUSER VARCHAR2(30 BYTE) , MACHINE VARCHAR2(30 BYTE) , TERMINAL VARCHAR2(30 BYTE) , OPERATION VARCHAR2(30 BYTE) , OBJTYPE VARCHAR2(30 BYTE) , OBJNAME VARCHAR2(30 BYTE) , OBJ_OWNER VARCHAR2(30 BYTE) ) TABLESPACE USERS ; -- NOW CREATE TRIGGER TO AUDIT CHANGES ALTER TRIGGER AUDIT_DDL_CHANGES DISABLECREATE TRIGGER AUDIT_DDL_CHANGES    AFTER create OR drop OR alter       ON ATOORPU.SCHEMA  -- Change SCOTT to your schema name!!!       -- ON DATABASE BEGIN   INSERT INTO ddl_audit_log VALUES         (SYSDATE,          SYS_CONTEXT('USERENV', 'SESSION_USER'),          SYS_CONTEXT('USERENV', 'OS_USER'),          SYS_CONTEXT('USERENV', 'HOST'),          SYS_CON...

I was playing around with some plsql code today.Just posting this sample plsql code as I thought this can help people. This is a sample code that will search for all tables with name  ABC,ABC1,ACB2 in all schema's in database and execute a NOAUDIT against these tables. This sql can be modified to accommodate any changes where you want to run a query against all tables & schema's. sample code: declare     result sys_refcursor;     V_OWNER Varchar2(100):='SCHEMA_NAME'; --- update this with schema you want to audit.     strTableOwner Varchar2(100); strTableName Varchar2(100);     strQuery varchar2(300); begin open result for     select owner,table_name from user_tables where     table_name in ('ABC','ABC1',ABC2')  and owner = 'V_OWNER' order by table_name; loop     fetch result into strTableOwner,strTableName;     exit when result%notfound;           DBMS_OUTPUT.P...

---- DISABLE TABLE NAMES STARTING WITH TMP OR BAK in Database ---- Intially : create table tmp_test (fname varchar2(20)); After enabling below trigger, no more tables can be created in database starting with TMP or BAK: create or replace TRIGGER NO_TMP_TABS_TRIG BEFORE CREATE ON DATABASE DECLARE  x user_tables.table_name%TYPE; BEGIN   SELECT ora_dict_obj_name   INTO x   FROM DUAL;   IF SUBSTR(x, 0, 4) = 'TMP_' or SUBSTR(x, 0, 4) = 'BAK_' THEN     RAISE_APPLICATION_ERROR(-20099, 'TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%');   END IF; END NO_TMP_TABS_TRIG; Lets test it : create table tmp_test (fname varchar2(20)); Error : Error starting at line : 15 in command - create table tmp_test (fname varchar2(20)) Error at Command Line : 15 Column : 1 Error report - SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-20099: TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%.

Monitoring tracks the approximate number of  deletes, updates & inserts  operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary.  Enabling table monitoring : create table TEST as select OBJECT_name from all_objects; table TEST created. select count(*) from test;   COUNT(*) ----------     120118 select table_name,inserts,updates,deletes,truncated,timestamp    from sys.dba_tab_modifications   where table_owner='ATOORPU' and table_name='TEST';   --- no rows selected   Enable FLUSH_DATABASE   exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;   anonymous block completed   ENABLE MONITORING ON TABLE:   alter table TEST monitoring;   select table_name,inserts,updates,deletes,truncated,timestamp    from sys.dba_tab_modifi...

A general question among most of developers is  " HOW can I know when LAST DDL was done??" The best option will be to go and look at the table details.  While you can also query the same details from user_objects/dba_objects. Lets create a table test1 sql > create table test1 (id number); table TEST1 created. sql >   select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1'; OBJECT_NAME            OBJECT_TYPE         CREATED           LAST_DDL         -------------------------------------------------    ---------------------     -------------- TEST1                              TABLE                     05-06-15 15:5...

SQL to find the  LAST_DDL_TIME change time of an Oracle object in the database. -- Get the name, type, date of change of the DDL of a user object. select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where owner not in ('SYS','SYSTEM');

Lets create a table :  CREATE TABLE "ABC"     ( "EMPNO" NUMBER(4,0),  "ENAME" VARCHAR2(10 BYTE),  "JOB" VARCHAR2(9 BYTE),  "MGR" NUMBER(4,0),  "HIREDATE" DATE,  "SAL" NUMBER(7,2),  "COMM" NUMBER(7,2),  "DEPTNO" NUMBER(2,0)    ) TABLESPACE "USERS" ; Now we will insert some data into it : Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20); Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30); Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30); Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (...

[atoorpu@ORACLE1 adump]$ pwd /u01/app/oracle/admin/ORCL/adump [atoorpu@ORACLE1 adump]$ ls -1 /u01/app/oracle/admin/ORCL/adump | wc -l 22273 [atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l 11363 [atoorpu@ORACLE1 adump]$ sqlplus /"As sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 9 10:40:58 2015 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options If the audit files are in the database (sys.aud$). They can be cleaned up using: SQL> DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,    use_last_arch_timestamp => TRUE); If the audit files are in the OS. They can be cleaned up using: SQL> BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,    use_last_arch_timestamp ...

How to audit failed logon attempts Oracle Audit -- failed connection Background: In some situation DBA team wants to audit failed logon attempts when "unlock account"  requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked. Audit concern: Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production. Approach: 1. Turn on Oracle audit function by set init parameter:                audit_trail=DB Note: database installed by manual script, the audit function may not tur...

Oracle AUDIT Explained Enabling Auditing in Database #1 You can specify DB,EXTENDED in either of the following ways: ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE; ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE; However, do not enclose DB, EXTENDED in quotes, for example: ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE; OS Directs all audit records to an operating system file. #2 Directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory  and SYS audit records, which are always written to the operating system audit trail #3 The operating system and database audit trails both capture many of the same types of actions. Operating System Audit Record Equivalent DBA_AUDIT_TRAIL View Column SESSIONID                                  ...