Sending Email Alerts Through Cron

Cron is the Linux task scheduler that is responsible for making sure scripts run at their specified times. Cron is often used for backup scripts and running custom scripts. In the event a task runs into problems or errors Cron generally tries to email the local administrator of the machine. this means it tries to send an email to itself.
We can change this default behavior by changing the MAILTO variable (It won't work if you have not setup an email server or SMTP)
Setting MAILTO variable
Edit the crontab by entering crontab -e and add
MAILTO=your.email@your_provider.com
It should look something as below

Specify email for each script
If we don’t want all output to go to the same email address we can specify the output of a particular script to go to a different email address
0 8 * * * /mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS/ETL/SQLs/load.sh | mail -s "QA Load 8AM" someaddress@email.com

If you don't want email alerts add ">/dev/null 2>&1" at the end of the command
0 8 * * * /mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS/ETL/SQLs/load.sh >/dev/null 2>&1

Long Running Jobs/Queries

Long Running JOBS
Select sid,serial#,username,opname,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, totalwork,sofar,time_remaining remaining, message
from v$session_longops
where time_remaining = 0 order by time_remaining desc
Long Query Progress
SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
ORDER BY elapsed_seconds
Long running Queries, Total, So far and Remaining time 
SELECT SID, username, SERIAL#, opname, start_time,elapsed_seconds, TOTALWORK,SOFAR,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE, time_remaining
FROM   V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1

List of tables available in Database

Execute below query as sys or system, it will give all available tables in database owner wise
SELECT owner, table_name FROM dba_tables
If you don't have sys or system privileges use below queries to find available tables in schema
SELECT owner, table_name FROM all_tables
SELECT table_name FROM user_tables

How to find Blocking Sessions??

What is Blocking Session??
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.
How to Find Blocked/Blocking other sessions??

select 

t.process, 

t.sid, 

t.SERIAL#, 

t.blocking_session, 

t.USER#, 

t.USERNAME

from 

v$session t 

where blocking_session is not null;

Identify blocked objects

If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other.
What is TM lock - TM (DML enqueue lock)- This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested. 
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'

The ID you get from this query refers to the actual database object which can help you to identify the problem

Oracle DataPump “INCLUDE” parameter and limit of 4000 characters

Today I am trying to export certain tables (202 in count) using expdp, but I got “UDE-00014: invalid value for parameter, ‘include'”:
expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG
INCLUDE=TABLE:"IN('AAA','BBB','CCC','DDD',
......
'ZZZ','AAAA','BBBB','CCCC')" COMPRESSION=ALL PARALLEL=8
“include” parameter accepts only 4000 characters. A simple workaround is to use a table to hold the names of the tables/objects and use this table with include parameter:
CREATE TABLE tables_list(table_name VARCHAR2(30) );
INSERT INTO table_list ( 'AAA' );
INSERT INTO table_list ( 'BBB' );
INSERT INTO table_list ( 'CCC' );
INSERT INTO table_list ( 'CCC' );
...
...
INSERT INTO table_list ( 'ZZZ' );
INSERT INTO table_list ( 'AAAA' );
INSERT INTO table_list ( 'BBBB' );
INSERT INTO table_list ( 'CCCC' );
COMMIT;

expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG include=TABLE:"IN (SELECT table_name FROM table_list)"