bash Dynamic array variable name

#!/bin/bash

# Declaring variables
CNT=1

# Declaring dynamic array with space
eval "arr_row${CNT}=( \"rajiv maharjan\" \"shova maharjan\" )"
let CNT=CNT+1
eval "arr_row${CNT}=( \"dilli maharjan\" \"sapana maharjan\" \"samjhana maharjan\" \"bipana maharjan\" \"tirtha maharjan\" )"

# Manually displaying dynamic array variable
echo "Displaying first row"
CNT1=1
VAR="\${arr_row${CNT1}[0]}"
echo ${VAR}
echo $(eval "echo ${VAR}")

CNT1=2
VAR="\${arr_row${CNT1}[0]}"
echo ${VAR}
echo $(eval "echo ${VAR}")
VAR="\${#arr_row${CNT1}[@]}"
#echo ${VAR}
#echo ${#arr_row2[@]}

# Using loop to iterate all the array elements
for i in $(seq 0 $(expr $(eval "echo $VAR") - 1))
do
        VAR1="\${arr_row${CNT1}[${i}]}"
        eval "echo ${VAR1}"
done

Read More

Compare Oracle tables with linux md5

Comparison between two table with same name in different schema 
Lets say MEMBERS be the table that need to be compared on schemas SCHEMA_A and SCHEMA_B.

Enabling timing to track down the time taken.

set timing on;

Dropping external table if exists from SCHEMA_A
drop table SCHEMA_A.MEMBERS_EXT_A;

Create external table from first schema, SCHEMA_A in our case  
CREATE TABLE SCHEMA_A.MEMBERS_EXT_A
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_A.csv')
)
AS SELECT * FROM SCHEMA_A.MEMBERS; 



Dropping external table if exists from SCHEMA_B
drop table SCHEMA_B.MEMBERS_EXT_B; 

Create external table from second schema, SCHEMA_B in our case
CREATE TABLE SCHEMA_B.MEMBERS_EXT_B
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_B.csv')
)
AS SELECT * FROM SCHEMA_B.MEMBERS;

Check the MD5 before removing header from each external csv file
time md5sum MEMBERS_EXT_A.csv
time md5sum MEMBERS_EXT_B.csv

Remove header from the external files
time sed -i '1,3d' MEMBERS_EXT_A.csv
time sed -i '1,3d' MEMBERS_EXT_A.csv

Compare md5 with following commands, If both tables contains exactly same data then md5 values will be same.
time md5sum MEMBERS_EXT_A.csv
time md5sum MEMBERS_EXT_B.csv


Here is the execution output

drop table SCHEMA_A.MEMBERS_EXT_A;

table MEMBERS_EXT_A dropped.
Elapsed: 00:00:00.385
CREATE TABLE SCHEMA_A.MEMBERS_EXT_A
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR

ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_A.csv')
)
AS SELECT * FROM SCHEMA_A.MEMBERS;

table MEMBERS_EXT_A created.

Elapsed: 00:00:48.268


drop table SCHEMA_B.MEMBERS_EXT_B;

table MEMBERS_EXT dropped.

Elapsed: 00:00:00.386


CREATE TABLE SCHEMA_B.MEMBERS_EXT_B
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
nologfile
)
LOCATION ('MEMBERS_EXT_B.csv')
)
AS SELECT * FROM SCHEMA_B.MEMBERS;

table MEMBERS_EXT_B created.

Elapsed: 00:00:45.530


[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_A.csv
a0d831508aca62dec56db1e1148afd06 MEMBERS_EXT_A.csv

real 0m8.005s
user 0m7.136s
sys 0m0.867s


[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_B.csv
78d0d09b757e97b6d9609709c3193270 MEMBERS_EXT_B.csv

real 0m7.990s
user 0m7.019s
sys 0m0.971s


[dilli@dilliraj mydir]$ time sed -i '1,3d' MEMBERS_EXT_A.csv

real 0m25.588s
user 0m5.089s
sys 0m20.489s


[dilli@dilliraj mydir]$ time sed -i '1,3d' MEMBERS_EXT_B.csv

real 0m26.335s
user 0m5.110s
sys 0m20.528s


[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_A.csv
10c7b4d9b8cb4506f97a542e16da5f0b MEMBERS_EXT_A.csv

real 0m8.276s
user 0m7.245s
sys 0m0.953s

[dilli@dilliraj mydir]$ time md5sum MEMBERS_EXT_B.csv
10c7b4d9b8cb4506f97a542e16da5f0b MEMBERS_EXT_B.csv

real 0m8.389s
user 0m7.331s
sys 0m0.983s

Read More

DDL trigger that will fire on create table

create or replace trigger
DDLTrigger
AFTER create or drop ON schema
declare
TRIG_NAME varchar2(32):='TRG_' || ora_dict_obj_name;
BEGIN
    if ora_sysevent='CREATE' and ora_dict_obj_type='TABLE'  and ora_dict_obj_name != 'MY_DML_LOG'  THEN
        Execute immediate('CREATE OR REPLACE TRIGGER ' || TRIG_NAME ||
            ' BEFORE INSERT OR UPDATE OR DELETE ON ' || ora_dict_obj_owner || '.' || ora_dict_obj_name ||
            ' declare
            begin
            if UPDATING then
                insert into my_dml_log values(''UPDATE'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            elsif DELETING then
                insert into my_dml_log values(''DELETE'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            elsIF INSERTING then
                insert into my_dml_log values(''INSERT'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            end if;
            end;');
        END IF;
END;
/

Read More

Create Oracle role with Password

A role is a set of privileges that can be granted to users or to other roles. We can add privileges to a role and then grant the role to a user. We can then enable the role and exercise the privileges granted by the role. A role contains all privileges granted to the role and all privileges of other roles granted to it. A new role is initially empty. We add privileges to a role with the GRANT statement.

Create role, grant privileges and grant role to existing user.

CREATE ROLE jr_dba
   IDENTIFIED BY "n(V6VW3x!tFOW!SB"; 

SQL> CREATE ROLE jr_dba
   IDENTIFIED BY "n(V6VW3x!tFOW!SB";   2  

Role created.

SQL> grant select any table, backup any table to jr_dba;

Grant succeeded.

SQL> 

SQL> grant connect, resource, jr_dba to dilli;

Grant succeeded.

SQL> 

Now you can login to with the user. When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

SQL> conn dilli  
Enter password: 
Connected.
SQL> 

SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

This example would enable the role called jr_dba with a password. You cannot enable role without password.

SQL> set role jr_dba;
set role jr_dba
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'JR_DBA'


SQL> set role jr_dba identified by "n(V6VW3x!tFOW!SB";

Role set.

SQL> 

Now role jr_dba is enabled and you have all privileges that roles jr_dba has. 
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
 14

SQL> 

We can set non-default role to default role for the user with syntax below.

alter user <username> default role <role_list>;
alter user dilli default role jr_dba;

SQL> show user
USER is "SYS"
SQL> 
SQL> alter user dilli default role jr_dba, connect, resource;

User altered.

SQL> 

SQL> conn dilli
Enter password: 
Connected.
SQL> select count(*) from scott.emp;     
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role jr_dba identified by "n(V6VW3x!tFOW!SB";

Role set.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
 14

SQL> 

Any Password protected roles cannot be set to default

Syntax for the setting default role

alter user <username> default role <role_list>;
You can even use keywords instead of a list of roles for <role_list> :

ALL : all roles granted to that user are set by default
NONE : all roles granted have to be set upon login
ALL EXCEPT <excluded_role_list> : all roles granted are set by default except the ones in <excluded_role_list>.

Read More