Oracle 11.2.0.2新特性——用户重命名(Rename User)
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
Categories: New Feature
最近评论