oracle更改使用者名稱

 

--建立測試使用者

create user haohao1 identified by oracle default tablespace users ;

grant connect,dba to haohao1;

SQL> conn haohao1/oracle@10.13.7.219:1521/orcl11g

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as haohao1

SQL> create table test1 (a number(1));

SQL> create table test2 (a number(1));

SQL> select owner,object_name,object_id from dba_objects a where a.owner='HAOHAO1';

OWNER OBJECT_NAME OBJECT_ID

------------------------------ -------------------------------------------------------

HAOHAO1 TEST1 85172

HAOHAO1 TEST2 85173

----修改使用者名稱

SQL> show user

User is "SYS"

SQL> select user#,name,password from user$ where name ='HAOHAO1';

USER# NAME PASSWORD

---------- ------------------------------ ------------------------------

84 HAOHAO1 F671C230A1E05C80

SQL> update user$ set name='HAOHAO' where user#=84;

SQL> COMMIT;

SQL> create user haohao1 identified by oracle default tablespace users;

create user haohao1 identified by oracle default tablespace users

ORA-01920: 使用者名稱 'HAOHAO1' 與另外一個使用者名稱或角色名發生衝突

---強制寫入資料檔案

SQL> alter system checkpoint;

----清楚快取資料字典資訊，強制oracle讀實際資料（即更改後的資料）

SQL> alter system flush shared_pool;

SQL> create user haohao1 identified by oracle default tablespace users;

SQL> grant connect,resource to haohao1;

SQL> commit;

SQL> select user#,name,password from user$ where name ='HAOHAO';

USER# NAME PASSWORD

---------- ------------------------------ ------------------------------

84 HAOHAO F671C230A1E05C80

SQL> select user#,name,password from user$ where name ='HAOHAO1';

USER# NAME PASSWORD

---------- ------------------------------ ------------------------------

85 HAOHAO1 F671C230A1E05C80

SQL> drop user haohao1;

SQL> select user#,name,password from user$ where name ='HAOHAO1';

USER# NAME PASSWORD

---------- ------------------------------ ------------------------------

SQL> conn haohao/oracle@10.13.7.219:1521/orcl11g

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as haohao

SQL> select owner,object_name,object_id from dba_objects a where a.owner='HAOHAO';

OWNER OBJECT_NAME OBJECT_ID

------------------------------ -------------------------------------------------------

HAOHAO TEST1 85172

HAOHAO TEST2 85173