How to change schema name?

oracle plsql schema oracle11gr2
adelak picture adelak · Sep 11, 2013 · Viewed 65.1k times · Source

I have created a user:

CREATE USER gds_map
IDENTIFIED BY gds_map;

And now I need to change a name. I tried to update or find other way but have not found nothing yet.

I will be glad of any hint.

Answer

mohamed stitane picture mohamed stitane · Nov 9, 2015

If you want to modify a schema's name,you should have the preveledegs on USER$

1. Get Id of a schema's name

SQL> select user#,NAME from SYS.user$ WHERE NAME='TEST';
USER# NAME
---------- ------------------------------
*93* TEST

2. modify the schema's name 

SQL> UPDATE USER$ SET NAME='NEW_SCHEMA_NAME' WHERE USER#=93;

3. finished commit 

SQL> COMMIT;

4. modify the system SCN

SQL> ALTER SYSTEM CHECKPOINT;

5.Then refresh shared_pool

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

6. Modify the new schema's password

SQL> ALTER USER new_schema  IDENTIFIED BY new_pass;

Related questions

MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?

I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error: ORA-01451: column to …

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More