Details SCN in Oracle Database

System Change Number (SCN) in Oracle Database

What is an SCN in Oracle?

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction

Following are the way to check current SCN in Oracle Database:

1. Fetch the current SCN number in v$database view.

Select CURRENT_SCN from v$database;

SQL> Select CURRENT_SCN from v$database;
CURRENT_SCN
-----------
3208446

2. Get from flashback package.

select dbms_flashback.get_system_change_number from dual;

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3208460

3. Get the SCN from timestamp_to_scn function.

select timestamp_to_scn(sysdate) from dual;

SQL> select timestamp_to_scn(sysdate) from dual;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
3208486

Example of TIMESTAMP_TO_SCN & SCN_TO_TIMESTAMP function

Select sysdate, timestamp_to_scn(sysdate) from dual;

SYSDATE TIMESTAMP_TO_SCN(SYSDATE)
——————- ————————-
02-04-2019 15:25:05 3408525

Select scn_to_timestamp(3408525) from dual;

SCN_TO_TIMESTAMP(3208525)
—————————————————————————
02-APR-19 03.25.03.000000000 PM

select timestamp_to_scn(to_timestamp('02-04-2019 15:25:05','DD-MM-YYYY HH24:MI:SS')) "SCN" from dual;
SCN
----------
3408525

 

PFile and SPFile

Enter user-name: wz/wz

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      F:\APP\XEN-ICT\PRODUCT\11.2.0\

                                                 DBHOME_1\DATABASE\SPFILEWZPDCL

                                                 .ORA

SQL> select value from v$parameter where name = ‘spfile’;

VALUE

——————————————————————————–

F:\APP\XEN-ICT\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEWZPDCL.ORA

SQL>

 

Welcome!

Welcome to Md. Mosarraf Hossen’s blog.

Fine Grained Auditing (FGA)

Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.

CONN audit_test/password CREATE TABLE emp ( empno     NUMBER(4) NOT NULL, ename     VARCHAR2(10), job       VARCHAR2(9), mgr       NUMBER(4), hiredate  DATE, sal       NUMBER(7,2), comm      NUMBER(7,2), deptno    NUMBER(2) ); INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Tim’, 1); INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Larry’, 50001); COMMIT;

The following policy audits any queries of salaries greater than £50,000.

CONN sys/password AS sysdba BEGIN DBMS_FGA.add_policy( object_schema   => ‘AUDIT_TEST’, object_name     => ‘EMP’, policy_name     => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column    => ‘SAL’); END;

Querying both employees proves the auditing policy works as expected.

	CONN audit_test/password SELECT sal FROM emp WHERE ename = ‘Tim’; SELECT sal FROM emp WHERE ename = ‘Larry’; CONN sys/password AS SYSDBA SELECT sql_text FROM   dba_fga_audit_trail; SQL_TEXT —————————————— SELECT sal FROM emp WHERE ename = ‘Larry’ 1 row selected. SQL>

Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:

	BEGIN DBMS_FGA.add_policy( object_schema   => ‘AUDIT_TEST’, object_name     => ‘EMP’, policy_name     => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column    => ‘SAL’, handler_schema  => ‘AUDIT_TEST’, handler_module  => ‘FIRE_CLERK’, enable          => TRUE); END; /

The DBMS_FGA package contains the following procedures:

• ADD_POLICY
• DROP_POLICY
• ENABLE_POLICY
• DISABLE_POLICY

In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.

	— Clear down the audit trail. CONN sys/password AS SYSDBA TRUNCATE TABLE fga_log$; SELECT sql_text FROM dba_fga_audit_trail; no rows selected. — Apply the policy to the SAL column of the EMP table. BEGIN DBMS_FGA.add_policy( object_schema   => ‘AUDIT_TEST’, object_name     => ‘EMP’, policy_name     => ‘SAL_AUDIT’, audit_condition => NULL, — Equivalent to TRUE audit_column    => ‘SAL’, statement_types => ‘SELECT,INSERT,UPDATE,DELETE’); END; /