PL/SQL Kill Db connections

select max(ctime), sid from dbms_lock_allocated d,v$lock l
where d.lockid = l.id1 and ctime >30
group by sid

 
select
    s1.username || ‘@’ || s1.machine
    || ‘ ( SID,S#=’ || s1.sid || ‘,’ || s1.serial# || ‘ )  is blocking ‘
    || s2.username || ‘@’ || s2.machine
    || ‘ ( SID,S#=’ || s2.sid || ‘,’ || s2.serial# || ‘ )’
        AS blocking_status
from
    v$lock l1,
    v$session s1,
    v$lock l2,
    v$session s2
where
    s1.sid = l1.sid
    and s2.sid = l2.sid
    and l1.BLOCK = 1
    and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2;
select  ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’;’ from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >300);
column oracle_username format a15
column os_user_name format a15
column object_name format a37
column object_type format a37
select a.session_id,a.oracle_username, a.os_user_name, b.owner “OBJECT OWNER”, b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id
select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;
select
     username,
     v$lock.sid,
     trunc(id1/power(2,16)) rbs,
     bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,
     id2 seq,
     lmode,
     request
from v$lock, v$session
where v$lock.type = ‘TX’
and v$lock.sid = v$session.sid
and v$session.username = USER
/

 

 

Check free tablespace

select b.tablespace_name, tbs_size SizeMb, to_char(a.free_space/tbs_size * 100,’99.99′) || ‘%’ FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
column owner format A7
column bytes format 99999999999
break ON owner  ON report
compute SUM  OF bytes ON owner report
 
SELECT owner, table_name, NVL(num_rows*avg_row_len,0) bytes
FROM dba_tables
WHERE tablespace_name=’OWB_DATA’  and NVL(num_rows*avg_row_len,0) >10000
ORDER BY NVL(num_rows*avg_row_len,0) desc
/
select distinct(a.TABLESPACE_NAME) TABLESPACE_NAME,NVL(a.AUTOEXTENSIBLE,’NO’) AUTOEXTD,
(a.INCREMENT_BY*(select value from v$parameter where lower(name)=’db_block_size’))/1024/1024 NEXT_SIZE,
a.bytes/1024/1024 SIZE_MB,
nvl(a.MAXBYTES/1024/1024,’0′) MAXSIZE,a.file_id,
REPLACE(
REPLACE(a.file_name,’/etrade/pkgs/linux/intel/oracle’,’${ORACLE_BASE}’)
,’/etrade/pkgs/oracle’,’${ORACLE_BASE}’)
as DATA_FILE_NAME
from dba_data_files a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and a.tablespace_name like ‘SPOTFIRE_DATA_500′
order by NVL(a.AUTOEXTENSIBLE,’NO’);

for the shrink (which has datafile number listed) :

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/1024/1024 )) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and a.tablespace_name=’&tsname’
/

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/1024/1024 )) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and a.tablespace_name=’&tsname’
/

see if this helps:
alter table <tbl name> enable row movement;
alter table <tbl name> shrink space;

Db Performance check

1) Get the Process ID (PID) from TOP command which consume high CPU Usages.

So the query to get the session details (SID) from OS PID (SPID) will be as per

following.

select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = (PID) ;

3) Once we get the session ID, base on this information we can get the actual SQL

statement which is causing
HIGH CPU usage on database server.

We can use the following query to get the actual SQL STATEMENT.

SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ;

–# from below query you will findout sid:-

SELECT se.username, ss.sid, ROUND (value/100) “CPU Usage”
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE ‘%CPU used by this session%’
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like ‘%CPU used by this session%’
and
se.SID = ss.SID
and
ss.status=’ACTIVE’
and
ss.username is not null
order by VALUE desc;

select sql_hash_value, count(*) from v$session
where status = ‘ACTIVE’ group by sql_hash_value order by 2 desc;

select sql_text,users_executing from v$sql where hash_value = <give hash value here

which is out put of above query>;

PL/SQL Merge statement for data update

MERGE into Product_Sales  a

USING( SELECT distinct cusip,thirtydayaverage
        FROM vw_daily_volume
        WHERE vw_daily_volume.cusip in ( select distinct cusip from Product_Sales
                                              where process_date =( SELECT MAX(PROCESS_DATE) FROM Product_Sales)
                                             )
          AND created_ts = (select max(created_ts) from vw_daily_volume)
     )t1 
  ON (
      a.cusip = t1.cusip and
       a.process_date = (SELECT MAX(PROCESS_DATE) FROM Product_Sales)
  )
  WHEN matched then
  UPDATE set a.average_daily_volume = NVL(a.average_daily_volume,t1.thirtydayaverage);

Recover table from Oracle Flashback Feature

Data restore completed using ORACLE’s flashback feature.

=== Setps taken:
— FOr auditing Purpose
SQL> exec dbms_output.put_line(‘00021258 ‘);
PL/SQL procedure successfully completed.

— Retrive Data from Flashback history
SQL> create table Shema_name.table_name_ASOF as SELECT  *  FROM Shema_name.table_name AS OF TIMESTAMP TO_TIMESTAMP(‘2014-07-17 06:01:00’, ‘YYYY-MM-DD HH:MI:SS’);
Table created.

— Create backup of current table
SQL> create table Shema_name.table_name_BAKUP_0717 as SELECT  *  FROM Shema_name.table_name ;
Table created.

— Wipe out Bad data
SQL> truncate table  Shema_name.table_name;
Table truncated.

— Load data from Flashback
SQL> insert into  Shema_name.table_name select * from Shema_name.table_name_ASOF;
204973 rows created.

SQL> commit;
Commit complete.