Introduction PL/SQL

Divers pas de Commentaire »

PL/SQL est un langage qui intègre SQL et permet de programmer de manière procédurale. Il est spécifique ? Oracle. Pour SQL Server il existe par exemple un équivallent : TRANSAC SQL

Globalement, avec PL/SQL on aura ? notre disposition un vrai langage de programmation moins intuitif mais aussi plus puissant que le SQL. Les 2 langages sont éminemment complémentaires.

Les principaux avantages / inconvénients sont les suivants :
- ne dispense pas de connaître le SQL,
- permet le traitement par bloc de SQL et donc optimise le trafic réseau,
- l’utilisation de variable de stockage et de type simple et strucuré dynamique (%TYPE, %ROWTYPE, etc)
- des traitements plus complexes, notamment pour la gestion des cas particuliers et des erreurs (traitement des exceptions),
- l’utilisation de librairies standards prédéfinies (supplied PLSQL packages, comme les RDBMS_xxx),
- un paramétrage et la création d’ordres SQL dynamiques.

Le PL/SQL peut être utilisé sous 3 formes :

* un bloc de code, executé comme une commande SQL, viua un interpréteur standard (SQL+ ou iSQL*PLus)
* un fichier de commande PL/SQL
* un programme stocké (procédure, fonction, package ou trigger)

ordres SQL supportés dans PL/SQL

Les instructions du langage de manipulation de données (LMD) et certaines instructions de gestion de transaction, ? savoir :
- INSERT, UPDATE, DELETE, SELECT,
- COMMIT, ROLLBACK, SAVEPOINT,
- LOCK TABLE,
- SET TRANSACTION READ ONLY.
Avec les versions antérieures ? la 9i, Les ordres du LDD ne sont pas supportés par défaut il faut utiliser un package spécial comme dans le script suivant.

Autres composants du langage PL/SQL

Les principales autres instructions (non SQL) du PL/SQL sont les suivantes :
CLOSE, EXECUTE IMMEDIATE, FETCH, FORALL, GOTO, IF, LOOP, NULL, OPEN, FOR, RAISE, RETURN

A cela s’ajoute les opérateurs :
d’affectation : ‘:=’, ‘(SELECT) INTO’ et ‘(FETCH) INTO’
+ les opérateurs classqiues du SQL : ||, >, >, >=, <=, <>, !=, …

La liste des mots réservés PL/SQL est disponible ici,
http://didier.deleglise.free.fr/plsql/plsql_mots_reserves.pdf…au format PDF.


Blocs et sections PL/SQL

Les blocs de code s’appellent également des blocs anonymes.
Ils commenceront simplement par un ‘BEGIN’ ou un ‘DECLARE’.
Ils sont composés de 1 ? 3 sections :
Type de section obligatoire ? Mots clés
section déclarative non DECLARE…
section exécutable oui BEGIN …END;
section de traitement des exceptions non EXCEPTION

la section ‘exception’ quand elle est présente est incluse dans la section exécutable et NON PAS ? la suite de celle ci.

(DD is proud to announce…)le plus petit bloc PL/SQL au monde:

SQL> BEGIN
NULL;
END;
.
/
Procedure PL/SQL terminee avec succes.

le bloc ne peut pas être vide et doit contenir au moins une instruction…le ‘.’ sous SQL*PLus sert a arrêter la saisie du code PL/SQL et le ‘/’ comme toujours ? executer le contenu du buffer de commande courant.

avec une déclaration de variable un (tout petit) bloc PL/SQL ca donne ça :

SQL> DECLARE x INTEGER;
BEGIN
X := 1;
END;
.
Procedure PL/SQL terminee avec succes.

un bloc avec des exceptions a donc la structure suivante :

DECLARE — mes déclarations de variables…
BEGIN — début de la section executable
– mes ordres SQL et PLSQL
EXCEPTION
– mon traitement des exceptions
END; — la fin du bloc executable

Gestion des exceptions

Les exceptions peuvent se déclencher automatiquement en cas d’erreur système ou de pb réseau par exemple, ou explicitement avec l’instruction ‘RAISE’

Il existe des exception prédéfinies, des exceptions génériques et des exceptions définies par l’utilisateur.

exemple :

DECLARE S VARCHAR(15);
BEGIN
S := ‘Salut’;
DBMS_OUTPUT.PUT_LINE(S||’ les gars’);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘On a une exception générique ici’);
END;

WHEN OTHERS permet de gérer toutes les exceptions qui ne sont pas prédéfinies dans Oracle.
De manière classique en utilisant le SQLCODE (le code de retour donnant le no de l’erreur Oracle survenue)


EXCEPTION
— pb de connexion, une exception prédéfinie
WHEN LOGIN_DENIED Then

— dans tous les autres cas…
WHEN OTHERS THEN
If SQLCODE = … Then …
Elsif SQLCODE = … Then …

End if ;
END;

Voici la liste de ces dernières :

Nom exception Code Erreur
———————— ———–
ACCESS_INTO_NULL ORA-06530
CASE_NOT_FOUND ORA-06592
COLLECTION_IS_NULL ORA-06531
CURSOR_ALREADY_OPEN ORA-06511
DUP_VAL_ON_INDEX ORA-00001
INVALID_CURSOR ORA-01001
INVALID_NUMBER ORA-01722
LOGIN_DENIED ORA-01017
NO_DATA_FOUND ORA-01403
NOT_LOGGED_ON ORA-01012
PROGRAM_ERROR ORA-06501
ROWTYPE_MISMATCH ORA-06504
SELF_IS_NULL ORA-30625
STORAGE_ERROR ORA-06500
SUBSCRIPT_BEYOND_COUNT ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
SYS_INVALID_ROWID ORA-01410
TIMEOUT_ON_RESOURCE ORA-00051
TOO_MANY_ROWS ORA-01422
VALUE_ERROR ORA-06502
ZERO_DIVIDE ORA-01476

NO_DATA_FOUND est fréquemment utilisée dans les programmes, lorsqu’on veut finir une boucle de lecture de lignes par exemple.
LOGIN_DENIED également, acr les pbs de connexions sont en pratique assez nombreux.

Contraintes d’intégrité

Administration Oracle, Divers pas de Commentaire »

Les contraintes sont des règles de gestion, qui doivent (normalement) être vérifiées lors des mises ? jour (INSERT, UPDATE, DELETE).
Ces contrôles sont centralisés dans la base, au niveau de la structure de la table elle même et non pas déportées sur l’application cliente.
Une mise ? jour qui ne satisfait pas une contrainte, déclenche une erreur d’exécution :’…CONSTRAINT VIOLATED…’
Dans certains cas les contraintes peuvent être momentanément invalidées (DISABLEd ou DEFERRED).

Il y a plusieurs types de contraintes :

- NULL : valeur indéfinie (??)
- NOT NULL : valeur obligatoire (non indéfinie)
- DEFAULT : une valeur par defaut si non renseignée
- UNIQUE : !
- PRIMARY KEY : clé primaire (identifiant de la table, obligatoire ET unique)
- FOREIGN KEY : clé étrangère ‘colonne qui référence la clé primaire d’une autre table)
- CHECK : doit vérifier une condition

On peut créer des contraintes directement avec l’instruction ‘CREATE TABLE’ ou en ajouter / modifier / supprimer, respectivement avec les instructions ‘ALTER TABLE ADD | MODIFY |DROP CONSTRAINT …’

exemples de contraintes

– exemple de table avec des colonnes …
– et des contrainte NOT NULL, UNIQUE, PRIMARY KEY et CHECK

SQL> DROP TABLE employe_dd;

SQL> CREATE TABLE employe_dd
( employee_id NUMBER(6) PRIMARY KEY
, last_name VARCHAR2(25) NOT NULL
, salary NUMBER(8,2)
, CONSTRAINT emp_salary_min_dd CHECK (salary > 0)
, email VARCHAR2(25) NOT NULL
, CONSTRAINT emp_email_dd UNIQUE (email)
, commission_pct NUMBER(2,2)
, department_id NUMBER(4)
) ;

– exemple de table avec clé primaire et étrangère NOMMEES rajoutées ? posteriori
– ici la contrainte NOT NULL est nommée…

SQL> DROP TABLE departement_dd;

SQL> CREATE TABLE departement_dd
( department_id NUMBER(4)
, department_name VARCHAR2(30) CONSTRAINT dept_name_dd_nn NOT NULL
, location_id NUMBER(4)
) ;

SQL> ALTER TABLE departement_dd
ADD ( CONSTRAINT dept_id_dd_pk PRIMARY KEY (department_id));

SQL> ALTER TABLE employe_dd
ADD ( CONSTRAINT emp_dept_dd_fk FOREIGN KEY (department_id)
REFERENCES departement_dd );

Note : la contrainte d’intégrité référentielle impose qu’un employé appartienne a un département EXISTANT.
Symétriquement, si l’on veut supprimer un département qui a des employes on viole la contrainte :
SQL> delete from departments where department_id=10;
–> erreur : ORA-02292: violation de contrainte (HR.EMP_DEPT_FK) d’intégrité – enregistrement fils existant

Il existe une clause ‘ON DELETE CASCADE’ qui permet de déclencher une suppression automatique des lignes ‘filles’ si une ligne ‘mère’ est supprimée : supprimer par exemple tous les emplyés associés ? un departement qui vient d’être supprimé…

SQL> ALTER TABLE employees
ADD ( CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments ON DELETE CASCADE )
SQL> select count(*) from employees
WHERE department_id=100;
–> 6
SQL> DELETE from departments WHERE department_id=100;
–> 1 row deleted
mais les lignes de ‘employees’ correspondantes ont également été supprimées :
SQL> select count(*) from employees
WHERE department_id=100;
–> 0

Infos dans le référentiel

SQL> select * from user_constraints

ou + précisément

SQL> select constraint_name, constraint_type, table_name,
search_condition,r_constraint_name, status
from user_constraints
where table_name like ‘%DD’

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_CONST_NAME STATUS
—————– ————— ———- —————- ———— ——
EMP_SALARY_MIN_DD C EMPLOYE_DD salary > 0 - ENABLED
SYS_C004030 C EMPLOYE_DD EMAIL IS NOT NULL - ENABLED
SYS_C004029 C EMPLOYE_DD LAST_NAME IS NOT NULL - ENABLED
DEPT_NAME_DD_NN C DEPARTEMENT_DD D_NAME IS NOT NULL - ENABLED
EMP_DEPT_DD_FK R EMPLOYE_DD - DEPT_ID_DD_PK ENABLED
SYS_C004032 P EMPLOYE_DD - - ENABLED
EMP_EMAIL_DD U EMPLOYE_DD - - ENABLED
DEPT_ID_DD_PK P DEPARTEMENT_DD - - ENABLED

invalidation de contraintes

Les clauses ‘DEFERRED’ et ‘DEFERRABLE’ permettent de différer l’application de la contrainte ? la fin de la
transaction :

SQL> create table test(n number check ( n in (10, 20) ) deferrable initially deferred );
SQL> alter session set constraint = deferred;
SQL> insert into test values(100);
–> OK
SQL> insert into test values(200);
SQL> commit;
–> erreur sur la transaction
ORA-02091: transaction annulée
ORA-02290: violation de contraintes (HR.SYS_C004039) de vérification

SQL> alter session set constraint = immediate;
SQL> insert into test values (100);
–> erreur sur l’insertion :
ORA-02290: violation de contraintes (HR.SYS_C004039) de vérification

On peut également utiliser les clause ENABLE/DISABLE de ‘ALTER TABLE’
ENABLE VALIDATE : s’applique aux lignes existantes dans la table
ENABLE NOVALIDATE : aux ligne futures
DISABLE : inhibe la contrainte

SQL> CREATE TABLE test (n number , constraint nb_ok check ( n in (10, 20) ));
SQL> insert into test values (100);
– erreur : ORA-02290: violation de contraintes (HR.NB_OK) de vérification
SQL> ALTER TABLE test DISABLE CONSTRAINT nb_ok ;
SQL> insert into testing values(100);
–> 1 ligne insérée (la contrainte était inhibée)

ALTER TABLE test ENABLE NOVALIDATE CONSTRAINT nb_ok;
–> OK : Table Altered…

ALTER TABLE test ENABLE VALIDATE CONSTRAINT nb_ok;
–> erreur : ORA-02293: impossible de valider (HR.NB_OK)
- violation d’une contrainte de contrôle

la clause validate, force le controle de la contraine sur la lignes existantes et la valeur ’100′ insérée précédemment est non conforme…

Voici un exemple complet de création de mini base avec contraintes, d’après les tables de démo HR officielles d’Oracle :

Rem
Rem $Header: hr_cre.sql 29-aug-2002.11:44:03 hyeh Exp $
Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved.
Rem

REM ****************************************************
REM Create the REGIONS table to hold region information for locations
REM HR.LOCATIONS table has a foreign key to this table.

Prompt ****** Creating REGIONS table ….

CREATE TABLE regions
( region_id NUMBER
CONSTRAINT region_id_nn NOT NULL
, region_name VARCHAR2(25)
);

CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);

ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ;

REM ********************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations.
REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.

Prompt ****** Creating COUNTRIES table ….

CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
ORGANIZATION INDEX;

ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;

REM ********************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM HR.DEPARTMENTS has a foreign key to this table.

Prompt ****** Creating LOCATIONS table ….

CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;

CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;

ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
PRIMARY KEY (location_id)
, CONSTRAINT loc_c_id_fk
FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;

Rem Useful for any subsequent addition of rows to locations table
Rem Starts with 3300

CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;

REM **************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.

Prompt ****** Creating DEPARTMENTS table ….

CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
) ;

CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;

ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
PRIMARY KEY (department_id)
, CONSTRAINT dept_loc_fk
FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;

Rem Useful for any subsequent addition of rows to departments table
Rem Starts with 280

CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
NOCACHE
NOCYCLE;

REM *********************************************
REM Create the JOBS table to hold the different names of job roles within the company.
REM HR.EMPLOYEES has a foreign key to this table.

Prompt ****** Creating JOBS table ….

CREATE TABLE jobs
( job_id VARCHAR2(10)
, job_title VARCHAR2(35)
CONSTRAINT job_title_nn NOT NULL
, min_salary NUMBER(6)
, max_salary NUMBER(6)
) ;

CREATE UNIQUE INDEX job_id_pk
ON jobs (job_id) ;

ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
PRIMARY KEY(job_id)
) ;

REM ***********************************************
REM Create the EMPLOYEES table to hold the employee personnel
REM information for the company.
REM HR.EMPLOYEES has a self referencing foreign key to this table.

Prompt ****** Creating EMPLOYEES table ….

CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;

CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;

ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
, CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
) ;

ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;

Rem Useful for any subsequent addition of rows to employees table
Rem Starts with 207

CREATE SEQUENCE employees_seq
START WITH 207
INCREMENT BY 1
NOCACHE
NOCYCLE;

REM ***********************************************
REM Create the JOB_HISTORY table to hold the history of jobs that
REM employees have held in the past.
REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table.

Prompt ****** Creating JOB_HISTORY table ….

CREATE TABLE job_history
( employee_id NUMBER(6)
CONSTRAINT jhist_employee_nn NOT NULL
, start_date DATE
CONSTRAINT jhist_start_date_nn NOT NULL
, end_date DATE
CONSTRAINT jhist_end_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT jhist_job_nn NOT NULL
, department_id NUMBER(4)
, CONSTRAINT jhist_date_interval
CHECK (end_date > start_date)
) ;

CREATE UNIQUE INDEX jhist_emp_id_st_date_pk
ON job_history (employee_id, start_date) ;

ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
PRIMARY KEY (employee_id, start_date)
, CONSTRAINT jhist_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs
, CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees
, CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
) ;

Compatibilité import / export 9i / 10g

Administration Oracle, Divers 1 Commentaire »

En résumé
version import = version base cible
version export V9 compatible avec base 10 et 11G

matrice de compatibilté export / import

+———+————————————————————+ |EXPORT | IMPORT into: | | from +——-+——–+——–+——–+——–+——–+——-+ | \/ | 8.1.x | 8.1.7 | 9.0.1 | 9.2.0 | 10.1.0 | 10.2.0 | 11.1.0| +———+——-+——–+——–+——–+——–+——–+——-+ |5.x 1) 2)|EXP5x | EXP5x | EXP5x | EXP5x | EXP5x | EXP5x | EXP5x | |6.x 2)|EXP6x | EXP6x | EXP6x | EXP6x | EXP6x | EXP6x | EXP6x | |7.x 3)|EXP7x | EXP7x | EXP7x | EXP7x | EXP7x | EXP7x | EXP7x | +———+——-+——–+——–+——–+——–+——–+——-+ |8.0.3 |EXP803 | EXP803 | EXP803 | EXP803 | EXP803 | EXP803 | EXP803| |8.0.4 |EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804| |8.0.5 |EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805| |8.0.6 |EXP806 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806| +———+——-+——–+——–+——–+——–+——–+——-+ |8.1.5 |EXP815 | EXP815 | EXP815 | EXP815 | EXP815 | EXP815 | EXP815| |8.1.6 |EXP81x | EXP816 | EXP816 | EXP816 | EXP816 | EXP816 | EXP816| |8.1.7 |EXP81x | EXP817 | EXP817 | EXP817 | EXP817 | EXP817 | EXP817| +———+——-+——–+——–+——–+——–+——–+——-+ |9.0.1 |EXP81x | EXP817 | EXP901 | EXP901 | EXP901 | EXP901 | EXP901| |9.2.0 | N/S | EXP817 | EXP901 | EXP920 | EXP920 | EXP920 | EXP920| +———+——-+——–+——–+——–+——–+——–+——-+ |10.1.0 4)| N/S | EXP817 | EXP901 | EXP920 | 4) | |10.2.0 4)| Not Supported | EXP920 | 4) | +———+——-+——–+——–+——–+——–+——–+——-+ |11.1.0 4)| Not Supported | EXP920 | 4) | +———+——-+——–+——–+——–+——–+——–+——-+

Quelques tests

——————————————————-

srv1:/oracle/9iDB/bin >exp scott/tiger@b9 file=/tmp/exp9b9.dmp

Export: Release 9.2.0.6.0 – Production on Tue Mar 11 16:52:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Export terminated successfully without warnings.

——————————————————-

srv1:/oracle/10GDB/bin > exp scott/tiger@b10 file=/tmp/exp10b10.dmp

Export: Release 10.2.0.3.0 – Production on Tue Mar 11 17:05:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64
bit Production
With the Partitioning, OLAP and Data Mining options

Export terminated successfully without warnings.

——————————————————-

srv1:/oracle/10GDB/bin > exp scott/tiger@b9 file=/tmp/exp10b9.dmp

Export: Release 10.2.0.3.0 – Production on Tue Mar 11 16:49:35 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

——————————————————-

srv1:/oracle/10GDB/bin >imp scott/tiger@b9 file=/tmp/exp9b9.dmp

Import: Release 10.2.0.3.0 – Production on Tue Mar 11 16:56:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

IMP-00058: ORACLE error 6550 encountered
ORA-06550: line 1, column 33:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully

——————————————————-

srv-1:/oracle/9iDB/bin >exp scott/tiger@b10 file=/tmp/exp9b10.dmp

Export: Release 9.2.0.6.0 – Production on Tue Mar 11 17:00:56 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64
bit Production
With the Partitioning, OLAP and Data Mining options

EXP-00008: ORACLE error 37002 encountered
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP tech
nical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at « SYS.DBMS_AW », line 93
ORA-06512: at « SYS.DBMS_AW », line 122
ORA-06512: at « SYS.DBMS_AW_EXP », line 473
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema
_info_exp
. exporting statistics
Export terminated successfully with warnings.

——————————————————-

srv1:/oracle/9iDB/bin >imp scott/tiger@b9 file=/tmp/exp10b10.dmp

Import: Release 9.2.0.6.0 – Production on Tue Mar 11 17:13:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 – 64bit Producti
on
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining op
tions
JServer Release 9.2.0.6.0 – Production

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

Paramètres d’instance et de session

Administration Oracle, Divers pas de Commentaire »

Les paramètres d’initialisation

Les ressources utilsées par une base, essentiellement ressources mémoires, buffers, processus et fichiers sont paramétrables, et permettent d’ajuster son fonctionnement, voire de faire de l’optimisation (tuning).
Certains paramètres ont des valeurs libres (dans un domaine de valeurs, bien sûr), d’autres plus restrictifs sont dépendants du systèmes d’exploitation, ou dérivés d’autres paramètres.

Lors de son démarrage, une instance Oracle lit ses paramètres dans un fichier, dit de démarrage ou d’initialisation.
La totalité des paramètres d’initialisation est décrite dans la documentation Oracle Oracle 10gR2 reference (attention PDF de 30MO!).

note : augmenter les ressources allouées ? la base en augmentant les valeurs de certains

paramètres est une bonne idée…jusqu’? un certain point. La mémoire ne peut pas,on s’en doute, être entièrement dévolue ? la SGA…
Quelle que soit la base il est obligatoire de spécifier explicitement au strict minimum 2 paramètres :

‘DB_NAME’ : le nom de la base de données
‘CONTROL_FILES’ : la liste des fichiers de contrôle utilisés

Les autres paramètres les plus fréquemment utilisés sont les suivants (S:statique, M:modifiable, D: dérivé d’un autre paramètre)

COMPATIBLE S Compatibilité arrière avec les versions précédentes (>= 9.2.0)
DB_DOMAIN S Nom de domaine, qui associé au DB_NAME donne un nom de base uniqueNom de domaine, qui associé au DB_NAME donne un nom de base unique
DB_RECOVERY_FILE_DEST M Répertoire de destination de la zone de FLASH recovery
LOG_ARCHIVE_DEST_n M Répertoire des fichiers REDO LOGS archivés
NLS_LANGUAGE D Langue utilisée (fonction de NLS_LANG dans l’environnement Unix ou windows)
NLS_TERRITORY M le territoire (France par ex). Impacte les dates, et la monnaie
OPEN_CURSORS M nb max de cursor ouvrable par une application
PGA_AGGREGATE_TARGET M si 0, dimensionnement auto de la PGA, pour les proccess utilisateur server
PROCESSES S nb max de process connectés ? Oracle (background et user)
SESSIONS D = 1,1 * PROCESSES
SGA_TARGET D si 0 auto dimensionnement de la SGA
SHARED_SERVER M 0 si serveur dédié, n = nb de serveurs partagés lancés au startup instance
SP_FILE S emplacement du SP File
UNDO_MANAGEMENT S Manual | Auto, par defaut Manual indique qu’on utilise des Rollback segments

Paramètres statiques et dynamiques

Certains paramètres peuvent être modifiés en temps réel, ils sont dits dynamiques, d’autres seront pris en compte au prochain redémarrage de la base, ils sont dits statiques.
Les paramètres dynamiques peuvent être modifiés pour la durée de la session,, pour la durée de l’instance ou de manière permanente avec la commande ‘ALTER SESSION’ ou ‘ALTER SYSTEM’.
Voici les syntaxes :

ALTER SESSION SET =
ALTER SYSTEM SET = [SCOPE = MEMORY | SPFILE | BOTH] [DEFERRED] [COMMENT = '']

exemples

SQL> ALTER SESSION SET NLS_LANGUAGE=’FRENCH’;


Informations sur les paramètres

On peut utiliser les commandes SQL*Plus SHOW :

SQL> SHOW PARAMETER

active_instance_count integer  
aq_tm_processes integer 0
archive_lag_target integer 0
asm_diskgroups string  
asm_diskstring string  
asm_power_limit integer 1
audit_file_dest string F:ORACLEPRODUCT10.2.0ADMIN ORCLADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
background_core_dump string partial
background_dump_dest string F:ORACLEPRODUCT10.2.0ADMIN ORCLBDUMP
backup_tape_io_slaves… boolean… FALSE…

ou en filtrant avec un mot clé :

SQL> SHOW PARAMETER AUDIT

audit_file_dest string F:ORACLEPRODUCT10.2.0ADMIN ORCLADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

ou utiliser des vues virtuelles du dictionnaire :

V$PARAMETER : les paramètres en cours d’utilisation (de la session donc…)
V$PARAMETER2 : les mêmes, mais mieux présentés…
V$SPPARAMETER : les paramètres du SPFILE (valable au (re)démarrage de l’instance)
V$SYSTEM_PARAMETER : paramètres de l’instance en cours en mémoire

SQL> select a.name, a.value courante, sp.value sp_file, mem.value memoire
from v$parameter a, v$spparameter sp, v$system_parameter mem
where a.name=sp.name
and mem.name=a.name
and a.name = ‘open_cursors’

NAME COURANTE SP_FILE MEMOIRE
open_cursors 1100 1100 1100

SQL> alter system set open_cursors=1200;
– on modifie la valeur courante de l’instance, ce qui a un impact sur la session
– mais ne sera plus valable au redemarrage de la base (spfile non modifié…)

NAME COURANTE SP_FILE MEMOIRE
open_cursors 1200 1100 1200

SQL> — infos sur parametres, par defaut ou non ?
SQL> SELECT NAME , VALUE, ISDEFAULT, DESCRIPTION
FROM V$PARAMETER

NAMEtracefile_identifier VALUE ISDEFAULTTRUE DESCRIPTIONtrace file custom identifier
lock_name_space   TRUE lock name space used for generating lock names for standby/clone database
processes 150 FALSE user processes
sessions 170 TRUE user and system sessions
timed_statistics TRUE TRUE maintain internal timing statistics
timed_os_statistics 0 TRUE internal os statistic gathering interval in seconds

SQL> — combine de parametres spécifiés dans le SPFILE ?
SQL> SELECT count(*)
FROM V$SPPARAMETER
WHERE ISSPECIFIED IS TRUE
COUNT(*)
———-
23