Difference between user_segments and dba_segments

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...

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.

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

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

Quote:

---

Originally Posted by 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

---

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