Script to Check Object Count in a Schema in Oracle

If you’re managing an Oracle database and need to quickly assess the number of objects in a schema, SQL queries can provide a quick solution. Whether you’re working with a single schema or multiple schemas, knowing the object count helps in database management. This guide shows how to use SQL queries to get the count of various objects such as tables, views, and more in Oracle.

Check Object Count for a Single Schema

To count the different object types in a single schema, use the following query. Replace 'HR' with your desired schema name.

SET PAGESIZE 50;
SET LINESIZE 200;
SET LONG 200;
SET WRAP OFF;
SET HEADING ON;
SET FEEDBACK OFF;
COLUMN owner FORMAT A10;
COLUMN object_type FORMAT A20;
COLUMN object_name FORMAT A30;

SELECT * FROM (
  SELECT owner, object_type, object_name
  FROM dba_objects
  WHERE owner = 'HR'
)
PIVOT (
  COUNT(object_name)
  FOR object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'DATABASE LINK', 'INDEX', 'SEQUENCE', 'SYNONYM', 'JOB', 'LOB', 'MATERIALIZED VIEW')
)
ORDER BY 1, 2;

This query provides a summary of object types (tables, views, procedures, etc.) and their counts in the specified schema.

Check Object Count for Multiple Schemas

To check object counts across multiple schemas, use the following query. It checks the object count for both the DBSNMP and HR schemas:

SET PAGESIZE 50;
SET LINESIZE 200;
SET LONG 200;
SET WRAP OFF;
SET HEADING ON;
SET FEEDBACK OFF;
COLUMN owner FORMAT A10;
COLUMN object_type FORMAT A20;
COLUMN object_name FORMAT A30;

SELECT * FROM (
  SELECT owner, object_type, object_name
  FROM dba_objects
  WHERE owner IN ('DBSNMP', 'HR')
)
PIVOT (
  COUNT(object_name)
  FOR object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'DATABASE LINK', 'INDEX', 'SEQUENCE', 'SYNONYM', 'JOB', 'LOB', 'MATERIALIZED VIEW')
)
ORDER BY 1, 2;

This query provides a comparative count of different object types across the selected schemas, helping you efficiently manage multiple schemas in your Oracle database.

Conclusion

By running these queries, you can easily monitor the object count in individual or multiple schemas in your Oracle database. This helps in maintaining an organized and efficient database structure.