:

RSSDatabase Monitoring

Oracle Hidden Parameters

Under normal circumstances you shouldn’t have any need to modify the Oracle hidden parameters, which all start with the underscore character. This is probably why you can’t see them very easily in the database with the normal SHOW command. If you would like to find out what hidden Oracle parameters are set within your database, […]

Continue Reading

What’s in The Oracle Buffer Cache?

First of all, you can try running this query: SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS   FROM V$DB_CACHE_ADVICE      AND BLOCK_SIZE    = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’)      AND ADVICE_STATUS = ‘ON’; This many not return any rows depending upon what other initilisation parameters you have or have not set. You may find […]

Continue Reading

ORA-30036: unable to extend segment by 4 in undo tablespace ‘UNDOTBS1’

One of our databases crashed recently because the disks were taken offline by mistake – fortunately, it was a development one! When I brought it back online and people started to use it, one of the developers got the following error message: ERROR at line 1: ORA-30036: unable to extend segment by 4 in undo […]

Continue Reading

Control File Parallel Write Wait Event

Usually, you will find that high wait times on the control  file parallel write wait event will be indicative of high log switching. The query below will show you which session performed  control file transactions. select                a.sid,                decode(a.type, ‘BACKGROUND’,  ‘BACKGROUND-‘||substr(a.program, instr(a.program,'(‘,1,1)), ‘FOREGROUND’) type,                b.time_waited,                round(b.time_waited/b.total_waits,4) average_wait,        […]

Continue Reading

ORA-00054: resource busy and acquire with NOWAIT specified

The ORA-00054: resource busy and acquire with NOWAIT specified error message usually happens when there are active transactions against an object in the database which mean other sessions cannot get the lock they require on the object to complete their transaction. There are two ways that a transaction can attempt to obtain a lock in […]

Continue Reading

Monitor Oracle UNDO

Monitor Oracle UNDO

Before I go into detail about how to check your UNDO tablespace in Oracle, I’d like to very briefly outline what UNDO is used for in the Oracle database. Quick Recap…Essential Info In short, the UNDO tablespace is used to store before images of database blocks which have had some kind of change made to […]

Continue Reading

Monitoring TEMP Space Usage in Oracle

Monitoring TEMP Space Usage in Oracle

It’s a common problem encountered by many DBAs on a daily basis; developers writing queries which run out of TEMP space. It can come at the worst of times, too… For example, you’ve run in the scripts many times overnight into a development environment and they worked fine. They are signed off and run into […]

Continue Reading