Renaming a Column
Administration Tips
How do you rename a column in Oracle 7, 8 or 8i? The short answer is that, in anything earlier than 9i, you can't. A couple of workarounds spring to mind, however. You could create a view on the table, with the view definition containing the new column name. For example, given an EMP table defined thus: CREATE TABLE EMP(
EMPNO NUMBER, JOB CHAR(10), SAL NUMBER); ...we could create a view on it like this: CREATE VIEW BLAHEMP AS SELECT EMPNO EMPLOYEE, JOB JOB_DESC, SAL SALARY FROM EMP;
The syntax is, effectively, 'select real_column <space> column_alias...'. Now we can do a 'select * from blahemp' and get the following result: EMPLOYEE ---------7369 7499 7521 7566 7654 7698 7782
JOB_DESC --------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER
SALARY ---------800 1600 1250 2975 1250 2850 2450
(Notice the new column aliases are displayed, as though they were the real column names). Of course, creating a new object such as a view means that you'll need to re-jig all your permission grants, so that Users have access to the new view (and probably lose direct access to the base table -otherwise what was the point in creating the view in the first place?!) The other possible workaround is to use the CTAS command (Create Table ... As Select ...) to create a completely new table containing the right column names: Copyright Š Howard Rogers 2001
10/17/2001
Page 1 of 2
Renaming a Column
CREATE TABLE NEWEMP
Administration Tips
(EMPLOYEE,
JOB_DESC,SALARY) AS SELECT EMPNO, JOB,SAL FROM EMP;
Assuming you still want the table to be known as "EMP" and not "NEWEMP", you then have to peform the following actions: DROP TABLE EMP; CREATE TABLE EMP AS SELECT
*
FROM NEWEMP;
DROP TABLE NEWEMP;
...but this is an expensive option: it requires two full tablescans (one of the original table and one of the new version), the new table has no constraints on it, nor any indexes, and all Users that had rights to EMP have no rights whatsoever to the newly-minted EMP, even though it's name is the same (because its object number has nevertheless changed) -so you have to re-create all constraints and indexes, and re-grant all permissions. You can make the exercise rather less expensive by sticking the "nologging" clause in the CTAS command: CREATE TABLE NEWEMP NOLOGGING AS SELECT
...
FROM EMP;
...but that still doesn't get around the business of full tablescans being done all over the place, and introduces the requirement to perform a new backup of EMP, since "nologging" renders an object effectively unrecoverable until a new backup has been successfuly taken. For how you pull the trick off in 9i, see the "How do I rename a column in 9i?" tip.
Copyright Š Howard Rogers 2001
10/17/2001
Page 2 of 2