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

ORA-01548: active rollback segment ' SYSSMU13 3758039048$' found, terminate dropping tablespace

Error noticed while creating tablespace.

  1  create tablespace tbs_moha
  2  datafile '/MOHA/oradata/orcl/moha_01.dbf' size 5G
  3  autoextend on
  4* maxsize unlimited
SQL> /
create tablespace tbs_moha
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Find the name of tablespace associated with the missing datafile.

SQL> select d.name,t.name
  2  from v$datafile d join v$tablespace t
  3  using(ts#);

NAME
--------------------------------------------------------------------------------
NAME
------------------------------
/home/oracle/BACKUP/undotbs02.dbf
UNDOTBS2

Check Undo Parameters.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2

Shutdown the database, change undo_mangement to Manual and start the database

SQL> shutdown abort;
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

Modify pfile and set undo_management='Manual'

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.

Create new undo tablespace, shutdown database, set undo management auto and undo tablespace to newly created tablespace

  1  create undo tablespace undotbs01
  2  datafile '/u01/app/oracle/oradata/orcl/undotbs01_01.dbf' size 10m
  3  autoextend on
  4* maxsize unlimited
SQL> /

Tablespace created.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  MANUAL
undo_retention        integer  900
undo_tablespace        string  UNDOTBS2

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

open pfile and modify undo_management='Auto' and undo_tablespace='Undotbs01'

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  undotbs01

Now create tablespace will create successfully

  1  create tablespace tbs_moha
  2  datafile '/MOHA/oradata/orcl/moha01.dbf' size 1G
  3  autoextend on
  4* maxsize unlimited
SQL> /

Tablespace created.

Following command failed due to existing rollback segment on the old undo tablespace.

SQL> grant dba to usr_moha;
grant dba to usr_moha
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 15 cannot be read at this time
ORA-01110: data file 15: '/home/oracle/BACKUP/undotbs02.dbf'

So tried to drop old undo tablespace

SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU12_3370723747$' found, terminate
dropping tablespace

Shutdown database and add following parameter
This parameter is oracle's hidden and undocumented parameters. So use at your own risk.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.
vi initorcl.ora
*._offline_rollback_segments=(_SYSSMU12_3370723747$)

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU13_3758039048$' found, terminate
dropping tablespace

Append _SYSSMU13_3758039048$ to the end of the _offline_rollback_segments. Keep going until the the rollback segment and drop tablespace successfully

*._offline_rollback_segments=(_SYSSMU32_3855527180$,_SYSSMU33_2662784219$,_SYSSMU34_2457069885$,_SYSSMU35_1632431741$,_SYSSMU36_3409971511$,_SYSSMU37_2689280970$,_SYSSMU38_389146150$,_SYSSMU39_3304332257$,_SYSSMU40_2103468367$,_SYSSMU41_2387847232$,
_SYSSMU12_3370723747$
,_SYSSMU13_3758039048$
,_SYSSMU14_2103326955$
,_SYSSMU15_3693451306$
,_SYSSMU16_3316561802$
,_SYSSMU17_2198165518$
,_SYSSMU18_3012613150$
,_SYSSMU19_1884560376$
,_SYSSMU20_2031163871$
,_SYSSMU21_1352513242$
)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

vi initorcl.ora

SQL> create spfile from pfile;

File created.

SQL> startup;

ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.

SQL> drop tablespace UNDOTBS2;

Tablespace dropped.

Finally remove all the parameters and restart the database.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  undotbs01
SQL> show parameter rollback

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments      string  _SYSSMU32_3855527180$, _SYSSMU
       33_2662784219$, _SYSSMU34_2457
       069885$, _SYSSMU35_1632431741$
       , _SYSSMU36_3409971511$, _SYSS
       MU37_2689280970$, _SYSSMU38_38
       9146150$, _SYSSMU39_3304332257
       $, _SYSSMU40_2103468367$, _SYS
       SMU41_2387847232$, _SYSSMU12_3
       370723747$, _SYSSMU13_37580390
       48$, _SYSSMU14_2103326955$, _S
       YSSMU15_3693451306$, _SYSSMU16

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
       _3316561802$, _SYSSMU17_219816
       5518$, _SYSSMU18_3012613150$,
       _SYSSMU19_1884560376$, _SYSSMU
       20_2031163871$, _SYSSMU21_1352
       513242$


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

vi initorcl.ora

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size      2228944 bytes
Variable Size    322964784 bytes
Database Buffers    88080384 bytes
Redo Buffers      4272128 bytes
Database mounted.
Database opened.
SQL> show parameter offline_rollback_segments;
SQL> grant dba to usr_moha;

Grant succeeded.

SQL> 

Read More