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,

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
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

How to Solve ORA-04031 with Oracle Support Provided Tool???

What is ORA-04031 and why?

ORA-04031 is error message related to lack of available SGA memory component. When a process needs some memory from SGA and memory is not available in this situation ORA-04031 occurs.
ORA-04031 message look like in DB Alert log file


Steps to solve ORA-04031 error with Oracle support tool
Database admins can easily solve this error by re-sizing SGA but the solution would be temporary. To get accurate solution use Oracle support “ORA-4031 Troubleshooting Tool“.
How to use ORA-4031 Troubleshooting Tool
1) Login to the Oracle Support (support.oracle.com) and search for “Tools and Training Documents” in Knowledge tab. Click on “Tools and Training Documents

2) Search for “Diagnostics Tools Catalog

3) Look for “ORA-4031 Troubleshooting Tools

4) ORA-4031 Troubleshooting Tools page looks like as below

It has four options

  • Troubleshoot a new issue
  • Review a troubleshooting report
  • Upload new files and re-run a troubleshooting report
  • Upload new files and re-run a troubleshooting report

5) Upload trace and alert log files

6) After upload you will get Oracle Recommended Solution. Below is the full report.


7) If recommended solution doesn’t work, you can always create a Service Request from this tool itself

How to fix missing files during 12c database installation?

There will be 2 ZIP files in Oracle 12c database software

– We have to unzip both zip files in to one folder.
– The common misunderstanding is keeping 1of2 and 2of2 in same folder
– If we are not unzipping both zip files in one directory and started setup we see below file missing error during installation.

To avoid this issue
– Abort installation
– open V47115-01_2of2 file and copy all directories under

Paste into C:\V47115-01_1of2.zip\database\stage\Components

Now run setup.exe as administrator. You should finish the installation successfully!!!