{"id":4177,"date":"2025-06-08T10:55:00","date_gmt":"2025-06-08T10:55:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4177"},"modified":"2025-06-10T15:54:24","modified_gmt":"2025-06-10T15:54:24","slug":"schema-object-count","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/schema-object-count\/","title":{"rendered":"Schema Object Count"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Quick SQL queries to get the count of various objects (like tables, views, packages, etc.) in a specific Oracle schema \u2014 useful for monitoring and maintaining your database structure.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 <strong>Check Object Count for a Single Schema<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Use this to get the count of different object types (TABLE, VIEW, etc.) in a single schema.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">\ud83d\udd04 Replace <code>'HR'<\/code> with your target schema name.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SET PAGESIZE 50;\nSET LINESIZE 200;\nSET LONG 200;\nSET WRAP OFF;\nSET HEADING ON;\nSET FEEDBACK OFF;\nCOLUMN owner FORMAT A10;\nCOLUMN object_type FORMAT A20;\nCOLUMN object_name FORMAT A30;\n\nSELECT * FROM (\n  SELECT owner, object_type, object_name\n  FROM dba_objects\n  WHERE owner = 'HR'\n)\nPIVOT (\n  COUNT(object_name)\n  FOR object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'DATABASE LINK', 'INDEX', 'SEQUENCE', 'SYNONYM', 'JOB', 'LOB', 'MATERIALIZED VIEW')\n)\nORDER BY 1, 2;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udccc <strong>Note:<\/strong> This gives you a pivoted summary of all object types in that schema.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 <strong>Check Object Count for Multiple Schemas<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Use this when you want a comparative object count across more than one schema.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">\ud83d\udd04 Replace <code>'DBSNMP', 'HR'<\/code> with your required schema list.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SET PAGESIZE 50;\nSET LINESIZE 200;\nSET LONG 200;\nSET WRAP OFF;\nSET HEADING ON;\nSET FEEDBACK OFF;\nCOLUMN owner FORMAT A10;\nCOLUMN object_type FORMAT A20;\nCOLUMN object_name FORMAT A30;\n\nSELECT * FROM (\n  SELECT owner, object_type, object_name\n  FROM dba_objects\n  WHERE owner IN ('DBSNMP', 'HR')\n)\nPIVOT (\n  COUNT(object_name)\n  FOR object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'DATABASE LINK', 'INDEX', 'SEQUENCE', 'SYNONYM', 'JOB', 'LOB', 'MATERIALIZED VIEW')\n)\nORDER BY 1, 2;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udccc <strong>Note:<\/strong> Great for comparing object types between schemas for audit or cleanup tasks.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83e\uddfe Final Tip<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Running these queries periodically can help track object growth, enforce standards, and ensure a clean, well-managed database environment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Quick SQL queries to get the count of various objects (like tables, views, packages, etc.) in a specific Oracle schema \u2014 useful for monitoring and maintaining your database structure. \u2705 Check Object Count for a Single Schema Use this to get the count of different object types (TABLE, VIEW, etc.) in a single schema. \ud83d\udd04 [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4177","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}