-- 11g and Higher

ALTER SESSION SET PLSCOPE_SETTINGS='identifiers:all' 
/

SELECT --*
 name, type, usage, line
FROM  user_identifiers
WHERE  object_type = 'PACKAGE BODY'
ORDER BY  name, type, line
;

SELECT  resource_name, current_utilization, max_utilization, limit_value 
FROM  v$resource_limit
;

SELECT db_link, owner_id  FROM v$dblink
;

-- Session Wait

SELECT * FROM v$session_wait  ORDER BY sid, seq#
;

SELECT * FROM v$session_wait 
WHERE event NOT IN (
  'pmon timer'
 ,'smon timer'
 ,'rdbms ipc message'
 ,'SQL*Net message from client'
)
ORDER BY sid, seq#
;

-- Tablespace allocation/usage Statistics

SELECT
 allc.obj#,
 allc.object_name,
 allc.tablespace_name,
 allc.object_type,
 usg.value   usage_value,
 allc.value  alloc_value
FROM  v$segment_statistics allc,
  v$segment_statistics usg
WHERE  allc.obj# = usg.obj#
AND    usg.statistic# = 19    --usg.statistic_name = 'space used'
AND    allc.statistic# = 20   --allc.statistic_name = 'space allocated'
AND    allc.value >0
AND    allc.owner = user
;

-- Segment statistics/Recyclebin to be purged
SELECT * FROM v$segstat;

SELECT  DISTINCT
 allc.obj#,
 allc.object_name,
 segs.tablespace_name,
 allc.object_type,
 usg.value   usage_value,
 allc.value  alloc_value
FROM  v$segstat allc, v$segstat usg,
  v$segment_statistics segs
WHERE  allc.obj# = usg.obj#
AND    allc.obj# = segs.obj#
AND    usg.statistic# = 19    --usg.statistic_name = 'space used'
AND    allc.statistic# = 20   --allc.statistic_name = 'space allocated'
AND    allc.value >0
AND    segs.owner = user
;

-- Recyclebin data to purged
SHOW  RECYCLEBIN; 

SELECT * FROM recyclebin;

PURGE RECYCLEBIN;