Database Blog

Patch Error while appying 22502456 RDBMS Database Patch Set Update 11.2.0.4.160419

          Patch Error while appying 22502456  RDBMS Database Patch Set Update 11.2.0.4.160419 Error: ——— The patch directory area must be a number. ERROR: OPatch failed because…

Read More

Current Database Account

set termout off set head off set termout on select ‘User: ‘|| user || ‘ on database ‘ || global_name,        ‘(Terminal=’||USERENV(‘TERMINAL’)||        ‘, Session-Id=’||USERENV(‘SESSIONID’)||’)’ from…

Read More

Datafile Activity

SELECT d.name file_name, d.blocks, f.phywrts, f.phyrds, f.readtim, f.writetim, t.name tablespace_name, f.phywrts/d.blocks wrts_per_block, f.phyrds/d.blocks rds_per_block FROM v$filestat f, v$datafile d, sys.ts$ t WHERE f.file#=d.file# AND d.blocks >0 AND t.ts#=d.ts# UNION ALL…

Read More

buffer_busy_sample

column wait format a15 column address noprint column piece noprint break on wait select /*+ ordered */ distinct   w.wait,   t.address,   t.piece,   translate(t.sql_text, chr(13), ‘ ‘) sql_text…

Read More

how to check the database health checkup

spool tuning_stats.txt ttitle ‘SYSTEM STATISTICS’ select ‘LIBRARY CACHE STATISTICS:’ from dual; ttitle off select ‘PINS – # of times an item in the library cache was executed – ‘||  …

Read More

how to create sql tuning task

SET LONG 10000; SET PAGESIZE 9999 SET LINESIZE 155 set verify off col recommendations for a150 accept task_name –        prompt ‘Task_Name: ‘  DECLARE  ret_val VARCHAR2(4000); BEGIN ret_val…

Read More

How to find out the active sql details

break on sid skip 1 on serial# on username on executions on status column sid format 9999 column username format a12 column sql_text form a64 select a.sid,a.serial#,a.username,c.executions,status, b.sql_text from v$session…

Read More

Steps to know the rollback segments used by a user

-- ############################################################################################# -- -- %Purpose: Show which Users are accessing which Rollback Segments. -- -- It is sometimes useful to know which users are accessing the rollback segments. -- This…

Read More

STEPS TO ANALYZE TABLE LIST CHAINED ROWS

-- ############################################################################################# -- -- %Purpose: Show block chaining (chained rows) with ANALYZE TABLE LIST CHAINED ROWS -- -- Use: Needs Oracle DBA Access -- -- ############################################################################################# -- set echo off…

Read More

Steps to know the Execution Plan of a DML statment

-- ############################################################################################# -- -- %Purpose: Displays the execution plan for a SQL DML statement -- -- The SQL statement should be in a separate text file, -- with either a…

Read More