Daily Usefull Oracle Database scripts

************Db  status script *****************************

SQL>select 'Hostname      : ' || host_name

      ,'Instance Name : ' || instance_name

      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime

      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||

       trunc( 24*((sysdate-startup_time) -

       trunc(sysdate-startup_time))) || ' hour(s) ' ||

       mod(trunc(1440*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||

       mod(trunc(86400*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' seconds' uptime

from sys.v_$instance;

********For RAC server use the below query************************

select 'Hostname      : ' || host_name

      ,'Instance Name : ' || instance_name

      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime

      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||

       trunc( 24*((sysdate-startup_time) -

       trunc(sysdate-startup_time))) || ' hour(s) ' ||

       mod(trunc(1440*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||

       mod(trunc(86400*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' seconds' uptime

from sys.gv_$instance;

                                      (or)

SQL> select to_char(start_time, ‘dd-mon-yy hh24:mi:ss’) start_time,

 to_char(end_time,‘dd-mon-yy hh24:mi:ss’) end_time

 from stats$dates;

************Which schemas  taking up all of the free space************

set pages 999

col "size MB" format 999,999,999

col "Objects" format 999,999,999

select      obj.owner "Owner"

,              obj_cnt "Objects"

,              decode(seg_size, NULL, 0, seg_size) "size MB"

from        (select owner, count(*) obj_cnt from dba_objects group by owner) obj

,              (select owner, ceil(sum(bytes)/1024/1024) seg_size

               from dba_segments group by owner) seg

where      obj.owner  = seg.owner(+)

order       by 3 desc ,2 desc, 1;

To check which tables are accessing frequently on the database********

 set lines 80;

set pages 999;

column avg_touches           format 999

column myname heading 'Name' format a30

column mytype heading 'Type' format a10

column buffers               format 999,999

SELECT  object_type mytype,object_name myname,blocks,COUNT(1) buffers,AVG(tch) avg_touches

FROM sys.x$bh a,dba_objects b,dba_segments s WHERE a.obj = b.data_object_id

and b.object_name = s.segment_name and b.owner not in ('SYS','SYSTEM')

GROUP BY   object_name,object_type,blocks,obj HAVING AVG(tch) > 5 AND  COUNT(1) > 20;

*****Rman backup job status************************************

col STATUS format a9

col hrs format 999.99

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,

elapsed_seconds/3600                   hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,   

       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"  

FROM V$SESSION_LONGOPS   

WHERE OPNAME LIKE 'RMAN%'  

  AND OPNAME NOT LIKE '%aggregate%'  

  AND TOTALWORK != 0   

  AND SOFAR  != TOTALWORK;  

**To find out the table size****************************************

SQL> select segment_type, sum(bytes/1024/1024) size_mb from dba_segments  where OWNER ='V500' and SEGMENT_NAME='CR_REPORT_STYLE_PROFILE' group by segment_type;

*****Checked and found no segment could not extend****************

 SELECT s.owner, s.segment_type, s.segment_name,s.tablespace_name ,s.next_extent

       FROM dba_segments s

      WHERE s.next_extent * ( 1 + s.pct_increase / 100 ) >

      (SELECT MAX( f.bytes ) FROM dba_free_space f

         WHERE f.tablespace_name = s.tablespace_name );

no rows selected

***Displaying Segment Information********************************

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX'     AND OWNER='ARADMIN' AND SEGMENT_NAME='T171_PERF_2';

***Query to check the tablespace usage metrics*******

SQL> select * from dba_tablespace_usage_metrics order by used_percent desc;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

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

D_R_MEDIUM                         251784          286336   87.9330577

D_R_SMALL                          375408          438016   85.7064582

L_R_SMALL                          788560          933760   84.4499657

*****Here is a simple way to calculate the average number of transactions per second since database startup

select round(sum(s.value / (86400 * (SYSDATE - startup_time))),3) "TPS" from v$sysstat s ,v$instance i where s.NAME in ('user commits','transaction rollbacks');

*****script gives me the database growth as follows**************

select to_char(creation_time, 'RRRR Month') "MONTH",

sum(bytes)/1024/1024 "GROWTH IN MB"

from sys.v_$datafile

where creation_time >= SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

MONTH GROWTH IN MB;

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB

 from   v$datafile group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM') order by   1, 2;

how many  datafiles were added in month********

select 

  to_char(CREATION_TIME,'RRRR') year,

  to_char(CREATION_TIME,'MM') month,

  sum(bytes/1024/1024)  size_mb

 from    v$datafile

 group by

 to_char(CREATION_TIME,'RRRR'),

 to_char(CREATION_TIME,'MM')

 order by 

   1, 2;

This script will show "spaced used total" for a specific Oracle table, essentiall computing the Oracle table size over time************************

 col c1 format a15 heading 'snapshot|date' 

col c2 format a25 heading 'table|name' 

col c3 format 999,999,999 heading 'space|used|total'

select 

   to_char(begin_interval_time,'yy/mm/dd hh24:mm')     c1, 

   object_name c2,space_used_total c3 from 

   dba_hist_seg_stat       s, 

   dba_hist_seg_stat_obj   o, 

   dba_hist_snapshot       sn 

where    o.owner = 'V500' and    s.obj# = o.obj# and    sn.snap_id = s.snap_id and    object_name='CR_REPORT_STYLE_PROFILE' 

order by begin_interval_time;

****User quotas on tablespaces*********************************

select username

,      tablespace_name

,      decode(max_bytes, -1, 'unlimited'

       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"

from   dba_ts_quotas

where  tablespace_name not in ('TEMP');

************ Checking Active and Inactive session script **************

SQL>select sid,serial#,LOCKWAIT,STATUS,username from v$session;

SQL>select USERNAME,OSUSER,PROGRAM,LOGON_TIME from  v$session where status in ('ACTIVE');

SQL>select SID,SERIAL#,USERNAME,LOCKWAIT,STATUS,PROGRAM,LOGON_TIME from  v$session where USERNAME='XXXX' and STATUS='ACTIVE';

SQL>select USERNAME,OSUSER,PROGRAM,LOGON_TIME from  v$session where status in ('ACTIVE');

1

************To Chk is sorting is going on **************************

SQL>Select tablespace_name,USED_BLOCKS,FREE_BLOCKS,MAX_SORT_BLOCKS from   v$sort_segment;

SQL> Select   EXTENT_SIZE,CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS  from v$sort_segment;

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM   V$temp_space_header    GROUP  BY tablespace_name;

SQL>select FILE_NAME,BYTES/1024/1024 from dba_temp_files where 

TABLESPACE_NAME='TEMP';

SQL> ALTER TABLESPACE <tablespace_name>   ADD  TEMPFILE < tempfile name > SIZE <Est_mb>m                              

                                                      OR 

SQL>ALTER DATABAS TEMPFILE <tempfile name>    RESIZE  < Current_size + Est_mb>m;

**** dead locks script***************

SQL>select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

************* List locks script************************************

column lock_type format a12

column mode_held format a10

column mode_requested format a10

column blocking_others format a20

column username format a10

SQL>SELECT session_id

, lock_type

, mode_held

, mode_requested

, blocking_others

, lock_id1

FROM dba_lock l

WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread');

************ Show locked objects script***************************

set lines 100 pages 999

col username format a20

col sess_id format a10

col object format a25

col mode_held format a10

SQL>select oracle_username || ' (' || s.osuser || ')' username

, s.sid || ',' || s.serial# sess_id

, owner || '.' || object_name object

, object_type

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global') status

, decode(v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive', TO_CHAR(lmode)) mode_held

from v$locked_object v

, dba_objects d

, v$lock l

,v$session s

where v.object_id = d.object_id

and v.object_id = l.id1

and v.session_id = s.sid

order by oracle_username

, session_id;

************Show which row is locked script***********************

SQL>select do.object_name

, row_wait_obj#

, row_wait_file#

, row_wait_block#

, row_wait_row#

, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 

ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)

from v$session s

, dba_objects do

where sid=&sid

and s.ROW_WAIT_OBJ# = do.OBJECT_ID;

*********Check Running jobs status script***************************

SQL>SELECT job,schema_user, to_char(last_date, 'mm/dd/yyyy hh:mi pm') lastdate, to_char(next_date, 'mm/dd/yyyy hh:mi pm') nextdate, failures, broken, what  FROM dba_jobs  ORDER BY next_date;

                                Or

SELECT 

 'Job:'|| job,

 WHAT,

 'Next:'|| to_char(NEXT_DATE,'dd-Mon-yyyy HH24:MI'),

 ' Last:'|| to_char(LAST_DATE,'dd-Mon-yyyy HH24:MI'),

 ' Broken:'|| BROKEN

 FROM dba_jobs;

                               Or

SQL>select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,BROKEN,FAILURES,TOTAL_TIME from user_jobs;

Create a DBMS_STATS.gather_schema_stats job that runs daily at 11pm (23:00) ******************

 Set SERVEROUT ON

 Set pagesize 200

 

 DECLARE

 jobno NUMBER;

 BEGIN

 DBMS_JOB.submit

 (job => jobno,

 what => 'DBMS_STATS.gather_schema_stats(ownname => ''scott'', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);',

 next_date => trunc(sysdate)+23/24,

 interval => 'SYSDATE + 1',

 no_parse => TRUE );

 

 DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno));

 COMMIT;

 END;

 /

******To detect Oracle long running queries script*******************

SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;

***To detect long operations**************************************

select sid,username,opname,sql_address,

        to_char(start_time ,'mm/dd/yyyy hh24:mi:ss') as sttime,

        to_char(last_update_time,'mm/dd/yyyy hh24:mi:ss') as luptime,

        to_char(time_remaining,'9999999999') as timeremain,

        to_char(elapsed_seconds,'999999999') as elapstime

from v$session_longops a

join v$sqltext_with_newlines b on b.address=a.sql_address

where  (last_update_time - start_time) >= (1/24/4)

order by sid,opname,a.sql_address,last_update_time desc;

**************To Show all running SQLs in the Db**************

SQL>select distinct spid, s.sid, s.serial#,to_char(sysdate -

last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",

       logon_time,

       osuser,

       s.program,

       schemaname,

       sql_text

  from v$session s,

       v$process p,

       v$sql t

  where s.paddr=p.addr

    and t.hash_value(+)=s.sql_hash_value

    and s.type !='BACKGROUND';

Query to check which user process has occupied high memory usage*****

SQL> SELECT spid, program,

            pga_max_mem      max,

            pga_alloc_mem    alloc,

            pga_used_mem     used,

            pga_freeable_mem free

       FROM V$PROCESS

      WHERE spid = 471102;

***what the session is doing and how much CPU they have consumed******

SQL>select ss.sid,se.command,ss.value CPU ,se.username,se.program 

from v$sesstat ss, v$session se

where ss.statistic# in 

(select statistic# 

from v$statname 

where name = 'CPU used by this session')

and se.sid=ss.sid 

and ss.sid>6

order by ss.sid;

*****Users and Sessions CPU consumption can be obtained by below query*****

Set lines 1000

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;

 shows Day wise,User wise,Process id of server wise- CPU and I/O consumption****

set linesize 140

col spid for a6

col program for a35 trunc

select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,

ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,

round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day

from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg

where s.paddr=p.addr and ss.sid=s.sid

and ss.statistic#=12 and si.sid=s.sid

and bg.paddr(+)=p.addr

and round((ss.value/100),0) > 10

order by 8;

*****To find out what sql  session(s) are executing******

SQL>select s.sid, event, wait_time, w.seq#, q.sql_text

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr=p.addr and

s.sid=&p and s.sql_address=q.address;

                       or

SELECT SQL_TEXT

FROM V$SQLAREA

WHERE (ADDRESS, HASH_VALUE) IN

      (SELECT SQL_ADDRESS, SQL_HASH_VALUE

       FROM V$SESSION

       WHERE SID = &sid_number);

               or

SQL>SELECT sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%online discount%' AND sql_text NOT LIKE '%v$sql%';

****To check username,osuser,terminal,sid,serial# connected to the Database*****

SELECT USERNAME, OSUSER, TERMINAL, SID, SERIAL#,

       PROCESS, PROGRAM FROM V$SESSION ORDER BY OSUSER;

               

*****Show all connected users*********************

set lines 100 pages 999

col ID format a15

select username

,      sid || ',' || serial# "ID"

,      status

,      last_call_et "Last Activity"

from   v$session

where  username is not null

order by status desc

,        last_call_et desc;

*****Time since last user activity*********************

set lines 100 pages 999

select username

,      floor(last_call_et / 60) "Minutes"

,      status

from   v$session

where  username is not null

order by last_call_et;

******Show tables that have analyze dates older than today**********

select     count(last_analyzed) left_to_do

from      dba_tables

where    owner = '&schema'

and       trunc(last_analyzed) < trunc(sysdate)

order     by 1;

                                   OR

SELECT OWNER,TABLE_NAME, NUM_ROWS,LAST_ANALYZED,DURATION FROM DBA_TABLES WHERE OWNER='ARADMIN' AND LAST_ANALYZED < (SYSDATE -7) AND NUM_ROWS >100

                                  OR

select OWNER,TABLE_NAME,LAST_ANALYZED,NUM_ROWS,STATUS from dba_tables where OWNER=’ARADMIN’ order by LAST_ANALYZED;

Enjoy the work and do like me if you like this article!!!

Queries related to TEMP Tablespace

To check instance-wise total allocated, total used TEMP for both rac and non-rac

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;

Total Used and Total Free Blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

Another Query to check TEMP USAGE

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';
To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace

col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024
size_m
, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#);

Additional checks

select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Changing the default temporary Tablespace

SQL> alter database default temporary tablespace TEMP;

Database altered.

To add tempfile to Temp Tablespace

alter tablespace  temp  add tempfile '&tempfilepath' size 1800M;

alter tablespace temp add tempfile '/m001/oradata/SID/temp02.dbf' size 1000m;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp04.dbf' size 1800M autoextend on maxsize 1800M;

To resize the  tempfile in Temp Tablespace

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M

alter database tempfile '/SID/oradata/data02/temp12.dbf' autoextend on maxsize 1800M;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp05.dbf' size 1800m reuse;

To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block"
from v$sort_usage
group by username;

To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'

To find  Total Space Allocated for Temp Tablespace

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB'  from dba_temp_files where tablespace_name='TEMP';

Get 10 sessions with largest temp usage

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Displays the amount of IO for each tempfile

SELECT SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd AS blocks_read,
f.phyblkwrt AS blocks_written,
f.phyblkrd + f.phyblkwrt AS total_io
FROM   v$tempstat f,v$tempfile t
WHERE  t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id  order by MB DESC) a where rownum<10;

Check for ORA-1652

show parameter background

cd <background dump destination>

ls -ltr|tail

view <alert log file name>

shift + G ---> to get the tail end...

?ORA-1652 ---- to search of the error...

shift + N ---- to step for next reported error...

I used these queries to check some settings:

-- List all database files and their tablespaces:
select  file_name, tablespace_name, status
,bytes   /1000000  as MB
,maxbytes/1000000  as MB_max
from dba_data_files ;

-- What temporary tablespace is each user using?:
select username, temporary_tablespace, default_tablespace from dba_users ;

-- List all tablespaces and some settings:
select tablespace_name, status, contents, extent_management
from dba_tablespaces ;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN STATUS
------------------------------ --------- ---------- ---------
SYSTEM                         PERMANENT DICTIONARY ONLINE
TOOLS                          PERMANENT DICTIONARY ONLINE
TEMP                           TEMPORARY DICTIONARY OFFLINE
TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:
select count(*)  from dba_all_tables
where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get
-- NO rows for this:
column owner        format a20
column object_type  format a30
column object_name  format a40
select
o.owner  ,o.object_name
,o.object_type
from sys_objects s
,dba_objects o
,dba_data_files df
where df.file_id = s.header_file
and o.object_id = s.object_id
and df.tablespace_name = 'TEMP' ;

Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;

Enjoy the work and do like me if you like this article!!!.

To check the logical corruption in oracle database

Step A:
$  rman target / catalog rman/rman@xxxxx log=corruption_check_db.log
RMAN>run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup validate check logical database;
}

RMAN>exit;

Step B: Once the validation completes, please query database using below statements.
set pagesize 20000
set linesize 200
col owner format a20
col segment_name format a20
col segment_type format a20
col partition_name format a20
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 99999999999999
col segment_name format a30
Spool corrupt_segments.lst
select systimestamp from dual;
select db_unique_name,database_role from v$database;
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

Step C:
select * from v$database_block_corruption;

select FILE#, BLOCK#, BLOCKS, to_char(NONLOGGED_START_CHANGE#, '999999999999999') NONLOGGED_START_CHANGE#
from v$nonlogged_block;

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#

order by file#, corr_start_block#;

Enjoy the work and do like me if you like this arcticle!!!