By
James F. Koopmann
Take control of Oracle's queue with a step by step approach to getting rid of those pesky DBMS_JOBs.
Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.
If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.
A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn't very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately--never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn't work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn't an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.
Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn't much good information out there. This is why I want to share my method, so that you won't be stuck up against the wall with this problem and nowhere to turn, as I was.
Lets first go through a few different methods of viewing the information about job queues.
Viewing scheduled dbms_jobs
When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.
scheduled_dbms_jobs.sql
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
What Jobs are Actually Running
A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.
running_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
What Sessions are Running the Jobs
Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.
session_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.
Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session
Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill '9 spid
The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.
9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.
ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.
Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.
Monday, January 19, 2009
Friday, January 16, 2009
Resizing Undo Tablespaces
Resizing Undo Tablespaces
Author
Ural Ural
Oracle DBA
How do you resize undo tablepaces ? This is how I do it in an Oracle 9i database.
Problem is the undo tablespace has grown up to the limit of its maxsize
and we need to compact it or just we want to make it smaller due to a space problem
--Find undo info
SELECT NAME, VALUE
FROM v$parameter
WHERE LOWER (NAME) LIKE '%undo%';
NAME|VALUE
undo_management|AUTO
undo_tablespace|UNDOTBS1
--Find undo tablespace datafile info
SELECT file_name, BYTES / 1024 / 1024 mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
ORDER BY file_name;
/*
FILE_NAME|MB
/datac3/oradata/andlp2/undotbs1_01.dbf|1793
/datac6/oradata/andlp2/undotbs1_02.dbf|235
/datac7/oradata/andlp2/undotbs1_03.dbf|1679
*/
--Execute the following query to determine who is using a particular UNDO or Rollback Segment:
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
--No rows returned
--Create a second temporary undo tablespace
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/datac7/oradata/andlp2/undotbs2_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M
ONLINE
BLOCKSIZE 8K;
--Set this new undo tablespace as undo tablespace of the database
ALTER SYSTEM SET undo_tablespace = UNDOTBS2;
--You can monitor the undo extents of all the undo tablespaces by using the following query
SELECT tablespace_name, segment_name, SUM (blocks), SUM (BYTES) / 1024
FROM dba_undo_extents
GROUP BY tablespace_name, segment_name;
--Since we are going to drop the old undo tablespace we need to know if any session is
--using any undo extent of this undo tablespace
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS1'
/*
SID_SERIAL|ORAUSER|PROGRAM|UNDOSEG|Undo|TABLESPACE_NAME
615,998|USER1|runform30x@host1 (TNS interface)|_SYSSMU102$|8K|UNDOTBS1
*/
--You can get more info about the undo extent that is online (used)
SELECT *
FROM dba_rollback_segs
WHERE status = 'ONLINE'
AND tablespace_name = 'UNDOTBS1'
ORDER BY tablespace_name, segment_name
/*
SEGMENT_NAME|OWNER|TABLESPACE_NAME|SEGMENT_ID|FILE_ID|BLOCK_ID|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|STATUS|INSTANCE_NUM|RELATIVE_FNO
_SYSSMU102$|PUBLIC|UNDOTBS1|102|33|4473|131072||2|32765||ONLINE||33
*/
--Monitor the status of online undo extents
SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS1'
AND segment_id = usn;
/*
SEGMENT_NAME|XACTS|STATUS
_SYSSMU102$|1|PENDING OFFLINE
*/
--See it is in "PENDING OFFLINE" status until the transaction that is using it ends.
--See all the rollback info
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
/*
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU102$|PENDING OFFLINE|42|42065920|42065920|1
_SYSSMU262$|ONLINE|4|253952|253952|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|2|122880|122880|0
_SYSSMU271$|ONLINE|2|122880|122880|0
*/
--Find the active transactions using undo extents
SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
/*
USERNAME|XIDUSN|UBAFIL|UBABLK|USED_UBLK
MARDATA|102|30|153176|1
*/
/*
After all active transactions are committed, the status of Undo segment
_SYSSMU102$ is automatically switched from PENDING OFFLINE mode to OFFLINE mode.
PENDING OFFLINE mode does not allow any deletion process of tablespace.
You can delete the tablespace in OFFLINE mode.
If no segment is selected after you execute the SQL statement below,
it means the segment is already switched to OFFLINE mode.
SQL> SELECT SEGMENT_NAME, XACTS, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS_02'
AND SEGMENT_ID = USN;
If the segment is selected and XACTS points to zero (0), rollback segment
does not contain any pending transactions, and therefore,
the segment is switched to OFFLINE mode immediately
*/
--Wait for the active transaction to finish then monitor again
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU262$|ONLINE|5|319488|319488|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|3|188416|188416|0
--As you can see all undo extents of the old undo tablespace UNDOTBS1 are dropped already
--now we can drop the tablespace
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;
Recreate the original configuration :
After that we can create again the original configuration, so recreate the old undo again
and switch to it
ALTER SYSTEM SET undo_tablespace = UNDOTBS1;
--See if there are any active undo extents of UNDOTBS2
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU1$|ONLINE|2|122880|122880|0
_SYSSMU2$|ONLINE|2|122880|122880|0
_SYSSMU3$|ONLINE|2|122880|122880|0
_SYSSMU4$|ONLINE|2|122880|122880|0
_SYSSMU5$|ONLINE|2|122880|122880|0
_SYSSMU6$|ONLINE|2|122880|122880|0
_SYSSMU7$|ONLINE|2|122880|122880|0
_SYSSMU8$|ONLINE|2|122880|122880|0
_SYSSMU9$|ONLINE|2|122880|122880|0
_SYSSMU10$|ONLINE|2|122880|122880|0
--See if there is any rollback segments of UNDOTBS2
SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS2'
AND segment_id = usn;
--No rows returned
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
Author
Ural Ural
Oracle DBA
How do you resize undo tablepaces ? This is how I do it in an Oracle 9i database.
Problem is the undo tablespace has grown up to the limit of its maxsize
and we need to compact it or just we want to make it smaller due to a space problem
--Find undo info
SELECT NAME, VALUE
FROM v$parameter
WHERE LOWER (NAME) LIKE '%undo%';
NAME|VALUE
undo_management|AUTO
undo_tablespace|UNDOTBS1
--Find undo tablespace datafile info
SELECT file_name, BYTES / 1024 / 1024 mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
ORDER BY file_name;
/*
FILE_NAME|MB
/datac3/oradata/andlp2/undotbs1_01.dbf|1793
/datac6/oradata/andlp2/undotbs1_02.dbf|235
/datac7/oradata/andlp2/undotbs1_03.dbf|1679
*/
--Execute the following query to determine who is using a particular UNDO or Rollback Segment:
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
--No rows returned
--Create a second temporary undo tablespace
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/datac7/oradata/andlp2/undotbs2_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M
ONLINE
BLOCKSIZE 8K;
--Set this new undo tablespace as undo tablespace of the database
ALTER SYSTEM SET undo_tablespace = UNDOTBS2;
--You can monitor the undo extents of all the undo tablespaces by using the following query
SELECT tablespace_name, segment_name, SUM (blocks), SUM (BYTES) / 1024
FROM dba_undo_extents
GROUP BY tablespace_name, segment_name;
--Since we are going to drop the old undo tablespace we need to know if any session is
--using any undo extent of this undo tablespace
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS1'
/*
SID_SERIAL|ORAUSER|PROGRAM|UNDOSEG|Undo|TABLESPACE_NAME
615,998|USER1|runform30x@host1 (TNS interface)|_SYSSMU102$|8K|UNDOTBS1
*/
--You can get more info about the undo extent that is online (used)
SELECT *
FROM dba_rollback_segs
WHERE status = 'ONLINE'
AND tablespace_name = 'UNDOTBS1'
ORDER BY tablespace_name, segment_name
/*
SEGMENT_NAME|OWNER|TABLESPACE_NAME|SEGMENT_ID|FILE_ID|BLOCK_ID|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|STATUS|INSTANCE_NUM|RELATIVE_FNO
_SYSSMU102$|PUBLIC|UNDOTBS1|102|33|4473|131072||2|32765||ONLINE||33
*/
--Monitor the status of online undo extents
SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS1'
AND segment_id = usn;
/*
SEGMENT_NAME|XACTS|STATUS
_SYSSMU102$|1|PENDING OFFLINE
*/
--See it is in "PENDING OFFLINE" status until the transaction that is using it ends.
--See all the rollback info
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
/*
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU102$|PENDING OFFLINE|42|42065920|42065920|1
_SYSSMU262$|ONLINE|4|253952|253952|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|2|122880|122880|0
_SYSSMU271$|ONLINE|2|122880|122880|0
*/
--Find the active transactions using undo extents
SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
/*
USERNAME|XIDUSN|UBAFIL|UBABLK|USED_UBLK
MARDATA|102|30|153176|1
*/
/*
After all active transactions are committed, the status of Undo segment
_SYSSMU102$ is automatically switched from PENDING OFFLINE mode to OFFLINE mode.
PENDING OFFLINE mode does not allow any deletion process of tablespace.
You can delete the tablespace in OFFLINE mode.
If no segment is selected after you execute the SQL statement below,
it means the segment is already switched to OFFLINE mode.
SQL> SELECT SEGMENT_NAME, XACTS, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS_02'
AND SEGMENT_ID = USN;
If the segment is selected and XACTS points to zero (0), rollback segment
does not contain any pending transactions, and therefore,
the segment is switched to OFFLINE mode immediately
*/
--Wait for the active transaction to finish then monitor again
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU262$|ONLINE|5|319488|319488|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|3|188416|188416|0
--As you can see all undo extents of the old undo tablespace UNDOTBS1 are dropped already
--now we can drop the tablespace
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;
Recreate the original configuration :
After that we can create again the original configuration, so recreate the old undo again
and switch to it
ALTER SYSTEM SET undo_tablespace = UNDOTBS1;
--See if there are any active undo extents of UNDOTBS2
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU1$|ONLINE|2|122880|122880|0
_SYSSMU2$|ONLINE|2|122880|122880|0
_SYSSMU3$|ONLINE|2|122880|122880|0
_SYSSMU4$|ONLINE|2|122880|122880|0
_SYSSMU5$|ONLINE|2|122880|122880|0
_SYSSMU6$|ONLINE|2|122880|122880|0
_SYSSMU7$|ONLINE|2|122880|122880|0
_SYSSMU8$|ONLINE|2|122880|122880|0
_SYSSMU9$|ONLINE|2|122880|122880|0
_SYSSMU10$|ONLINE|2|122880|122880|0
--See if there is any rollback segments of UNDOTBS2
SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS2'
AND segment_id = usn;
--No rows returned
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
Saturday, January 3, 2009
Oracle 9, 10g, 11g - Each active session
Oracle 9, 10g, 11g - Each active session
Last updated 04 September 2008 with Streams monitoring
select STATUS, wait_event, QC_SLAVE, PARALLEL_SESS, QC_SID,
SID, USERNAME, OSUSER, MACHINE, TERMINAL,
PROGRAM, LOGON_TIME, SERVER, SPID, PROCESS, SERIAL#,
TYPE, MODULE, ACTION, SCHEMANAME,
CURRENT_USER, TEMP_MB_USED, locks_blocking, locks_not_blocking, waiting_count, holding_count,
IO_block_gets, IO_consistent_gets, IO_physical_reads, IO_block_changes,
IO_consistent_changes,
to_char(PGA_USED_MEM_MB, 'FM99990.09') PGA_USED_MEM_MB,
to_char(PGA_ALLOC_MEM_MB, 'FM99990.09') PGA_ALLOC_MEM_MB,
to_char(PGA_MAX_MEM_MB, 'FM99990.09') PGA_MAX_MEM_MB,
INACTIVITY_DAYS, OPEN_CURSORS, PARAM_OPEN_CURSORS, Param_sess_cached_cursors,
to_char(PERC_OPEN_CURSORS, 'FM99990.09') PERC_OPEN_CURSORS,
to_char(open_cursors_and_cache_closed, 'FM99990.09') open_cursors_and_cache_closed,
PLAN_VARIATIONS,
SQL_HASH_VALUE, trim(to_char(SQL_ADDRESS||' ')) SQL_ADDRESS,
trim(to_char(saddr||' ')) saddr, KILL_SESSION,
START_TRACE, STOP_TRACE,
KILL_UNIX_PROCESS, undo_records, undo_start_time,
trim(STREAM_INFO || ' ' || STREAM_CAPTURE_INFO || ' ' || STREAM_APPLY_READER_INFO || ' ' || STREAM_APPLY_COORDINATOR) stream,
STREAM_CAPTURE_PROBLEM stream_status from (
select a.*
, (select event from v$session_wait where sid = a.sid and not event in ( --select event from perfstat.stats$idle_event
'AQ Proxy Cleanup Wait',
'ASM background timer',
'DBRM Logical Idle Wait',
'DIAG idle wait',
'EMON idle wait',
'EMON slave idle wait',
'IORM Scheduler Slave Idle Wait',
'KSV master wait',
'LNS ASYNC archive log',
'LNS ASYNC dest activation',
'LNS ASYNC end of log',
'LogMiner: client waiting for transaction',
'LogMiner: generic process sleep',
'LogMiner: reader waiting for more redo',
'LogMiner: slave waiting for activate message',
'LogMiner: waiting for processes to soft detach',
'LogMiner: wakeup event for builder',
'LogMiner: wakeup event for preparer',
'LogMiner: wakeup event for reader',
'MRP redo arrival',
'Null event',
'PING',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Par Recov Execute',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'PX Deque wait',
'PX Idle Wait',
'Queue Monitor Shutdown Wait',
'Queue Monitor Slave Wait',
'Queue Monitor Wait',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'STREAMS apply coord waiting for slave message',
'STREAMS apply slave idle wait',
'STREAMS apply slave waiting for coord message',
'STREAMS capture process filter callback wait for ruleset',
'STREAMS fetch slave waiting for txns',
'STREAMS waiting for subscribers to catch up',
'Space Manager: slave idle wait',
'Streams AQ: RAC qmn coordinator idle wait',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: delete acknowledged messages',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams capture: waiting for archive log',
'Streams fetch slave: waiting for txns',
'class slave wait',
'client message',
'cmon timer',
'dispatcher timer',
'fbar timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'knlqdeq',
'lock manager wait for remote message',
'master wait',
'null event',
'parallel query dequeue',
'parallel recovery coordinator waits for slave cleanup',
'parallel recovery slave idle wait',
'parallel recovery slave next change',
'parallel recovery slave wait for change',
'pipe get',
'pmon timer',
'pool server timer',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'virtual circuit status',
'wait for activate message',
'wait for unread message on broadcast channel',
'wakeup event for builder',
'wakeup event for preparer',
'wakeup event for reader',
'wakeup time manager',
'watchdog main loop'
)) wait_event
, (select decode(a.sid, m.sid, '************', '') from v$mystat m where rownum=1) current_user
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', TRUE); end;') start_trace
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', FALSE); end;') stop_trace
, ('ALTER SYSTEM DISCONNECT SESSION ''' || sid ||','||serial#||''' IMMEDIATE/*spid='||spid||'*/') kill_session
, ('kill -9 '||spid) kill_unix_process
, (select count(*) from v$lock where sid = a.sid and block != 0) locks_blocking
, (select count(*) from v$lock where sid = a.sid and block = 0) locks_not_blocking
, (open_cursors * 100 / param_open_cursors) perc_open_cursors
--Is the session using a query with a variated plan?
, (select count(*) from (
select hash_value, count(*) from (
select hash_value, plan_hash_value from v$sql group by hash_value, plan_hash_value
) group by hash_value having count(*) > 1
) where hash_value = sql_hash_value) plan_variations
-- R O L L B A C K
, (select decode(sort_space_mb, null, 'No temporary used',
sort_space_mb || 'MB, ' || to_char(sort_space_mb * 100 / (sum(bytes_used)/1024/1024), 'FM90.99')||'% of used, '||
to_char(sort_space_mb * 100 / (sum(bytes_used + bytes_free)/1024/1024), 'FM90.99')||'% of ' || (sum(bytes_used + bytes_free)/1024/1024) ||'MB')
from v$temp_space_header) temp_MB_used
from (
select
s.status, s.saddr
, nvl((select decode(px.qcinst_id,NULL, 'Master(QC)', 'Slave') from v$px_session px where px.sid = s.sid), 'Master') QC_Slave
, (select decode(count(*), 0, 0, count(*)-1) from v$px_session px where px.qcsid=(select qcsid from v$px_session where sid =s.sid)) parallel_sess
, (select decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) from v$px_session px where px.sid = s.sid) QC_SID
, (select spid from v$process where addr = paddr) spid
, (select value from v$parameter where name = 'open_cursors') param_open_cursors
, (select value from v$parameter where name = 'session_cached_cursors') Param_sess_cached_cursors
, s.sid, decode(s.username, null, 'Server process', s.username) username
, osuser, machine, terminal, program
, logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server, process, s.serial#, type, s.module, s.action,
resource_consumer_group,
sql_hash_value, sql_address, schemaname
--ORACLE 8 MUST COMMENT ALL begin
, (select pga_used_mem/1024/1024 from v$process where addr = paddr) pga_used_mem_MB
, (select pga_alloc_mem/1024/1024 from v$process where addr = paddr) pga_alloc_mem_MB
, (select pga_max_mem/1024/1024 from v$process where addr = paddr) pga_max_mem_MB
--ORACLE 8 MUST COMMENT All end
, (select sum(V$SORT_USAGE.blocks *
--(select value from v$parameter where upper(name) = 'DB_BLOCK_SIZE')
--ORACLE 8 MUST COMMENT THIS LINE BELOW AND ENABLE THE ABOVE
dba_tablespaces.block_size
)/1024/1024 from V$SORT_USAGE, dba_tablespaces
where dba_tablespaces.tablespace_name = V$SORT_USAGE.tablespace and V$SORT_USAGE.session_addr = s.saddr) sort_space_MB
, (select a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic#
and b.name = 'opened cursors current' and a.sid = s.sid) open_cursors
, (select count(*) from v$open_cursor where sid=s.sid) open_cursors_and_cache_closed
, V$SESS_IO.block_gets IO_block_gets, V$SESS_IO.consistent_gets IO_consistent_gets
, V$SESS_IO.physical_reads IO_physical_reads, V$SESS_IO.block_changes IO_block_changes
, V$SESS_IO.consistent_changes IO_consistent_changes
, (select count(*) from DBA_WAITERS where waiting_session = s.sid) waiting_count
, (select count(*) from DBA_WAITERS where holding_session = s.sid) holding_count
, (select sum(used_urec) from V$TRANSACTION where s.saddr = ses_addr) undo_records
, (select min(start_time) from V$TRANSACTION where s.saddr = ses_addr) undo_start_time
/* stream */
, (select decode(module, 'STREAMS', module||', ' || action, null) from v$session where sid=s.sid) STREAM_INFO
/* stream capture */
, (select state || ', [' || CAPTURE_NAME || ' capt. name], [' || ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) || '/' ||
((SYSDATE - CAPTURE_TIME)*86400 || ' latency sec]') from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_INFO
, (select decode(state, 'CAPTURING CHANGES', 0, 1) from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_PROBLEM
, (select '[Apply status: ' || DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') || ']' ||
'[Program: ' || SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) || ']'
from V$STREAMS_APPLY_READER, DBA_APPLY ap
where SID = s.SID AND SERIAL# = s.SERIAL# and V$STREAMS_APPLY_READER.APPLY_NAME = ap.APPLY_NAME)
STREAM_APPLY_READER_INFO
, (SELECT 'Apply Coordinator: ' || apply_name || ', ' || c.state || ', process: ' || substr(s.program,instr(s.program,'(')+1,4)
FROM v$streams_apply_coordinator c where c.sid = s.sid and c.serial# = s.serial#)
STREAM_APPLY_COORDINATOR
from
v$session s, V$SESS_IO
--where not username is null --avoid Oracle processes
where V$SESS_IO.sid (+)= s.sid
) a
) b
Last updated 04 September 2008 with Streams monitoring
select STATUS, wait_event, QC_SLAVE, PARALLEL_SESS, QC_SID,
SID, USERNAME, OSUSER, MACHINE, TERMINAL,
PROGRAM, LOGON_TIME, SERVER, SPID, PROCESS, SERIAL#,
TYPE, MODULE, ACTION, SCHEMANAME,
CURRENT_USER, TEMP_MB_USED, locks_blocking, locks_not_blocking, waiting_count, holding_count,
IO_block_gets, IO_consistent_gets, IO_physical_reads, IO_block_changes,
IO_consistent_changes,
to_char(PGA_USED_MEM_MB, 'FM99990.09') PGA_USED_MEM_MB,
to_char(PGA_ALLOC_MEM_MB, 'FM99990.09') PGA_ALLOC_MEM_MB,
to_char(PGA_MAX_MEM_MB, 'FM99990.09') PGA_MAX_MEM_MB,
INACTIVITY_DAYS, OPEN_CURSORS, PARAM_OPEN_CURSORS, Param_sess_cached_cursors,
to_char(PERC_OPEN_CURSORS, 'FM99990.09') PERC_OPEN_CURSORS,
to_char(open_cursors_and_cache_closed, 'FM99990.09') open_cursors_and_cache_closed,
PLAN_VARIATIONS,
SQL_HASH_VALUE, trim(to_char(SQL_ADDRESS||' ')) SQL_ADDRESS,
trim(to_char(saddr||' ')) saddr, KILL_SESSION,
START_TRACE, STOP_TRACE,
KILL_UNIX_PROCESS, undo_records, undo_start_time,
trim(STREAM_INFO || ' ' || STREAM_CAPTURE_INFO || ' ' || STREAM_APPLY_READER_INFO || ' ' || STREAM_APPLY_COORDINATOR) stream,
STREAM_CAPTURE_PROBLEM stream_status from (
select a.*
, (select event from v$session_wait where sid = a.sid and not event in ( --select event from perfstat.stats$idle_event
'AQ Proxy Cleanup Wait',
'ASM background timer',
'DBRM Logical Idle Wait',
'DIAG idle wait',
'EMON idle wait',
'EMON slave idle wait',
'IORM Scheduler Slave Idle Wait',
'KSV master wait',
'LNS ASYNC archive log',
'LNS ASYNC dest activation',
'LNS ASYNC end of log',
'LogMiner: client waiting for transaction',
'LogMiner: generic process sleep',
'LogMiner: reader waiting for more redo',
'LogMiner: slave waiting for activate message',
'LogMiner: waiting for processes to soft detach',
'LogMiner: wakeup event for builder',
'LogMiner: wakeup event for preparer',
'LogMiner: wakeup event for reader',
'MRP redo arrival',
'Null event',
'PING',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Par Recov Execute',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'PX Deque wait',
'PX Idle Wait',
'Queue Monitor Shutdown Wait',
'Queue Monitor Slave Wait',
'Queue Monitor Wait',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'STREAMS apply coord waiting for slave message',
'STREAMS apply slave idle wait',
'STREAMS apply slave waiting for coord message',
'STREAMS capture process filter callback wait for ruleset',
'STREAMS fetch slave waiting for txns',
'STREAMS waiting for subscribers to catch up',
'Space Manager: slave idle wait',
'Streams AQ: RAC qmn coordinator idle wait',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: delete acknowledged messages',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams capture: waiting for archive log',
'Streams fetch slave: waiting for txns',
'class slave wait',
'client message',
'cmon timer',
'dispatcher timer',
'fbar timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'knlqdeq',
'lock manager wait for remote message',
'master wait',
'null event',
'parallel query dequeue',
'parallel recovery coordinator waits for slave cleanup',
'parallel recovery slave idle wait',
'parallel recovery slave next change',
'parallel recovery slave wait for change',
'pipe get',
'pmon timer',
'pool server timer',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'virtual circuit status',
'wait for activate message',
'wait for unread message on broadcast channel',
'wakeup event for builder',
'wakeup event for preparer',
'wakeup event for reader',
'wakeup time manager',
'watchdog main loop'
)) wait_event
, (select decode(a.sid, m.sid, '************', '') from v$mystat m where rownum=1) current_user
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', TRUE); end;') start_trace
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', FALSE); end;') stop_trace
, ('ALTER SYSTEM DISCONNECT SESSION ''' || sid ||','||serial#||''' IMMEDIATE/*spid='||spid||'*/') kill_session
, ('kill -9 '||spid) kill_unix_process
, (select count(*) from v$lock where sid = a.sid and block != 0) locks_blocking
, (select count(*) from v$lock where sid = a.sid and block = 0) locks_not_blocking
, (open_cursors * 100 / param_open_cursors) perc_open_cursors
--Is the session using a query with a variated plan?
, (select count(*) from (
select hash_value, count(*) from (
select hash_value, plan_hash_value from v$sql group by hash_value, plan_hash_value
) group by hash_value having count(*) > 1
) where hash_value = sql_hash_value) plan_variations
-- R O L L B A C K
, (select decode(sort_space_mb, null, 'No temporary used',
sort_space_mb || 'MB, ' || to_char(sort_space_mb * 100 / (sum(bytes_used)/1024/1024), 'FM90.99')||'% of used, '||
to_char(sort_space_mb * 100 / (sum(bytes_used + bytes_free)/1024/1024), 'FM90.99')||'% of ' || (sum(bytes_used + bytes_free)/1024/1024) ||'MB')
from v$temp_space_header) temp_MB_used
from (
select
s.status, s.saddr
, nvl((select decode(px.qcinst_id,NULL, 'Master(QC)', 'Slave') from v$px_session px where px.sid = s.sid), 'Master') QC_Slave
, (select decode(count(*), 0, 0, count(*)-1) from v$px_session px where px.qcsid=(select qcsid from v$px_session where sid =s.sid)) parallel_sess
, (select decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) from v$px_session px where px.sid = s.sid) QC_SID
, (select spid from v$process where addr = paddr) spid
, (select value from v$parameter where name = 'open_cursors') param_open_cursors
, (select value from v$parameter where name = 'session_cached_cursors') Param_sess_cached_cursors
, s.sid, decode(s.username, null, 'Server process', s.username) username
, osuser, machine, terminal, program
, logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server, process, s.serial#, type, s.module, s.action,
resource_consumer_group,
sql_hash_value, sql_address, schemaname
--ORACLE 8 MUST COMMENT ALL begin
, (select pga_used_mem/1024/1024 from v$process where addr = paddr) pga_used_mem_MB
, (select pga_alloc_mem/1024/1024 from v$process where addr = paddr) pga_alloc_mem_MB
, (select pga_max_mem/1024/1024 from v$process where addr = paddr) pga_max_mem_MB
--ORACLE 8 MUST COMMENT All end
, (select sum(V$SORT_USAGE.blocks *
--(select value from v$parameter where upper(name) = 'DB_BLOCK_SIZE')
--ORACLE 8 MUST COMMENT THIS LINE BELOW AND ENABLE THE ABOVE
dba_tablespaces.block_size
)/1024/1024 from V$SORT_USAGE, dba_tablespaces
where dba_tablespaces.tablespace_name = V$SORT_USAGE.tablespace and V$SORT_USAGE.session_addr = s.saddr) sort_space_MB
, (select a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic#
and b.name = 'opened cursors current' and a.sid = s.sid) open_cursors
, (select count(*) from v$open_cursor where sid=s.sid) open_cursors_and_cache_closed
, V$SESS_IO.block_gets IO_block_gets, V$SESS_IO.consistent_gets IO_consistent_gets
, V$SESS_IO.physical_reads IO_physical_reads, V$SESS_IO.block_changes IO_block_changes
, V$SESS_IO.consistent_changes IO_consistent_changes
, (select count(*) from DBA_WAITERS where waiting_session = s.sid) waiting_count
, (select count(*) from DBA_WAITERS where holding_session = s.sid) holding_count
, (select sum(used_urec) from V$TRANSACTION where s.saddr = ses_addr) undo_records
, (select min(start_time) from V$TRANSACTION where s.saddr = ses_addr) undo_start_time
/* stream */
, (select decode(module, 'STREAMS', module||', ' || action, null) from v$session where sid=s.sid) STREAM_INFO
/* stream capture */
, (select state || ', [' || CAPTURE_NAME || ' capt. name], [' || ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) || '/' ||
((SYSDATE - CAPTURE_TIME)*86400 || ' latency sec]') from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_INFO
, (select decode(state, 'CAPTURING CHANGES', 0, 1) from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_PROBLEM
, (select '[Apply status: ' || DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') || ']' ||
'[Program: ' || SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) || ']'
from V$STREAMS_APPLY_READER, DBA_APPLY ap
where SID = s.SID AND SERIAL# = s.SERIAL# and V$STREAMS_APPLY_READER.APPLY_NAME = ap.APPLY_NAME)
STREAM_APPLY_READER_INFO
, (SELECT 'Apply Coordinator: ' || apply_name || ', ' || c.state || ', process: ' || substr(s.program,instr(s.program,'(')+1,4)
FROM v$streams_apply_coordinator c where c.sid = s.sid and c.serial# = s.serial#)
STREAM_APPLY_COORDINATOR
from
v$session s, V$SESS_IO
--where not username is null --avoid Oracle processes
where V$SESS_IO.sid (+)= s.sid
) a
) b
sessions script 2
See any locking session
select * from dba_locks
order by blocking_others
Use utllockt.sql to see locking sessions
Remember that catblock.sql should have ran, you can verify this by using:
select * from dba_locks
sqlplus (windows)>@?\rdbms\admin\utllockt
sqlplus (unix)>@?/rdbms/admin/utllockt
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
20 None
9 DML Exclusive Exclusive 54132 0
In this case the session 20 is locking session 9, so kill session 20
Library cache pin locks
SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
, 'kill -9 ' || spid
FROM x$kglpn p, v$session s, v$process
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='DA741F24'
and v$process.addr = s.paddr
A session is hang, kill anyone interfering
SELECT *
FROM v$access
WHERE OBJECT IN (SELECT object_name
FROM dba_objects
WHERE object_id IN (SELECT object_id
FROM v$locked_object
WHERE session_id = 32))
select 'ALTER SYSTEM DISCONNECT SESSION ''' || sid || ', ' || v$session.serial# ||
''' immediate/*spid=' || spid || '*/'
from v$session, v$process
where sid in (
select sid from v$access
where object = 'VTCONNECTORSTATE'
group by sid
)
and v$process.addr = v$session.paddr
select * from (
SELECT s.schemaname, p.username, s.sid, p.pid, p.spid, s.username ora,
DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL', 'TS','TEMPORARY SEGMENT ', 'TD','TABLE LOCK',
'TM','ROW LOCK', l2.type ) vlock,
DECODE(l2.type, 'TX','DML LOCK', 'TS','TEMPORARY SEGMENT', 'TD',DECODE(l2.lmode+l2.request,
4,'PARSE ' || u.name || '.' || o.name, 6,'DDL', l2.lmode+l2.request), 'TM','DML ' || u.name ||
'.' || o.name, l2.type ) type,
DECODE(l2.lmode+l2.request, 2,'RS', 3 ,'RX', 4 ,'S', 5 ,'SRX', 6 ,'X', l2.lmode+l2.request ) lmode ,
DECODE(l2.request, 0,NULL, 'WAIT') wait
FROM v$process p, v$_lock l1, v$lock l2, v$resource r, sys.obj$ o, sys.user$ u, v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND l2.type <> 'RT'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
)
Lock simulation
The updates performed in this example does not modify the actual values!
select id, de_VC1 from prova where id in (193556, 196756)
ID DE_VC1
---------- --------------------
193556 mon
196756 mon
session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 193556;
1 row updated.
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 145
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 145 Transaction Exclusive None 196615 676 270 Not Blocking
session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 196756;
1 row updated.
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 156
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 156 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 270 Not Blocking
session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 196756;
--session hang waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 883 Not Blocking
ALDO 145 DML Row-X (SX) None 53176 0 883 Not Blocking
ALDO 145 Transaction None Exclusive 262191 680 81 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 349 Blocking
ALDO 156 DML Row-X (SX) None 53176 0 349 Not Blocking
session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 193556;
--session hang waiting
session 1, sid 145>ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session 2, sid 156>--continue waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 1246 Blocking
ALDO 145 DML Row-X (SX) None 53176 0 1246 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 712 Not Blocking
ALDO 156 Transaction None Exclusive 196615 676 9 Not Blocking
ALDO 156 DML Row-X (SX) None 53176 0 712 Not Blocking
Trace generated
Dump file c:\oracle\product\10.2.0\admin\al12\udump\al12_ora_5428.trc
Wed Feb 22 11:27:34 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:985M/2046M, Ph+PgF:2957M/3938M, VA:1713M/2047M
Instance name: al12
Redo thread mounted by this instance: 1
Oracle process number: 19
Windows thread id: 5428, image: ORACLE.EXE (SHAD)
*** 2006-02-22 11:27:34.171
*** ACTION NAME:() 2006-02-22 11:27:34.156
*** MODULE NAME:(SQL*Plus) 2006-02-22 11:27:34.156
*** SERVICE NAME:(al12) 2006-02-22 11:27:34.156
*** SESSION ID:(35.38) 2006-02-22 11:27:34.156
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update prova set de_VC1 = 'sess1' where id = 196756 (the session was waiting, then take ORA-00060)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020021-000005aa 19 35 X 20 46 X
TX-0003001f-000005b4 20 46 X 19 35 X
session 35: DID 0001-0013-00000007 session 46: DID 0001-0014-00000005
session 46: DID 0001-0014-00000005 session 35: DID 0001-0013-00000007
Rows waited on:
Session 46: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAB
(dictionary objn - 53176, file - 7, block - 4122, slot - 1)
Session 35: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAE
(dictionary objn - 53176, file - 7, block - 4122, slot - 4)
Information on the OTHER waiting sessions:
Session 46:
pid=20 serial=30 audsid=4264 user: 55/ALDO
O/S info: user: root, term: D2E7B5729B, ospid: 5276:1080, machine: WORKGROUP\D2E7B5729B
program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update prova set de_VC1 = 'sess2' where id = 193556 (the session may continue waiting if sess1 do nothing)
End of information on OTHER waiting sessions.
===================================================
Get all long running queries
SELECT /*+ rule */
SYSDATE tracciamento, a.SID SID, b.serial# serial, b.last_call_et,
a.event wait_event, b.username username, b.machine machine,
b.program programma, sq.sql_text
FROM v$session_wait a, v$session b, v$sql sq
WHERE event NOT IN
('smon timer',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'SQL*Net message from dblink',
'jobq slave wait'
)
AND a.SID = b.SID
AND b.sql_address = sq.address
AND b.last_call_et > 60
AND b.username NOT IN ('SYS', 'SYSTEM', 'EXPDB')
AND b.SID NOT IN (SELECT SID
FROM dba_jobs_running)
ORDER BY b.last_call_et DESC
Master sessions and parallel spawned sessions
col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pages 100
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from
v$px_session px,
v$session s
where
px.sid=s.sid (+)
and
px.serial#=s.serial#
order by 5 , 1 desc;
select * from dba_locks
order by blocking_others
Use utllockt.sql to see locking sessions
Remember that catblock.sql should have ran, you can verify this by using:
select * from dba_locks
sqlplus (windows)>@?\rdbms\admin\utllockt
sqlplus (unix)>@?/rdbms/admin/utllockt
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
20 None
9 DML Exclusive Exclusive 54132 0
In this case the session 20 is locking session 9, so kill session 20
Library cache pin locks
SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
, 'kill -9 ' || spid
FROM x$kglpn p, v$session s, v$process
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='DA741F24'
and v$process.addr = s.paddr
A session is hang, kill anyone interfering
SELECT *
FROM v$access
WHERE OBJECT IN (SELECT object_name
FROM dba_objects
WHERE object_id IN (SELECT object_id
FROM v$locked_object
WHERE session_id = 32))
select 'ALTER SYSTEM DISCONNECT SESSION ''' || sid || ', ' || v$session.serial# ||
''' immediate/*spid=' || spid || '*/'
from v$session, v$process
where sid in (
select sid from v$access
where object = 'VTCONNECTORSTATE'
group by sid
)
and v$process.addr = v$session.paddr
select * from (
SELECT s.schemaname, p.username, s.sid, p.pid, p.spid, s.username ora,
DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL', 'TS','TEMPORARY SEGMENT ', 'TD','TABLE LOCK',
'TM','ROW LOCK', l2.type ) vlock,
DECODE(l2.type, 'TX','DML LOCK', 'TS','TEMPORARY SEGMENT', 'TD',DECODE(l2.lmode+l2.request,
4,'PARSE ' || u.name || '.' || o.name, 6,'DDL', l2.lmode+l2.request), 'TM','DML ' || u.name ||
'.' || o.name, l2.type ) type,
DECODE(l2.lmode+l2.request, 2,'RS', 3 ,'RX', 4 ,'S', 5 ,'SRX', 6 ,'X', l2.lmode+l2.request ) lmode ,
DECODE(l2.request, 0,NULL, 'WAIT') wait
FROM v$process p, v$_lock l1, v$lock l2, v$resource r, sys.obj$ o, sys.user$ u, v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND l2.type <> 'RT'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
)
Lock simulation
The updates performed in this example does not modify the actual values!
select id, de_VC1 from prova where id in (193556, 196756)
ID DE_VC1
---------- --------------------
193556 mon
196756 mon
session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 193556;
1 row updated.
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 145
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 145 Transaction Exclusive None 196615 676 270 Not Blocking
session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 196756;
1 row updated.
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 156
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 156 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 270 Not Blocking
session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 196756;
--session hang waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 883 Not Blocking
ALDO 145 DML Row-X (SX) None 53176 0 883 Not Blocking
ALDO 145 Transaction None Exclusive 262191 680 81 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 349 Blocking
ALDO 156 DML Row-X (SX) None 53176 0 349 Not Blocking
session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 193556;
--session hang waiting
session 1, sid 145>ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session 2, sid 156>--continue waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id
username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 1246 Blocking
ALDO 145 DML Row-X (SX) None 53176 0 1246 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 712 Not Blocking
ALDO 156 Transaction None Exclusive 196615 676 9 Not Blocking
ALDO 156 DML Row-X (SX) None 53176 0 712 Not Blocking
Trace generated
Dump file c:\oracle\product\10.2.0\admin\al12\udump\al12_ora_5428.trc
Wed Feb 22 11:27:34 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:985M/2046M, Ph+PgF:2957M/3938M, VA:1713M/2047M
Instance name: al12
Redo thread mounted by this instance: 1
Oracle process number: 19
Windows thread id: 5428, image: ORACLE.EXE (SHAD)
*** 2006-02-22 11:27:34.171
*** ACTION NAME:() 2006-02-22 11:27:34.156
*** MODULE NAME:(SQL*Plus) 2006-02-22 11:27:34.156
*** SERVICE NAME:(al12) 2006-02-22 11:27:34.156
*** SESSION ID:(35.38) 2006-02-22 11:27:34.156
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update prova set de_VC1 = 'sess1' where id = 196756 (the session was waiting, then take ORA-00060)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020021-000005aa 19 35 X 20 46 X
TX-0003001f-000005b4 20 46 X 19 35 X
session 35: DID 0001-0013-00000007 session 46: DID 0001-0014-00000005
session 46: DID 0001-0014-00000005 session 35: DID 0001-0013-00000007
Rows waited on:
Session 46: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAB
(dictionary objn - 53176, file - 7, block - 4122, slot - 1)
Session 35: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAE
(dictionary objn - 53176, file - 7, block - 4122, slot - 4)
Information on the OTHER waiting sessions:
Session 46:
pid=20 serial=30 audsid=4264 user: 55/ALDO
O/S info: user: root, term: D2E7B5729B, ospid: 5276:1080, machine: WORKGROUP\D2E7B5729B
program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update prova set de_VC1 = 'sess2' where id = 193556 (the session may continue waiting if sess1 do nothing)
End of information on OTHER waiting sessions.
===================================================
Get all long running queries
SELECT /*+ rule */
SYSDATE tracciamento, a.SID SID, b.serial# serial, b.last_call_et,
a.event wait_event, b.username username, b.machine machine,
b.program programma, sq.sql_text
FROM v$session_wait a, v$session b, v$sql sq
WHERE event NOT IN
('smon timer',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'SQL*Net message from dblink',
'jobq slave wait'
)
AND a.SID = b.SID
AND b.sql_address = sq.address
AND b.last_call_et > 60
AND b.username NOT IN ('SYS', 'SYSTEM', 'EXPDB')
AND b.SID NOT IN (SELECT SID
FROM dba_jobs_running)
ORDER BY b.last_call_et DESC
Master sessions and parallel spawned sessions
col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pages 100
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from
v$px_session px,
v$session s
where
px.sid=s.sid (+)
and
px.serial#=s.serial#
order by 5 , 1 desc;
This script produces a very detailed report on Current Lock Waits in the database including a list of holding and waiting sessions and the types of locks they are holding.
set serveroutput on size 10000
declare
cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'LOCALHOST',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'LOCALHOST\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
set serveroutput on size 10000
declare
cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'LOCALHOST',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'LOCALHOST\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
Friday, January 2, 2009
Fast Index Rebuild.
An index should be considered for rebuilding under any of the following conditions:
* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
Fast Index Rebuild.
The REBUILD option of the ALTER INDEX statement is executed to quickly rebuild an index using the existing index instead of the table:
alter index emp_idx1 rebuild parallel
tablespace user_tblspc1
storage (pctincrease 0);
Index altered.
Any Modifications to the STORAGE clause can be made at this time and the parallel option can also be used if parallelism is enabled in your database.
Using the REBUILD option of the ALTER INDEX statement for quickly rebuilding an index requires the existing index instead of the table. So necessary space should be available to store both the indexes during the REBUILD operation.
You can also REBUILD online clause to allow DML operations on the table. Index Rebuild online is not applicable for Bitmap indexes or for indexes that enfore referential Integirity Constraints.
This code genenates a script to rebuld all the indexes with height greater than 3 for all the users except for SYS user. The script needs to be executed from SYS schema. This can also be executed from individual schemas by minor changes. If you need to exclude indxes of some other users that can also be added/changed in cGetIdx cursor. To execute this code from any schema other than SYS. Execute on DBMS_SQL must be granted from SYS(not system) to the the specified user.
Indexes with height less than three (3) should not be rebuilded. If you want to go for indexes with less heights that can also be customized by changing the height parameter.
Set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
Fast Index Rebuild.
The REBUILD option of the ALTER INDEX statement is executed to quickly rebuild an index using the existing index instead of the table:
alter index emp_idx1 rebuild parallel
tablespace user_tblspc1
storage (pctincrease 0);
Index altered.
Any Modifications to the STORAGE clause can be made at this time and the parallel option can also be used if parallelism is enabled in your database.
Using the REBUILD option of the ALTER INDEX statement for quickly rebuilding an index requires the existing index instead of the table. So necessary space should be available to store both the indexes during the REBUILD operation.
You can also REBUILD online clause to allow DML operations on the table. Index Rebuild online is not applicable for Bitmap indexes or for indexes that enfore referential Integirity Constraints.
This code genenates a script to rebuld all the indexes with height greater than 3 for all the users except for SYS user. The script needs to be executed from SYS schema. This can also be executed from individual schemas by minor changes. If you need to exclude indxes of some other users that can also be added/changed in cGetIdx cursor. To execute this code from any schema other than SYS. Execute on DBMS_SQL must be granted from SYS(not system) to the the specified user.
Indexes with height less than three (3) should not be rebuilded. If you want to go for indexes with less heights that can also be customized by changing the height parameter.
Set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
Automatic index rebuild and reorganizing
----------------------------------------------------
-- Automatic index rebuild and reorganizing
--
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation
--
-- Written by perb@episerver
----------------------------------------------------
declare @sql nvarchar(1000)
declare @indexname nvarchar(255)
declare @tablename nvarchar(255)
declare @fragmentation float
declare @msg nvarchar(512)
declare @dbid int
declare @indexCount int
set nocount on
set @dbid=db_id()
set @indexCount = 0
declare c CURSOR FOR
Select 'ALTER INDEX ' + i.name + ' ON ' + object_name(d.object_id) + CASE WHEN avg_fragmentation_in_percent>30 THEN ' REBUILD' ELSE ' REORGANIZE' END as [sql],
convert(decimal(5,2), avg_fragmentation_in_percent) fragmentation,object_name(d.object_id),i.name
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where d.avg_fragmentation_in_percent > 10
order by avg_fragmentation_in_percent desc
select N'See "Messages" tab for progress!' as Info
raiserror (N'Reading index fragmentation..',0,1) WITH NOWAIT
raiserror (N' ',0,1) WITH NOWAIT
open c
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
while @@FETCH_STATUS = 0
begin
SET @msg = N'Found fragmented index..'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Name: ' + @indexname
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Table: ' + @tablename
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Fragmentation: ' + cast(@fragmentation as nvarchar) + '%s'
raiserror (@msg,0,1,'%') WITH NOWAIT
exec sp_executesql @sql
set @indexCount = @indexCount + 1
SET @msg = N' Defrag done!'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' '
raiserror (@msg,0,1) WITH NOWAIT
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
end
close c
deallocate c
SET @msg = N'--------------------------------'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N'Found and defragged ' + cast(@indexCount as nvarchar) + N' index(es)'
raiserror (@msg,0,1) WITH NOWAIT
-- Automatic index rebuild and reorganizing
--
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation
--
-- Written by perb@episerver
----------------------------------------------------
declare @sql nvarchar(1000)
declare @indexname nvarchar(255)
declare @tablename nvarchar(255)
declare @fragmentation float
declare @msg nvarchar(512)
declare @dbid int
declare @indexCount int
set nocount on
set @dbid=db_id()
set @indexCount = 0
declare c CURSOR FOR
Select 'ALTER INDEX ' + i.name + ' ON ' + object_name(d.object_id) + CASE WHEN avg_fragmentation_in_percent>30 THEN ' REBUILD' ELSE ' REORGANIZE' END as [sql],
convert(decimal(5,2), avg_fragmentation_in_percent) fragmentation,object_name(d.object_id),i.name
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where d.avg_fragmentation_in_percent > 10
order by avg_fragmentation_in_percent desc
select N'See "Messages" tab for progress!' as Info
raiserror (N'Reading index fragmentation..',0,1) WITH NOWAIT
raiserror (N' ',0,1) WITH NOWAIT
open c
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
while @@FETCH_STATUS = 0
begin
SET @msg = N'Found fragmented index..'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Name: ' + @indexname
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Table: ' + @tablename
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Fragmentation: ' + cast(@fragmentation as nvarchar) + '%s'
raiserror (@msg,0,1,'%') WITH NOWAIT
exec sp_executesql @sql
set @indexCount = @indexCount + 1
SET @msg = N' Defrag done!'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' '
raiserror (@msg,0,1) WITH NOWAIT
fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
end
close c
deallocate c
SET @msg = N'--------------------------------'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N'Found and defragged ' + cast(@indexCount as nvarchar) + N' index(es)'
raiserror (@msg,0,1) WITH NOWAIT
Subscribe to:
Posts (Atom)