2 Replies Latest reply: Jan 1, 2013 11:08 AM by rp0428 RSS

    The differences between CAT and TAB table

    652398
      1. What's the differences between CAT and TAB?
      2. Why doesn't TAB exist in dict?
      3. What the appropriate situation for CAT and TAB?

      Thanks a lot for your kindly reply.

      sys@ORCL> desc cat
      Name Null? Type
      ----------------------------------------------------- -------- ------------------------------------
      TABLE_NAME NOT NULL VARCHAR2(30)
      TABLE_TYPE VARCHAR2(11)

      sys@ORCL> desc tab
      Name Null? Type
      ----------------------------------------------------- -------- ------------------------------------
      TNAME NOT NULL VARCHAR2(30)
      TABTYPE VARCHAR2(7)
      CLUSTERID NUMBER

      sys@ORCL> select * from dict where table_name='CAT';

      TABLE_NAME COMMENTS
      ---------- -----------------------------------
      CAT Synonym for USER_CATALOG


      sys@ORCL> select * from dict where table_name='USER_CATALOG';

      TABLE_NAME COMMENTS
      -------------------- -----------------------------------------------------------------
      USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
        • 2. Re: The differences between CAT and TAB table
          rp0428
          You've posted enough to know that for every post you need to provide your 4 digit Oracle version (SELECT * FROM V$VERSION)
          >
          1. What's the differences between CAT and TAB?
          2. Why doesn't TAB exist in dict?
          3. What the appropriate situation for CAT and TAB?
          >
          1, 2, 3 - TAB has been deprecated so don't use it.

          The basic definitioni for all of those is in the Database Reference
          http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2127.htm#sthref1535
          >
          CAT

          CAT is a synonym for USER_CATALOG.
          . . .
          USER_CATALOG

          USER_CATALOG lists tables, views, clusters, synonyms, and sequences owned by the current user. Its columns are the same as those in "ALL_CATALOG".
          . . .
          DICT

          DICT is a synonym for DICTIONARY.
          . . .
          DICTIONARY

          DICTIONARY contains descriptions of data dictionary tables and views.
          . . .
          TAB

          TAB is included for compatibility. Oracle recommends that you do not use this view.
          >
          TAB has been deprecated. It is similar to CAT in that it shows USER objects but it does not show SEQUENCEs like CAT does.

          If you look at the source code for the TAB and USER_CATALOG views you can see the differences. This code is copyrighted by Oracle Corporation with all rights reserved.
          /* Formatted on 1/1/2013 8:50:07 AM (QP5 v5.115.810.9015) */
          CREATE OR REPLACE FORCE VIEW SYS.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."_CURRENT_EDITION_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#(+);
          . . .
          /* Formatted on 1/1/2013 8:54:45 AM (QP5 v5.115.810.9015) */
          CREATE OR REPLACE FORCE VIEW SYS.USER_CATALOG
          (
             TABLE_NAME,
             TABLE_TYPE
          )
          AS
             SELECT   o.name,
                      DECODE (o.type#,
                              0, 'NEXT OBJECT',
                              1, 'INDEX',
                              2, 'TABLE',
                              3, 'CLUSTER',
                              4, 'VIEW',
                              5, 'SYNONYM',
                              6, 'SEQUENCE',
                              'UNDEFINED')
               FROM   sys."_CURRENT_EDITION_OBJ" o
              WHERE   o.owner# = USERENV ('SCHEMAID')
                      AND ( (o.type# IN (4, 5, 6))
                           OR (o.type# = 2 /* tables, excluding iot - overflow and nested tables */
                               AND NOT EXISTS
                                     (SELECT   NULL
                                        FROM   sys.tab$ t
                                       WHERE   t.obj# = o.obj#
                                               AND (BITAND (t.property, 512) = 512
                                                    OR BITAND (t.property, 8192) = 8192))))
                      AND o.linkname IS NULL;