Difference between user_segments and dba_segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Difference between user_segments and dba_segments

  1. #1
    Join Date
    Feb 2009
    Posts
    49

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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    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.
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    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

  4. #4
    Join Date
    Feb 2009
    Posts
    49
    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

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by tmgsam View Post
    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';"
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width