Ask TEM Dba

I temporarily reduced db_recovery_file_dest_size to force the database to free reclaimable space from recovery_file_dest. This reduced disk usage of ASM_ORADATA217 by over 320GB. SYS>show parameter recovery NAME [...]

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which[...]

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied[...]

This query provides a list of queries (1,000 characters only) involved in full table scans. The total number of rows and blocks for each table is also displayed so that you can determine whether the full scan is degradin[...]

This script displays instance background process information. The script works when the database is MOUNTed or OPENed. -- Oracle 8I select A.SID, A.SERIAL#, A.PROGRAM, P.PID, [...][...]

This script generates a spool file that can be run from a SQL*Plus session to list the differences between 2 tables with identical structure. This can be useful if 2 similar tables need to be compared across different[...]

I found an interesting script that I used sometime back to compare the structure of 2 tables. Thought I should preserve and blog it for the benefit of all. — SCRIPT START — Rem script name – compare_tab[...]

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view: SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_hea[...]

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or S[...]

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax. First identify the offending session as follows: SELECT s.sid, s.serial#, s.osuser, s.program FROM v$session s[...]