Difference between user_segments and dba_segments

[tmgsam]

Hi Team,

SQL> select sum(bytes) from dba_segments where segment_type='TABLE';

SUM(BYTES)
----------
4.8594E+11

SQL> select sum(bytes) from user_segments where segment_type='TABLE';

SUM(BYTES)
----------
1453907968

SQL>

Waht is the difference between above queries.why there is difference in byets for both queries.Please help...

[gandolf989]

So if you want the queries to return the same information you need to specify a schema owner for the dba_segments view.

select sum(bytes) from dba_segments where owner='THISUSER' AND segment_type='TABLE';

In general views that start with user only show objects in the schema that you are logged in as, while views that start either all_ or dba_ show objects in every schema. The difference between all_ and dba_ is that the dba_ views have columns the all_ views don't.

[Tifa]

Data Dictionary View Prefixes
USER_ User’s view (what is in the user’s schema)
ALL_ Expanded user’s view (what the user can access)
DBA_ Database administrator’s view (what is in all users’ schemas)

Source: Oracle® Database Concepts 10g Release 2 (10.2) B14220-02

[tmgsam]

Ok Agreed thanks.But now can u pls tell me how I can know current schema name if i am using user_segments;
ie
select sum(bytes) from user_segments wher segment_type='TABLE';

Here how can I find schema name.

Thanks,
Sam

[gandolf989]

Ok Agreed thanks.But now can u pls tell me how I can know current schema name if i am using user_segments;
ie
select sum(bytes) from user_segments wher segment_type='TABLE';

Here how can I find schema name.

Thanks,
Sam

user will return the current schema user. For example "select user, sum(bytes) from user_segments where segment_type='TABLE';"