Oracle DBAs - Scripts

Sumit Kumar

How to check the tables which are modified since past N days?


SELECT table_owner, table_name, inserts, updates, deletes, timestamp, truncated FROM sys.dba_tab_modifications where timestamp >= (select sysdate-3 from dual);

How to Gather stats manually?


exec dbms_stats.gather_table_stats(ownname=>'SCHEMA',tabname=>'TABLE-NAME',estimate_percent=>20,cascade=>TRUE);

OR

EXEC DBMS_STATS. gather_schema_stats('SCHEMA', estimate_percent => 15)

select owner,table_name, last_analyzed from dba_tables where table_name like 'TABLE-NAME%';

How to Check TEMP Total/USAGE/FREE ?


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;

How to Check who is using TEMP and how much ?


set lines 300
col b.tablespace format a10
col a.username format a15
col SIZE format a8
col SID_SERIAL format a10
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;