Oracle Fine-Grained Auditing (Oracle FGA)

Today my first time using Oracle Fine-Grained Auditing. Why do I need to use Oracle Fine-Grained Auditing?
This is because there is a table that is accessed by many procedures, triggers and packages. So that in case of deletion, I can not track from which the “DELETE” order came from.

I’ve tried using a trigger on a table to track it down. But this action, I think is still not enough. So I tried to use Oracle FGA.

Here’s an example that I use Oracle FGA :

– truncate the audit trail log
CONN sys/password AS SYSDBA
TRUNCATE TABLE fga_log$;

– add the policy TST_AUDIT to the MIS schema all column of the TRD_STATEMENTS table
BEGIN
DBMS_FGA.add_policy (object_schema => ‘MIS’,
object_name => ‘TRD_STATEMENTS’,
policy_name => ‘TST_AUDIT’,
audit_condition => NULL,
audit_column => NULL,
statement_types => ‘DELETE’);
END;

– check the audit trail result
SELECT * FROM dba_fga_audit_trail;

Cancel Oracle Trigger to Execute The Commands

Hi, I want to cancel the trigger of a table that does not work if the logic condition is not met. So as if I want the trigger to rollback (for example = cancel delete in before-delete trigger). At first, the trigger like this :

CREATE OR REPLACE TRIGGER MIS.TRIG01_TABLE_A
BEFORE DELETE
ON PANDAZEN.TABLE_A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO PANDAZEN.TABLE_B
SELECT * FROM PANDAZEN.TABLE_A;
END;

The trick to achieve what I want, ie – cancel delete command in the before-delete trigger is to use the command

raise_application_error

So, now triggernya be like this :

CREATE OR REPLACE TRIGGER MIS.TRIG01_TABLE_A
BEFORE DELETE
ON PANDAZEN.TABLE_A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF (:OLD.TABLE_A_VALUE = ‘OLD’) THEN
RAISE_APPLICATION_ERROR (-20000, ‘CANCEL DELETE’);
END IF;

INSERT INTO PANDAZEN.TABLE_B
SELECT * FROM PANDAZEN.TABLE_A;
END;

Bizarreness / Oddity of START WITH … CONNECT BY in Oracle SQL

Usually, the “start with .. connect by” clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). For example, see this data hierarchical :

000
    001
        100
            110
                111
                112
    002
        200
        250
    004
999

then the sql scripts like this :
create table tmp_test_connectby (
ORG_ID varchar2(3 byte),
org_parent varchar2(3 byte),
constraint c01 unique (org_id)
);

insert into tmp_test_connectby values (’000′, null);
insert into tmp_test_connectby values (’999′, null);

insert into tmp_test_connectby values (’001′, ’000′);
insert into tmp_test_connectby values (’002′, ’000′);
insert into tmp_test_connectby values (’004′, ’000′);

insert into tmp_test_connectby values (’100′, ’001′);
insert into tmp_test_connectby values (’110′, ’001′);

insert into tmp_test_connectby values (’111′, ’110′);
insert into tmp_test_connectby values (’112′, ’110′);

insert into tmp_test_connectby values (’200′, ’002′);
insert into tmp_test_connectby values (’250′, ’002′);

commit;

select lpad(‘ ‘,2*(level-1)) || to_char(org_id) s
from tmp_test_connectby
start with org_parent is null
connect by prior org_id = org_parent;
———————————————
From here, if i want to get result

002
    200
    250

i run this sql,
SELECT org_id
FROM tmp_test_connectby
WHERE org_id IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent = :b1))
ORDER BY org_id;

but when i make an update to record like this
update tmp_test_connectby
set org_parent = ’999′
where org_id = ’999′;

and run this again,
SELECT org_id
FROM tmp_test_connectby
WHERE org_id IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent = :b1))
ORDER BY org_id;

then you may have your database memory consume to nothing,
you may got this error too :

ORA-04030: out of process memory when trying to allocate 80 bytes (kxs-heap-w,cursor work heap)
ORA-04031: unable to allocate 8224 bytes of shared memory…

so be carefull about using “START WITH … CONNECT BY”, if something wrong, check your data

How to exit from a procedure in Oracle

To exit from function is easy, just put “RETURN”

CREATE OR REPLACE FUNCTION MIS.FC_GET_ACCRUAL_COM(VAGR IN VARCHAR2) RETURN NUMBER IS
VNUM NUMBER;
BEGIN
SELECT AGR INTO VNUM FROM TAGR WHERE AGR = ‘AA’;

IF VNUM = 0 THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
RETURN VNUM;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/

To exit from procedure also easy, just use “RAISE”

CREATE OR REPLACE PROCEDURE TEST_EXIT
IS
HOI EXCEPTION;
BEGIN
RAISE HOI;
DBMS_OUTPUT.PUT_LINE (‘hoi’);
EXCEPTION
WHEN HOI
THEN
NULL;
END;

Delete duplicate rows or one of the double records

Many a time we face cases where we have to delete one of double record.

So what can we do? Here an example of it,

SQL> select * from customer;

LAST_NAME FIRST_NAME NATION
—————————————–
XHI WILLIAM INDONESIA
BRUNI CARLA FRANCE
THERON KEIRA ENGLAND
PITT DAVID USA
PITT DAVID USA

To get the double record,

SELECT last_name, first_name
FROM customer
GROUP BY last_name, first_name
HAVING count(‘x’) > 1;

LAST_NAME FIRST_NAME
————————–
PITT DAVID

To delete one of the double record, (please add some filter to specify which record can be deleted)
1. standard
DELETE FROM CUSTOMER A
WHERE ROWID (
SELECT MAX(ROWID)
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
);

2. using IN
DELETE FROM CUSTOMER A
WHERE (A.last_name, A.first_name, A.nation) IN (
SELECT B.last_name, B.first_name, B.nation
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
AND A.rowid > B.rowid);

3. For the better query use “EXISTS” instead of “IN”,

DELETE FROM CUSTOMER A
WHERE EXISTS (
SELECT ‘x’
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
AND A.rowid > B.rowid);

Move to another tablespace

1. To move a TABLE TO another TABLESPACE, issue the following command:

ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace>;

2. To move an INDEX, use the following command:

ALTER INDEX <index_name> REBUILD TABLESPACE <new_tablespace>;

3. To move the LOB when moving the table, use the following:

ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace> LOB (lob_item) STORE AS (TABLESPACE      <another_new_tablespace>);

4. To move the PARTITION TABLE to another TABLESPACE, use:
ALTER TABLE <table_name> MOVE PARTITION <partition_name> TABLESPACE <new_tablespace>;