SMB file sharing between Mac OS Catalina to Ubuntu Mantic 23.10

Connect to SQL Server Database 2019 using Oracle SQL Developer

 

Migrate Data from SQL Server Database 2019 to Oracle Database 21c using Oracle SQL Developer

 

FTP file sharing from Mac OS Catalina to Ubuntu 22.04

 

Install MacOS Catalina 10.5 On Virtual box 6 1 Ubuntu 22 04

 

Using Oracle DML Database Trigger For Auditing Purpose

 Auditing Table means keeping monitoring of all the dml activities occurred on a specific table of the database like Insert, Update or Delete single row row at a time from the table and when. It is like tracking the users activity who are messing with your data.

Monitoring user activity for missing, manipulating records is important part for a business. In this video I tried to show how to use trigger on a table to get information about the client machine, ip address etc. All this information will be stored in a table which can be used in presentation to analyses any manipulated data.
Please download the following script from the below link

https://drive.google.com/file/d/1fH79eeLUd-Xcz42qSjsAfK8wYfgtE1y9/view?usp=sharing

 

 

Extract data from all tables of all schema into csv file using UTL_DIR

I was working on a metadata comparison project using UTL_DIR to write to file on  database server As having  knowledge on data dictionary I have manipulated dba_* tables to get information and extract it to csv files. I am providing below the list of database metadata object I used to get the information.

1.   DBA_USERS
2.   DBA_TABLES
3.   DBA_TAB_PRIVS
4.   DBA_ROLE_PRIVS
5.   DBA_TAB_COLUMNS
6.   DBA_INDEXES
7.   DBA_PART_TABLES
8.   DBA_VIEWS
9.   DBA_MVIEWS
10. DBA_SOURCE
11. DBA_SEQUENCES
12. DBA_SYNONYMS
13. DBA_LOBS
14. DBA_SCHEDULER_JOBS

For UTL_FILE there are 2 output handler, one for extracting the metadata and another to log the activity.

This extractor will extract all schema objects metadata which is ORACLE_MAINTAINED='N' (Applicable in 12c only).

The script in given below  This script will run on any Oracle 12c environment logged in as SYSTEM or SYS users.


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

set serveroutput on;
declare
refcur_cur SYS_REFCURSOR;
type view_record_name_t is RECORD
(
OWNER dba_VIEWS.OWNER%type,
VIEW_NAME dba_VIEWS.VIEW_NAME%type,
TEXT_LENGTH dba_VIEWS.TEXT_LENGTH%type
);
all_users_record_name dba_users%rowtype;
view_record_name view_record_name_t;
table_record_name dba_tables%rowtype;
--column_record_name column_record_name_t;
column_record_name dba_tab_columns%rowtype;
index_record_name dba_indexes%rowtype;
partition_record_name dba_part_tables%rowtype;
mview_record_name dba_mviews%rowtype;
source_record_name dba_source%rowtype;
sequence_record_name dba_sequences%rowtype;
synonym_record_name dba_synonyms%rowtype;
lobs_record_name dba_lobs%rowtype;
jobs_record_name dba_scheduler_jobs%rowtype;
tab_privs_record_name dba_tab_privs%rowtype;
role_privs_record_name dba_role_privs%rowtype;
users_record_name dba_users%rowtype;
fhandle UTL_FILE.FILE_TYPE;
fhandle_err UTL_FILE.FILE_TYPE;
uname user_users.username%type;
systimes varchar2(15);
rowconut number;
serverHostName varchar2(100);
serverInstanceName varchar2(100);
begin

begin
 select max(username) into uname from user_users;
 EXCEPTION
 when OTHERS then
     uname := 'dummy';
end;

serverHostName:= sys_context('userenv','server_host');
serverInstanceName:= sys_context('userenv','instance_name') ;

fhandle_err := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_InfoAndErrors_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.log' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_users_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select *  FROM DBA_USERS WHERE ORACLE_MAINTAINED='N' order by USERNAME;
utl_file.put_line(fhandle_err,'Start extracting users from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'USERNAME~USER_ID~ACCOUNT_STATUS~LOCK_DATE~EXPIRY_DATE');       
        loop
            fetch refcur_cur into users_record_name;
            utl_file.put_line(fhandle,users_record_name.USERNAME||'~'||users_record_name.USER_ID||'~'||users_record_name.ACCOUNT_STATUS||'~'||users_record_name.LOCK_DATE||'~'||users_record_name.EXPIRY_DATE);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting users from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_tab_privs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select * from dba_tab_privs where GRANTEE in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1,2,3,5;
utl_file.put_line(fhandle_err,'Start extracting table_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'GRANTEE~OWNER~TABLE_NAME~GRANTOR~PRIVILEGE~GRANTABLE~HIERARCHY');       
        loop
            fetch refcur_cur into tab_privs_record_name;
            utl_file.put_line(fhandle,tab_privs_record_name.GRANTEE||'~'||tab_privs_record_name.OWNER||'~'||tab_privs_record_name.TABLE_NAME||'~'||tab_privs_record_name.GRANTOR||'~'||tab_privs_record_name.PRIVILEGE||'~'||tab_privs_record_name.GRANTABLE||'~'||tab_privs_record_name.HIERARCHY);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting table_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_role_privs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select * from dba_role_privs where GRANTEE in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1,2,3,4;
utl_file.put_line(fhandle_err,'Start extracting role_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'GRANTEE~GRANTED_ROLE~ADMIN_OPTION~DEFAULT_ROLE'
                         ) ;       
        loop
            fetch refcur_cur into role_privs_record_name;
            utl_file.put_line(fhandle,role_privs_record_name.GRANTEE||'~'||role_privs_record_name.GRANTED_ROLE||'~'||role_privs_record_name.ADMIN_OPTION||'~'||role_privs_record_name.DEFAULT_ROLE);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting role_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
            location =>  'UTL_DIR'     -- File location
            ,filename=> uname||'_tables_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
            ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select * from dba_tables where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1;
utl_file.put_line(fhandle_err,'Start extracting tables....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~TABLESPACE_NAME~STATUS~NUM_ROWS');       
        loop
            fetch refcur_cur into table_record_name;
            begin
                execute immediate 'select count(*) from '||table_record_name.OWNER||'.'||table_record_name.TABLE_NAME into rowconut;
                EXCEPTION
                    when others then
                    rowconut:=0;               
            end;
            utl_file.put_line(fhandle,table_record_name.OWNER||'~'||table_record_name.TABLE_NAME||'~'||table_record_name.TABLESPACE_NAME||'~'||table_record_name.STATUS||'~'||rowconut);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting tables from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
            location =>  'UTL_DIR'     -- File location
            ,filename=> uname||'_tab_columns_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
            ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       
--nvl(OWNER,' ') OWNER,nvl(TABLE_NAME,' ') TABLE_NAME,nvl(COLUMN_NAME,' ') COLUMN_NAME,nvl(DATA_TYPE,' ') DATA_TYPE,nvl(DATA_LENGTH,0) DATA_LENGTH,nvl(DATA_PRECISION,0) DATA_PRECISION,nvl(DATA_SCALE,0) DATA_SCALE,nvl(NULLABLE,' ') NULLABLE,nvl(COLUMN_ID,0) COLUMN_ID
open refcur_cur for select * from dba_tab_columns where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by table_name,column_id;
utl_file.put_line(fhandle_err,'Start extracting columns....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~COLUMN_NAME~DATA_TYPE~DATA_LENGTH~DATA_PRECISION~DATA_SCALE~NULLABLE~COLUMN_ID'
                         );       
        loop
            fetch refcur_cur into column_record_name;
            utl_file.put_line(fhandle,column_record_name.OWNER||'~'||column_record_name.TABLE_NAME||'~'||column_record_name.COLUMN_NAME||'~'||column_record_name.DATA_TYPE||'~'||column_record_name.DATA_LENGTH||'~'||column_record_name.DATA_PRECISION||'~'||column_record_name.DATA_SCALE||'~'||column_record_name.NULLABLE||'~'||column_record_name.COLUMN_ID);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting columns from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--elsif i.object_type='INDEX' then
        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_indexes_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',
                    max_linesize => 32767 -- Open mode: w = write.
                        );       
        open refcur_cur for
            select * from dba_indexes where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by TABLE_NAME,INDEX_NAME;
utl_file.put_line(fhandle_err,'Start extracting indexes....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~INDEX_NAME~INDEX_TYPE~TABLE_OWNER~TABLE_NAME~TABLE_TYPE~UNIQUENESS~COMPRESSION~TABLESPACE_NAME~DISTINCT_KEYS~STATUS~NUM_ROWS'
                         );       
        loop
            fetch refcur_cur into index_record_name;
            utl_file.put_line(fhandle,  index_record_name.OWNER||'~'||index_record_name.INDEX_NAME||'~'||index_record_name.INDEX_TYPE||'~'||index_record_name.TABLE_OWNER||'~'||index_record_name.TABLE_NAME||'~'||index_record_name.TABLE_TYPE||'~'||index_record_name.UNIQUENESS||'~'||index_record_name.COMPRESSION||'~'||index_record_name.TABLESPACE_NAME||'~'||index_record_name.DISTINCT_KEYS||'~'||index_record_name.STATUS||'~'||index_record_name.NUM_ROWS);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting indexes from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

    --elsif i.object_type='TABLE PARTITION' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_table_partitions_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_part_tables where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1;

utl_file.put_line(fhandle_err,'Start extracting partitions from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~PARTITIONING_TYPE~SUBPARTITIONING_TYPE~PARTITION_COUNT~PARTITIONING_KEY_COUNT~STATUS~DEF_TABLESPACE_NAME'
                         );       
        loop
            fetch refcur_cur into partition_record_name;
            utl_file.put_line(fhandle,partition_record_name.OWNER||'~'||partition_record_name.TABLE_NAME||'~'||partition_record_name.SUBPARTITIONING_TYPE||'~'||partition_record_name.PARTITION_COUNT||'~'||partition_record_name.PARTITIONING_KEY_COUNT||'~'||partition_record_name.STATUS||'~'||partition_record_name.DEF_TABLESPACE_NAME);
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting partitions from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='VIEW' then
        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_views_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open  refcur_cur for
            select nvl(OWNER,' ') OWNER,nvl(VIEW_NAME,' ') VIEW_NAME,nvl(TEXT_LENGTH,0) TEXT_LENGTH from dba_views  where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by view_name;

utl_file.put_line(fhandle_err,'Start extracting views from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~VIEW_NAME~TEXT_LENGTH');--,TEXT');       
        loop
            BEGIN
                fetch refcur_cur into view_record_name;
                utl_file.put_line(fhandle,view_record_name.OWNER||'~'||view_record_name.VIEW_NAME||'~'||view_record_name.TEXT_LENGTH);--||'~'||view_record_name.TEXT);       
                EXCEPTION
                    when OTHERS then
                    exit;
            END;
            exit when refcur_cur%notfound;
    -- Open mode: w = write.   
    end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting views from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='MATERIALIZED VIEW' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_mviews_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_mviews where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;
utl_file.put_line(fhandle_err,'Start extracting mviews from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~MVIEW_NAME~CONTAINER_NAME~QUERY~QUERY_LEN');       
        loop
            fetch refcur_cur into mview_record_name;
            utl_file.put_line(fhandle, mview_record_name.OWNER||'~'||mview_record_name.MVIEW_NAME||'~'||mview_record_name.CONTAINER_NAME||'~'||mview_record_name.QUERY||'~'||mview_record_name.QUERY_LEN);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting mviews from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--    elsif (i.object_type='FUNCTION'  or i.object_type='TYPE' or i.object_type='PACKAGE BODY' or i.object_type='PACKAGE' or i.object_type='TRIGGER') then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_types_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_source where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by TYPE,NAME,LINE;
utl_file.put_line(fhandle_err,'Start extracting types from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~NAME~TYPE~LINE~TEXT'
                         );       
        loop
            fetch refcur_cur into source_record_name;
            utl_file.put_line(fhandle,source_record_name.OWNER||'~'||source_record_name.NAME||'~'||source_record_name.TYPE||'~'||source_record_name.LINE||'~'||source_record_name.TEXT);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting types from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='SEQUENCE' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_sequences_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_sequences where sequence_owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting sequences from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'SEQUENCE_OWNER~SEQUENCE_NAME~MIN_VALUE~MAX_VALUE~INCREMENT_BY~CYCLE_FLAG~ORDER_FLAG~CACHE_SIZE~LAST_NUMBER');       
        loop
            fetch refcur_cur into sequence_record_name;
            utl_file.put_line(fhandle,sequence_record_name.SEQUENCE_OWNER||'~'||sequence_record_name.SEQUENCE_NAME||'~'||sequence_record_name.MIN_VALUE||'~'||sequence_record_name.MAX_VALUE||'~'||sequence_record_name.INCREMENT_BY||'~'||sequence_record_name.CYCLE_FLAG||'~'||sequence_record_name.ORDER_FLAG||'~'||sequence_record_name.CACHE_SIZE||'~'||sequence_record_name.LAST_NUMBER);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting sequences from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='SYNONYM' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_synonyms_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_synonyms where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;
utl_file.put_line(fhandle_err,'Start extracting synonyms from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~SYNONYM_NAME~TABLE_OWNER~TABLE_NAME~DB_LINK'
                         );       
        loop
            fetch refcur_cur into synonym_record_name;
            utl_file.put_line(fhandle,synonym_record_name.OWNER||'~'||synonym_record_name.SYNONYM_NAME||'~'||synonym_record_name.TABLE_OWNER||'~'||synonym_record_name.TABLE_NAME||'~'||synonym_record_name.DB_LINK);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting synonyms from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='INDEX PARTITION' then
    --elsif i.object_type='LOB' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_lobs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_lobs where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting lobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~COLUMN_NAME~SEGMENT_NAME~TABLESPACE_NAME~INDEX_NAME~CHUNK~ENCRYPT~FORMAT'
                         );       
        loop
            fetch refcur_cur into lobs_record_name;
            utl_file.put_line(fhandle,lobs_record_name.OWNER||'~'||lobs_record_name.TABLE_NAME||'~'||lobs_record_name.COLUMN_NAME||'~'||lobs_record_name.SEGMENT_NAME||'~'||lobs_record_name.TABLESPACE_NAME||'~'||lobs_record_name.INDEX_NAME||'~'||lobs_record_name.CHUNK||'~'||lobs_record_name.ENCRYPT||'~'||lobs_record_name.FORMAT);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting lobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

    --elsif i.object_type='JOB' then
        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_jobs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_scheduler_jobs where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting jobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~JOB_NAME~JOB_STYLE~JOB_CREATOR~JOB_TYPE~JOB_ACTION~NUMBER_OF_ARGUMENTS~SCHEDULE_OWNER~SCHEDULE_NAME~SCHEDULE_TYPE~START_DATE~REPEAT_INTERVAL~END_DATE~ENABLED'
                         );       
        loop
            fetch refcur_cur into jobs_record_name;
            utl_file.put_line(fhandle,jobs_record_name.OWNER||'~'||jobs_record_name.JOB_NAME||'~'||jobs_record_name.JOB_STYLE||'~'||jobs_record_name.JOB_CREATOR||'~'||jobs_record_name.JOB_TYPE||'~'||jobs_record_name.JOB_ACTION||'~'||jobs_record_name.NUMBER_OF_ARGUMENTS||'~'||jobs_record_name.SCHEDULE_OWNER||'~'||jobs_record_name.SCHEDULE_NAME||'~'||jobs_record_name.SCHEDULE_TYPE||'~'||jobs_record_name.START_DATE||'~'||jobs_record_name.REPEAT_INTERVAL||'~'||jobs_record_name.END_DATE||'~'||jobs_record_name.ENABLED);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting jobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--    end if;
utl_file.fclose(fhandle_err);
--end loop;

EXCEPTION
 --  WHEN UTL_FILE.INVALID_PATH THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File location is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20000,'File location is invalid.');
   
 --  WHEN UTL_FILE.INVALID_MODE THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The open_mode parameter in FOPEN is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20001,'The open_mode parameter in FOPEN is invalid.');

 --  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File location is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20002,'File handle is invalid.');

 --  WHEN UTL_FILE.INVALID_OPERATION THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File could not be opened or operated on as requested.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20003,'File could not be opened or operated on as requested.');

 --  WHEN UTL_FILE.READ_ERROR THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Operating system error occurred during the read operation.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20004,'Operating system error occurred during the read operation.');

 --  WHEN UTL_FILE.WRITE_ERROR THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Operating system error occurred during the write operation.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20005,'Operating system error occurred during the write operation.');

 --  WHEN UTL_FILE.INTERNAL_ERROR THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Unspecified PL/SQL error.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20006,'Unspecified PL/SQL error.');

 --  WHEN UTL_FILE.CHARSETMISMATCH THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'A file is opened using FOPEN_NCHAR~but later I/O ' ||
 --                                    'operations use nonchar functions such as PUTF or GET_LINE.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20007,'A file is opened using FOPEN_NCHAR~but later I/O ' ||
 --                                    'operations use nonchar functions such as PUTF or GET_LINE.');

 --  WHEN UTL_FILE.FILE_OPEN THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested operation failed because the file is open.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20008,'The requested operation failed because the file is open.');

 --  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
 --                                    'be within the range 1 to 32767.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20009,'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
 --                                    'be within the range 1 to 32767.');

 --  WHEN UTL_FILE.INVALID_FILENAME THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The filename parameter is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20010,'The filename parameter is invalid.');

 --  WHEN UTL_FILE.ACCESS_DENIED THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Permission to access to the file location is denied.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20011,'Permission to access to the file location is denied.');

 --  WHEN UTL_FILE.INVALID_OFFSET THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
 --                                    'it should be greater than 0 and less than the total ' ||
 --                                    'number of bytes in the file.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20012,'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
 --                                    'it should be greater than 0 and less than the total ' ||
 --                                    'number of bytes in the file.');

 --  WHEN UTL_FILE.DELETE_FAILED THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested file delete operation failed.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20013,'The requested file delete operation failed.');

 --  WHEN UTL_FILE.RENAME_FAILED THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested file rename operation failed.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20014,'The requested file rename operation failed.');

  WHEN OTHERS THEN
    utl_file.fclose(fhandle);
    utl_file.fclose(fhandle_err);
    begin
        fhandle_err := utl_file.fopen(
                            location =>  'UTL_DIR'     -- File location
                            ,filename=> uname||'_InfoAndErrors_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.log' -- File name
                            ,open_mode    => 'a', -- Open mode: w = write.
        max_linesize => 32767 );       
            utl_file.put_line(fhandle_err,sqlcode||' - '||sqlerrm);
            utl_file.fclose(fhandle_err);
            EXCEPTION
                when others then
                utl_file.fclose(fhandle_err);
    end;
    RAISE;
end;
/

Connecting to non oracle databse from SQLDeveloper using third party database drivers.

Oracle SQLDeveloper is exclusively create to connect to Oracle database using either ojdbc driver or oracle instant client. But SQL Developer also provide utility to connect to non oracle database using Third party jdbc driver.

In my limitation of knowledge I have connected to databases like DB2, SQL Server, Sysbase and MySQL.

In this post I will not show how to connect ot individual database but as whole the steps to connect to third party database.

Select Tools from the main menu and select Preferences. From the preferences window expand the database node from the left pane. From there select Third Party jdbc driver and a list box will be found with a label Third-Party driver path.

On the bottom you will find an Add Entry button, click on the button to add new third party jdbc driver. Browse your PC to find the jdbc driver with extension .jar. Click on Ok after you see the entry ofvthe jdbc driver along with it's path into the listbox. Click on Ok.

Suppose you add jdbc driver sqljdbc6.jar to connect to SQL Server database. You will find a tab right left to the Oracle tab. Make your connection entry under the tab and click on connect.

In my later post I will come up with more on this as how to connect to different databases using jdbc driver and link to find such jdbc drivers.

Difference between coalesce and nvl in Oracle PLSQL.

We all know about basic of nvl function in oracle and somehow a bit about coalesce. But question is when to use nvl and when to use coalesce.

Suppose in a case where you are trying to read a column value and if it is null we read some default values like 0 in case of number and for character say something like 'X'.

So if the name of the column is Salary we write nvl(Salary,0) or coalesce(Salary,0). Now it is know that coalesce is a bit slower than nvl. As coalesce can take multiple arguments but nvl can take only 2. So when it is q question of only checking one single variable then nvl is the best option. But if you need to check multiple variable then coalesce is better option.

Suppose we have 4 columns to check i.e. coalesce(Amount1, Amount2, Amount3, Amount4,0). This can also be written using case when statement, i.e. case when Amount1 is not null then Amount1 when Amount2 is not null then Amount2 when Amount3 is not null then Amount3 when Amount4 is not null then Amount4 else 0 end. In case with nvl it can be written as nvl(Amount1,nvl(Amount2,nvl(Amount3,nvl(Amount4,0)))). But using so many nvl statement is not only junky code but also can lead to performance issue. So it is good to use coalsce in case of more than one column or else nvl.

How to make database objects accessible using synonyms in oracle.

When it comes to application development it is not always happens that application developer are so much sound with database knowledge. They know their own part of task to execute. Like to connect to database, to bind with the database object and to fetch data, insert / update or delete data. But it is not possible for them to take account of each and every database objects.

Let us start with an example. An application is accessing a database schema ACC_DATA which contains all the tables / views. Now it is obvious that it will never have direct access to this schema user rather it will have to connect from an application user. In our case let us consider a technical user ACC_APP. Now ACC_APP have read / write access to ACC_DATA. That mean it can select / insert / update and delete from tables in ACC_DATA.

Now supposed some new views have been created in ACC_DATA and application developer have been instructed to add those views in some new reports to be displayed in the application GUI. Now these application handles several other schema users data through ACC_APP. So it is not possible for the developer to keep track as from which schema these tables are supposed to fetch data and use schemaname.table name inside the application code and it is not also feasible in all scenario to user this kind of schemaname.tablename as it is not permissible. So what is the solution?

The solution is to create a snyonym for the respective schema.tablename in ACC_APP. And developer can directly user the synonym name which will access the tablename using the synonym thinking it as the actual tablename, so it acts as pseudo tablename.

So whenever a new table is added, the request is send to dba to add one synonym into ACC_APP.

Suppose a new table CUST_DATA is created in ACC_DATA. We already consider that grant select any table privileges is provivded to the user ACC_APP. So exclusive privileges are not required to provide for this table to ACC_APP and we assume the privilege is already provided when the table is created.

So DBA should run one command like CREATE SYNONYM CUST_DATA for ACC_DATA.CUST_DATA.

Now let us consider the situation where none of the SYNONYMS existis in ACC_APP and the dba has to create synonyms for all existing tables / VIEWS in ACC_DATA in ACC_APP. So if the number of tables / views are thousands then dba should be absconding from office from the very next day. So dba shouls create a script which will create SYNONYMS for all existing tables / views in ACC_APP. Here below I an writing down that script.

DECLARE

CURSOR REC IS SELECT TABLE_NAME FROM ALL_USERS WHERE USERNAME='ACC_DATA';
BEGIN
    FOR I IN REC LOOP
        EXECUTE IMMEDIATE 'CREATE SYNONYM ACC_APP.'||I.TABLE_NAME||' FOR ACC_DATA.'||I.TABLE_NAME;
    END LOOP;
END;
/

So all tables / views of ACC_DATA will be accessible with this pseudo tablename / synonyms within ACC_APP.

Working with ROLES in Oracle

Role is kind of template which not only helps in encapsulating set of privileges provided to a certain user but also it helps in maintaining business rules for organization. User management can be handled smoothly with use of roles. Granting ad-hoc privileges to users is not recommend at enterprise level architecture.

In this content I will try to explain how role helps in managing business rules and security.

Suppose a company X have one datawarehouse application handling application for daily batch and transaction. It uses one oracle database connecting a schema X_DATA. X_Data contains all the business objects and hold the versioned data everyday after the daily batch job completes and provide reports to end user through application. Now end user have to login to the database using individual personal user which will access the objects of X_DATA.

From this set of application users there are 2 types of users. 
1. User who can view the data from report and update certain data if necessary
2. User who can only view the data.

So for providing such kind of privileges 2 roles are created.
i. READ_DWH_ROLE - This will provide select privileges to all tables / view of X_DATA to the user.
2. READ_WRITE_DWH_ROLE - This will provide select / update privileges to users to tables / views of X_DATA

Now let us see what command was used by DBA to add privileges to the above role on X_DATA schema user.

i.  READ_DWH_ROLE - grant select any tables on X_DATA to READ_DWH_ROLE.
ii. READ_WRITE_DWH_ROLE - grant select any table,update any table on X_DATA to READ_WRITE_DWH_ROLE

Now end user request the required role from some tool or mail with manager's approval to dba to provide access of the role to the user if exists or create new user and provide access.

Lets us assume that there is an end user Mike Anderson requesting a new user mikean requesting the role READ_DWH_ROLE and another user Peter Vaun requesting the role READ_WRITE_DWH_ROLE. The request got approved by manager and went to DBA.

DBA will login to system user and will create the to new user. Since this user will not be owner of any db objects but only access objects of X_DATA.

CREATE USER MIKEAN IDENTIFIED BY MIKEAN DEFAULT TABLESPACE TBLPSPC_X_DATA;
CREATE USER PETVAUN IDENTIFIED BY PETVAUN DEFAULT TABLESPACE TBLPSPC_X_DATA;

GRANT CONNECT TO MIKEAN;
GRANT CONNECT TO PETVAUN;

GRANT READ_DWH_ROLE TO MIKEAN;
GRANT READ_WRITE_DWH_ROLE TO PETVAUN;

So it's all set. Now mikean can read tables / views of all X_DATA witn select query or some application accessing the object using X_DATA.table_name, X_DATA.viewname and petvaun can read / update tables/ views of X_DATA.

Now suppose  the application developer is not aware of accessing this objects from X_DATA schema and he is only provided the tablename / view name and if he put the table or view name inside his application like select * from tablename instead of select * from X_DATA.tablename the application will definitely get and oracle error as table or view does not exists. In that case it is neccessary for the user to add another request for creating synonym privileges and create synonyms for all table and view of X_DATA on itself.

In that case dba has to run first grant create synonym privileges to the usrs.

GRANT CREATE ANY SNONYM TO MIKEAN;
GRANT CREATE ANY SYONYOM TO PETVAUN;

Then create a script like this and execute.

DECLARE

CURSOR REC IS SELECT TABLE_NAME FROM ALL_USERS WHERE USERNAME='X_DATA';
BEGIN
    FOR I IN REC LOOP
        EXECUTE IMMEDIATE 'CREATE SYNONYM MIKEAN.'||I.TABLE_NAME||' FOR X_DATA.'||I.TABLE_NAME;
        EXECUTE IMMEDIATE 'CREATE SYNONYM PETVAUN.'||I.TABLE_NAME||' FOR X_DATA.'||I.TABLE_NAME;
    END LOOP;
END;
/
Now if the application user the table names / view names it will actually use the synoym which will actually invoke the underlying tablename and viewname.

So this is the first part of creating role and adding privilege and assigning it to the user.

Wait for the next post to find out more on roles.

Updating view in Oracle plsql

As we all know that a view columns cannot be updated like we do in table. But there are certain ways to update the corresponding table columns of a view using instead of triggers.

A trigger need to be created on the view that need to be updated with instead of clause and inside the trigger the corresponding table columns can be updated.

Let us see this with an example.

Say we have to tables.

1. Employee
The structure of the employee table is as such below.


EMPID    NUMBER(20),
EMPNAME  VARCHAR2(100),
DEPTID   NUMBER(20),
AGE      NUMBER(3),
CTC      NUMBER

2. Department
DEPTID    NUMBER(10),
DEPTNAME  NUMBER

Now we have a view name employee_view whose query is as below

CREATE OR REPLACE VIEW EMPLOYEE_VIEW AS
SELECT EMPNAME,DEPTNAME,AGE,CTC
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON D.DEPTID = E.DEPTID

This view can be easily inserted / updated / deleted using an insert / update / delete statement.

update employee_view set empname = :empname, deptname = :deptname where empid = :empid;

So in what case a view is not updatable?

1. Using distinct operator
2. Using group or order by clause
3. Using connect by, start with clause
4. Using a sub query
5. Using any kind of functions or oracle internal aggregated functions (point number 2 applicable)
6. Using join condition.

In the above case a instead of trigger need to be created. Let us assume that the view query is a such

CREATE OR REPLACE VIEW EMPLOYEE_DEPT_VIEW AS
SELECT EMPNAME,DEPTNAME,AGE,CTC
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON D.DEPTID = E.DEPTID
ORDER BY DEPTNAME,EMPNAME
Now if we try to insert / update / delete  it will not allow as join condition have been used in the query.

In this scenario we need to add one instead of trigger which will insert into (may not be for the above view query as not all columns are present and depending on not null constraints or check constraints) the table, update the table or delete from table based on the dml query that will be written within the instead of trigger.

Here given below are given 2 instead of trigger on insert / update or delete.

Update

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_VIEW AS
INSTEAD OF UPDATE ON EMPLOYEE_VIEW
FOR EACH ROW
BEGIN
    UPDATE EMPLOYEE SET EMPNAME=:NEW.EMPNAME, AGE = :NEW.AGE, CTC = :NEW.CTC WHERE EMPID = :OLD.EMPID;
    UPDATE DEPARTMENT SET DEPTNAME = :NEW.DEPTNAME WHERE DEPTID = :OLD.DEPTID;
EMD;/
As from the above we observe that for update we are taking all :new.fieldname for the updated value and since key field is empid for employee and deptid is key field for deprtment.

Now lets insert data into the EMPLOYEE_DEPT_VIEW

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF INSERT ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
 INSERT INTO EMPLOYEE VALUES(:NEW.EMPID,:NEW.EMPNAME,:NEW.DEPTID,:NEW.AGE,:NEW.CTC);
 INSERT INTO DEPARTMENT VALUES(:NEW.DEPTID,:NEW.DEPTNAME);
END;

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF DELETE ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
 DELETE FROM EMPLOYEE WHERE EMPID=:NEW.EMPID;
 DELETE FROM DEPARTMENT WHERE DEPTID = :NEW.DEPTID;
END;
Now if we write a delete query like delete from EMPLOYEE_DEPT_VIEW where empid = :old.empid and deptid = :old.deptid;
This will delete corresponding record from employee and deprtment. IF the value of any of the field like empid or deptid does not match then that record will not be deleted but the other matching record will be deleted.


CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF UPDATE ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
 UPDATE EMPLOYEE SET EMPNAME = :NEW.EMPNAME, AGE = :NEW.AGE, CTC = :NEW.CTC WHERE EMPID=:NEW.EMPID;
 UPDATE DEPARTMENT SET DEPTNAME = :NEW.DEPTNAME WHERE DEPTID = :NEW.DEPTID;
END;
Now if we write a delete query like update EMPLOYEE_DEPT_VIEW where empid = :old.empid and deptid = :old.deptid;
This will update corresponding record columns from employee and deprtment. IF the value of any of the field like empid or deptid does not match then that record will not be updated but the other matching record will be updated.