{"id":115,"date":"2024-12-14T16:13:36","date_gmt":"2024-12-14T16:13:36","guid":{"rendered":"https:\/\/w3buddy.com\/?p=115"},"modified":"2026-01-15T13:12:23","modified_gmt":"2026-01-15T07:42:23","slug":"sql-query-to-check-object-count-in-oracle-schema","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/sql-query-to-check-object-count-in-oracle-schema\/","title":{"rendered":"SQL Query to Check Object Count in Oracle Schema"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">If you&#8217;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&#8217;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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Check Object Count for a Single Schema<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To count the different object types in a single schema, use the following query. Replace <code>'HR'<\/code> with your desired schema name.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">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><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides a summary of object types (tables, views, procedures, etc.) and their counts in the specified schema.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Check Object Count for Multiple Schemas<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To check object counts across multiple schemas, use the following query. It checks the object count for both the <code>DBSNMP<\/code> and <code>HR<\/code> schemas:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">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><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides a comparative count of different object types across the selected schemas, helping you efficiently manage multiple schemas in your Oracle database.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;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&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-115","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/115","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=115"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/115\/revisions"}],"predecessor-version":[{"id":1437,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/115\/revisions\/1437"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=115"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}