본문 바로가기
Oracle/운영

컬럼사이즈 변경 및 데이터타입 변경

by 취미툰 2020. 8. 25.
반응형

테이블의 컬럼사이즈를 변경해달라는 요청이 가끔 있는데요, (저도 지원은 해주지만 까먹기 때문에...) 블로그에 기록차 적어놓고 한번더 정리하는 글을 남기려고 합니다.

 

컬럼사이즈변경

명령어

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를 이용한 방법을 추천드립니다.

 

 

반응형

댓글