Re: Renaming schema

Andrew,

Okay, this isn't exactly a schema rename. The usual problem is having large tables that can cost a lot of time to physically move. The exchange partition command doesn't physically move anything, but just changes the dictionary, so it is immediate and not related to table size. The key is that the exchange partition command can take a schema prefix on the table. So basically you exchange the table into a partition, then exchange it back into a table. The key is the other table is actually in another schema. As I said, I haven't had a reason to try this myself. And yeah, you'll have to use exp/imp or something like that to move all the other objects, but they are small and fast to exp/imp.

    Two even better ideas: 1) chose schema names carefully, 2) maintain a discipline of creating objects only with scripts. To change schemas, you can re-execute the scripts. This is the ITIL model of implementing production changes only after they have been implemented on a test instance. The script allows you to do the same actions on production as you did on test. This is also known as a controlled environment. ;-)

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l