Renaming Schema Name
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Renaming Schema Name

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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.

  2. #2
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    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?




  3. #3
    Join Date
    May 2001
    Posts
    41
    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.




  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Can't I just change the schema name in user$ ?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  6. #6
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    No, you shouldn't directly modify such tables, which are owned by SYS.

  7. #7
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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 ?

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width