Renaming Schema Name

[Horace]

Hi,

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.

Any advice would be much appreciated.

[millerg]

Horace,

How about creating another user 'H', grant select on all of HALIFAX's table to H, create views in the H schema of each HALIFAX table (with the same names) and select from the H views?

[rameshgiri26]

create one more user and grant the permission on that create the synonyms for the other schema table..


create user xyz identified by passwd default tablespace temporary tablespace temp quota unlimited on ;

grant connect,resource to xyz;
grant select all on abc to xyz; - select,insert, delete like any permision's grant that user.


connect to xyz then create the synonyms related.

[Horace]

Can't I just change the schema name in user$ ?

[pando]

sure you can, make a backup before you do it because the posibility of corrupting the databse is extremely high

do it if you are just playing around with a test database but if it´s production you better leave it alone

[millerg]

No, you shouldn't directly modify such tables, which are owned by SYS.

[Horace]

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 ?

[hrishy]

Hello

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.

regards
Hrishy