Nota

Questa esercitazione richiede l'accesso a Oracle Cloud. Per iscriversi a un account gratuito, consulta Inizia a utilizzare Oracle Cloud Infrastructure Free Tier.
Utilizza valori di esempio per le credenziali, la tenancy e i compartimenti di Oracle Cloud Infrastructure. Al termine del laboratorio, sostituisci questi valori con quelli specifici del tuo ambiente cloud.

Abilita la funzionalità estesa del tipo di dati per i database Oracle

Introduzione

Oracle Database 12c ha introdotto il parametro MAX_STRING_SIZE che controlla la dimensione massima dei tipi di dati VARCHAR2, NVARCHAR2 e RAW. L'impostazione del parametro MAX_STRING_SIZE su EXTENDED consente di impostare la dimensione del tipo di dati su un limite di 32767 byte. Durante l'impostazione del valore MAX_STRING_SIZE su EXTENDED, il parametro di inizializzazione COMPATIBLE del database deve essere 12.0.0.0 o una versione successiva.

Questa esercitazione descrive in dettaglio i task per abilitare Oracle Database a sfruttare i nuovi tipi di dati estesi che richiedono azioni di upgrade specifiche.

Obiettivi

Utilizza i tipi di dati VARCHAR2, NVARCHAR2 e RAW a un limite di 32767 byte.

Prerequisiti

La versione del database deve essere Oracle 12.2 O successiva.

Task 1: impostare il tipo di dati esteso su un database non di tipo container

eseguire il comando seguente.

SQL> SHOW PARAMETER MAX_STRING_

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

Avviare il database in modalità di aggiornamento.

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.

Impostare il valore MAX_STRING_SIZE su 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 |

Eseguire lo script 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.

Riavviare il database.

shutdown immediate;
startup

SQL> show parameter max_string

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

Task 2: impostare il tipo di dati esteso su un container database (CDB$ROOT)

Eseguire i comandi seguenti.

[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.

Impostare il valore MAX_STRING_SIZE su EXTENDED.

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

System altered.

Avviare il database in modalità di aggiornamento.

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

Eseguire lo script catcon.pl.

Nota: utilizzare lo script catcon.pl per eseguire lo script rdbms/admin/utl32k.sql nella radice e in tutti i PDB nel CDB per aumentare la dimensione massima delle colonne VARCHAR2, NVARCHAR2 e RAW. L'opzione -force_pdb_mode 'UPGRADE' viene utilizzata per garantire che tutti i PDB, comprese le copie radice dell'applicazione, siano aperti in modalità di migrazione. Immettere la password SYS quando richiesto.

[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 |

Task 3: impostare il tipo di dati esteso su PDB

Eseguire i comandi riportati di seguito per modificare il database.

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   |

Aprire PDB RATPRD_PDB1 in modalità di aggiornamento, necessario per modificare il parametro max_string_size.

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        |

Eseguire utl32k.sql per modificare la dimensione massima delle colonne VARCHAR2,NVARCHAR2 e RAW sul PDB RATPRD_PDB1.

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.

Convalidare il valore del parametro max_string_size su 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 |

Riferimento per Oracle Database: MAX_STRING_SIZE

Conferme

Autore - Hakim Ahamad (Principal Cloud Architect)

Altre risorse di apprendimento

Esplora altri laboratori su docs.oracle.com/learn o accedi a contenuti di formazione gratuiti sul canale YouTube di Oracle Learning. Inoltre, visitare education.oracle.com/learning-explorer per diventare Explorer di Oracle Learning.

Per la documentazione sul prodotto, visitare il sito Oracle Help Center.

Titolo e informazioni sul copyright

Enable the Extended Data Type Capability for Oracle databases

F87592-01

October 2023