SQL--Oracle9i重命名表名和重命名表中的column名

在Oracle 9i以后就可以支持在線重命名table和表的column名　

--9i重命名表名和重命名表中的某column的名：

--語法：
alter table table_name rename column col_old_name to col_new_name;

--查看employee的表結構
SQL> desc scott.employee;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)    Y                        
ENAME    VARCHAR2(10) Y                        
JOB      VARCHAR2(9)  Y                        
MGR      NUMBER(4)    Y                        
HIREDATE DATE         Y                        
SAL      NUMBER(7,2)  Y                        
COMM     NUMBER(7,2)  Y                        
DEPTNO   NUMBER(2)    Y                        

SQL>
--rename column sal to salary
alter table scott.employee rename column sal to salary;
--再查看employee的表結構
SQL> desc scott.employee;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)    Y                        
ENAME    VARCHAR2(10) Y                        
JOB      VARCHAR2(9)  Y                        
MGR      NUMBER(4)    Y                        
HIREDATE DATE         Y                        
SALARY   NUMBER(7,2)  Y                        
COMM     NUMBER(7,2)  Y                        
DEPTNO   NUMBER(2)    Y                        

SQL>
-------------------------------------------------------------------------
--重命名表名
--rename old_table_name to new_table_name;
--你必须是对象的所有者

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as scott

SQL> desc tab;
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
TNAME     VARCHAR2(30)                          
TABTYPE   VARCHAR2(7)  Y                        
CLUSTERID NUMBER       Y                        

SQL> select tname from tab;

TNAME
------------------------------
BONUS
DEPT
EMP
EMPLOYEE
SALGRADE
TEST

6 rows selected

SQL> rename employee to employee1;

Table renamed

SQL> select tname from tab;

TNAME
------------------------------
BONUS
DEPT
EMP
EMPLOYEE1
SALGRADE
TEST

6 rows selected

SQL> rename scott.employee1 to scott.employee;

rename scott.employee1 to scott.employee

ORA-01765: specifying table's owner name is not allowed

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51671/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987/viewspace-51671/