select * from tab; 2

[ddrillich]

Good Day,

"select * from tab;" shows me two tables while "select count(*) from all_tables;" gives 132 tables.
I was under the impression that the former will show me all the tables as well. Obviously I'm wrong. So, what does "select * from tab;" show?

Thanks,
Dan

 

[SantaMufasa]

Dan,

Actually, tab is a UNION of rows from user_tables, user_views, and user_synonyms. All of these previous objects are ones that the querying user owns. ALL_<anything> lists objects to which you have permission, meaning the objects that the querying user owns, plus the objects that other users own to which the querying user has access.

Does this resolve the issue?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ddrillich]

Dave,

"select count(*) from user_tables;" returns indeed two rows. BTW, where can I see the definition of tab?

Thanks,
Dan

 

[Turkbear]

Hi,
In TOAD or Oracle's Enterprise manager (or by querying dba_views)

elect 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# (+)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

If, by "definition" you mean "its structure", you can do a:

SQL> describe tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 TNAME                                     NOT NULL VARCHAR2(30)
 TABTYPE                                            VARCHAR2(7)
 CLUSTERID                                          NUMBER

If, instead, you mean "the SELECT that defines the TAB view", you can:

SQL> set long 50000
SQL> select text from all_views where view_name = 'TAB';

TEXT
--------------------------------------------------------
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# (+)

Let us know if this is what you wanted

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ddrillich]

Thanks you guys!

Have a nice weekend.
Dan