oracle-databaseobjectoracle10gsubtypesupertype

Subtype Supertype with Oracle Object Type Creation. Limit on the number of subtypes?


I have run into an issue when creating a object type in Oracle 10g that inherits from a supertype. We currently have many object types that inherit from this supertype and recently the compiler started throwing the following errors

ORA-30745: error occured while trying to add column "SYS_NC_ROWINFO$" in table "DATA_CACHE.CACHE_ENTRIES"

ORA-01792: maximum number of columns in a table or view is 1000

Is there a cap on the number of subtypes you can generate that inherit from a supertype?


Solution

  • When you create tables with columns based on user-defined types, Oracle creates additional "secret" columns for you under the covers. For example:

    SQL> create type emp_data_t  as object (empno number, ename varchar2(30));
      2  /
    
    Type created.
    
    SQL> create table emp_data_table (id int, emp_data emp_data_t);
    
    Table created.
    

    This table appears to have 2 columns:

    SQL> desc emp_data_table
     Name                       Null?    Type
     -------------------------- -------- ------------------------
     ID                                  NUMBER(38)
     EMP_DATA                            EMP_DATA_T
    

    ... but it really has four:

    SQL> select name
      2  from sys.col$
      3  where obj# = (select object_id 
      4                from user_objects 
      5                where object_name='EMP_DATA_TABLE');
    
    NAME
    ------------------------------
    ID
    EMP_DATA
    SYS_NC00003$
    SYS_NC00004$
    

    As you have seen, Oracle has a limit of 1000 columns per table. This limit will include any of these hidden columns derived from types and supertypes. It looks like your table has exceeded this limit.