DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Object tables don't show up in user_tables?

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Object tables don't show up in user_tables?

    I recently came across this issue in both an 8.1.7.4 database and a 9.2.0.5 database. I created a table based off an object. The table showed up in user_objects as object_type='TABLE', but did not show up in user_tables. Does anybody have an explanation as to why this is?

    Code:
    jeffh@dev920.us> create or replace type money_t as object
      2  (
      3     amount number,
      4     currency varchar2(3)
      5  );
      6  /
    
    Type created.
    
    Elapsed: 00:00:00.06
    jeffh@dev920.us> create or replace type moneyhistory_t as object
      2  (
      3     id number(10),
      4     price money_t
      5  );
      6  /
    
    Type created.
    
    Elapsed: 00:00:00.07
    jeffh@dev920.us> create table moneyhistory_o of moneyhistory_t (
      2     id not null,
      3     price not null,
      4     constraint moneyhistory_pk
      5        primary key (id)
      6  )
      7  object identifier is primary key;
    
    Table created.
    
    Elapsed: 00:00:00.12
    jeffh@dev920.us> select object_name, object_type from user_objects;
    
    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ ------------------
    MONEYHISTORY_O                 TABLE
    MONEYHISTORY_PK                INDEX
    MONEYHISTORY_T                 TYPE
    MONEY_T                        TYPE
    SYS_YOID0000061754$            TYPE
    
    Elapsed: 00:00:00.02
    jeffh@dev920.us> select table_name from user_tables;
    
    no rows selected
    
    Elapsed: 00:00:00.01
    jeffh@dev920.us> desc MONEYHISTORY_O
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER(10)
     PRICE                                     NOT NULL MONEY_T
    Jeff Hunter

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    Expected behavior.


    Do a

    select * from USER_OBJECT_TABLES WHERE TABLE_NAME = 'MONEYHISTORY_O'
    /

    You'll see it.

    See Note:93410.1 on Metalink.

    Excerpt :

    "Explanation: ============ A table that contains a user-defined datatype is called an object table. This is a special kind of table that holds objects and provides a relational view of the attributes of those objects. Object tables are not contained in the USER_TABLES table. They are included in the USER_OBJECT_TABLES table. "

Posting Permissions

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


Click Here to Expand Forum to Full Width