Gérer les données

1. Gérer les données Réalisé par : DAAIF Jabran CHERKAOUI Khaoula ABBAR Amina BOUSTANI Sara Encadré par : M.HANOUNE Année universitaire : 2013-2014

2. Définition • SQL (StructuredQueryLanguage) est un Langage de requêtes structuré qui est destiné à interroger ou piloter une base de données. • La partie langage de manipulation des données de SQL permet de rechercher, d'ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles. • Un ensemble d’ordre du LMD groupé en une unité de travail logique constitue ce qu’on appelle une transaction.

3. La commande INSERT • INSERT insère des lignes dans une table existante : • INSERT INTO ma_table (collone1, collone2, collone3, ..) • VALUES ('chaine', numéro , NULL, ..); • Exemple : • INSERT INTO dept(deptno, dname, loc) • VALUES (40, ‘développent’ , ‘casa’);

5. Etant donner que vous pouvez insérer une nouvelle ligne en précisant une valeur pour chaque colonne, il n’est pas obligatoire de lister les colonnes dans la clause INSERT. Dans ce cas les valeurs doivent être fournies dans l’ordre par défaut des colonnes dans la table. • Exemple: • INSERT INTO dept • VALUES (60, ‘finance’, NULL); • Assurez-vous que la colonne cible admet une valeur NULL en vérifiant l’état NULL à l’aide de la commande DESCRIBE. • DESCRIBE dept

6. La commande UPDATE • UPDATE Modifie un ensemble des lignes existant dans une table : • UPDATE ma_table • SET colonne = valeur • WHERE condition; • Exemple : • UPDATE emp • SET deptno = 20 • WHERE empno=7782;

8. On peut modifier plusieurs lignes à la fois. • Si vous omettez la clause WHERE, toutes les lignes sont modifiées. • Il est possible d’utiliser des sous interrogations multi-colonne dans la clause SET d’un ordre UPDATE : • UPDATE emp • SET (deptno, job) = • ( SELECT job, deptno • FROM emp • WHERE empno= 7499 ) • WHERE empno=7782; • Utilisez une sous-interrogation synchronisée pour mettre à jour les lignes d’une table basée sur des lignes d’une autre table.

9. La commande DELETE • DELETE Supprime un ensemble de lignes existant dans une table: • DELETE FROM ma_table • WHERE condition; • Exemple: • DELETE FROM dept • WHERE dname= ‘finance’;

11. Si vous omettez la clause WHERE toutes les lignes de la table seront supprimées : • DELETE FROM dept • Utilisez une sous-interrogation synchronisée pour supprimer uniquement les lignes existantes également dans une autre table.

12. Les commandes commit et rollback • La commande COMMIT:termine une transaction exécutée avec succès. Elle a pour effet de valider et d'enregistrer dans la base de données toutes les modifications effectuées durant la transaction

13. La commande ROLLBACK :annule la transaction en cours et restitue les données dans l'état où elles se trouvaient avant le début de la transaction.

14. Data pump • Data Pumpest un utilitaire serveur qui peut être utilisé pour déplacer des données et/ou des métadonnées entre des bases Oracle • Permet le chargement et le déchargement des données et de métadonnées à très grande vitesse dans des bases Oracle • décide automatiquement des méthodes d’accès aux données à déplacer ; que ce soit, chargement des données par chemin direct ou par tables externes • tous les travaux data Pump arrêtés soit volontairement ou involontairement, suite a une panne peuvent être redémarrer sans perte de données

15. Data Pump Export/Import • DATA PUMP export est un utilitaire permettant de décharger des données et des métadonnées dans un ensemble de fichier de system d’exploitation nommé jeu de fichier dump qui enregistre les informations d’un programme

16. DATA PUMP Import : utilisé pour charger sur un système cible les métadonnées et les données stockées dans un jeu de fichiers d’export

17. Les Data Pump Export & Import peuvent être employés pour : • Effectuer un export direct entre une base de données distante et un jeu de fichiers dump • Ou, charger la base de données cible directement à partir de la base source sans fichiers intermédiaires

18. Data pump export

20. Base de donnée ( database ) fait l’appelé de toute la base Schémas fait appelle à une table d’une ou plusieurs schémas Tables: sélectionne les tables à exporter Tablespace : appel tous les tables contenu dans le tablespace

21. Data pump import

22. Directory Objects Oracle a introduit les concepts des Directory Objects (DO) en Oracle 8i. Les DOs est une structure logique qui représente les répertoires physiques dans les fichiers systèmes du serveur. (Alias pour les répertoires physiques) À l’origine, un DO était utilisé seulement dans le contexte du package PL/SQL (DBMS_LOB), pour administrer et accéder aux fichiers localisés sous le répertoire identifié par le DO correspondant. Cependant depuis Oracle 9i, le DO est maintenant utilisé dans plusieurs traits d’Oracle, comme EXTERNAL TABLE and PL/SQL UTL_FILE package. Parmis ces traits ontrouve: • Seuls les SYS utilisateurs peuvent avoir les DOs (même si un autre utilisateur les a créé). • Les noms des DOs sont uniques (parce que touts les répertoires sont localisé dans un seul namespace, nommé SYS). • Les permissions des DOs ne sont pas les mêmes que les permissions qu’a le système d’exploitation sur le serveur des fichiers système. • Les privilèges discrets des bases de données ne peuvent pas être accordés aux fichiers contenus dans le répertoire physique présenté par les DOs.

23. Directory Objects Les avantages des DOs: • En ce qui concerne l’utilisation du package UTL_FILE, contrairement aux versions précédentes, nous n’avons plus besoins de spécifier le chemin du répertoire du fichier système dans le fichier init.ora (paramètre UTL_FILE_DIR). Ainsi, nous pouvons changer le chemin dynamiquement sans avoir à terminer et à redémarrer l’instance. • Il y a un niveau de sécurité plus élevé et un contrôle précis en administration des applications qui utilise le UTL_FILE. Par exemple, il est désormais plus facile de maintenir 5 DOs, chacun d’eux dirigeant vers un répertoire physique particulier dans le fichier système, plutôt qu’avoir plusieurs entrées pour le paramètre UTL_FILE_DIR dans le fichier init.ora. Pour créer un DO, l’utilisateur doit avoir les privilèges suivants: CREATE ANY DIRECTORY. CREATE OR REPLACE DIRECTORY test_files AS ‘E:\oracleWork’; Par défaut, un utilisateur possède le privilège READ WRITE. Cependant, si on souhaite l’accorder, on procède comme suivant : GRANT READ ON DIRECTORY test_files TO PUBLIC;

24. Directory Objects Comment on utilise un DO? Dans l’exemple suivant, on souhaite accéder aux données dans un fichier plat (base de données orienté texte) sans les charger dans la base de données. Pour simplifier cette opération, Oracle fournit le concept des tables externes. En principe, ces tables sont en lecture seule, et sont utilisées pour accéder aux données externes comme si elles faisaient partie d’une table ou d’une base de données. Les fichiers plats contenants les données sont stockés dans un répertoire physique, identifiés par les tables externes utilisant les DOs. L’utilisation des DOs empêche les accès READ WRITE non autorisés au fichiers du SE (data/ fichier log) par les utilisateurs de la base de données. On considère les données suivantes dans un fichier  "emp_load.dat“, cefichierdoit se trouver dans le répertoire physique "E:\oracleWork" identifié par le DO TEST_FILES précédemment créé.

25. Directory Objects Le DDL pour créer la table externe sera comme suit: CREATE TABLE emp_external ( emp_id NUMBER(4) , ename VARCHAR2(12) , job VARCHAR2(12) , mgr_id NUMBER(4) , hiredate DATE , salary NUMBER(8) , comm NUMBER(8) , dept_id NUMBER(2)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY TEST_FILES ACCESS PARAMETERS (records delimited BY newline fields terminated BY ',') LOCATION ('emp_load.dat') ); Pour afficher les données, on procède comme pour une table ordinaire: SELECT * FROM emp_external;

26. SQL*Loader SQL*Loader est un utilitaire fourni par Oracle qui permet de charger les données depuis un fichier plat dans une ou plusieurs tables de base de données, sous Windows il est présent dans le répertoire: %ORACLE_HOME%\bin SQL*Loader peut être utilisé dans les cas suivants: • Charger les données à travers le réseau si les fichiers de données se trouvent dans des systèmes autres que la base de données. • Charger les données depuis plusieurs sources (fichiers de données) durant la même session de chargement. • Charger les données dans plusieurs tables durant la même session de chargement. • Spécifier le jeu de caractères des données. • Charger des données sélectivement. (vous pouvez charger les données basé sur les valeurs des records) • Manipuler les données avant de les charger, en utilisant les commandes SQL. • Générer des valeurs de clé de séquence uniques en des colonnes précises. • Utiliser le système de fichier du SE pour accéder aux fichiers de données. • Charger les données depuis un disque, CD ou n’importe quel support physique de données. • Générer des rapports d’erreurs sophistiqués, qui sont extrêmement importants pour le diagnostic des pannes (troubleshooting). • Charger des données relationnelles complexes et arbitraires. • Utiliser des fichiers de données secondaires pour charger les fichiers LOBs et les collections. • Utiliser soit un chemin de chargement conventionnel ou direct. Tandis que le chemin conventionnel est très flexible, le chemin direct fournit une performance de chargement nettement supérieure.

27. SQL*Loader: Environnement SQL*Loader est lancé avec la commande suivante: C:\>Sqlldr {liste des paramètres}

28. SQL*Loader Log File: ou bien le fichier journal, il contient le résumé des actions qui ont eu lieu lors du chargement des données, telles que la date d’exécution, les noms des fichiers I/O, arguments des commandes, des informations sur la table, des informations sur les fichiers de données, et des informations sur les données insérées. Bad File: (fichier des enregistrements refusés), il contient les enregistrements qui ont été rejeté soit par SQL*LOADER soit par Oracle. Discard File: (fichier rebut), le fichier DISCARD peut être spécifié lors l'appel de la commande ou alors directement dans le fichier contrôle. Ce fichier est créé uniquement sur demande explicite et détaille les enregistrements qui n'ont pas été retenu par SQL*Loader.

29. SQL*Loader Les paramètres les plus utilisés dans la ligne de commande de SQL*Loader:

30. SQL*Loader Mode de chargement : • insert : insère les données dans une table vide • append : insère les données à la suite des données existantes • replace : insère les données en remplaçant les données existantes • truncate : insère les données après un TRUNCATE ( ici cette solution peut être utile pour faire diminuer le HWM ). NB: la ligne de commande qui permet de lancer sqlldr peut s’écrire de 3 manières: - Sqlldr system/password regionctl. - Sqlldr control=regions.ctluserid=system/password - Sqlldr system/manager control=regions.ctl

31. Le fichier de contrôle SQL*Loader Le fichier de contrôle SQL*Loader est un fichier texte écrit en langage SQL*Loader, c’est la clé pour n’importe quel processus de chargement. Le fichier de contrôle fournit au SQL*Loader les informations suivantes: • Le nom est l’emplacement du fichier de données d’entrée. • Le format des enregistrements dans le fichier d’entrée. • Le nom de/des tables à charger. • La correspondance entre les champs dans l’enregistrement d’entrée et les colonnes dans les tables de la base de données en train d’être chargées. • Des critères définissant quels enregistrements dans le fichier d’entrée à transmettre dans la tables destinations. • Noms et emplacements du Bad File, et du Discard File. Quelques uns de ces éléments peuvent être passé au SQL*loader en tant que paramètres de ligne de commande. Par exemple, le nom et l’emplacement du fichier d’entrée peuvent être passés en ligne de commande, même chose pour le Bad File et le Discard File Il est aussi possible pour que le fichier de contrôle contienne les données à charger, ça se fait pour les petites quantité de données à transmettre à travers le net.

32. Le fichier de contrôle SQL*Loader Syntaxe générale du fichier du contrôle: {LOAD | CONTINUE_LOAD} [DATA] [CHARACTERSET character_set] [INFILE clause [INFILE clause...]] [INSERT | APPEND | REPLACE | TRUNCATE] INTO TABLE clause [INTO TABLE clause...] [WHEN conditions] [FIELDS [delimiter clause]] [TRAILING [NULLCOLS] [SKIP skip_count] (field list) [BEGINDATA]

33. Le fichier de contrôle SQL*Loader Exemple Control Filewww.dba-ora.fr OPTIONS (DIRECT=FALSE) LOAD DATA INFILE * BADFILE 'dba-ora.bad' DISCARDFILE 'dba-ora.dsc' TRUNCATE PRESERVE BLANKS INTO TABLE SCOTT."EMP" WHEN (deptno = '20') FIELDS terminated by ";" Optionally enclosed by '"' TRAILING NULLCOLS ( empnoINTEGER EXTERNAL NULLIF (empno="NULL"), enameCHAR "UPPER(:ename)", job CHAR "RTRIM(:job)", mgr INTEGER EXTERNAL NULLIF (mgr="NULL"), hiredateDATE "MM/DD/YYYY HH24:MI:SS" NULLIF (hiredate="NULL"), sal DECIMAL EXTERNAL NULLIF (sal="NULL"), comm DECIMAL EXTERNAL NULLIF (comm="NULL"), deptnoINTEGER EXTERNAL NULLIF (deptno="NULL") ) BEGINDATA 7369;"smith";"CLERK ";7902;"12/17/1980 00:00:00";800,50;;20 7499;"Allen";"SALESMAN";NULL;"02/20/1981 00:00:00";1600;300;30 7521;"WARD";"SALESMAN";7698;"02/22/1981 00:00:00";1250;500,56;30 7566;"JONES";"MANAGER ";7839;"04/02/1981 00:00:00";2975;NULL;20 7654;"MARTIN";"SALESMAN";7698;"09/28/1981 00:00:00";1250;1400;30

34. Considérations relatives à la syntaxe des fichiers 00200-00299: DDL Syntax SQL*Loader-200 FORMAT clause should not be present - flat data files only Cause: SQL/DS FORMAT clause is not supported. Action: Remove the FORMAT command from the control file or comment it out. SQL*Loader-250 work data sets are not used by SQL*Loader Cause: The control file contains a WRKDDN statement. SQL*Loader ignores this clause. Action: No action required. This is an informational message. SQL*Loader-251 sort devices are not used by SQL*Loader Cause: The control file contains a SORTDEVT statement. SQL*Loader ignores this clause. Action: No action required. This is an informational message. SQL*Loader-252 sort data sets are not used by SQL*Loader Cause: The control file contains a SORTNUM statement. SQL*Loader ignores this clause. Action: No action required. This is an informational message. SQL*Loader-253 DB2 partition number has no significance -- ignored Cause: The control file contains a PART statement. SQL*Loader ignores this clause. Action: No action required. This is an informational message.

35. Considérations relatives à la syntaxe des fichiers SQL*Loader-254 cannot have DISCARDFILE specs here when multiple datafiles Cause: The control file contained multiple INFILE statements and a DISCARDFILE statement was found below the RESUME clause. Action: Move the DISCARDFILE statement above the RESUME clause, so it is adjacent to one of the INFILE statements. SQL*Loader-255 log file for error recovery not used by SQL*Loader Cause: The control file contains a LOG statement. SQL*Loader ignores this clause. Action: No action required. This is an informational message. SQL*Loader-256 SORTED INDEXES option allowed only for direct path Cause: The control file contains a SORTED INDEXES statement, but it was not used in a direct path load. Action: Specify a direct path load with DIRECT=TRUE on the command line, remove the statement from the control file, or comment it out. SQL*Loader-257 index name specified in SORTED INDEXES does not exist on table name Cause: A non-existent index was specified in the SORTED INDEXES clause. Either the index does not exist or its name was misspelled. Action: Create the index, change the spelling, remove the specification, or comment it out.

36. Considérations relatives à la syntaxe des fichiers SQL*Loader-258 maximum number of sorted indexes num exceeded on table name. Cause: There are too many indexes in the SORTED INDEX clause. The message displays the maximum number that are permitted. Action: Reduce the number of indexes specified in the SORTED INDEX clause or use the conventional path load instead of the direct path load. SQL*Loader-259 could not escalate DDL share lock to exclusive on table name Cause: This error occurs when another user has a parse lock on the table, for example, when another user is doing a select on the table. The parse lock should clear momentarily. Action: Give the parse lock a chance to clear and then retry or else use the conventional path load. SQL*Loader-260 index num is in an invalid state Cause: The specified index is in an invalid state. Action: Drop and re-create the index. SQL*Loader-262 PIECED keyword (on column num) allowed only when path is direct Cause: The PIECED keyword cannot be used in a conventional path load. Action: Remove the PIECED keyword or use the direct path load.

37. Considérations relatives à la syntaxe des fichiers SQL*Loader-263 PIECED column num must be last specified column in table name Cause: A column that is not the last column was specified as PIECED. Action: Remove the PIECED keyword or place the column last. SQL*Loader-264 file mode token name parsed but ignored Cause: An obsolete file mode token was used in the control file. As of Release 1.1 of SQL*Loader, the file-processing options string is used to control file processing, rather than keywords like STREAM, RECORD, FIXED, and VARIABLE. Action: No action required. This message is informational. Removing the obsolete keywords will eliminate the message without changing the way in which the datafile is processed. SQL*Loader-265 unable to get default character set name Cause: SQL*Loader was unable to locate the default character set name for the environment. Action: Supply a character set name with the CHARACTERSET keyword.

38. Considérations relatives à la syntaxe des fichiers SQL*Loader-266 unable to locate character set handle for name Cause: SQL*Loader could not find the character set handle for the named character set. Action: Correct the character set name. SQL*Loader-267 control file must be first datafile Cause: The control file is specified as containing data using the INFILE "*" clause, but other datafiles were named first. Action: Move the INFILE "*" clause so that it is the first datafile declared in the control file. SQL*Loader-268 UNRECOVERABLE keyword may be used only in direct path Cause: The UNRECOVERABLE keyword can only be specified in the direct path load. Action: Use the direct path load or remove the keyword. (Conventional path loads are always recoverable). SQL*Loader-269 Null string not allowed as clause comparison text Cause: A clause is being compared to a null string. Action: Modify the clause to compare to at least one character.

39. Considérations relatives à la syntaxe des fichiers SQL*Loader-270 table name has index defined upon it Cause: Parallel load was specified into a table that has an index defined for it. Action: Drop the index or indexes defined for the table or do not use parallel load. SQL*Loader-271 not a parallel load. Table level OPTIONS statement ignored Cause: A table-level OPTIONS statement was specified for a non-parallel load. Action: Remove the OPTIONS statement from the control file. SQL*Loader-272 table level OPTIONS statement ignored Cause: In the parallel load option, the file specified on the command line overrides the file specified in the control file. Action: Remove the OPTIONS statement from the control file.

40. Données d’entée et fichiers de données (1) • SQL*Loader lit les données d’un ou plusieurs fichiers désignés dans le fichier de contrôle. • Les données sont organisées en enregistrements • Un fichier de données peux présenter l’un des trois formats suivants: • Format d’enregistrement de type fixe • Format d’enregistrement de type variable • Format d’enregistrement de type flue

41. Données d’entée et fichiers de données (2) Format d’enregistrement de type fixe: • Tous les enregistrements qu'il contient sont de même longueur (en octets). • Syntaxe: INFILE <datafile_name> "fix n " ; • Exemple:load datainfile ’exampledat’ "fix 10 " example.dat 10into table example

42. Données d’entée et fichiers de données (3) Format d’enregistrement de type variable: • La longueur de chaque enregistrement d'un champ de type caractère est incluse au début de chaque enregistrement dans le fichier de données. • Syntaxe: INFILE "datafile_name" "var n"; • Exemple: load datainfile ’example.dat’ "var 3 " into table example

43. Données d’entée et fichiers de données (4) Format d’enregistrement de type flux: • La taille des enregistrements n'est pas indiquée ; rechercher par SQL*Loader a la fin de l’enregistrement. • Syntaxe: INFILE <datafile_name> ["strterminator_string"]; • Exemple: load datainfile ’example.dat’ "str ’|\n’ " into table example

44. Méthodes de chargement

45. Comparaison du chargement du données par chemin direct et par chemin conventionnel

46. Charger des données par SQL*Loader (1)

47. Charger des données par SQL*Loader (2) • La même ligne de commande mais écrite de trois manières différentes : • Sqlldrsystem/passwordregion.ctl • Sqlldrcontrol=regions.ctl userid=system/password • Sqlldrsystem/manager control=regions.ctl

48. FIN