Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Thumbs down Unanswered: diff b/w CAT & TAB

    hi,

    when i gave the following query

    SELECT * FROM TAB;

    i got a list containing TABLENAME, TABLETYPE and CLUSTERID

    at the same time when i gave the following query

    SELECT * FROM CAT;

    i got a list containing TABLENAME and TABLETYPE.

    Can anybody tell me what is the difference of this statement and what is

    CLUSTER and CLUSTERID.

    Thanx in advance.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    TAB is a view over 2 tables, sys.tab$ & sys.obj$. Both these are system tables. Where as CAT is a view over sys.obj$ table.
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Here is the source code for the view TAB

    CREATE OR REPLACE VIEW 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.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# (+)



    Source code for the view CAT (ie.,USER_CATALOG)

    CREATE OR REPLACE VIEW 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.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) = 192))))
    and o.linkname is null
    SATHISH .

  4. #4
    Join Date
    Nov 2003
    Posts
    87

    Thumbs down

    thanx.

    but when i gave the statement..

    select * from sys.tab$;

    im getting folwing err..

    ERROR at line 1:
    ORA-00942: table or view does not exist

    same with sys,obj$ also.

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi Raseena,

    SYS.TAB$ & SYS.OBJ$ are the system tables. U can't access them directly.

    These are the column in SYS.TAB

    OBJ# NUMBER NOT NULL,
    DATAOBJ# NUMBER,
    TS# NUMBER NOT NULL,
    FILE# NUMBER NOT NULL,
    BLOCK# NUMBER NOT NULL,
    BOBJ# NUMBER,
    TAB# NUMBER,
    COLS NUMBER NOT NULL,
    CLUCOLS NUMBER,
    PCTFREE$ NUMBER NOT NULL,
    PCTUSED$ NUMBER NOT NULL,
    INITRANS NUMBER NOT NULL,
    MAXTRANS NUMBER NOT NULL,
    FLAGS NUMBER NOT NULL,
    AUDIT$ VARCHAR2 (38) NOT NULL,
    ROWCNT NUMBER,
    BLKCNT NUMBER,
    EMPCNT NUMBER,
    AVGSPC NUMBER,
    CHNCNT NUMBER,
    AVGRLN NUMBER,
    AVGSPC_FLB NUMBER,
    FLBCNT NUMBER,
    ANALYZETIME DATE,
    SAMPLESIZE NUMBER,
    DEGREE NUMBER,
    INSTANCES NUMBER,
    INTCOLS NUMBER NOT NULL,
    KERNELCOLS NUMBER NOT NULL,
    PROPERTY NUMBER NOT NULL,
    TRIGFLAG NUMBER,
    SPARE1 NUMBER,
    SPARE2 NUMBER,
    SPARE3 NUMBER,
    SPARE4 VARCHAR2 (1000),
    SPARE5 VARCHAR2 (1000),
    SPARE6 DATE



    These are the column in SYS.OBJ

    OBJ# NUMBER NOT NULL,
    DATAOBJ# NUMBER,
    OWNER# NUMBER NOT NULL,
    NAME VARCHAR2 (30) NOT NULL,
    NAMESPACE NUMBER NOT NULL,
    SUBNAME VARCHAR2 (30),
    TYPE# NUMBER NOT NULL,
    CTIME DATE NOT NULL,
    MTIME DATE NOT NULL,
    STIME DATE NOT NULL,
    STATUS NUMBER NOT NULL,
    REMOTEOWNER VARCHAR2 (30),
    LINKNAME VARCHAR2 (128),
    FLAGS NUMBER,
    OID$ RAW (16),
    SPARE1 NUMBER,
    SPARE2 NUMBER,
    SPARE3 NUMBER,
    SPARE4 VARCHAR2 (1000),
    SPARE5 VARCHAR2 (1000),
    SPARE6 DATE
    SATHISH .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •