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?????

EXPORT/IMPORT is the cleanest way to accomplish this using
the fromuser= ... touser ... that I'm aware of

Gregg

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)

interesting SQL, but why bother?

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

:rolleyes:

Quote:

---

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...

I agree the SQL is suicidal.

Quote:

---

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.

abhaysk, I appreciate your inquisitiveness. :)