Oracle VARCHAR2() төрлийн хамгийн их уртыг тохируулах талаар сонирхуулъя.
Oracle Database 12c -с хойш өгөгдлийн VARCHAR2() төрөлд хамгийн ихдээ 32767 byte хэмжээг зааж өгдөг болсон.
Oracle нь хамгийн их хэмжээг хянах MAX_STRING_SIZE параметрийг ашигладаг . Хэрэв MAX_STRING_SIZE -н утга нь STANDARD бол хамгийн их хэмжээ нь 4000 byte байна.
SQL> show parameter max_string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL>
a.ta нэртэй table үүсгэе.
SQL> create table a.ta (id number, name varchar2(100));
Table created.
SQL>
a.ta table -н name баганын хэмжээг нэмж үзье. MAX_STRING_SIZE -н 4000 byte байгаа болохоор хэмжээнээс их урттай утга байна гэсэн алдаа зааж байна.
SQL> alter table a.ta modify name varchar2(4001);
alter table a.ta modify name varchar2(4001)
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
MAX_STRING_SIZE -г өөрчилье.
Өгөгдлийн санг унтрааж асаана.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Асаахдаа UPGRADE горимд асаана.
SQL> startup upgrade
MAX_STRING_SIZE параметрийн утгыг EXTENDED болгож өөрчилнө.
SQL> alter system set max_string_size=EXTENDED;
System altered.
SQL>
$ORACLE_HOME/rdbms/admin -фолдерт байгаа utl32k.sql скриптийг ажиллуулна.
SQL> @ $ORACLE_HOME/rdbms/admin/utl32k.sql
Session altered.
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.
1 row 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
--------------------------------------------------------------------------------
11/28/2023 14:48:35.722450000
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.
Session altered.
SQL>
Өгөгдлийн санг унтрааж асаах
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
MAX_STRING_SIZE -н утга EXTENDED болсон байна.
SQL> show parameter max_string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
SQL>
a.ta table -н name баганыг 32767 byte болгож өөрчилж үзье.
SQL> alter table a.ta modify name varchar2(32767);
Table altered.
SQL> desc a.ta;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(32767)
SQL>