To Check the Object count of a Schema in Oracle

Oracle object types are user-defined data types that make it possible to shape real-world entities such as customers and supply orders as objects in the database.

Underneath the object layer, data is stored in a tabular format, that is in rows and columns but we can work with the data in terms of the real-world entities which are customers and supply orders, that make the data meaningful. For example—Instead of thinking in terms of columns and tables when we query the database, we can simply select a customer name. Examples of objects: Tables, Index, Cluster, etc.

When working in a real-world environment, sometimes we need to retrieve the count of objects present underneath a schema so that we can compare the records after performing any schema activity. This will help us to know if any objects counts have been mismatched post activities.

The below query will give us the count of objects in a schema. Click here for sample output.

set lines 333 pages 111
col OWNER for a19
col OBJECT_TYPE for a21

select owner,object_type,count(*) 
from dba_objects where owner in ('TESTUSER4')
group by owner,object_type order by owner,object_type;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.