Materialized view a study notes

Every time you use a normal view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in (also known as snapshots in prior releases), unlike a view it contains space and storage just like a regular table.You can either use materialized view against a local table or a remote table .Using materialized views against remote tables is the simplest way to achieve replication of data between sites.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it.

Materialized Views are mainly used for two reasons,
1) Replication of data to separate remote databases.
2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

With Materialized Views the performance can be improved significantly, because when a materialized view is created it stores all the data along with the execution plans. 

Basic syntax:-
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;

This is a very basic syntax , even you can specify storage level parameter while configuring materialized view .

The BUILD clause options are shown below,
IMMEDIATE(Default) : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.

The REFRESH clause options are shown below,
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.- and its time consuming .
Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.

FORCE (Default): A fast refresh is attempted. If one is not possible a complete refresh is performed.If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

Note:- A materialized view get locked while its being refreshed .

A refresh can be triggered in one of two ways,
ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

Note:- If you want to refresh materialized view automatically they you must set job_queue_processes=n 

ON DEMAND (Default)  : The refresh is initiated by a manual request or a scheduled task.
if your refresh interval is very large and you need to refresh in between use the follwoing procedure.

SQL> EXEC DBMS_MVIEW.REFRESH('mv_name', 'C');
C -> Complete refresh 
F ->FAST refresh 
? -> Force refresh

When you create a materialized view a table segment(with same name) will get automatically created to hold the data represented by the materialized view .

If you have a large table , then creating materialized through normal method take time, especially when base table on a remote box. In this case you can make use of PREBUILT TABLE . Here you can export the  table from remote box and import the table on the target box .Depends on the requirement you can either import the full table or import a part of the table by using impdp parameter QUERY.

That is ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query. 

One simple example  is given below , 

SQL> conn hr/hr
Connected.
SQL> create table my_objects as select object_name,object_type,created from user_objects;

Table created.

SQL> create materialized view mv_objects on prebuilt table as select object_name,object_type,created from user_objects;
create materialized view my_objectsd on prebuilt table as select object_name,object_type,created from user_objects
     *
ERROR at line 1:
ORA-12059: prebuilt table "HR"."MV_OBJECTS" does not exist

That is for materialized view we need to use the same name that we used for prebuilt table.
SQL> create materialized view my_objects on prebuilt table as select object_name,object_type,created from user_objects;

Materialized view created.

SQL> create table t as select * from employees;

Table created.

SQL> col object_name for a15
SQL> set lines 222
SQL> select object_name,object_type,created from user_objects where object_name='T';

OBJECT_NAME     OBJECT_TYPE             CREATED
--------------- ----------------------- ---------
T               TABLE                   07-FEB-16

Check the contents of the materialized view 

SQL>  select object_name,object_type,created from my_objects where object_name='T';

no rows selected

As Materialized view is not yet refreshed , I did a manual refresh .

SQL> EXEC DBMS_MVIEW.REFRESH('MY_OBJECTS','C');

PL/SQL procedure successfully completed.

SQL> select object_name,object_type,created from my_objects where object_name='T';

OBJECT_NAME     OBJECT_TYPE             CREATED
--------------- ----------------------- ---------
T               TABLE                   07-FEB-16

SQL>

Note:- You can't do any DML on the underlying  table that hold the data of the materialized view . if you tried to do so , you will get error like following .

SQL> insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16');
insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16')
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL>

ENABLE QUERY REWRITE
A materialized view can be stored in the same database as it's base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. 

The following query does an aggregation of the data in the EMP table.

CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Create a materialized view to perform the aggregation in advance, making sure you specify the ENABLE QUERY REWRITE clause.

CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE 
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM   emp
GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    21 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV |     3 |    21 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

More example of the query rewrite functionality is given in  below link.
http://www.sqlsnippets.com/en/topic-12918.html

CREATE MATERIALIZED VIEW LOG
A complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log. 

Common usage syntax:- 
create materialized view log on table with primarykey|rowid ;
where ,
PRIMARY KEY 
Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log(this is default)
Note:- Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID. 
ROWID 
Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.

Note:- The materialized view log supports fast refresh for primary key materialized views only. If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default. However, the database does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time.

If your table have a PRIMARY KEY, you don't need to creata materialized view log WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.

As more often rowids can change.Partitioned tables with enable row movement allow rowids  to change.  ALTER TABLE t MOVE will change rowids.  In 10g more and more things will  change rowids.So oracle recommented to create primary key based materialized view log.

Eg:- create materialized view log on employees with primary key;

Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.

Basic example for creating a materialized view ,

CREATE MATERIALIZED VIEW mv_emp_pk
 REFRESH FAST START WITH SYSDATE 
 NEXT  SYSDATE + 1/1440 ---- > every one minute 
 WITH PRIMARY KEY -- this is default , no need to specify though 
 AS SELECT employee_id,name,salary FROM emp@remote_db;

ON COMMIT
In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.

Assuming that employee table  have primary key , so create a materialized view log on employee table.If your table does'nt have a primary key defined you will get error like ORA-12014: table does not contain primary key constraint.
sql>conn hr/hr
Connected
SQL> create materialized view log on employees with primary key;
Materialized view log created.

You can see following table got created while creating materialized view log on employees table ,
MLOG$_EMPLOYEES:- This is a table created along with the materialized view. It contains data that has changed in the base table.
RUPD$_EMPLOYEES:- This table is created when a materialized view uses primary key for fast refresh. This is used to support updatable materialized views.

SQL> conn / as sysdba
Connected.
SQL> grant select any table to mahi;

Grant succeeded.

SQL> grant create any materialized view to mahi;

Grant succeeded.

SQL> create database link hrlink connect to hr identified by hr using 'study';

Database link created.

SQL> conn mahi/mahi
Connected.
SQL>
SQL> create materialized view mv_hr_employee REFRESH FAST ON COMMIT as select * from employees@hrlink;
create materialized view mv_hr_employee
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

While googling I learned following - 
Things learned:- This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported in remote databases. 

SQL> conn hr/hr
Connected.
SQL> create materialized view mv_employee REFRESH FAST ON COMMIT as select * from employees;

Materialized view created.

SQL> select count(1) from employees;

  COUNT(1)
----------
       107
SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       107
SQL> delete from employees where EMPLOYEE_ID=206;

1 row deleted.

SQL> select count(1) from employees;

  COUNT(1)
----------
       106
SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       107
SQL> commit;

Commit complete.

SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       106
SQL>

As soon as I put commit , my materialized view got reflected 

Check the details of the materialized view using following query ,
SQL> select mview_name, refresh_method, refresh_mode, build_mode, fast_refreshable from user_mviews where mview_name = 'MV_EMPLOYEE';

How to know when was the last refresh happened on materialized views:
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;

Difference between View vs Materialized View in database
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

Points to note:- 
*It is  always recommended to gather the statistics of the underlying table after materialized view got created .

*Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DDL on the base table.

*If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits

Value based and Fine grained auditing in oracle

A. value base auditing
The standard database auditing just described can catch the fact that a command was executed against a table, but not necessarily the rows that were affected. For example, issuing AUDIT INSERT ON HR.EMPLOYEES will cause an audit record to be generated whenever a row is inserted into the named table, but the record will not include the actual values of the row that was inserted. On occasion, you may want to capture these. This can be done by using  Value-based auditing. It used database triggers for its functioning.

When a user inserts, updates, or deletes data from a table with the appropriate trigger attached, the trigger works in the background to copy audit information toa table that is designed to contain the audit information. Value-based auditing tends to degrade performance more than standard database auditing because the audit trigger code must be executed each time the insert, update, or delete operation occurs.
--creation of trigger_table
SQL> create table system.audit_employees (osuser long, changedt date, ipaddr varchar2(90), newsalary varchar2(500));
Table created.
SQL>

--creation of trigger
SQL> CREATE OR REPLACE TRIGGER system.hrsalary_audit
  2  AFTER UPDATE OF salary
  3  ON hr.employees
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7  IF :old.salary != :new.salary THEN
  8  INSERT INTO system.audit_employees
  9  VALUES (sys_context('userenv','os_user'), sysdate,
 10  sys_context('userenv','ip_address'),
 11  :new.employee_id ||
 12  ' salary changed from '||:old.salary||
 13  ' to '||:new.salary);
 14  END IF;
 15  END;
 16  /
Trigger created.
SQL>

--now if anyone change the salary in the hr.employees table the entry will be made in the system's audit_employees table.

SQL> update hr.employees set salary=4500 where employee_id=124;
1 row updated.

--entries done in the audit table.

SQL> select * from audit_employees;
OSUSER                         CHANGEDT  IPADDR       NEWSALARY
------------------------------ --------- ------------ -------------------------------------
KSNDMCPC2012004\Admin          16-FEB-13              124 salary changed from 5800 to 4500

Fine grained auditing
Database auditing can capture all accesses to a table, whether SELECT or DML
operations. But it cannot distinguish between rows, even though it might well be that
only some rows contain sensitive information.

Fine-grained auditing, or FGA, can be configured to generate audit records only when
certain rows are accessed, or when certain columns of certain rows are accessed. It can
also run a block of PL/SQL (through audit handler)  code when the audit condition is breached. FGA is configured with the package DBMS_FGA. To create an FGA audit policy,
use the ADD_POLICY procedure, which takes these arguments:

OBJECT_SCHEMA:- The name of the user who owns the object to be audited. This
defaults to the user who is creating the policy.
OBJECT_NAME:- The name of the table to be audited.
POLICY_NAME:- Every FGA policy created must be given a unique name.
AUDIT_CONDITION:- An expression to determine which rows will generate an audit
record. If left NULL, access to any row is audited.
AUDIT_COLUMN:- A list of columns to be audited. If left NULL, then access to any
column is audited.
HANDLER_SCHEMA:- The username that owns the procedure to run when the audit
condition is met. Default is the user who is creating the policy.
HANDLER_MODULE:- A PL/SQL procedure to execute when the audit condition is met.
ENABLE:- By default, this is TRUE: the policy will be active and can be
disabled with the DISABLE_POLICY procedure. If FALSE, then the ENABLE_POLICY procedure must be used to activate the policy.
STATEMENT_TYPES:- One or more of SELECT, INSERT, UPDATE, or DELETE to define
which statement types should be audited. Default is SELECT only.
AUDIT_TRAIL:- Controls whether to write out the actual SQL statement and its
bind variables to the FGA audit trail. The default is to do so.
AUDIT_COLUMN_OPTS:- Determines whether to audit if a statement addresses any or all
of the columns listed in the AUDIT_COLUMNS argument. Options
are DBMS_FGA.ANY_COLUMNS, the default, or DBMS_FGA_ALL_COLUMNSThe other DBMS_FGA procedures are to enable, disable, or drop FGA policies.
To see the results of fine-grained auditing, query the DBA_FGA_AUDIT_TRAIL view.

For example for FGA auditing click on following link

http://vishwanath-dbahelp.blogspot.in/2011/09/setting-fga-at-schema-level.html

Auditing in Oracle needs to know - part3

Next is privilege level auditing,

B. Auditing on privilege Level
All system privileges can be audited. The different privileges can be selected from system_privilege_map. Statement and privilege auditing are seperated in the oracle documentation and data dictionary views . However they use identical syntax and considering them as identical will simplify things greater. For example ,

SQL> enabe audit drop any table;

the reflect will populate both in DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS .
First note that the AUDIT syntax does not make a distinction between statement
and privilege options. Is “CREATE SESSION” a statement or a privilege option?
What about “DROP ANY TABLE?” According to the Oracle views, each appears
to be both, but only one audit trail record results from each audited action.

Example: auditable system privileges

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from system_privilege_map;
PRIVILEGE NAME
---------- ----------------------------------------
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE

..... not entire result is shown ......

-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP

We are giving alter system privileges to test user and enabling audit on it,

SQL> grant alter system to test;

Grant succeeded.

SQL> audit alter system by access;

Audit succeeded.

SQL> select sid, serial# from v$session where username='TEST';

SID SERIAL#
---------- ----------
149 1568

SQL> conn test
Enter password:
Connected.
SQL> alter system kill session '149,1568' immediate;

System altered.

SQL> conn /as sysdba
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME                      ACTION_NAME
------------------------------ ----------------------------
TEST                               ALTER SYSTEM
TEST                                LOGOFF BY CLEANUP
TEST                                 LOGOFF

SQL>

C. Auditing on object Level

Objects that can be audited are : tables, views, sequences, packages, stored
procedures/functions. Note that because some objects may be dependant of other
objects (example function -> view -> table ) as a result several audit records
might be inserted when these objects are audited.

Object auditing options are set for all users of the database and cannot be set
for individual users.What options can be set? This can be seen from all_def_audit_opts.

Example object auditing options

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

These correspond to the following object options respectively:

alter, audit, comment, delete, grant, index, insert, lock, rename, select, update, reference and execute .

All except reference and execute can be applied to tables, otherwise you will get the error ora-1982 "invalid auditing option for tables".

SQL> audit select on scott.emp by session;

Audit succeeded.

Check: Which objects are audited

SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner='SCOTT' and OBJECT_NAME='EMP';

OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

Generate some audit information:

SQL> connect scott/scott
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect hr/hr
SQL> select * from scott.emp;
=> ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect system/system
SQL> select * from scott.emp;
=> all scott.emp's rows are shown

Results of auditing:

SQL> connect system/system
SQL> select username, priv_used, ses_actions from dba_audit_object where obj_name='EMP' and owner='SCOTT';
USERNAME    PRIV_USED                  SES_ACTIONS
----------       -------------------           -------------------
SCOTT                                            ---------S------
MAHI            SELECT ANY TABLE      ---------S------
HR                                                  ---------F------
SYSTEM         SELECT ANY TABLE      ---------S------

Auditing in Oracle needs to know - part2

Apart from SYSDBA auditing, Oracle provides three auditing techniques:

1. standart auditing

Auditing based on statement, Privileges and object level. 

In standard auditing we won't get any information of the event like what actually happened on tables ,for example what got inserted or deleted etc . Value-based audit will give this feature .

2. Value-based auditing
Value-based auditing uses database triggers. Whenever a row is inserted, updated,
or deleted, a block of PL/SQL code will run that can (among other things)
record complete details of the event. 

3.Fine-grained auditing
Fine-grained auditing allows tracking access to tables according to which rows
(or which columns of the rows) were accessed. It is much more precise than
either database auditing or value-based auditing, and it can limit the number
of audit records generated to only those of interest.

The standard auditing having 3 levels of auditing:

Statement: Audit all action at any type of objects.

Privilege: audit action on system level privileges

Object_level: Specific audit action lie select, update, insert or delete.

There is no clear cut demarcation between these three auditing , all are some how related to each other.

For all the 3 level of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also audit on if the access was successful (whenever successful) or not (whenever not successful)

The difference between BY SESSION and BY ACCESS is that when you specify BY SESSION Oracle will try to merge multiple audit entries into one record when the session and the action audited match.

That is ,

audit by access- audit every time you access- will give accurate picture about what is going on .

audit by session - audit only once per access during the session - Here after session got created and if you are going to insert 100 times on a table ,then it will logged only for the first attempt .

 CREATE SESSION is a DDL statement Oracle audits this statement by access.

So for statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.

A. statemente level auditing

Statement auditing is the selective auditing of related groups of statements for a type of database structure or type of schema object for ddl (data definition language) or dml (data manipulation language) statements. For example when 'audit table' is audited create , drop and truncate table statements are audited. Statement level auditing is broad .

The statements that can be audited can be seen from stmt_audit_option_map.

SQL> select * from stmt_audit_option_map;

SQL> select * from stmt_audit_option_map where name like '%TABLE%'; etc,

How to use;

SQL> sho user;

USER is "SYS"

SQL> audit table by scott, system; -- ddl level

Audit succeeded.

SQL> conn scott/scott

Connected.

SQL> create table aud_test as select * from EMP;

Table created.

SQL> drop table aud_test;

Table dropped.

SQL>

SQL> alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL>  select USERNAME,obj_name,ACTION_NAME,TIMESTAMP from DBA_AUDIT_TRAIL;

USERNAME     OBJ_NAME     ACTION_NAME         TIMESTAMP

------------ ------------ ------------------- --------------------

SCOTT        AUD_TEST     CREATE TABLE        15-FEB-2013 14:02:35

SCOTT        AUD_TEST     DROP TABLE          15-FEB-2013 14:22:32

Statement level auditing at DML level :-

Eg:-

SQL> audit insert table by scott whenever successful;

 Note:- In statement level auditing we are not specifying any object name to audit , that is statement level auditing broad in its audit action .

we can audit the session level event by using following statement audit ,

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> audit session by access;

Audit succeeded.

SQL> conn scott/scott
Connected.
SQL> conn hr/hr
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL>  select username,timestamp,logoff_time,ACTION_NAME from dba_Audit_session;

USERNAME     TIMESTAMP            LOGOFF_TIME          ACTION_NAME

------------ -------------------- -------------------- -------------------

SYSMAN       15-FEB-2013 16:25:18 15-FEB-2013 16:31:29 LOGOFF

SCOTT        15-FEB-2013 16:25:31 15-FEB-2013 16:25:34 LOGOFF

HR           15-FEB-2013 16:25:35 15-FEB-2013 16:26:36 LOGOFF

SQL>

Audit that ebabled on statement level can be viewed by ,

 SQL> select * from DBA_STMT_AUDIT_OPTS;

Note the following views gives you which audits are enabled:

dba_obj_audit_opts
dba_priv_audit_opts
dba_stmt_audit_opts

Disabling Audit:-

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

noaudit table by scott;

noaudit insert table by scott;

noaudit session by access;

Auditing in Oracle needs to know - part1

No matter how good your security policies are, there will be occasions when a policy is not enough.The most extreme example of this is you, the database administrator. Anyone with the SYSDBA privilege can do anything within the database. So it is necessary to audit all SYSDBA activity. For normal users, you may also wish to track what they are doing. You may not be able to prevent them from breaking company rules on access to data, but you can track the fact that they did it.

Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file. From Oracle 11g, auditing is enabled for some system level privileges.

SYSDBA auditing
There is an instance parameter AUDIT_SYS_OPERATIONS. If it is set to TRUE (the default is FALSE), then every statement issued by a user connected AS SYSDBA or AS SYSOPER is written out to the operating system’s audit trail. This gives a complete record of all work done by the DBA.Your system needs to be configured in such a way that the DBA has no access to the audit records that track his activity: they should be accessible only to the computer’s system administrator. If the DBA were also the system administrator, then the auditing would be useless.
The destination of the SYS audit records is platform specific. On Windows, it is the Windows Application Log; on Unix it is controlled by the AUDIT_FILE_DEST parameter. This parameter should point to a directory on which the Oracle owner has write permission

How to setup SYSDBA auditing
1. Before setting up any kind of database auditing, an instance parameter must be set: AUDIT_TRAIL. This has four possible values:
• NONE (or FALSE) Database auditing is disabled, no matter what auditing you attempt to configure and is default.
• OS Audit records will be written to the operating system’s audit trail: the Application Log on Windows, or the AUDIT_FILE_DEST directory on Unix.
• DB (or TRUE) The audit records are written to a data dictionary table, SYS.AUD$. There are so many views that let you see the contents of this table.
• DB_EXTENDED As DB, but including information on the SQL statements with bind variables that generated the audit records.

Example:-

SQL> sho user;
USER is "SYS"
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\CRAZE\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

SQL> alter system set audit_trail=DB;
alter system set audit_trail=DB
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL> alter system set audit_sys_operations=TRUE scope=spfile;

System altered.

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\CRAZE\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  931135488 bytes
Fixed Size                  2070096 bytes
Variable Size             285215152 bytes
Database Buffers          637534208 bytes
Redo Buffers                6316032 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\CRAZE\ADUMP
audit_sys_operations                 boolean     TRUE
audit_trail                          string      DB
SQL> alter user mahi identified by mahi;

User altered.

SQL> alter user mahi default tablespace USERS;

User altered.

SQL>

You can see the activity done by user connected as sysdba in event viewer

Here I usedaudit_trail to DB for my audit purpose .The audit records go to the filesystem (in UNIX) or Event Log (in Windows), not to the database tables, even if the parameter audit_trail is set to DB. The location in UNIX is the filesystem specified by the initialization parameter audit_file_dest, which defaults to $ORACLE_HOME/rdbms/audit.This parameter is independent of the other audit parameter audit_trail. The SYSDBA auditing can be enabled or disabled regardless of whether regular auditing is set.

what is PCTFREE , PCTUSED and FREELIST in oracle

Before discussing with PCTFREE and PCTUSED we need to understand the structure of a database block.
Overview of Data Blocks

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks. The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Below illustrates the format of a data block.

Header (Common and Variable)

The header contains general block information, such as the block address and the type of segment (for example, data or index).

Table Directory

This portion of the data block contains information about the table having rows in this block.

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.

Overhead

The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Row Data

This portion of the data block contains table or index data. Rows can span blocks.

Free space

Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).

In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.

The PCTFREE parameter 

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:

PCTFREE 20 

This states that 20% of each data block in this table's data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size.Below figure illustrates PCTFREE.

The PCTUSED Parameter

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a CREATE TABLE statement:

PCTUSED 40 

In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Until reaching this value oracle utilize the free space for the updation of existing rows .Below figure illustrates this.

How PCTFREE and PCTUSED work together

PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Below figure illustrates the interaction of these two parameters.

In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space (PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can use the space once it falls below PCTFREE, the space reserved for updates but not accessible to inserts.

A FREELIST is where Oracle keeps tracks of blocks under the high-water mark for an object. Each will have at least one FREELIST associated with it. As blocks are used, they will be placed or taken off the FREELIST as needed. It is important to note that only blocks under the high-water mark of an object will be found on the FREELIST. The blocks that remain above the high-water mark will be used only when the FREELISTS are empty. In this fashion, Oracle postpones increasing the high-water mark for an object until it must.An object may have more than one FREELIST. If you anticipate heavy insert or update activity on an object by many concurrent users, configuring more than one FREELIST can make a major positive impact on performance (at the cost of possible additional storage). Individual sessions will be assigned to different FREELISTS, and when they need space, they will not contend with each other.

For each data and index segment, Oracle maintains one or more free lists—lists of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE. These blocks are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the INSERT statement, and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

After you issue a DELETE or UPDATE statement, Oracle processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions.

An object may have more than one FREELIST. If you anticipate heavy insert or update activity on an object by many concurrent users, configuring more than one FREELIST can make a major positive impact on performance (at the cost of possible additional storage). Individual sessions will be assigned to different FREELISTS, and when they need space, they will not contend with each other.  Every segment will have 1 freelist by default .If there is only one FREELIST, only one transaction at a time may review and modify this list—they would have to wait for each other. Multiple FREELISTS and FREELIST GROUPS serve the purpose of increasing concurrency in such a case, as the transactions may each be looking at different lists and not contending with each other.