Oracle DBAs - Scripts

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);


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, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY, C.block_size
) D
WHERE A.tablespace_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,
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;