6 Replies Latest reply on May 26, 2012 5:56 PM by VC

    Tab table in Oracle database

    user12090988
      Hi All,

      This is regarding the Tab table in the oracle database.
      When I query this Tab table using a database account I cannot see some of the tables in it, where as using another database accout I can see all those tables.
      Any idea why?
        • 1. Re: Tab table in Oracle database
          sb92075
          user12090988 wrote:
          Hi All,

          This is regarding the Tab table in the oracle database.
          When I query this Tab table using a database account I cannot see some of the tables in it, where as using another database accout I can see all those tables.
          Any idea why?
          privileges or lack thereof
          • 2. Re: Tab table in Oracle database
            900442
            Hi,
            Please post sql you used to query the tab table? Are you referring to a tablespace or a table. Check priviliges as SB mentioned.

            Thanks...
            Raj...
            • 3. Re: Tab table in Oracle database
              kuljeet singh -
              both user own different tables.

              Defination of TAB view is below
                1* select owner,text from dba_views where view_name='TAB'
              
              OWNER                          TEXT
              ------------------------------ ----------------------------------------
              SYS                            select o.name,
                                                   decode(o.type#, 2, 'TABLE', 3, 'CL
                                             USTER',
                                                          4, 'VIEW', 5, 'SYNONYM'), t
                                             .tab#
                                               from  sys.tab$ t, sys."_CURRENT_EDITIO
                                             N_OBJ" o
                                               where o.owner# = userenv('SCHEMAID')
                                               and o.type# >=2
                                               and o.type# <=5
                                               and o.linkname is null
                                               and o.obj# = t.obj# (+)
              
              
              
              it showing user tables detail
              
              SQL> sho user
              USER is "SCOTT"
              SQL>
              
              SQL>  select * from tab;
              
              TNAME                          TABTYPE  CLUSTERID
              ------------------------------ ------- ----------
              DEPT                           TABLE
              EMP                            TABLE
              BONUS                          TABLE
              SALGRADE                       TABLE
              EMP2                           TABLE
              T6                             TABLE
              T3                             TABLE
              EMP1                           TABLE
              EMP1_TEMP                      TABLE
              T$1                            TABLE
              T$2                            TABLE
              TEST                           TABLE
              TT                             TABLE
              BULK                           TABLE
              
              14 rows selected.
              
              SQL> select table_name from user_tables;
              
              TABLE_NAME
              ------------------------------
              DEPT
              EMP
              BONUS
              SALGRADE
              EMP2
              T6
              T3
              EMP1_TEMP
              T$1
              T$2
              TEST
              TT
              BULK
              EMP1
              
              14 rows selected.
              
              SQL>
              SQL>
              SQL>
              SQL> create table tst(no number);
              
              Table created.
              
              
              SQL>  select table_name from user_tables;
              
              TABLE_NAME
              ------------------------------
              DEPT
              EMP
              BONUS
              SALGRADE
              EMP2
              T6
              T3
              EMP1_TEMP
              T$1
              T$2
              TST
              TEST
              TT
              BULK
              EMP1
              
              15 rows selected.
              
              SQL> select * from tab;
              
              TNAME                          TABTYPE  CLUSTERID
              ------------------------------ ------- ----------
              DEPT                           TABLE
              EMP                            TABLE
              BONUS                          TABLE
              SALGRADE                       TABLE
              EMP2                           TABLE
              T6                             TABLE
              T3                             TABLE
              EMP1                           TABLE
              EMP1_TEMP                      TABLE
              T$1                            TABLE
              T$2                            TABLE
              TST                            TABLE
              TEST                           TABLE
              TT                             TABLE
              BULK                           TABLE
              
              15 rows selected.
              • 4. Re: Tab table in Oracle database
                939824
                Check for the roles assigned to both users.
                You can check this in dba_role_privs.
                • 5. Re: Tab table in Oracle database
                  user12090988
                  Hi All,
                  Thanks for your responses.
                  Is there any special privilege required for a schema to view tables belonging to other schema in the "TAB" view?
                  • 6. Re: Tab table in Oracle database
                    VC
                    user12090988 wrote:
                    Hi All,
                    Thanks for your responses.
                    Is there any special privilege required for a schema to view tables belonging to other schema in the "TAB" view?
                    No, Because tab always gives you the objects relating to current user

                    Here is the view definition of tab
                    CREATE OR REPLACE VIEW tab (
                      tname,
                      tabtype,
                      clusterid
                    ) AS
                    select o.name,
                          decode(o.type#, 2, 'TABLE', 3, 'CLUSTER',
                                 4, 'VIEW', 5, 'SYNONYM'), t.tab#
                      from  sys.tab$ t, sys.obj$ o
                      where o.owner# = userenv('SCHEMAID')
                      and o.type# >=2
                      and o.type# <=5
                      and o.linkname is null
                      and o.obj# = t.obj# (+)
                    You can use all_objects instead