This Is Why Streams Cannot Apply "RENAME"!

You've probably mentioned it in the "Types of DDL Changes Ignored by an Apply Process" section of the documentation, Streams processes cannot apply a RENAME command, though it can capture it and you can catch it in a DDL handler. But, do you know why?

The answer to that question actually stands in the 2 commands below:

alter session set current_schema=scott;

rename emp to emp2;
*
ERROR at line 1:
ORA-03001: unimplemented feature

For some obscure reasons RENAME cannot be used after current_schema has been changed. Unfortunately the apply process changes the current schema to apply DDL so that it can be executed, even if the schema impacted by the DDL command is not in the source command. So, rename does not work. Obviously you can write a DDL handler and parse the command to manage that manually. But if you think that's easy, ask yourself why the Streams development team did not to that themselves to provide the feature out-of-the-box... Then run:

create table "alter table rename"(col1 number);

/* alter table rename */ rename "alter table rename" to "rename";

There is a few remaining mystery, though: If ALTER TABLE ... RENAME TO ... doesn't suffer those limits, why do DBA and developers execute RENAME to rename a table? Will that change in 11g Release 2?