**************************************

Memory usage for backgroung processes

**************************************

SELECT p.program,

p.spid,

pm.category,

pm.allocated,

pm.used,

pm.max_allocated

FROM V$PROCESS p, V$PROCESS_MEMORY pm

WHERE p.pid = pm.pid

AND p.spid = 2587;

*********************************************

PGA Memory allocation to background process

*********************************************

SELECT spid, program,

pga_max_mem max,

pga_alloc_mem alloc,

pga_used_mem used,

pga_freeable_mem free

FROM V$PROCESS

WHERE spid = 2587;

*********************************

To know used blocks information

*********************************

set serveroutput on

declare

TOTAL_BLOCKS number;

TOTAL_BYTES number;

UNUSED_BLOCKS number;

UNUSED_BYTES number;

LAST_USED_EXTENT_FILE_ID number;

LAST_USED_EXTENT_BLOCK_ID number;

LAST_USED_BLOCK number;

begin dbms_space.unused_space(‘SYSTEM’, ‘TEST’, ‘TABLE’,

TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,

LAST_USED_EXTxENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,

LAST_USED_BLOCK);

dbms_output.put_line(‘OBJECT_NAME = CUSTOMER’);

dbms_output.put_line(‘———————-’);

dbms_output.put_line(‘TOTAL_BLOCKS = ||TOTAL_BLOCKS);

dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);

end;

/

********************************

Keep and Recycle cache advices

********************************

SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS

FROM V$DB_CACHE_ADVICE

WHERE NAME = ‘KEEP’

AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’)

AND ADVICE_STATUS = ‘ON’;

SELECT ds.BUFFER_POOL,

Substr(do.object_name,1,9) object_name,

ds.blocks object_blocks,

Count(* ) cached_blocks

FROM dba_objects do,

dba_segments ds,

v$bh v

WHERE do.data_object_id = v.objd

AND do.owner = ds.owner (+)

AND do.object_name = ds.segment_name (+)

AND do.object_type = ds.segment_type (+)

AND ds.BUFFER_POOL IN (‘KEEP’,'RECYCLE’)

GROUP BY ds.BUFFER_POOL,

do.object_name,

ds.blocks

ORDER BY do.object_name,

ds.BUFFER_POOL;

Many a times we would be facing problem that “temp tablespace is out of space” and even after resizing or adding tempfile, still problem will exist. We really want to know who is consuming the space. The below queries will help you in identifying the culprit….

####################################
FINDING TEMP USAGE
###################################
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
 FROM   V$temp_space_header
 GROUP  BY tablespace_name;

===Utilization by User:

select s.username, u.”USER”, u.tablespace, u.contents, u.extents, u.blocks
        from   sys.v_$session s, sys.v_$sort_usage u
        where  s.saddr = u.session_addr;
SELECT distinct p.NAME,
       s.VALUE
FROM   v$sessTat s,
       v$sTatName p
WHERE  s.sTatIsTic# = p.sTatIsTic#
       AND p.NAME IN (‘session uga memory’,
                      ‘session uga memory max’)
       AND s.sId IN (SELECT sId
                     FROM   v$Session
                     WHERE  UserName = ‘&USERNAME’);