Object Count Analysis (objcnt.sql)

What This Script Does

Provides a comprehensive count of database objects grouped by owner and object type, including tracking of invalid objects. This script filters out system schemas to focus on application and user objects.

The Script

rem  objcnt.sql
rem
ttitle 'Object Count Analysis'
rem
col owner format a20
col object_type format a20
col object_cnt format 99999
col invalid_cnt format 99999
rem
select owner
     , object_type
     , count(*) object_cnt
     , sum(decode( status, 'VALID', null, 1 )) invalid_cnt
  from sys.dba_objects
 where owner like nvl(upper('&owner'),'%')
   and object_type like nvl(upper('&type'),'%')
   and object_name like nvl(upper('&object'),'%')
   and owner not in ('SYS','SYSTEM','WMSYS','OUTLN','SCOTT','TSMSYS','PUBLIC')
 group by owner, object_type
 order by owner, object_type
/

Usage

-- Count all objects for all schemas (excluding system schemas)
@objcnt.sql
-- Enter: [press enter for all owners]
-- Enter: [press enter for all types]
-- Enter: [press enter for all objects]

-- Count objects for specific owner
@objcnt.sql
-- Enter: SCOTT (for specific schema)
-- Enter: [press enter for all types]
-- Enter: [press enter for all objects]

-- Count specific object types
@objcnt.sql
-- Enter: [press enter for all owners]
-- Enter: TABLE (for tables only)
-- Enter: [press enter for all objects]

-- Count specific objects
@objcnt.sql
-- Enter: HR (for specific schema)
-- Enter: [press enter for all types]
-- Enter: EMP% (for objects starting with EMP)

Parameters

The script prompts for:

owner: Schema name pattern (% for all non-system schemas)
type: Object type pattern (% for all types)
object: Object name pattern (% for all objects)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on SYS.DBA_OBJECTS

Sample Output

                                Object Count Analysis

OWNER                OBJECT_TYPE          OBJECT_CNT INVALID_CNT
-------------------- -------------------- ---------- -----------
HR                   INDEX                        15           0
HR                   SEQUENCE                      3           0
HR                   TABLE                         7           0
HR                   TRIGGER                       2           1
HR                   VIEW                          4           0
SALES                FUNCTION                      8           2
SALES                INDEX                        45           0
SALES                PACKAGE                      12           1
SALES                PACKAGE BODY                 12           3
SALES                PROCEDURE                    23           0
SALES                SEQUENCE                      5           0
SALES                TABLE                        23           0
SALES                TRIGGER                      15           2
SALES                VIEW                         18           1

Key Output Columns

OWNER: Schema/owner name
OBJECT_TYPE: Type of database object
OBJECT_CNT: Total count of objects
INVALID_CNT: Number of invalid objects

Object Types Tracked

Table Objects

TABLE
INDEX
TRIGGER
SEQUENCE

Code Objects

PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY

View Objects

VIEW
MATERIALIZED VIEW

Other Objects

TYPE
SYNONYM
DATABASE LINK

Filtered Schemas

The script excludes these system schemas:

SYS
SYSTEM
WMSYS
OUTLN
SCOTT
TSMSYS
PUBLIC

Common Use Cases

Schema Analysis

-- Analyze objects in specific application schema
@objcnt.sql
-- Enter: APP_SCHEMA
-- Review object distribution and invalid counts

Invalid Object Detection

-- Find schemas with invalid objects
@objcnt.sql
-- Look for non-zero INVALID_CNT values
-- Focus remediation efforts

Development Environment Comparison

-- Compare object counts between environments
@objcnt.sql
-- Verify deployment completeness
-- Identify missing or extra objects

Capacity Planning

-- Understand object distribution across schemas
@objcnt.sql
-- Plan for growth and resource allocation

Code Inventory

-- Count stored procedures, functions, and packages
@objcnt.sql
-- Enter: [schema]
-- Enter: PROCEDURE (or FUNCTION, PACKAGE)

Analysis Insights

High Invalid Counts

May indicate compilation issues
Could suggest dependency problems
Requires investigation and remediation

Object Distribution

Shows schema complexity
Indicates development patterns
Helps understand application structure

Schema Comparison

Compare development vs. production
Verify deployment consistency
Identify environment differences

Invalid Object Investigation

Follow-up Queries

-- Find specific invalid objects
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status != 'VALID'
  AND owner = 'SCHEMA_NAME';

Compilation Commands

-- Recompile invalid objects
EXEC DBMS_UTILITY.compile_schema('SCHEMA_NAME');

Performance Impact

Low Impact: Simple aggregation query
Efficient Indexing: Uses object name indexes
Filtered Results: Excludes large system schemas

Customization Options

Include Different Schemas

-- Modify the exclusion list
and owner not in ('SYS','SYSTEM','CUSTOM_SYSTEM_SCHEMA')

Add Status Breakdown

-- Include more status details
, sum(decode(status, 'VALID', 1, 0)) valid_cnt
, sum(decode(status, 'INVALID', 1, 0)) invalid_cnt

Object Name Analysis

-- Add object name patterns
, count(case when object_name like 'TMP_%' then 1 end) temp_objects

dinvalid - Invalid objects analysis
dobject - Detailed object information
dtable - Table analysis
dindex - Index analysis