Learn Database Concepts

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

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

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

                                        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 >>   Click on start Right click on Computer Click Properties Click Advanced system settings Click Environment Variables Go to system variables find Path and edit it 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 cr...

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