This discussion is archived
2 Replies Latest reply: Jan 1, 2013 9:08 AM by rp0428 RSS

The differences between CAT and TAB table

652398 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points