DBAPundits.com

Oracle provides a procedure to insert messages to the alert log and/or trace files for testing/development purposes. This can be used to check the effectiveness of the monitoring tools/scripts used in the environment, to understand how well the monitoring tool captures the messages in the alert log.

Usage:

SQL> exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');

PL/SQL procedure successfully completed.

SQL>

To test whether your monitoring tool captures error messages such as an ORA-00600, try executing the below:

SQL> exec dbms_system.ksdwrt(2, 'ORA-00600: Testing monitoring tool');

PL/SQL procedure successfully completed.

SQL>

The below query helps to identify the session that has locked the resource that your session is trying to lock.

You can try executing the query/job after some time (to allow the other session to complete) or kill the session that has locked the resource to proceed with this situation, use the below query to identify the session.

set linesize 140
set pages 100
col username       format a20
col "SID,SESSION#" format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ' (' || s.osuser || ')' username
  ,  s.sid || ',' || s.serial# "SID,SESSION#"
  ,  owner || '.' || object_name object
  ,  object_type
  ,  decode( l.block
     ,       0, 'Not Blocking'
     ,       1, 'Blocking'
     ,       2, 'Global') status
  ,  decode(v.locked_mode
    ,       0, 'None'
    ,       1, 'Null'
    ,       2, 'Row-S (SS)'
    ,       3, 'Row-X (SX)'
    ,       4, 'Share'
    ,       5, 'S/Row-X (SSX)'
    ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
 from       v$locked_object v
 ,  dba_objects d
 ,  v$lock l
 ,  v$session s
 where      v.object_id = d.object_id
 and        v.object_id = l.id1
 and        v.session_id = s.sid
 order by oracle_username,session_id;

Sample output:

USERNAME	SID,SESSION#	OBJECT		OBJECT_TYPE	STATUS		MODE_HELD
--------	------------	-------------	-----------	------		---------
SCOTT (oracle)	86,30		SCOTT.LOCK_TEST	TABLE		Not Blocking 	Row-X (SX)

To kill the locking session, do as below:

SQL> alter system kill session '86,30';

To identify the locked rows, use the below query:

set lines 200
col object_name for a30
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
				ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from	v$session s
,	dba_objects do
where	s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('<object_name_from_above_query>')
;

OBJECT_NAME	ROW_WAIT_OBJ#	ROW_WAIT_FILE#	ROW_WAIT_BLOCK#	ROW_WAIT_ROW#	ROW_ID
-----------	-------------	--------------	---------------	-------------	------
LOCK_TEST	41862		4		70		0		AAAKOGAAEAAAABGAAA

To simulate the occurrence of this error for testing/learning purposes, do as below in 2 sessions:

Session 1:

SQL> conn scott/tiger;
Connected.

SQL> create table lock_test (x number);
Table created.

SQL> insert into lock_test values(100);
1 row created.

SQL> commit;
Commit complete.

SQL> update lock_test set x=500;
1 row updated.

-- Do not commit.

Session 2:

SQL> conn scott/tiger;
Connected.
declare
y number;
begin
select x into y from lock_test for update nowait;
end;
/

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4

In scenarios where there is no disk space to hold the export dumps or when migrating schemas across different versions of Oracle, this script helps to do the export/import over the network or on the same server using the Unix pipe.

In this example, data is exported from the Oracle Database instance named PROD to the instance named DEV. The export utility connects to the PROD database using the Net Service name, writes the export dump to a Unix pipe, which is read by the import process that loads the data to the DEV database.

Create a file exp_imp.sh:

#!/usr/bin/ksh

export ORACLE_SID=DEV
export ORACLE_HOME=/oracle/product/10204

# make a unix pipe
export PIPE=exp_imp_pipe
mknod exp_imp_pipe p

# Start the Export and run it in background  - Dont omit the '&' at the end.
exp scott/tiger@PROD file=exp_imp_pipe log=exp_PROD.log buffer=1024000 consistent=y OWNER=HR,APP direct=y statistics=none compress=n  &

# Start the Import
imp scott/tiger ignore=y full=y file=exp_imp_pipe log=imp_DEV.log commit=y buffer=1024000 RESUMABLE=Y &

For big schemas, CONSISTENT=Y may not work as the export may fail with an ORA-01555 snapshot too old error, so you may need to revist the parameter. RESUMABLE=Y at the import side can help to make the session ‘resumable’ in case the import hits any space issues on the target database. The export/import logs can be monitored to view the progress of the activity.

In this example the user scott has enough privilege to exp/imp the data, make sure that you connect to the databases as a privileged user and needless to say, it is recommended to run the job in nohup mode to avoid issues in case connectivity to the server gets disturbed.

One of the most used script used by the DBA in day-to-day work, this script gives the space usage of each tablespace in the database.

set heading on
set pagesize 500
set lines 400

column tablespace       format a30                heading "Tablespace"
column avail            format 9,999,999,999,999  heading "MB Avail."
column used             format 9,999,999,999,999  heading "MB Used"
column free             format 9,999,999,999,999  heading "MB Free"
column pct              format 999                heading "Pct"

compute sum of avail used free on report
break on report
select  a.tablespace_name "Tablespace",
        a.avail,
        a.avail-b.free used,
        b.free,
        round(nvl((a.avail-b.free)/a.avail*100,0))      "Pct"
from
(select tablespace_name, round(sum(bytes)/1048576)     avail
        from    sys.dba_data_files
        group by tablespace_name
        UNION
        select  tablespace_name,round(sum(bytes_free+bytes_used)/1048576)
        from v$temp_space_header
        group by tablespace_name)       a,
(select tablespace_name, round(sum(bytes)/1048576)     free
        from    sys.dba_free_space
        group by tablespace_name
        UNION
        select  tablespace_name,round(sum(bytes_free)/1048576)
        from v$temp_space_header
        group by tablespace_name)       b
where  a.tablespace_name = b.tablespace_name (+);

The below query comes handy to understand the archivelog generation of an Oracle database on an hourly /daily basis, per thread – in case of RAC databases.

Archivelog generation on a daily basis:

set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Archive log generation on an hourly basis:

set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Sample output:

HOUR                   THREAD#         MB   ARCHIVES
------------------- ---------- ---------- ----------
2009 08 20 12:00:00          1      31268        339
2009 08 20 13:00:00          1       4994         55
2009 08 20 14:00:00          1       4412         48
2009 08 20 15:00:00          1       4805         52
2009 08 20 16:00:00          1       3364         37
2009 08 20 17:00:00          1         22          1
2009 08 20 21:00:00          1          9          1

Also, the following script is useful to find the archivelog switches on an hourly basis that happened in the past one week, I got this from http://kubilaykara.blogspot.com/2008/02/redo-log-generation.html and is quite an useful one.

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

Sample output:

DAY 00 01 02 03 04 05 06 09 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -
01-NOV-09 0 1 1 0 1 0 1 0 1 4 1 1 0 1 0 1 1 0 1 1 1 1 1 1
02-NOV-09 0 1 1 1 1 0 1 1 1 1 0 1 1 7 1 1 1 1 1 2 1 1 1 1
03-NOV-09 1 2 2 1 1 1 1 1 1 1 1 2 1 1 1 1 1 2 1 1 2 1 1 2
04-NOV-09 1 1 8 1 7 2 1 1 1 2 1 1 2 1 2 1 2 1 2 1 2 1 2 2
05-NOV-09 2 1 2 1 2 2 1 2 1 2 2 1 2 2 1 2 2 2 1 2 2 2 2 2
06-NOV-09 2 1 2 2 2 2 1 2 2 2 2 2 1 2 2 1 0 2 0 0 0 0 1 0
09-NOV-09 0 0 1 7 0 0 1 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0