Home » SQL & PL/SQL » SQL & PL/SQL » Occupied space from Data Dictionary
Occupied space from Data Dictionary [message #199905] Thu, 26 October 2006 13:37 Go to next message
Messages: 1
Registered: October 2006
Junior Member
Hi everybody,

I'm not very familiar with Data Dictionary views but I need to do simple analysis of the space occupied by users for specified tablespace and the space occupied by specific user in all tablespaces. Now I'm using queries involving DBA_SEGMENTS such as:

select owner, sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'tablespace_name' group by owner order by sum(bytes) desc;

select tablespace_name, sum(bytes)/1024/1024 from dba_segments where owner = 'owner' group by tablespace_name

One more advanced Oracle user draw my attention to the issue that these statements return a space occupied by tables and indexes only and omit a space occupied by definitions of views, procedures, functions, packages and such because on the level of segments their belong to user SYS.

He recommended me to look into DBA_OBJECT_SIZE to solve the problem but in this view nor the DBA_OBJECTS view there is no information about object's tablespace. If I would suppose that all omitted objects are in SYSTEM tablespace would be this assumption correct?

Does anybody have any idea how to neatly modify the query to fix described problem?
Re: Occupied space from Data Dictionary [message #199908 is a reply to message #199905] Thu, 26 October 2006 14:23 Go to previous message
Mahesh Rajendran
Messages: 10673
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> omit a space occupied by definitions of views, procedures, functions, packages
The space occupied by these objects (or the DDL of the object) are usually very negligible and is generally not considered as "space occupied by users"
All object defintions are stored in dictionary in the form of metadata in system tablespace. This metadata (data about the data) can spawn across different v$/x$ tables and views.
Previous Topic: Oracle Stored Procedure - Must Be Delcared?
Next Topic: Tuning Help - Can this be sped up??
Goto Forum:

Current Time: Fri Jul 21 17:57:38 CDT 2017

Total time taken to generate the page: 0.19559 seconds