Snippet Name: DBA Objects Description: Returns a summary of object types by the tablespaces where they reside. Also see:» Add PSOUG Search to SQL Developer » Converting Rows to Columns » Database Links: CURRENT_USER » Instant Test Database with DCBA » Show info on current context » Lookup Oracle error messages » Display and release DBMS_LOCK locks » Display locks and latches » Show rollback segment stats » Show active transactions » List supported INIT.ORA parameters » Display database SGA statistics » Measure the Buffer Cache Hit Ratio » List security related profile informat... » Find users with deadly privileges » Audit User Logins (User Login Trigger) » Block TOAD and other tools » Kill Session » Extents » DBA Users » DBA Tablespaces » DBA triggers » DBA Sessions » DBA Roles » DBA Links » DBA Jobs » Job Queue » DBA Free Space » Data Files » DBA Extents Comment: (none) Language: Highlight Mode: PLSQL Last Modified: March 01st, 2009
Description: Returns a summary of object types by the tablespaces where they reside.
Also see:» Add PSOUG Search to SQL Developer » Converting Rows to Columns » Database Links: CURRENT_USER » Instant Test Database with DCBA » Show info on current context » Lookup Oracle error messages » Display and release DBMS_LOCK locks » Display locks and latches » Show rollback segment stats » Show active transactions » List supported INIT.ORA parameters » Display database SGA statistics » Measure the Buffer Cache Hit Ratio » List security related profile informat... » Find users with deadly privileges » Audit User Logins (User Login Trigger) » Block TOAD and other tools » Kill Session » Extents » DBA Users » DBA Tablespaces » DBA triggers » DBA Sessions » DBA Roles » DBA Links » DBA Jobs » Job Queue » DBA Free Space » Data Files » DBA Extents
DROP TABLE ext_temp / CREATE TABLE ext_temp AS SELECT DISTINCT tablespace_name, owner, segment_name FROM dba_extents WHERE owner != 'SYS' / spool dba_obj_count1.lst SELECT tablespace_name, object_type, COUNT(*) FROM dba_objects a, ext_temp b WHERE object_name = segment_name AND a.owner = b.owner GROUP BY tablespace_name, object_type /