Many operations take time in the Database like full table scan, backup, full index scan, and joins. We can estimate the % completion or monitor the session using the v$session_longops view in Oracle. This is a very useful view and provides quite an accurate estimate.
How to find the %completion for the long operation for a session
select OPNAME,SOFAR/TOTALWORK*100 ,to_char(start_time,'dd-mon-yy hh:mi')
started ,elapsed_seconds/60/60 from v$session_longops where sid=&1;
How to find all the longops in the Oracle Database
select sid, SOFAR, to_char(LAST_UPDATE_TIME,'DD-MON-RRRR:HH24:MI:SS') , to_char(start_time,'DD-MON-RRRR:HH24:MI:SS'),TIME_REMAINING from V$SESSION_LONGOPS where time_remaining > 0 and TOTALWORK != 0
In the case of Oracle RAC, we need to use gv$ view
select inst_id,sid, SOFAR, to_char(LAST_UPDATE_TIME,'DD-MON-RRRR:HH24:MI:SS') , to_char(start_time,'DD-MON-RRRR:HH24:MI:SS'),TIME_REMAINING from gV$SESSION_LONGOPS where time_remaining > 0 and TOTALWORK != 0
How to find the % Backup completion from Longops
SELECT inst_id,SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",START_TIME,LAST_UPDATE_TIME
FROM GV$SESSION_LONGOPS
WHERE OPNAME LIKE '%RMAN%'
---AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
Hope you like these queries
alter system kill session: We can kill the oracle session using alter system kill session in the Oracle database. Sessions are marked for the kill if not killed immediately
oracle kill own session privilege: oracle database does not provide any direct answer to oracle kill own session privilege but this can be achieved with the help of procedure
Active Session History: Check out about Active Session History, how it is configured, how to find performance bottlenecks using ASH, ASH report generation, ASH queries
Oracle Database Health check: check out important Oracle Database Health check for troubleshooting problems, unusual behavior in oracle database
Scripts to check locks in oracle database: Scripts to check locks in oracle database, unlock oracle table, find session holding oracle table locks, check the lock on the package
How to check temp tablespace in Oracle: check out How to check temp tablespace in Oracle, How to resize the tempfile, how to drop the tempfile, How to check the temp usage by Session
how to get table definition in oracle: Check out how to get table definition in oracle, oracle show index definition, get ddl of a materialized view in oracle, get the query of a view in oracle
Recommended Courses
The following are some of the recommended courses you can buy if you want to get a step further
Given below are the links to some of the courses
Oracle DBA 11g/12c – Database Administration for Junior DBA : This course is good for the people who are starting as Junior DBA or aspire to be Oracle DBA. This will provide a good understanding of backup & recovery and General administration tasks
Oracle Database: Oracle 12C R2 RAC Administration : This course covers the installation, administration of Oracle RAC. A good course for Oracle DBA who want to upgrade his skills for Oracle RAC
Oracle Data Guard: Database Administration for Oracle 12C R2 : This course covers the installation, administration of Oracle Dataguard. A good course for Oracle DBA who want to upgrade his skills for Oracle Dataguard
