AUDIT DDLS in database with trigger

-- 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_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME,
         ORA_DICT_OBJ_OWNER
        );
END;

Sample output :

Sample Audit Table Output

Column level triggers - Oracle

In this case I have  requirement where I need to update account_status column in same table with user status. When ever user deleted flag is 0

Lets create a table:

Create table users users (username varchar2(20)),fullname varchar2(30),account_status varchar2(10) default OPEN,deleted number(1), LOCK_DATE date);

Now insert some values:

insert into users values('ARVIND111','ARVIND KUMAR','',,''SYSDATE);
insert into users values('RAGHU111','RAGHU RAM','','',SYSDATE);
insert into users values('RAJ111','RAJ KUMAR','',''SYSDATE);
insert into users values('HARI111','HARI KRISHNA','',''SYSDATE);


Lets say you want to update a column with the account status 'OPEN' or 'LOCKED'. when ever we have a update in another column.

Sample :

In the below case, when ever we update deleted 0, we will update another column saying the account_status is open. If deleted =1, then the account_status is locked.

Lets create trigger now :

create or replace TRIGGER USER_LOCKDATE
BEFORE UPDATE OF DELETED ON users
FOR EACH ROW
BEGIN
  IF (:NEW.DELETED=0)
  then
  :NEW.ACCOUNT_STATUS := 'OPEN';
  ELSE
  :NEW.ACCOUNT_STATUS := 'LOCKED';
  :new.LOCK_DATE := SYSDATE;
  end if;
  END;

Note: 
Enable trigger by using. ( Alter trigger USER_LOCKDATE enable; )
Disable trigger by using. ( Alter trigger USER_LOCKDATE disable; )

Now with above trigger, when you update deleted column, it will update the account_status and lock_date.

Trigger to disable create objects in database starting with TMP or BAK

---- 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%.

create shared disks (vdi) on Virtualbox for ASM - vboxmanage command line

                                        vboxmanage command line 

For our scenario :

Lets create shared disks (vdi) on Virtualbox for ASM setup. 

If you are getting error like below make sure you have virtual-box set in your environmental path:

In my case I have to add "C:\Program Files\Oracle\VirtualBox\" to my path. To add it to your environmental path goto :

Startup >> computer (right click) >> properties >> advanced system settings >> environmental variables >>  

1. Click on start
2. Right click on Computer
3. Click Properties
4. Click Advanced system settings
5. Click Environment Variables
6. Go to system variables
7. find Path and edit it
8. add your file location at the end of the box.

Add your virtual box installed path, usually c\programe files \ oracle

Now test the vboxmanage cmd :

You will get something like this if you have any virtual machines created.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\arvind>vboxmanage list vms
"linux1" {69b544d2-bf64-4f83-a525-a7f2dd2007e7}


Now  lets create a shared asm drive for ASM :

Note : Always pick a different location, but make sure they are outside the existing VM directory.

$ mkdir C:/VirtualBox/
$ cd C:/VirtualBox/
$
$ # Create the disks and associate them with VirtualBox as virtual media.

VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI --variant Fixed

C:\Virtualdisks>VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI
 --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: c8a5ef46-7304-4ea0-b685-e1efbf6ab41e

$ # Connect them to the VM.

VBoxManage storageattach linux1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asm1.vdi --mtype shareable


$ # Make shareable.

$ VBoxManage modifyhd asm1.vdi --type shareable

We now have shared disks added to Virtual box.

We can also add these shared disks via Graphical interface :

See below for instructions:


Step 1 :

Select >> add hard disks >>

Step 2:


select vdi

Step 3


select fixed size

Step 4

it will be added now.

Step 5


select (virtual box top left) File >> virtual Media manager >> select disk you want to modify >> right click >> click modify

Step 6


Select modify and close.

Note : 

If you are going to use this for RAC setup. make sure you attach this shared disks to both of your environments.

Trigger to backup the data before delete or update on a table - Oracle

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 (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);

Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);

select count(*) from abc;
-- 5 rows

Lets create a backup table to store data. we are only storing part of the original table data, we can edit as per requirement :

  CREATE TABLE "SCOTT"."ABC_BAK" 
   ( "EMPNO" NUMBER(4,0), 
"ENAME" VARCHAR2(10 BYTE), 
"JOB" VARCHAR2(9 BYTE), 
"MGR" VARCHAR2(20 BYTE), 
"T_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
   ) TABLESPACE "USERS" ;


   select count(*) from abc_bak;
   
   0 rows
   
   
Now lets create a trigger, that will triger all the data from the table and store it in backup table before delete or update:

CREATE OR REPLACE TRIGGER ABC_BAK1 
BEFORE UPDATE OR DELETE 
  ON ABC FOR EACH ROW
  BEGIN
      INSERT INTO ABC_BAK
 ( EMPNO,
   ENAME,
   JOB,MGR
    )
VALUES
 ( :old.EMPNO,
   :old.ENAME,
   :old.JOB,
   :old.MGR); 
END;


===========================================================

NOTE : You also add username and Host machine by adding below to trigger
You need to declare the variable first and then assgn values to it

DECLARE
   v_username varchar2(10);
      
  BEGIN
  -- Find username of person performing the DELETE on the table

   SELECT user INTO v_username FROM dual;

Add these to values ----

V_USERNAME,
   sys_context('userenv','host')   --- You can also add host machine here

===========================================================

Lets test our trigger is working fine.

delete from abc where ename=SCOTT;
delete from abc where ename=ALENN;
update abc set ename=ADAM where ename=ADAMS;
.
.
.
.
do some activity and test then validate the bak table.


Select * from abc_bak;

    EMPNO ENAME      JOB       MGR                  T_STAMP                       

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

      7788 SCOTT                                     09-FEB-15 06.35.28.862219000 PM 

      7499 ALLEN                                     09-FEB-15 06.35.28.862219000 PM 

      7521 WARD                                      09-FEB-15 06.35.28.862219000 PM 

      7876 ADAMS      CLERK                          09-FEB-15 06.35.53.457126000 PM 

      7900 JAMES      CLERK                          09-FEB-15 06.35.53.462433000 PM 

      7902 FORD       ANALYST                        09-FEB-15 06.35.53.466738000 PM 

      7934 MILLER     CLERK                          09-FEB-15 06.35.53.472108000 PM 

      7788 SCOTT      ANALYST   7566                 09-FEB-15 06.37.30.307425000 PM 

 8 rows selected