Checking other user v$object_usage

Sometimes you don´t have the password and don´t want to change it (even for a tiny amount of time)

The solution is go straight to base tables

select u.name

, io.name

, t.name

, decode(bitand(i.flags, 65536), 0, ‘NO’, ‘YES’)

, decode(bitand(ou.flags, 1), 0, ‘NO’, ‘YES’)

, ou.start_monitoring

, ou.end_monitoring

from

sys.user$ u

, sys.obj$ io

, sys.obj$ t

, sys.ind$ i

, sys.object_usage ou

where

i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

and u.user# = io.owner#

 

Lack of reference about the original author.

Dealing with 4031

 

4031

 

First query – show the size of missing space

 

select sum(kghlunfu) Total_4031 ,kghlunfs Size_miss from x$kghlu group by kghlunfs

 

Show the possible query with problem

 

select substr(kglnaobj,1,30) sub, count(*) from x$kglob

 

group by substr(kglnaobj,1,30)

 

having count(*)>200

 

order by 1;

 

_kghdsidx_count >500M per _

 

Shows the number of subpools (default is 1)

 

select nam.ksppinm NAME, val.KSPPSTVL VALor

 

from x$ksppi nam, x$ksppsv val

 

where nam.indx = val.indx and

 

nam.ksppinm like ‘%kghdsidx%’ order by 1 ;

 

　

 

Shows the segmentation of shared pool

 

SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,

 

To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),’999,999.00′)||’k’ “AVG SIZE”

 

FROM X$KSMSP GROUP BY KSMCHCLS;

 

　

 

　

 

oradebug setmypid

 

oradebug unlimit

 

oradebug dump heapdump 536870914

 

oradebug dump library_cache 11

 

oradebug dump heapdump 2

 

oradebug tracefile_name

 

close_trace

 

　

 

　

 

SPOOL FRIDAY_31AUGU_1159PM_MEMINFO.out

 

select * from v$sgastat;

 

select ksmchcom ChunkComment, ksmchcls Status, sum(ksmchsiz) Bytes

 

from x$ksmsp group by ksmchcom, ksmchcls;

 

　

 

select KSMCHIDX “SubPool”, ‘sga

 

heap(‘||KSMCHIDX||’,0)’sga_heap,ksmchcom ChunkComment,

 

decode(round(ksmchsiz/1000),0,’0-1K’, 1,’1-2K’, 2,’2-3K’,3,’3-4K’,

 

4,’4-5K’,5,’5-6k’,6,’6-7k’,7,’7-8k’,8,

 

‘8-9k’, 9,’9-10k’,’> 10K’) “size”,

 

count(*),ksmchcls Status, sum(ksmchsiz) Bytes, max(ksmchsiz) Max_Bytes

 

from x$ksmsp

 

where KSMCHCOM = ‘free memory’

 

group by ksmchidx, ‘sga heap(‘||KSMCHIDX||’,0)’,ksmchcom,

 

ksmchcls,

 

decode(round(ksmchsiz/1000),0,’0-1K’, 1,’1-2K’, 2,’2-3K’,3,’3-4K’,

 

4,’4-5K’,5,’5-6k’,6,’6-7k’,7,’7-8k’,8,

 

‘8-9k’, 9,’9-10k’,’> 10K’);

 

　

 

SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,

 

To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),’999,999.00′)||’k’ “AVG

 

SIZE”

 

FROM X$KSMSP GROUP BY KSMCHCLS;

 

SPOOL OFF;

 

Save the spool files.

 

　

 

　

 

2. $ sqlplus /nolog

 

connect / as sysdba

 

Alter session set max_dump_file_size = unlimited

 

oradebug setmypid

 

oradebug unlimit

 

oradebug dump heapdump 2

 

oradebug tracefile_name <<< this command provides the full path and filename of the

 

trace file just generated >>>

 

exit

 

/dados/db/10.2.0/admin/BRMDEV_brmdev/udump/brmprj_ora_8023.trc

 

　

 

3. Close and open a new session .

 

sqlplus /nolog

 

connect / as sysdba

 

alter session set max_dump_file_size = unlimited

 

oradebug setmypid

 

oradebug unlimit

 

oradebug dump library_cache 11

 

oradebug tracefile_name <<< this command provides the full path and filename of the

 

trace file just generated >>>

 

exit

 

/dados/db/10.2.0/admin/BRMDEV_brmdev/udump/brmprj_ora_8629.trc

 

Save the trace files.

 

PROCEDURE MONITORAMENTO

 

CREATE OR REPLACE PROCEDURE GC_DBA_4031_PROD2 AS

 

qtd_errors number;

 

begin

 

select sum(total_4031) into qtd_errors from gc_dba_4031;

 

if qtd_errors>0 then

 

dbms_system.ksdwrt(2,’ORA-00600: URGENTE – 4031 iniciando em PRODUCAO02 BY HELIO DBA’);

 

end if;

 

END;

 

/

Temporary Tablespace usage

 

 

Temporary usage per session

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

 

　

Temporary space in use currently

 

select sum( u.blocks * blk.block_size)/1024/1024 “Mb. in sort segments” , (hwm.max * blk.block_size)/1024/1024 “Mb. High Water Mark” from v$sort_usage u, (select block_size from dba_tablespaces where contents = ‘TEMPORARY’) blk , (select segblk#+blocks max from v$sort_usage where segblk# = (select max(segblk#) from v$sort_usage) ) hwm group by hwm.max * blk.block_size/1024/1024;

 

　

 

Max space used

 

select tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) “GB in Use”,

 

round(FREE_BLOCKS*8/1024/1024,2) “Free GB”,round(MAX_SIZE/1024,2) “Msize GB”,

 

round(MAX_USED_BLOCKS*8/1024/1024,2) “Max used GB”

 

from v$sort_segment;

 

PS. Need update the source