Paràmetres de configuració

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Paràmetres de configuració d'Oracle DB¶

Els paràmetres de configuració de la base de dades, també coneguts com a paràmetres d'inicialització, defineixen el comportament i la configuració de la instància d'Oracle. Segons si es poden canviar en calent o no, es distingeixen dos tipus:

Dinàmics: es poden modificar en temps real, sense reiniciar la base de dades. Poden aplicar-se a nivell de sessió (ALTER SESSION) o a nivell de sistema (ALTER SYSTEM).
Estàtics: requereixen reiniciar la base de dades per aplicar-se. Solen tindre un impacte major en l'arquitectura interna d'Oracle, i les modificacions s'han de fer al SPFILE.

`SPFILE`¶

La configuració de la instància es gestiona mitjançant un fitxer anomenat SPFILE (Server Parameter File), que es llig automàticament quan s'inicia la instància i conté els valors dels paràmetres de configuració.

Segons el sistema operatiu, el SPFILE es troba en una ubicació diferent:

Linux/Unix: $ORACLE_BASE/dbs/spfile<SID>.ora (per exemple, spfileXE.ora).
Windows: %ORACLE_BASE%\database\spfile<SID>.ora.

En aquestes rutes, <SID> (System Identifier) és el nom de la instància.

Es pot consultar el SPFILE en ús amb:

SHOW PARAMETER spfile;

NAME             TYPE        VALUE
---------------- ----------- ------------------------------
spfile           string      /opt/oracle/dbs/spfileXE.ora

Consulta de paràmetres¶

Els paràmetres de configuració es poden consultar de dues maneres:

Des de SQL Developer: al desplegable de la barra lateral esquerra, secció Reports > Database Parameters, es pot triar entre All Parameters o Non-default Parameters.

Figura 1. Informe de paràmetres de configuració.

Amb consultes SQL sobre les vistes del diccionari de dades:

v$parameter: paràmetres de configuració actuals (nivell de sessió).
v$system_parameter: paràmetres que es poden canviar en temps d'execució (nivell de sistema).
v$spparameter: paràmetres del fitxer spfile, que només es poden canviar reiniciant la base de dades.

ud1/parameters.sql

-- Paràmetres de la sessió actual
select name, value, default_value, ismodified
from v$parameter
where name in ('nls_date_format', 'nls_timestamp_format', 'nls_numeric_characters', 'optimizer_mode', 'query_rewrite_enabled', 'recyclebin');

-- Paràmetres del sistema
select name, value, default_value, ismodified
from v$system_parameter
where name in ('nls_date_format', 'nls_timestamp_format', 'nls_numeric_characters', 'optimizer_mode', 'query_rewrite_enabled', 'recyclebin');

-- Paràmetres del fitxer spfile
select *
from v$spparameter
where name in ('nls_date_format', 'nls_timestamp_format', 'nls_numeric_characters', 'optimizer_mode', 'query_rewrite_enabled', 'recyclebin');

Modificació de paràmetres¶

Un paràmetre es pot modificar només per a la sessió actual o per a tota la instància. La sintaxi general és:

ALTER SESSION SET <parametre> = <valor>;
ALTER SYSTEM SET <parametre> = <valor> [SCOPE = {SPFILE | MEMORY | BOTH}];

ALTER SESSION: aplica el canvi només a la sessió actual.
ALTER SYSTEM: aplica el canvi a tot el sistema (totes les sessions).

SCOPE: (opcional) indica on i quan té efecte el canvi. Només s'utilitza amb ALTER SYSTEM.

Valors de SCOPE

Valor	Significat
`SPFILE`	Modifica el valor al `SPFILE`. S'aplica en la següent arrancada de la instància
`MEMORY`	Té efecte immediat, però no es guarda al `SPFILE` (es perd en reiniciar)
`BOTH`	El canvi es fa tant en memòria com al `SPFILE`

Només els paràmetres dinàmics permeten SCOPE=MEMORY o SCOPE=BOTH. Els estàtics només poden canviar-se amb SCOPE=SPFILE (requereixen reinici).

Documentació oficial d'Oracle DB

Exercici: canvi de paràmetres

Paràmetre	Valor
`nls_date_format`	`DD/MM/YYYY`
`nls_timestamp_format`	`DD/MM/YYYY HH24:MI:SS`
`nls_numeric_characters`	`,`
`optimizer_mode`	`First_rows_100`
`query_rewrite_enabled`	`false`
`recyclebin`	`off`

Modifica els paràmetres anteriors a nivell de sessió.
Comprova els canvis realitzats a nivell de sessió (vista NLS_SESSION_PARAMETERS).
Modifica els mateixos paràmetres a nivell de sistema, amb els canvis permanents (requereix reiniciar la base de dades).
Comprova els canvis realitzats a nivell de sistema (vista v$system_parameter).

Solució

ALTER SESSION SET NLS_DATE_FORMAT ='DD/MM/YYYY';
ALTER SESSION SET nls_timestamp_format ='DD/MM/YYYY HH24:MI:SS';
ALTER SESSION SET nls_numeric_characters =', ';
ALTER SESSION SET optimizer_mode = First_rows_100;
ALTER SESSION SET query_rewrite_enabled = false;
ALTER SESSION SET recyclebin = off;

select *
from NLS_SESSION_PARAMETERS
where parameter in ('NLS_DATE_LANGUAGE', 'NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT',
                    'NLS_TIMESTAMP_TZ_FORMAT', 'NLS_ISO_CURRENCY', 'NLS_TERRITORY');

ALTER SYSTEM SET NLS_DATE_FORMAT ='DD/MM/YYYY' scope=SPFILE;
ALTER SYSTEM SET nls_timestamp_format ='DD/MM/YYYY HH24:MI:SS' scope=SPFILE;
ALTER SYSTEM SET nls_numeric_characters =', ' scope=SPFILE;
ALTER SYSTEM SET optimizer_mode = First_rows_100 scope=BOTH;
ALTER SYSTEM SET query_rewrite_enabled = false scope=BOTH;
ALTER SYSTEM SET recyclebin = off scope=SPFILE;

select *
from v$system_parameter
where name in ('nls_date_format', 'nls_timestamp_format', 'nls_numeric_characters',
               'optimizer_mode', 'query_rewrite_enabled', 'recyclebin');

Exercici: canvi d'idioma

Busca el paràmetre relacionat amb l'idioma de les dates (ajuda't de v$nls_parameters) i mostra'n el valor actual.
Assigna-li el valor corresponent a l'alemany.
Torna a mostrar el valor del paràmetre.

Mostra el mes actual (haurà de vore's en alemany):

SELECT TO_CHAR(SYSDATE, 'Month') AS mes FROM dual;

Torna a deixar el valor per defecte i comprova que el mes torna a mostrar-se en l'idioma per defecte.

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.