Home » SQL & PL/SQL » SQL & PL/SQL » Difference between the Following
Difference between the Following [message #418351] Fri, 14 August 2009 05:35 Go to next message
catchmeifyoucan
Messages: 1
Registered: August 2009
Location: Chennai
Junior Member

what is the difference between
select * from tab:
select table_name from user_table
Re: Difference between the Following [message #418353 is a reply to message #418351] Fri, 14 August 2009 05:45 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Well to begin with


1. Select table_name from user_table will give an error


SQL>select table_name from user_table
  2  /
select table_name from user_table
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>


2. Select * from tab will have 3 columns whereas Select table_name from user_table will have one column returned


3. Views in the schema will not be listed in the query Select table_name from user_Tables

4. You could have tried yourself the query to identify the difference



[Update] Typo corrected

[Updated on: Fri, 14 August 2009 05:55]

Report message to a moderator

Re: Difference between the Following [message #418355 is a reply to message #418351] Fri, 14 August 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TAB
USER_TABLES
SQL> desc user_tables
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                       NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                           VARCHAR2(30)
 CLUSTER_NAME                              VARCHAR2(30 CHAR)
 IOT_NAME                                  VARCHAR2(30 CHAR)
 STATUS                                    VARCHAR2(8 CHAR)
 PCT_FREE                                  NUMBER
 PCT_USED                                  NUMBER
 INI_TRANS                                 NUMBER
 MAX_TRANS                                 NUMBER
 INITIAL_EXTENT                            NUMBER
 NEXT_EXTENT                               NUMBER
 MIN_EXTENTS                               NUMBER
 MAX_EXTENTS                               NUMBER
 PCT_INCREASE                              NUMBER
 FREELISTS                                 NUMBER
 FREELIST_GROUPS                           NUMBER
 LOGGING                                   VARCHAR2(3 CHAR)
 BACKED_UP                                 VARCHAR2(1 CHAR)
 NUM_ROWS                                  NUMBER
 BLOCKS                                    NUMBER
 EMPTY_BLOCKS                              NUMBER
 AVG_SPACE                                 NUMBER
 CHAIN_CNT                                 NUMBER
 AVG_ROW_LEN                               NUMBER
 AVG_SPACE_FREELIST_BLOCKS                 NUMBER
 NUM_FREELIST_BLOCKS                       NUMBER
 DEGREE                                    VARCHAR2(10 CHAR)
 INSTANCES                                 VARCHAR2(10 CHAR)
 CACHE                                     VARCHAR2(5 CHAR)
 TABLE_LOCK                                VARCHAR2(8 CHAR)
 SAMPLE_SIZE                               NUMBER
 LAST_ANALYZED                             DATE
 PARTITIONED                               VARCHAR2(3 CHAR)
 IOT_TYPE                                  VARCHAR2(12 CHAR)
 TEMPORARY                                 VARCHAR2(1 CHAR)
 SECONDARY                                 VARCHAR2(1 CHAR)
 NESTED                                    VARCHAR2(3 CHAR)
 BUFFER_POOL                               VARCHAR2(7 CHAR)
 ROW_MOVEMENT                              VARCHAR2(8 CHAR)
 GLOBAL_STATS                              VARCHAR2(3 CHAR)
 USER_STATS                                VARCHAR2(3 CHAR)
 DURATION                                  VARCHAR2(15 CHAR)
 SKIP_CORRUPT                              VARCHAR2(8 CHAR)
 MONITORING                                VARCHAR2(3 CHAR)
 CLUSTER_OWNER                             VARCHAR2(30 CHAR)
 DEPENDENCIES                              VARCHAR2(8 CHAR)
 COMPRESSION                               VARCHAR2(8 CHAR)
 DROPPED                                   VARCHAR2(3 CHAR)

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

Regards
Michel
Re: Difference between the Following [message #418357 is a reply to message #418355] Fri, 14 August 2009 06:12 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
@Michel
I am not sure if your describe tab and user_tables were intended for me. However just to clarify, since OP had mentioned difference between

select table_name from user_table and

Select * from tab

I had replied that Select table_name from user_tables will returns one column compared to Select * from tab.
Re: Difference between the Following [message #418358 is a reply to message #418357] Fri, 14 August 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not sure if your describe tab and user_tables were intended for me.

If you have a look at post header you will see I replied to message #418351 that is OP's one and not yours which is message #418353.

Regards
Michel
Re: Difference between the Following [message #418417 is a reply to message #418358] Fri, 14 August 2009 22:47 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
TAB is a very old object for Oracle. It was the original USER_OBJECTS in the early days of Oracle. Then as RDBMS became mainstream, some committee created a standard for naming and content of data dictionaries of releational databases (one of the ways you know you have made it is when someone else tells you to use standards). Naturally Oracle did their best to comply and on that day USER_TABLES was born along with the data dictionary views we all use commonly today. TAB is retained for backwards compatibility.

If you see someone using it odds are they are an oracle old timer >15 years.

if you want to know if they are different, then you bet they are. Here is the ultimate technical answer:

  1* select text from dba_views where view_name = 'TAB'
SQL> /

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


Nice and simple that tab. "show the logged in user's tables,clusters,views,synonyms". Back in the begining we only had these four things. It was a simpler time.

Now for USER_TABLES

  1* select text from dba_views where view_name = 'USER_TABLES'
SQL> /

TEXT
-----------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
       decode(bitand(t.property, 1024), 0, null, co.name),
       decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
              0, null, co.name),
       decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
       decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
          decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
       decode(bitand(t.property, 32), 0, t.initrans, null),
       decode(bitand(t.property, 32), 0, t.maxtrans, null),
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
         decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
       decode(bitand(t.property, 32+64), 0,
                decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
       decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
       t.rowcnt,
       decode(bitand(t.property, 64), 0, t.blkcnt, null),
       decode(bitand(t.property, 64), 0, t.empcnt, null),
       decode(bitand(t.property, 64), 0, t.avgspc, null),
       t.chncnt, t.avgrln, t.avgspc_flb,
       decode(bitand(t.property, 64), 0, t.flbcnt, null),
       lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
       lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
       lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
       decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
       t.samplesize, t.analyzetime,
       decode(bitand(t.property, 32), 32, 'YES', 'NO'),
       decode(bitand(t.property, 64), 64, 'IOT',
               decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
               decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       decode(bitand(t.property, 8192), 8192, 'YES',
              decode(bitand(t.property, 1), 0, 'NO', 'YES')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
             decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
       decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
       decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
       decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
       decode(bitand(o.flags, 2), 0, NULL,
           decode(bitand(t.property, 8388608), 8388608,
                  'SYS$SESSION', 'SYS$TRANSACTION')),
       decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
       decode(bitand(o.flags, 2), 2, 'NO',
           decode(bitand(t.property, 2147483648), 2147483648, 'NO',
              decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
       decode(bitand(t.property, 1024), 0, null, cu.name),
       decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
       decode(bitand(t.property, 32), 32, null,
                decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),
       decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv('SCHEMAID')
  and o.obj# = t.obj#
  and bitand(t.property, 1) = 0
  and bitand(o.flags, 128) = 0
  and t.bobj# = co.obj# (+)
  and t.ts# = ts.ts#
  and t.file# = s.file# (+)
  and t.block# = s.block# (+)
  and t.ts# = s.ts# (+)
  and t.dataobj# = cx.obj# (+)
  and cx.owner# = cu.user# (+)
  and ksppi.indx = ksppcv.indx
  and ksppi.ksppinm = '_dml_monitoring_enabled'


hmmm.. not so easy as tab is it. I suggest that USER_TABLES shows any object that consumes space for rows (TABLE,MATERIALIZED VIEW,TABLE PARTITION,DIMENSION, etc.). Must be what all that BITAND'ng is for. And check this out: '_dml_monitoring_enabled' That is a head turner of sorts eh.

So TAB and USER_TABLES are not identical in rowsets. They each return different rows.

An interesting question, thanks for asking, brings back fond memories. Kevin

[Updated on: Fri, 14 August 2009 22:48]

Report message to a moderator

Previous Topic: grant select privelege
Next Topic: trim function to all char columns unix , oracle 10g (Merged)
Goto Forum:
  


Current Time: Thu Dec 08 00:20:17 CST 2016

Total time taken to generate the page: 0.11206 seconds