Monthly Archives: October 2013

Statspack Reports

/* When installing the Statspack package, you can either change to the ORACLE_HOME/rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin DIRECTORY WHEN calling the installation script, SPCREATE.SQL. */ — install statspack @?/rdbms/ADMIN/spcreate /* Enter appropriate information when prompted for the PERFSTAT user’s password, default tablespace, and temporary tablespace. The SPCREATE.SQL install script runs the following scripts […]


set echo off set head off set heading off set pagesize 0 set linesize 500 set verify off set trimspool on set feedback off ACCEPT T1 Prompt ‘Tablespace Name >>> ‘ ACCEPT T2 Prompt ‘Database Name   >>> ‘ spool &t1..sql select ‘alter tablespace &t1 read only;’ from dual ; SELECT ‘host cp -ip ‘||file_name||’  &t3/oradata/&t2/’||SUBSTR(file_name,instr(file_name,’/’,-1)+1,40) […]

enqueue lock info

select /*+ rule */  * from v$lock  where sid in ( select s.sid from v$session_wait w , v$session  s where w.sid=s.sid and s.status=’ACTIVE’ and w.event = ‘enqueue’ and s.type =’USER’)

Lock Holders and waiters

  SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,          id1, id2, lmode, request, type     FROM V$LOCK    WHERE (id1, id2, type) IN              (SELECT id1, id2, type FROM V$LOCK WHERE request>0)    ORDER BY id1, request

Top IO session

/* create table */ INSERT INTO  t1 /* AS */  SELECT s.sid,s.value FROM v$sesstat s, v$statname n WHERE s.statistic# = n.statistic# AND = ‘physical reads’ ORDER BY value DESC; exec dbms_lock.sleep(20); /* CREATE TABLE */ INSERT INTO  t2 /* AS */ SELECT s.sid,s.value FROM v$sesstat s, v$statname n WHERE s.statistic# = n.statistic# AND […]

Tuning logical IO

There are two main types of IO, logical IO and physical IO. We prefer logical IO because instance reads data from memory called logical IO which is much more faster than physical IO which reads data from hard disk. To lower the physical IO, we use db_buffer_cache parameter which assigns some part of the memory for caching hard disk […]

Check Standby Database

/* monitor Process activities */ SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY; /* Determining Which Logs Were Applied to the Standby Database */ SELECT THREAD#, MAX(SEQUENCE#) AS “LAST_APPLIED_LOG” FROM V$LOG_HISTORY GROUP BY THREAD#; /* Determining Which Logs Were Applied to the Standby Database of the last 100 – Standby */ select * from ( SELECT […]

Undo – Rollback Segment usage

select substr(a.os_user_name, 1, 8) “OS User”, substr(a.oracle_username, 1, 8) “DB User”, substr(b.owner, 1, 8) “Schema”, substr(b.object_name, 1, 20) “Object Name”, substr(b.object_type, 1, 10) “Type”, c.segment_id “RBS#”, to_number(substr(d.used_urec, 1, 12)) “# of Records” from v$locked_object a, dba_objects b, dba_rollback_segs c, v$transaction d, v$session e where a.object_id = b.object_id and a.xidusn = c.segment_id and a.xidusn = d.xidusn […]

Availability Report

set serveroutput on set echo off set feedback off exec availabilityreport(2013,10,’ORACLE’,’PARTIAL’) set linesize 1000 set pagesize 2000 select substr(a.systemname,1,10) systemname ,to_char(a.startdate,’dd-mm-yy hh24:mi’)||’ -‘ Startdate ,to_char(a.enddate,’hh24:mi’) endtime , trunc(a.durationsec/60,2) dur_min, substr(b.exp,1,20) ,decode (a.restart,’Y’,’Restart DB ‘,’ ‘) Restart , a.explanation from system_status_log a , reasontypecode b where a.systemname in ( select systemname from SYSTEM_STATUS_LOG_GROUP where groupname=’ORACLE’ ) […]

Oracle Linux uzerinde Oracle 11g database ( ) qurulmasi

Ilk once yukleyeceyimiz DB leri oz localimiza endiririk. # unzip unzip # unzip unzip # unzip unzip Linux uzerinde Oracle_krulum deye bir folder olushdururuq  ve lazim olan Oracle .zip fayli ora kopyalyiriq. [oracle@bakudcsprddb database]$ mkdir oracle_krulum/ [oracle@bakudcsprddb database]$ cd / [oracle@bakudcsprddb /]$ cd oracle_kurulum/ [oracle@bakudcsprddb oracle_kur]$ pwd /oracle_kurulum […]

How to stop Firewall in Linux machine

1 step: Check this command: #service iptables status 2 step: Check this command:  #service ip6tables status 3  step: Start this command : #chkconfig iptables off #service iptables stop #chkconfig ip6tables off #service ip6tables stop Thank you 😉

Tuning Shared Pool & Tuning Large Pool

Shared pool includes cursors, sql statements, plsql blocks which are parsed/executed and stored/cached in memory. It is important to optimize the plsql blocks and/or sql commands so sessions can use the same commands without parsing but only executing the statements. This improves the application response times. Hints to optimize your sql code will be given in the following sections. There […]

How can Inistall package for Oracle Database

[root@bakudcsprddb Packages]# yum list | more Loaded plugins: refresh-packagekit, rhnplugin This system is not registered with ULN. ULN support will be disabled. Installed Packages ConsoleKit.x86_64                  0.4.1-3.el6                         @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 ConsoleKit-libs.x86_64             0.4.1-3.el6                         @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 ConsoleKit-x11.x86_64              0.4.1-3.el6                         @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 DeviceKit-power.x86_64             014-1.el6                           @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 GConf2.x86_64                      2.28.0-6.el6                        @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 GConf2-gtk.x86_64                  2.28.0-6.el6                        @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 MAKEDEV.x86_64                     3.24-6.el6                          @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 ModemManager.x86_64                0.4.0-3.git20100628.el6             @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 NetworkManager.x86_64              1:0.8.1-5.el6                       @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 NetworkManager-glib.x86_64         1:0.8.1-5.el6                       @anaconda-OracleLinuxServer-201102031546.x86_64/6.0 NetworkManager-gnome.x86_64        […]


set lines 100 set pages 999 col username     format a20 col sess_id          format a10 col object            format a25 col mode_held  format a10 select    oracle_username || ‘ (‘ || s.osuser || ‘)’ username ,               s.sid || ‘,’ || s.serial# sess_id ,               owner || ‘.’ ||   object_name object ,               object_type ,               decode(               l.block                 ,               […]

Tablespace usage

–set pages 999 –col tablespace_name format a40 –col “size MB” format 999,999,999 –col “free MB” format 99,999,999 –col “% Used” format 999 select    tsu.tablespace_name, ceil(tsu.used_mb) “size MB” ,               decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) “free MB” ,               decode(100 – ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,                100 – ceil(tsf.free_mb/tsu.used_mb*100)) “% used” from      (select tablespace_name, sum(bytes)/1024/1024 used_mb                 from      dba_data_files group […]


SELECT a.sql_id,a.sample_time ,a.session_id, a.event,a.module, s.sql_text –program FROM   V$ACTIVE_SESSION_HISTORY a  ,dba_hist_sqltext s where a.sql_id=s.sql_id and a.user_id <> 0 —  exclude SYS user and a.sample_time  >  sysdate – 7/24 — and a.sql_id=’6wk5bs5a6t9n’

How to change RAC NoarchiveLog Mod to Archivelog mode

—————————————————————————— Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance: —————————————————————————— $ sqlplus “/ as sysdba” SQL> alter system set cluster_database=false scope=spfile sid=’orcl1′; Shutdown all instances accessing the clustered database: $ srvctl stop database -d orcl Using the local instance, MOUNT […]

ORA-01591 INDOUBT tran transaction purge

set feedback off;                                                                                    set linesize 80 set pagesize 0 spool rollback_force.sql select ‘rollback force ”’ ||local_tran_id ||”’;’ from dba_2pc_pending; spool off exit set feedback off;                                                                                    set linesize 80 set pagesize 0 spool purge.sql select ‘alter session set “_smu_debug_mode” = 4;’ from dual; select ‘exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(”’ ||local_tran_id ||”’);’ || chr(10) || ‘commit;’ from dba_2pc_pending; spool off […]


bash-3.00$ cd $ORACLE_HOME bash-3.00$ cd deinstall/ bash-3.00$ ls     jlib             response deinstall        deinstall.xml    readme.txt bash-3.00$ ./deinstall Checking for required files and bootstrapping … Please wait …

Full table scan

select sysdate opdate ,g.sid sid ,g.hedef table_name , s.USERNAME username ,s.machine machine , s.module module ,a.hash_value hash_value ,a.sql_text sql_text  from get_sure g ,v$session s,v$sqlarea a,v$sql_plan p where s.sid=g.SID and s.SQL_HASH_VALUE=a.HASH_VALUE and  p.operation = ‘TABLE ACCESS’ and p.OPTIONS=’FULL’ and  p.hash_value=a.HASH_VALUE order by hash_value;