MetaData.reflect() not seeing my tables in Oracle 11.2 XE

[Hank]

I'm running into a very strange problem when I attempt to reflect against my Oracle DB. I've already pinged the IRC channel, and asked a question on StackOverflow. I'm pasting that question verbatim below. If you need anything else, please let me know.

I'm attempting to reverse engineer an existing Oracle schema into some declarative SQLAlchemy models. My problem is that when I use [`MetaData.reflect`](http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=metadata.reflect#sqlalchemy.schema.MetaData.reflect), it doesn't find the tables in my schema, just a Global Temp Table. However, I can still query against the other tables.

I'm using SQLAlchemy 0.7.8, CentOS 6.2 x86_64, python 2.6, cx_Oracle 5.1.2 and Oracle 11.2.0.2 Express Edition. Here's a quick sample of what I'm talking about:

    >>> import sqlalchemy

    >>> engine = sqlalchemy.create_engine('oracle+cx_oracle://user:pass@localhost/xe')

    >>> md = sqlalchemy.MetaData(bind=engine)

    >>> md.reflect()

    >>> md.tables

    immutabledict({u'my_gtt': Table(u'my_gtt', MetaData(bind=Engine(oracle+cx_oracle://user:pass@localhost/xe)), Column(u'id', NUMBER(precision=15, scale=0, asdecimal=False), table=<my_gtt>), Column(u'parent_id', NUMBER(precision=15, scale=0, asdecimal=False), table=<my_gtt>), Column(u'query_id', NUMBER(precision=15, scale=0, asdecimal=False), table=<my_gtt>), schema=None)})

    >>> len(engine.execute('select * from my_regular_table').fetchall())

    4

[Michael Bayer]

how are these tables represented in the default schema of that database connection?  if they are referred to via oracle synonyms, then they don't exist there as tables, they're in some other schema.

The Oracle dialect features a helper called "oracle_resolve_synonyms", which will cause the dialect to resolve a synonym-named table, but that only works within the Table() call itself, that is, Table("somename", autoload=True, oracle_resolve_synonyms=True).   Right now the metadata.reflect() call does not make use of the "resolve_synonyms" feature.

So assuming this is what's going on, you'd need to specify the schema where the tables are actually present, such as:

md.reflect(schema="mytables")

when you do that, you'll get Table objects back which will include "mytables" as the "schema" argument.  When these Table objects are used, you'll see the schema explicitly referenced, that is, "select * from myschema.mytable".

[Hank Gay]

On Sep 21, 2012, at 12:03 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

>
> how are these tables represented in the default schema of that database connection? if they are referred to via oracle synonyms, then they don't exist there as tables, they're in some other schema.
>
> The Oracle dialect features a helper called "oracle_resolve_synonyms", which will cause the dialect to resolve a synonym-named table, but that only works within the Table() call itself, that is, Table("somename", autoload=True, oracle_resolve_synonyms=True). Right now the metadata.reflect() call does not make use of the "resolve_synonyms" feature.
>
> So assuming this is what's going on, you'd need to specify the schema where the tables are actually present, such as:
>
> md.reflect(schema="mytables")
>
> when you do that, you'll get Table objects back which will include "mytables" as the "schema" argument. When these Table objects are used, you'll see the schema explicitly referenced, that is, "select * from myschema.mytable".

The tables are created via simple `CREATE TABLE` statements executed by the user in question. Unless somebody has done something very tricky that I don't know about, no synonyms whatsoever are in use. I also tried `md.reflect(schema='user')` and got the same result. I also tried connecting as a system-level user and specifying the schema argument, on the theory that maybe the user who created the tables lacked some obscure Oracle privilege required for reflection to do its thing; that didn't work, either.

I suppose it's possible I have some weird edge case with configuration weirdness, because I haven't been able to reproduce with an isolated test case. Do you suppose you could give me a 50,000 ft. view of what the reflect() command does on the SQL side of things? That way I can try to dig in against the legacy schema where I'm seeing the problem.

I expect I'll be lurking in #sqlalchemy on Freenode for most of the day, as well.

Thanks a lot for the great tool, and the help.

--Hank

[Michael Bayer]

on your create_engine(), set "echo='debug'". it will show you all the catalog queries being emitted as well as the result rows that are being received for each one.

[Hank Gay]

On Sep 21, 2012, at 12:59 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

>
> on your create_engine(), set "echo='debug'". it will show you all the catalog queries being emitted as well as the result rows that are being received for each one.
>

Perfect! This allowed me to track down the problem. It turns out those tables, though *visible* to the user, were not *owned* by that user; they were owned by some other user (I think this particular schema was actually the result of an old data pump export/import process), and the reflection process only returns tables that are owned by the connected user. Now I'm off to track down the best way to fix this table ownership issue. Thanks a ton!

--Hank

[Michael Bayer]

oh right, I forgot to mention the Oracle dialect considers the "schema" to be the "owner". So if you said metadata.reflect(schema="ownername"), that would get at those tables.