ADVERTISEMENT

Schema Object Count

Quick SQL queries to get the count of various objects (like tables, views, packages, etc.) in a specific Oracle schema — useful for monitoring and maintaining your database structure.

✅ Check Object Count for a Single Schema

Use this to get the count of different object types (TABLE, VIEW, etc.) in a single schema.

🔄 Replace 'HR' with your target 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;

📌 Note: This gives you a pivoted summary of all object types in that schema.

✅ Check Object Count for Multiple Schemas

Use this when you want a comparative object count across more than one schema.

🔄 Replace 'DBSNMP', 'HR' with your required schema list.

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;

📌 Note: Great for comparing object types between schemas for audit or cleanup tasks.

🧾 Final Tip

Running these queries periodically can help track object growth, enforce standards, and ensure a clean, well-managed database environment.

ADVERTISEMENT