Some largely irrelevant info - We are running 8.1.6 on Windows 2000. Our application creates hundreds of tables containing result sets. In order to produce a final result set, these tables are unioned, intersected or minused (dependant on business rules) to achieve this. This generates masses of code which overflows Oracle's 32K Sql buffer. The code has to include the schema prefix, as we are using different schemas for different things. For example, the code might be "select * from schema.resulttable1 minus (select * from schema.resulttable2) intersect".........etc. If we shorten the schema name from, say, HALIFAX to H, then we will effectively have more room in the Sql buffer. The schema is very complex and is linked into many other repository tables.
So, enough of the waffle.........the question is........can I simply, somehow, rename the schema. I suspect that Oracle will not recommend/support this, but does anyone have experience of doing this successfully ? After all, I believe that the Schema name is just a description for an Object ID. Import\export to a new schema is really not an option ........trust me.
Thanks for the advice guys......
The resultset tables are created dynamically, so the schema is not static, so creating views is not really an option. The same applies for grants and synonyms. Re-doing these after every resultset table is created would more or less put the brakes on the whole process.
Ref Pando's "posibility of corrupting the databse is extremely high "
I know that modifying SYS tables is frowned upon, but isn't the name column in user$ just a description for the user#. Why would the db be corrupted ?
You can first try that thing to modify user$ on a test database and experiment....the problem is the oracle support guys will act cranky if they come to know that you have modifed that user$ column....so weigh out ur position b4 attempting such a feat.