Oracle ALL_OBJECTS Queries
-- All Objects of the database and size of USER segments.
-- All_objects and user_segment/extents in query below 
-- can be replaced by DBA objects DBA_segments/extents 
-- to get size of all objects.
-- Shown beside is a typical output of the query 
-- (below).

SELECT  DECODE(Grouping(object_type), 0, object_type,
 'Total  ') object_details,
 count(*) objects_,
 NVL(ROUND(Sum(s.bytes)/16384,3),0) AS blocks_,
 NVL(ROUND(Sum(s.bytes)/1024/1024/1024,3),0) AS size_Gb
FROM all_objects o,
     user_segments s,
     user_extents e
WHERE o.object_name = s.segment_name (+)
AND   s.segment_name = e.segment_name (+)
GROUP BY ROLLUP(object_type);

-- List of Invalid Package/Package Body
SELECT
 o1.object_id,
 o1.owner||'.'||o1.object_name  object_name,
 o1.object_type,
 o1.status,
 TO_CHAR(o1.created,'mm/dd/yyyy hh24:mi:ss') created_date
FROM all_objects o1
WHERE EXISTS
 (SELECT NULL
  FROM   all_objects o2
  WHERE  o1.object_name = o2.object_name
  AND    o2.status = 'INVALID')
AND   o1.owner = user
AND   INSTR(o1.object_type, 'PACKAGE') > 0
ORDER BY  o1.owner||'.'||o1.object_name, o1.object_type;

-- Locked Objects Query
SELECT
 do.owner, do.object_name, do.object_type,
 s.sid, s.serial#, s.status, s.osuser, s.machine,
 lo.locked_mode,
 t.start_scn, t.dependent_scn, t.used_urec Records,
 TO_CHAR(s.logon_time,'mm/dd/yyyy hh24:mi:ss') logon_time
FROM  v$locked_object lo,
      v$session s,
      v$transaction t,
      dba_objects do
WHERE  s.sid = lo.session_id
AND    lo.object_id = do.object_id
AND    lo.xidusn = t.xidusn
AND    lo.xidslot = t.xidslot
AND    t.addr = s.taddr;

  Oracle ALL_OBJECTS Query Output
ALL_Objects Summary Data

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 01, 2012

  1093