SQL queries

SQL SCRIPTS
------------

1. Gather stats queries, restore optimizer stats
2. Scripts to check TEMP tablespace usage and identify session
3. Datafile shrink report
4. Delete archive logs in the last n days
5. Find peoplesoft application instance running on the server
6. Database size
7. Archive log generation in last 30 days
8. Change AWR snapshot intervals
9. Memory consumption per domain
10.Check tablespace usage
11.Audit Report for critical tables
12. Get table dependency



oracle database gather stats
1. 

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (); 
 
BEGIN  
DBMS_STATS.GATHER_SCHEMA_STATS(
 OwnName        => '&SCHEMA_NAME'
 ,estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
 ,degree    => DBMS_STATS.AUTO_DEGREE) ;
END;

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
  OwnName        => 'SYSADM'
  ,TabName        => '&TABLE_NAME'
  ,estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
  ,Degree            => DBMS_STATS.AUTO_DEGREE
  ,Cascade           => TRUE);
END;



--restore table stats
begin
dbms_stats.restore_table_stats( ownname=> '&SCHEMA_NAME', tabname=> '&TABLE_NAME', as_of_timestamp => to_timestamp_tz('2013-01-07 19:45:00 +5:30', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
end;


restore schema stats
begin
dbms_stats.restore_schema_stats( ownname=> '&SCHEMA_NAME', as_of_timestamp => to_timestamp_tz('2013-01-07 19:50:00 +5:30', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') );
end;


2. 
oracle database Temporary tablespace usage
--------------------------------------------------------------------------------

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total

--------------------------------------------------------------------------------

oracle database temp usage per session
--------------------------------------------------------------------------------

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
--AND      T.blocks * TBS.block_size / 1024 / 1024  > 5024
ORDER BY S.sid


3.
oracle database Datafile shrink report
----------------------
col stat for a110
select /*+rule*/ 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*8192)/1024/1024)||'m;' stat,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/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(+)
order by 4;


4.
delete archive logs of all instances running on the computer 
-----------------------------------------------------------
ps -ef | grep ora_pmon | grep -v grep | awk '{print $8}' | awk '{gsub(/ora_pmon_/,"export ORACLE_SID=");}1' | grep -v awk | awk '{print $0,"; echo \"delete noprompt archivelog until time \x27 sysdate - 10 \x27 ;\" | rman target /;";}'

5.
Find peoplesoft application instance running on the server
----------------------------------------------------------
ps -ef | grep PSWATCHSRV | grep 8.51 |awk '{print $18}' | awk 'BEGIN {FS = "/"}  ; {print $8} '

6.
Database size
--------------
select 
a.bytes "Data Files GB", 
b.bytes "Temp Files GB", 
c.bytes "Log Files GB", 
d.bytes "Archive Logfiles 20 Days", 
(a.bytes + b.bytes + c.bytes + d.bytes) "Total GB" 
from 
(select sum(bytes/1024/1024/1024) bytes from dba_data_files) a ,
(select sum(bytes/1024/1024/1024) bytes from dba_temp_files) b,
(select sum (bytes/1024/1024/1024) bytes from v$log) c,
(select sum(BLOCKS*BLOCK_SIZE/1024/1024/1024) bytes from v$archived_log where to_date(first_time) > sysdate - 20 ) d;

7.
Archive log generation in last 30 days
--------------------------------------
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
where to_date(first_time) > sysdate - 30
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/


archive generation for last 30 days
--------------------------------------------
set linesize 160
set pagesize 100
SELECT to_date(first_time) DAY , decode(thread#,1,'NODE_1',2,'NODE_2',3,'NODE_2'), count (*)
from
v$log_history
where to_date(first_time) > sysdate - 30 
and thread# in (select distinct thread# from v$log a) GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time), thread# order by to_date(first_time)  ;



8.
Change AWR snapshot intervals
-----------------------------
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200, --in minutes
    interval  => 60); --in minutes
END;
/


9. peoplesoft application Memory consumption per domain
-----------------------------------------
ps -o rss,args -ef | grep RHRPRD | awk '{ s += $1}END{print s/1024/1024}'


10.

oracle database Check tablespace usage
---------------------

select 
  a.tablespace_name, 
  round(sum(a.bytes/1024/1024),2) TOTAL_MB,   
  round(sum
  ( 
    ( case 
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB
from 
  dba_data_files a,
  (
    select file_id, 
      sum(bytes) bytes 
    from dba_free_space 
    group by file_id
  ) b
where 
  a.file_id = b.file_id(+)
group by a.tablespace_name 
order by 2 desc;


11. oracle database tables Audit Report sql query 
------------------------------------

select CLIENT_ID "User", OBJECT_NAME "TABLE",EXTENDED_TIMESTAMP "Time", decode (SES_ACTIONS,
'S---------------','ALTER',
'-S--------------','AUDIT',
'--S-------------','COMMENT',
'---S------------','DELETE',
'----S-----------','GRANT',
'-----S----------','INDEX',
'------S---------','INSERT',
'-------S--------','LOCK',
'--------S-------','RENAME',
'---------S------','SELECT',
'----------S-----','UPDATE',
'-----------S----','REFERENCES',
'------------S---','REFERENCES',
'-------------S--','REFERENCES') "Action",
sql_text,
sql_bind "Values",
decode(RETURNCODE,'0','Sucess','Fail') "Result" 
from DBA_COMMON_AUDIT_TRAIL 
where db_user = 'SYSADM' ;


12. Get oracle database table dependency


exec dbms_utility.get_dependency('TABLE', 'MSAT_SYSADM','TESTTAB'); 

select Query to view long running sql's

set linesize 160
set pagesize 100

col machine for a15

select
machine,
ELAPSED_SECONDS,
TIME_REMAINING,
100 - (TIME_REMAINING*100/ELAPSED_SECONDS),
OPNAME  ,
b.TARGET,
b.sid
from
v$session a,
v$session_longops b
where a.sid=b.sid and
time_remaining != 0 ;