주:

이 사용지침서에서는 Oracle Cloud에 접근해야 합니다. 무료 계정에 등록하려면 Oracle Cloud Infrastructure Free Tier 시작하기를 참조하십시오.
Oracle Cloud Infrastructure 인증서, 테넌시 및 구획에 대한 예제 값을 사용합니다. 실습을 완료하면 이러한 값을 클라우드 환경에 고유한 값으로 대체합니다.

Oracle 데이터베이스에 대한 확장 데이터 유형 기능 사용

소개

Oracle Database 12c에는 VARCHAR2, NVARCHAR2 및 RAW 데이터 유형의 최대 크기를 제어하는 MAX_STRING_SIZE 파라미터가 도입되었습니다. MAX_STRING_SIZE 매개변수를 EXTENDED로 설정하면 데이터 유형 크기가 32767바이트 제한으로 설정됩니다. MAX_STRING_SIZE를 EXTENDED 값으로 설정하는 동안 COMPATIBLE 데이터베이스 초기화 매개변수는 12.0.0.0 이상이어야 합니다.

이 사용지침서에서는 특정 업그레이드 작업이 필요한 새 확장 데이터 유형을 활용할 수 있도록 Oracle Database를 사용으로 설정하는 작업에 대해 자세히 설명합니다.

목표

지원 VARCHAR2, NVARCHAR2 및 RAW 데이터 유형을 32767바이트 제한으로 활용할 수 있습니다.

필요 조건

데이터베이스 버전은 Oracle 12.2 이상이어야 합니다.

작업 1: 비컨테이너 데이터베이스에서 확장 데이터 유형 설정

다음 명령을 실행합니다.

SQL> SHOW PARAMETER MAX_STRING_

| NAME            | TYPE   | VALUE    |
| --------------- | ------ | -------- |
| max_string_size | string | STANDARD |

업그레이드 모드에서 데이터베이스를 시작합니다.

SQL> SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>STARTUP UPGRADE

ORACLE instance started.

Total System Global Area   1.4663E+10 bytes
Fixed Size                 15697000 bytes
Variable Size              1.1878E+10 bytes
Database Buffers           2717908992 bytes
Redo Buffers               51404800 bytes

Database mounted.
Database opened.

MAX_STRING_SIZE 값을 EXTENDED로 변경합니다.

SQL>  alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;

System altered.

SQL> show parameter MAX_STRING_SIZE

| NAME            | TYPE   | VALUE    |
| --------------- | ------ | -------- |
| max_string_size | string | EXTENDED |

utl32k.sql 스크립트 @?/rdbms/admin/utl32k.sql를 실행합니다.

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

Session altered.

1524 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

PL/SQL procedure successfully completed.

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if we encountered an error while modifying a column to
DOC> account for data type length change as a result of enabling or
DOC> disabling 32k types.
DOC>
DOC> Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

데이터베이스를 재시작합니다.

shutdown immediate;
startup

SQL> show parameter max_string

| NAME            | TYPE   | VALUE    |
| --------------- | ------ | -------- |
| max_string_size | string | EXTENDED |

작업 2: 컨테이너 데이터베이스에서 확장 데이터 유형 설정(CDB$ROOT)

다음 명령을 실행합니다.

[oracle@vm4 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 13:26:41 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> SHOW PARAMETER MAX_STRING_

| NAME            | TYPE   | VALUE    |
| --------------- | ------ | -------- |
| max_string_size | string | STANDARD |

SQL> show pdbs;

| CON_ID | CON_NAME    | OPEN MODE  | RESTRICTED |
| ------ | ----------- | ---------- | ---------- |
| 2      | PDB$SEED    | READ ONLY  | NO         |
| 3      | RATPRD_PDB1 | READ WRITE | NO         |

[oracle@vm4 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@vm4 admin]$ pwd
/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin
[oracle@vm4 ~]$  mkdir -p /home/oracle/mydir/utl32k_cdb_pdbs_output
[oracle@vm4 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 13:30:18 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

MAX_STRING_SIZE 값을 EXTENDED로 변경합니다.

SQL> ALTER SYSTEM SET MAX_STRING_SIZE=extended SCOPE=SPFILE;

System altered.

업그레이드 모드에서 데이터베이스를 시작합니다.

SQL> shutdown;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 3.2749E+10 bytes
Fixed Size                 12351112 bytes
Variable Size            4160751992 bytes
Database Buffers         2.8387E+10 bytes
Redo Buffers              188973056 bytes

Database mounted.
Database opened.

SQL> exit

catcon.pl 스크립트를 실행합니다.

주: catcon.pl 스크립트를 사용하여 루트 및 CDB의 모든 PDB에서 rdbms/admin/utl32k.sql 스크립트를 실행하여 VARCHAR2, NVARCHAR2 및 RAW 열의 최대 크기를 늘릴 수 있습니다. -force_pdb_mode 'UPGRADE' 옵션은 애플리케이션 루트 복제본을 포함한 모든 PDB가 마이그레이션 모드로 열리도록 하는 데 사용됩니다. 메시지가 나타나면 SYS 암호를 입력합니다.

[oracle@vm4 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/mydir/utl32k_cdb_pdbs_output' -b utl32k_cdb_pdbs_output utl32k.sql
catcon: ALL catcon-related output will be written to [/home/oracle/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_catcon_88242.lst]
catcon: See [/home/oracle/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output*.log] files for output generated by scripts
catcon: See [/home/oracle/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_*.lst] files for spool files, if any
Enter Password:    -> enter sys password
catcon.pl: completed successfully
[oracle@vm4 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 13:48:56 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3.2749E+10 bytes
Fixed Size                 12351112 bytes
Variable Size            4160751992 bytes
Database Buffers         2.8387E+10 bytes
Redo Buffers              188973056 bytes

Database mounted.
Database opened.

SQL> show parameter max_string_size;

| NAME            | TYPE   | VALUE    |
| --------------- | ------ | -------- |
| max_string_size | string | EXTENDED |

작업 3: PDB에서 확장된 데이터 유형 설정

다음 명령을 실행하여 데이터베이스를 변경합니다.

SQL> alter session set container = RATPRD_PDB1;

Session altered.

SQL> show pdbs

| CON_ID | CON_NAME    | OPEN MODE  | RESTRICTED |
| ------ | ----------- | ---------- | ---------- |
| 3      | RATPRD_PDB1 | READ WRITE | NO         |

SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 CLOSE IMMEDIATE;

Pluggable database altered.

SQL>  show pdbs

| CON_ID | CON_NAME    | OPEN MODE | RESTRICTED |
| ------ | ----------- | --------- | ---------- |
| 3      | RATPRD_PDB1 | MOUNTED   |

max_string_size 매개변수를 수정하는 데 필요한 업그레이드 모드에서 PDB RATPRD_PDB1를 엽니다.

SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 OPEN UPGRADE;

$
Pluggable database altered.

SQL>  show pdbs;

| CON_ID | CON_NAME    | OPEN MODE | RESTRICTED |
| ------ | ----------- | --------- | ---------- |
| 3      | RATPRD_PDB1 | MIGRATE   | YES        |

utl32k.sql을 실행하여 PDB RATPRD_PDB1에서 VARCHAR2,NVARCHAR2 및 RAW 열의 최대 크기를 수정합니다.

SQL> @$ORACLE_HOME/rdbms/admin/utl32k.sql;

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

PL/SQL procedure successfully completed.

STARTTIME :

09/07/2023 14:32:06.790646000

PL/SQL procedure successfully completed.

No errors.

PL/SQL procedure successfully completed.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if we encountered an error while modifying a column to
DOC> account for data type length change as a result of enabling or
DOC> disabling 32k types.
DOC>
DOC> Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

SQL> show pdbs;

| CON_ID | CON_NAME    | OPEN MODE | RESTRICTED |
| ------ | ----------- | --------- | ---------- |
| 3      | RATPRD_PDB1 | MIGRATE   | YES        |

SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 CLOSE IMMEDIATE;

Pluggable database altered.

max_string_size 매개변수 값이 EXTENDED로 변경되었는지 검증합니다.

SQL> ALTER PLUGGABLE DATABASE RATPRD_PDB1 open ;

Pluggable database altered.

SQL>  show pdbs;

| CON_ID | CON_NAME    | OPEN MODE  | RESTRICTED |
| ------ | ----------- | ---------- | ---------- |
| 3      | RATPRD_PDB1 | READ WRITE | NO         |

SQL> show parameter max_string_size;

| NAME            | TYPE   | VALUE    |
| --------------- | ------ | -------- |
| max_string_size | string | EXTENDED |

Oracle Database 참조: MAX_STRING_SIZE

수락

작성자 - Hakim Ahamad(주요 클라우드 설계자)

추가 학습 자원

docs.oracle.com/learn에서 다른 실습을 살펴보거나 Oracle Learning YouTube 채널에서 더 많은 무료 학습 콘텐츠에 액세스하십시오. 또한 Oracle Learning Explorer가 되려면 education.oracle.com/learning-explorer를 방문하십시오.

제품 설명서는 Oracle Help Center를 참조하십시오.

제목 및 저작권 정보

Enable the Extended Data Type Capability for Oracle databases

F87592-01

October 2023