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 [...]

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 [...]

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 [...]

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 [...]

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 [...]