Oracle Index overflow tables causes an exception during reflect()

[Former user]

Oracle's Index Overflow supporting tables doesn't allow DML/DDL queries and it causes an exception during reflection of these tables. Actually there is no need to reflect them because they don't store any readable data.

The proposal is to exclude them from reflection by modification of table names query: /lib/sqlalchemy/databases/oracle.py

Diff file is in the attachment.

[Former user]

Diff file for 0.5 maintenance branch

[Former user]

Diff file for 0.6 branch

[Mike Bayer]

OK I just need some background here. Per http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables012.htm , the kinds of tables we're talking about are a result of :

CREATE TABLE admin_docindex(
        token char(20), 
        doc_id NUMBER,
        token_frequency NUMBER,
        token_offsets VARCHAR2(2000),
        CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
    ORGANIZATION INDEX 
    TABLESPACE admin_tbs
    PCTTHRESHOLD 20
    OVERFLOW TABLESPACE admin_tbs2;

So do I understand that the above creates a second table, called the "overflow" table, where the IOT_NAME field is not null ? what would be the name of this table in the above case ? is it some generated thing ?

0.5.7 release needs to happen ASAP so this change might not make it in for that release.

[Former user]

Yes, Oracle creates a system table with name like SYS_IOT* (at least, Oracle 10g) which has no columns and no DML operators could be executed over them.

For the system table, ALL_TABLES.IOT_NAME points to the index-organized table. Thus condition "IOT_NAME is null" is sufficient to cut off index-overflow tables.

[Mike Bayer]

great that worked out very well and the patch + a test is in 58cef5b1321f94971d3c3ad446b384cf38d734ab bc5ad4e8afcdda269e0ec0d1c2fc40da13d2b999. thanks for the patch !

[Mike Bayer]

Removing milestone: 0.5.7 (automated comment)