Tuesday, September 15, 2015

PLSQL code to audit all (similar) tables in schema - oracle



I was playing around with some plsql code today.Just posting this sample plsql code as I thought this can help people.

This is a sample code that will search for all tables with name  ABC,ABC1,ACB2 in all schema's in database and execute a NOAUDIT against these tables. This sql can be modified to accommodate any changes where you want to run a query against all tables & schema's.


sample code:

declare
    result sys_refcursor;
    V_OWNER Varchar2(100):='SCHEMA_NAME'; --- update this with schema you want to audit.
    strTableOwner Varchar2(100);
strTableName Varchar2(100);
    strQuery varchar2(300);
begin

open result for
    select owner,table_name from user_tables where
    table_name in ('ABC','ABC1',ABC2')  and owner = 'V_OWNER' order by table_name;

loop
    fetch result into strTableOwner,strTableName;
    exit when result%notfound;
     
    DBMS_OUTPUT.PUT('NOAUDIT DELETE,UPDATE on '||strTableOwner||'.'||strTableName1||';');
   
strQuery := 'NOAUDIT DELETE,UPDATE on '||strTableOwner||'.'||strTableName1||' ';
    execute immediate strQuery;

 DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('SUCCESFULL');
end loop;

close result;

end;
/


Note : you can edit this part to what ever you want. >>>> NOAUDIT DELETE,UPDATE on

No comments:

Post a Comment