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.


How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Sruti Banerjee

Hello and welcome to DBsGuru, I am Sruti Banerjee, having experience of 3 years as an Oracle DBA, I have good interpersonal skills that can help in attaining the desired level of team coordination, self-starter & quick learner. I am having rich experience in Oracle database management, Backup and Recovery, Security, Installation, Up-gradation, Patching, Migration, Cloning of databases, Dataguard (Physical), and a wide range of other database administration activities.Thanks for the visits!Share Learn Grow!