V_$SORT_USAGE equivalent structure or table in abap

[Former Member]

Hi Experts,

I want to access the contents of this oracle data dictionary view V_$SORT_USAGE for a particular tablespace through an abap program, so is there any structure used by a fm or some database tables or view which is equivalent to this view V_$SORT_USAGE.

Through db02 I was able to get the structure similar to DBA_TABLESPACES of oracle database, in similar way i want information for this view in bw database

Does anybody know about this information,

It will be of great use to me.

Thanks,

Hema.

[jayanthi_jayaraman]

Hi,

While searching for the view V_$SORT_USAGE,I got the following information.Is it possible to call the view from a ABAP report using Native SQL?[EXEC SQL...ENDEXEC]

For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.

All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.

If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT

From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:

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 s.osuser, s.process, s.username, s.serial#,

sum(u.blocks)*vp.value/1024 sort_size

from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

where s.saddr = u.session_addr

and vp.name = 'db_block_size'

and s.osuser like '&1'

group by s.osuser, s.process, s.username, s.serial#, vp.value

/

[Former Member]

Hi Jayanthi,

I tried using exec sql it doesn't work, may be have to use some structures and fm which has this V_$SORT_USAGE structure.

thanks,

Prabs