2 Replies Latest reply on Jan 1, 2013 5:08 PM by rp0428

    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
        • 1. Re: The differences between CAT and TAB table
          Osama_Mustafa
          1 person found this helpful
          • 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;