How to identify lockers This article will explain about locks on rows and on objects in ORACLE. Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s). When there is a … Read more
I’m used to working in a UNIX environment and when I was asked to build a database and manage on Windows, I was faced with some new challenges. One of them is searching through the Alert log and without standard UNIX grep I wanted to write something that could perform the same function. Also show … Read more
How to calculate cumulative backups sizes in Oracle Database.
There are many ways to calculate database growth and I’ve certainly had a few, but this is probably the most accurate so far..
The company I work for would rebuild their tables and indexes every quarter, this did cause some concern for me and they were adamant that this needed to be done. I compromised and came up with this script.
DB Time is a period demonstrate measurement that is the entirety of Oracle process CPU utilization and non-inert hold up time. While advancing Oracle frameworks we regularly center around decreasing “time”, however ordinarily database work is additionally part of the condition. SELECT sum(Round(NVL((e.value – s.value),-1)/60/1000000,2))/1440 ||’ days’ “DB Time” FROM DBA_HIST_SYS_TIME_MODEL s, DBA_HIST_SYS_TIME_MODEL e WHERE … Read more
Calculate the log switches per hour over the last week. SET PAGESIZE 90 SET LINESIZE 150 set heading on column “00:00” format 9999 column “01:00” format 9999 column “02:00” format 9999 column “03:00” format 9999 column “04:00” format 9999 column “05:00” format 9999 column “06:00” format 9999 column “07:00” format 9999 column “08:00” format 9999 … Read more
We can monitor the progress of an undo operation by running the query shown below: select session.username , substr(session.program, 1, 19) command , transaction.used_ublk , from v$session session , v$transaction transaction where session.saddr = transaction.ses_addr; From the first session connected as SH we issue a DELETE statement SQL> conn customer/customer123 Connected. SQL> delete * from … Read more
This is some code how to correctly shrink a datafile. set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by … Read more