-- 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;
72900