컬럼사이즈 변경 및 데이터타입 변경
테이블의 컬럼사이즈를 변경해달라는 요청이 가끔 있는데요, (저도 지원은 해주지만 까먹기 때문에...) 블로그에 기록차 적어놓고 한번더 정리하는 글을 남기려고 합니다.
컬럼사이즈변경
명령어
alter table [테이블명] modify ([컬럼명] [데이터타입])
특징
- 컬럼사이즈 늘리는것은 바로 적용할 수 있습니다. (ex varchar2(20) -> varchar2(100))
- 컬럼사이즈를 줄이는것은 아래와 같은 제약사항이 있습니다.
변경을 하려는 컬럼에 값에 변경하려는 컬럼보다 큰 값이 들어가 있으면 안됩니다. 그렇지 않으면 ORA 에러가 발생합니다.
ORA-01440: column to be modified must be empty to decrease precision or scale
컬럼사이즈 늘리기
시나리오1
TEST 유저의 EMP 테이블의 컬럼을 늘려보자. JOB 컬럼의 값을 varchar2(10)으로 늘리자.
TEST@ysbae> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
TEST@ysbae> desc emp
Name Type
------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> alter table emp modify (job varchar2(10));
Table altered.
TEST@ysbae> desc emp
Name Type
------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
별다른 에러없이 늘려지는 것을 확인할 수 있습니다.
시나리오2
EMPNO에 PK를 지정하고 컬럼값을 NUMBER(10)으로 늘려보자
TEST@ysbae> alter table emp add constraint emp_pk primary key(empno);
Table altered.
TEST@ysbae> desc emp
Name Null? Type
--------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name='EMP';
OWNER CONSTRAINT_NAME C TABLE_NAME
-------------------- ----------------------------- -
TEST EMP_PK P EMP
EMP_PK이름의 PK가 생성된 것을 확인할 수 있고, 컬럼사이즈를 늘려보겠습니다.
TEST@ysbae> alter table emp modify (empno number(10));
Table altered.
TEST@ysbae> desc emp;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(10)
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
PK도 컬럼사이즈를 늘려주는것에는 잘 적용됩니다.
컬럼사이즈 줄이기
앞서 이야기 했듯, 컬럼에 변경하려는 값보다 큰 값이 있으면 변경이되지 않습니다.
시나리오1
컬럼값보다 크게 컬럼사이즈 줄여보자
ENAME의 사이즈를 varchar2(10) -> varchar2(6)으로 변경해보자. 컬럼값은 6자리가 넘지않는것을 확인하고 진행합니다.
TEST@ysbae> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------ --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
TEST@ysbae> desc emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(10)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> alter table emp modify (ename varchar2(6));
Table altered.
Elapsed: 00:00:00.01
TEST@ysbae> desc emp;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(10)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
시나리오2
PK의 컬럼값을 NUMBER(9)로 줄여보자
TEST@ysbae> alter table emp modify (empno number(9));
ORA-01440: column to be modified must be empty to decrease precision or scale
EMPNO의 컬럼값은 전부 4자리의 NUMBER값임에도 불구하고 NUMBER(9)로 변경 시 ORA에러를 발생시키며 되지 않습니다.
아래와 같은 방법으로 변경할 수 있습니다.CTAS 후에 기본테이블 내용을 TRUNCATE 시키고 컬럼사이즈 변경 후 값을 insert 하는 것입니다.
TEST@ysbae> create table emp_bak as select * from emp;
Table created.
TEST@ysbae> truncate table emp;
Table truncated.
TEST@ysbae> alter table emp modify (empno number(9));
Table altered.
TEST@ysbae> desc emp;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(9)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> insert into emp select * from emp_bak;
14 rows created.
TEST@ysbae> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------ --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
TEST@ysbae> desc emp;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(9)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> drop table emp_bak;
Table dropped.
데이터타입변경
데이터타입 변경은 컬럼에 값이 있으면 에러를 발생시키며 되지 않습니다.
ORA-01439: column to be modified must be empty to change datatype
명령어는 컬럼사이즈 변경과 동일하며 방법또한 컬럼사이즈 줄이는 방법과 동일하게 진행하면 됩니다.
시나리오1
SAL 컬럼을 VARCHAR2(10)으로 변경하라
TEST@ysbae> create table emp_bak as select * from emp;
Table created.
TEST@ysbae> truncate table emp;
Table truncated.
TEST@ysbae> alter table emp modify (sal varchar2(10));
Table altered.
TEST@ysbae> insert into emp select * from emp_Bak;
14 rows created.
TEST@ysbae> desc emp;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(9)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL VARCHAR2(10)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------ --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
시나리오2
PK의 데이터타입을 VARCHAR2(5)로 변경하고 인덱스의 상태를 확인하라
이번에는 dbms_metadata.get_ddl 를사용하여 DDL문을 추출하여 컬럼데이터타입을 변경해보겠습니다.
set linesize 200
set longchunksize 200000
set long 200000
set pages 0
TEST@ysbae> select dbms_metadata.get_ddl('TABLE','EMP','TEST') as DDL_QUERY from dual;
CREATE TABLE "TEST"."EMP"
( "EMPNO" NUMBER(9,0),
"ENAME" VARCHAR2(6),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" VARCHAR2(10),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
1 row selected.
TEST@ysbae> create table emp_bak2 as select * from emp;
Table created.
TEST@ysbae> drop table emp;
Table dropped.
CREATE TABLE "TEST"."EMP"
( "EMPNO" VARCHAR2(5),
"ENAME" VARCHAR2(6),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" VARCHAR2(10),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Table created.
TEST@ysbae> desc emp;
Name Null? Type
-------- -------- -------------
EMPNO NOT NULL VARCHAR2(5)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL VARCHAR2(10)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST@ysbae> insert into emp select * from emp_bak2;
14 rows created.
TEST@ysbae> select * from emp;
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10
14 rows selected.
인덱스 상태 확인
TEST@ysbae> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name='EMP';
OWNER CONSTRAINT_NAME C TABLE_NAME
-------------------- ----------------------------- -
TEST EMP_PK P EMP
TEST@ysbae> select index_name,index_type,table_name,status from user_indexes where table_name='EMP';
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS
-------------------------------------------------------------
EMP_PK NORMAL EMP VALID
dbms_metadata.get_ddl을 사용하여 DDL문을 추출하여 사용하는 방법도 사용해보았습니다. 덫붙여 테이블 재생성시 PK를 지정하면서 생성하기 때문에 인덱스가 정상적으로 생성되어 있고, 상태도 VALID인것을 확인할 수 있습니다.
시나리오3
컬럼을 하나 더 추가하여 변경된 데이터타입으로 INSERT합니다.
SAL 컬럼을 NUMBER(10)으로 변경하라.(SAL2 컬럼을 생성하여 변경할 예정)
TEST@ysbae> alter table emp add (sal2 number(10));
Table altered.
TEST@ysbae> select * from emp;
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20 (null)
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 (null)
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 (null)
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20 (null)
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 (null)
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30 (null)
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10 (null)
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20 (null)
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10 (null)
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 (null)
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20 (null)
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30 (null)
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20 (null)
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10 (null)
14 rows selected.
TEST@ysbae> update emp set sal2=sal;
14 rows updated.
-UPDATE는 순서가 그대로 유지되면서 컬럼에 들어갑니다.
TEST@ysbae> commit;
Commit complete.
TEST@ysbae> select * from emp;
7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 1250
7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 1250
7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30 2850
7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000 (null) 20 3000
7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10 5000
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100 (null) 20 1100
7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30 950
7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20 3000
7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10 1300
14 rows selected.
TEST@ysbae> alter table emp drop (sal);
Table altered.
Elapsed: 00:00:00.63
TEST@ysbae> desc emp;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO NOT NULL VARCHAR2(5)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SAL2 NUMBER(10)
TEST@ysbae> alter table emp add(sal number(10));
Table altered.
TEST@ysbae> update emp set sal=sal2;
14 rows updated.
TEST@ysbae> commit;
TEST@ysbae> desc emp
Name Null? Type
----------- -------- ----------------
EMPNO NOT NULL VARCHAR2(5)
ENAME VARCHAR2(6)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SAL NUMBER(10)
SAL의 데이터타입을 변경하였습니다. 이 방법의 단점은 컬럼 순서가 변경된다는 것입니다. CTAS를 사용한 방법은 컬럼순서까지 동일하게 유지할 수 있으므로 CTAS를 이용한 방법을 추천드립니다.