CODEWITHSUNDEEP
sqloracle-database
user3244644
user3244644

Reputation: 11

sqlplus list object name and size in kb of each object in a schema

I want to list all the objects names and size in kb of each objects in the a schema(e.g book) but I have no idea how to do it. any good reference that I can take a look?

Upvotes: 1

Views: 1265

Answers (1)

Mureinik
Mureinik

Reputation: 311018

The DBA_OBJECTS data dictionary view holds information about all the objects in the database. DBA_SEGMENTS holds information on segments (e.g. tables, indexes, materialized views, etc. See the full details in Oracle's documentation). Objects which are not backed by segments are just definitions stored in Oracle's internal tables (e.g. a view is no more than a couple of bytes of data its text takes), so they can probably safely be neglected.

Once we've established that, it's a simple matter of outer joining the two tables:

SELECT          object_name, object_type, bytes/1024 AS KB
FROM            dba_objects do
LEFT OUTER JOIN dba_segments ds on do.object_name = ds.segment_name
WHERE           do.owner = 'BOOK'

Note that unfortunately, there's no ALL_SEGMENTS variant of that table, so you'll have to use a user with permissions on the DBA_* views. Alternatively, you could log in with the user you want to retrieve data for and use the USER_* variant:

SELECT          object_name, object_type, bytes/1024 AS KB
FROM            user_objects uo
LEFT OUTER JOIN user_segments us on uo.object_name = us.segment_name

Upvotes: 1

Related Questions