Old but still valuable
Using the shared pool advisor in 9.2+. The v$shared_pool_advice view.
To make use of new feature of Oracle 9.2.x i.e. Shared Pool Advisory. This advisory advices on about library cache memory and predicts the effect of altering shared pool size on the total amount parsing activities in the system. + When it comes for parsing, if the appropiate memory is not allocated, one could land-up […]
COMMON Oracle Database Recoveries
1. Loss of data file(s) of SYSTEM tablespace Solution: SQL> SHUTDOWN IMMEDIATE; SQL> host cp backupfile file (Restore your data backup files from backup to related loss system’s datafiles) SQL> CONNECT / AS SYSDBA SQL> STARTUP MOUNT SQL> RECOVER DATABASE; SQL> ALTER DATABASE OPEN; 2. Loss of data file(s) of Non-System tablespace. Solution: >Don’t need […]
Add / Delete from dba_jobs
You should not delete from dba_jobs; you should use the dbms_job interface to remove the job. Note 61730.1 is a good overview of this interface to the job queue. I also put together a quick example of submitting and deleting a job: SQL> variable v_jobnum number; SQL> exec DBMS_JOB.SUBMIT (:v_jobnum, ‘DBMS_UTILITY.ANALYZE_SCHEMA(”SYS”,”COMPUTE”);’, tr unc(sysdate) + 1, […]
Script to Show System and Object Privs for a User
SET ECHO off REM NAME: TFSSYPRV.SQL REM USAGE:”@path/tfssyprv” REM ————————————————————————– REM REQUIREMENTS: REM SELECT ANY TABLE REM ————————————————————————– REM AUTHOR: REM Geert De Paep REM ————————————————————————– REM PURPOSE: REM Show the SYSTEM privileges a certain user has. REM ————————————————————————— REM EXAMPLE: REM SYSTEM PRIVILEGES REM MARTY REM CONNECT ALTER SESSION REM CREATE CLUSTER REM CREATE […]
32-bit? 64-bit?
swlist -l fileset | grep -i bit HP getconf KERNEL_BITS (show 32-bit support) Also try: isainfo -v Is my Operating System 64-bit? In Solaris, from the command line (you don’t have to be root in most cases) run this command: /usr/bin/isainfo -kv If your OS is 64-bit, you will see output like: 64-bit sparcv9 kernel […]
LIBRARY CACHE HIT RATIO
The Library Cache Hit Ratio reflects the percentage of time an object such as a package, procedure, or function was in memory. The value should be very high and around 99%. To identify the Library Cache Hit Ratio execute the following: SELECT SUM(pins-reloads)/SUM(pins)*100 “Library Cache Hit Ratio” FROM v$librarycache If any of the hit ratios […]
DB CACHE HIT RATIO
The DB Cache Hit Ratio is important as it reflects the percentage of time the data was found in memory without the need to go to disk. The ratio should be greater than 90% for OLTP type applications. A low buffer hit ratio implies that the excessive CPU is being spent on filling database buffers. […]
SYSTEM EVENTS
A crude check to determine how the Oracle instance is behaving overall can be found in the v$system_event view as shown below. SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM v$system_event The statistics in this view are cumulative and are representative from the time the instance was started. As a result, some of the statistics may […]
check_free.sql
rem This script give tablespace level statisitcs rem Note that if a tablespace has multiple datafiles this rem script DOES not display the free space for the rem tablespace. Instead, it displays at datafile level rem There is also another caveat, if a datafile has rem autoextend turned on then, this script does not account […]