Rename schema?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Rename schema?

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Posts
    67

    Rename schema?

    I need to rename schema junk to junk123. What is the best way - export/import? alter user rename? rename user?

    I see only the export/import as the realistic option - any other suggestions please?????

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    EXPORT/IMPORT is the cleanest way to accomplish this using
    the fromuser= ... touser ... that I'm aware of

    Gregg

  3. #3
    Join Date
    Nov 2002
    Posts
    80
    update sys.obj$ set OWNER# = (select user_id from dba_users where username = 'junk123') where OWNER# = (select user_id from dba_users where username = 'junk');


    I would be interested if the above sql works, I'm not brave enough to try it myself. (best do a backup 1st me thinks)

  4. #4
    Join Date
    Oct 2002
    Posts
    182

    Wink

    interesting SQL, but why bother?

    Just export 'JUNK' objects and import into 'JUNK123'.
    Easy as pie (depending on the pie. Mmmmm Rhubarb ...)

    - Cookies

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by onlysimon
    update sys.obj$ set OWNER# = (select user_id from dba_users where username = 'junk123') where OWNER# = (select user_id from dba_users where username = 'junk');


    I would be interested if the above sql works, I'm not brave enough to try it myself. (best do a backup 1st me thinks)
    Sounds pretty suicidal to me... Especially when you already have a supported method...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    I agree the SQL is suicidal.
    There Nothing You cannot Do, The problem is HOW.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by onlysimon
    I would be interested if the above sql works, I'm not brave enough to try it myself. (best do a backup 1st me thinks)
    Code:
    TEST_SYS> SELECT USER#,NAME FROM USER$ WHERE NAME='JUNK';
    
                   USER# NAME
    -------------------- ------------------------------
                      27 JUNK
    
    TEST_SYS> SELECT USER#,NAME FROM USER$ WHERE NAME='JUNK12';
    
                   USER# NAME
    -------------------- ------------------------------
                      25 JUNK12
    
    TEST_SYS> SELECT * FROM OBJ$ WHERE NAME='TEST_RENAME_USR';
    
                    OBJ#             DATAOBJ#               OWNER# NAME                                      NAMESPACE SUBNAME      
    -------------------- -------------------- -------------------- ------------------------------ -------------------- ----------
                    5935                 5935                   27 TEST_RENAME_USR                                   1              
    
    TEST_SYS> SELECT * FROM col$ WHERE OBJ#=5935;
    
                    OBJ#                 COL#              SEGCOL#         SEGCOLLENGTH               OFFSET NAME                   
    -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                    5935                    1                    1                   22                    0 ID                     
    
    TEST_SYS> UPDATE OBJ$ SET OWNER#=25 WHERE OWNER#=27;
    
    1 row updated.
    
    -- Updated the owner of the tables to JUNK12
    
    TEST_SYS> SELECT * FROM OBJ$ WHERE NAME='TEST_RENAME_USR';
    
                    OBJ#             DATAOBJ#               OWNER# NAME                                      NAMESPACE SUBNAME      
    -------------------- -------------------- -------------------- ------------------------------ -------------------- ----------
                    5935                 5935                   25 TEST_RENAME_USR                                   1              
    
    TEST_SYS> SELECT * FROM col$ WHERE OBJ#=5935;
    
                    OBJ#                 COL#              SEGCOL#         SEGCOLLENGTH               OFFSET NAME                   
    -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                    5935                    1                    1                   22                    0 ID                     
    
    TEST_SYS> COMMIT;
    
    Commit complete.
    
    -- I have now changed the owner from JUNK to JUNK12, Lets see the what happens.
    
    TEST_SYS> CONN JUNK12/JUNK12
    Connected.
    TEST_JUNK12> SELECT * FROM TAB;
    
    TNAME                          TABTYPE            CLUSTERID
    ------------------------------ ------- --------------------
    TEST_RENAME_USR                TABLE
    
    TEST_JUNK12> DESC TEST_RENAME_USR
    ERROR:
    ORA-04043: object TEST_RENAME_USR does not exist
    
    
    TEST_JUNK12> SELECT * FROM TEST_RENAME_USR;
    SELECT * FROM TEST_RENAME_USR
                  *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    TEST_JUNK12> CONN jUNK/jUNK
    Connected.
    TEST_JUNK> SELECT * FROM TAB;
    
    no rows selected
    
    TEST_JUNK> DESC TEST_RENAME_USR
     Name                                                                                                                           
     ----------------------------------------------------------------------------------------------------------------------------
     ID                                                                                                                             
    
    TEST_JUNK> SELECT * FROM TEST_RENAME_USR;
    
                      ID
    --------------------
                       1
    
    
    TEST_JUNK> DROP TABLE TEST_RENAME_USR;
    DROP TABLE TEST_RENAME_USR
               *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [16500], [kqdobu], [D], [0], [27], [TEST_RENAME_USR], [], []
    
     You will only end up in such errors & this is expected 
    TEST_JUNK>
    I think now you know what will happen if you wana play with SYS.xxx$ objects.

    There will be many other things u will have to take care...from above example you can see only the part of the Information of the table is changed to user JUNK12, and actual table contents are still listed under JUNK and not JUNK12...

    Abhay.
    Last edited by abhaysk; 07-04-2003 at 04:38 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2002
    Posts
    80

    Wink

    abhaysk, I appreciate your inquisitiveness.

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