oracle table rename requires the "TO" not schema-qualified

[kipstakkr]

I am trying to use op.rename_table with old_table_name, new_table_name and schema, but it is creating an incorrect SQL statement.

For example,
For the method call, op.rename_table('table_a', 'table_b', schema='tables')

Expected SQL statement to be executed:-

ALTER TABLE tables.table_a RENAME TO table_b

Actual SQL statement it is executing currently:-

ALTER TABLE tables.table_a RENAME TO tables.table_b

[zzzeek]

confirmed:

SQL> create table test_schema.foo(id integer);

Table created.

SQL> alter table test_schema.foo rename to bar;

Table altered.

SQL> alter table test_schema.bar rename to test_schema.bat;
alter table test_schema.bar rename to test_schema.bat
                                                 *
ERROR at line 1:
ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations

[zzzeek]

please use op.execute("ALTER TABLE tables.table_a RENAME TO table_b") for now, thanks.

[kipstakkr]

Cool. I'll go with your suggestion for now. Thanks. 👍

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Don't include schema in "to" portion of Oracle RENAME table https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/1803