11.2.0.2里新增了一个很有意思的新特性——用户重命名(Rename User),以前俺们都是Rename datafile呀,tablespace呀,Index呀,抑或是constraint之类,没想到User也可以重命名的。据说这个DDL操作的需求是来源于SAP: SAP identifies a specific SAP system by the name of the database schema. If the system is renamed, the schema needs also to be renamed. This happens quite often when a database is copied (i.e. for testing purposes) and the system gets therefore a new name. The schema should also get a new name.
废话不多说,验证一下先:
创建测试用户下一个测试表,查看testusr的ID是84
SQL> CREATE TABLE testusr.emp AS SELECT * FROM scott.emp;
SQL> SELECT USERNAME,USER_ID FROM dba_users WHERE created>(sysdate-1);
USERNAME USER_ID
------------------------------ ----------
TESTUSR 84
启用重命名特性需要修改隐含参数“_enable_rename_user”,并需要在restrict mode下Rename
SQL> CREATE pfile FROM spfile;
[oracle@cdcjp47 dbhome_1]$ vi dbs/initeastdb.ora
-- 添加
*._enable_rename_user=TRUE
SQL> shutdown immediate
SQL> startup restrict pfile='?/dbs/initeastdb.ora'
SQL> ALTER user testusr RENAME TO testusr1 IDENTIFIED BY Welcome1;
SQL> SELECT USERNAME,USER_ID FROM dba_users WHERE created>(sysdate-1);
USERNAME USER_ID
------------------------------ ----------
TESTUSR1 84
-- 看看数据,都在的,说明Rename User并不影响Object里面的内容
SQL> SELECT * FROM testusr1.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- ---------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
注意新用户名不要和现有用户名重名
SQL> ALTER user testusr1 RENAME TO scott IDENTIFIED BY 123;
ALTER user testusr1 RENAME TO scott IDENTIFIED BY 123
*
ERROR IN Line 1:
ORA-00604: error occurred at recursive SQL level 1.
ORA-00001:UNIQUE constraint (SYS.I_USER1) violated
Oracle 11.2.0.2新特性——用户重命名(Rename User)
最新推荐文章于 2024-05-07 14:35:09 发布