Cet article est repris d’un ancien blog et fusionné avec un autre
SQLPlus est l’outil incontournable du DBA Oracle. Même si beaucoup n’utilisent que des interfaces graphiques, il arrive un moment ou un autre où il doit se connecter à une machine qui ne propose pas cette commodité et dès lors, l’outil basique, livré avec le moteur, devient un passage obligé voire incontournable ou presque.
Je liste ici quelques trucs, astuces, commandes qui permettent d’utiliser SQLPlus de manière sympathique et sans trop de prise de tête.
Se connecter à une base avec sqlplus
Si vous êtes sur le serveur ou se trouve la base
$ sqlplus [...] Entrez le nom utilisateur : <utilisateur>/<mot de passe> [...] SQL>
Si votre tnsnames.ora est renseigné
$ sqlplus [...] Entrez le nom utilisateur : <utilisateur>/<mot de passe>@<alias tns> [...] SQL>
Si votre tnsnames.ora n’est pas renseigné
$ sqlplus [...] Entrez le nom utilisateur : <utilisateur>/<mot de passe>@//<serveur>:<port>/<service> [...] SQL>
Voir les constantes définies
SQL> def DEFINE _DATE = "09/10/12" (CHAR) DEFINE _CONNECT_IDENTIFIER = "//127.0.0.1:1521/orcl" (CHAR) DEFINE _USER = "SCOTT" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000300" (CHAR) DEFINE _RC = "1" (CHAR) SQL>
Utiliser les constantes
Constantes pré-existantes
SQL> select '&_EDITOR' from dual ; 'v -- vi SQL>
Constantes non existantes
Sans définition dynamique de la constante
SQL> select '&CNST' from dual ; Entrez une valeur pour cnst : TOTO 'TOT ---- TOTO SQL> define CNST SP2-0135: le symbole CNST est INDEFINI
Avec définition dynamique de la constante
SQ> select '&&CNST' from dual ; Entrez une valeur pour cnst : TITI 'TIT ---- TITI SQL> define CNST DEFINE CNST = "TITI" (CHAR)
Supprimer une constante
SQL> define CNST DEFINE CNST = "TITI" (CHAR) SQL> UNDEF CNST SQL> define CNST SP2-0135: le symbole CNST est INDEFINI
Utiliser un dépôt de scripts centralisé
SQL> def r=http://depot/scripts/sql SQL> @&r/cre_super_user.sql Utilisateur cree, compte verouille
On peut s’amuser à écrire les scripts dans un langage web comme PHP et donc protéger l’accès au scripts (plus ou moins).
Affecter et visualiser les variables de liaison (bind variables) définies dans la session
SQL> variable toto number
SQL> exec :toto:=7
SQL> variable titi number
SQL> exec :titi:=9
SQL> print
TOTO
----------
7
TITI
----------
9
Sauvegarder et restaurer les settings avant de les modifier
Sauvegarder
SQL> store set settings_sqlplus replace
Restaurer
SQL> @settings_sqlplus
Créer un ficher de sortie propre
SQL> set ver off -- evite l'affichage des réécritures de constantes SQL> set lin 32767 -- taille maxi d'une ligne SQL> set trimspool on -- enlève les blancs en fin de ligne dans le fichier de sortie SQL> set pages 0 -- page de taille infinie mais sans titres par défaut SQL> set emb on -- affiche les titres une foi SQL> set newp none -- évite les sauts de pages SQL> set termout off -- évite la sortie sur écran, seul le fichier sera renseigné SQL> set echo off -- évite l'affichage des requêtes et donc leur écriture SQL> set feed off -- évite les infos de fin de requêtes (n lignes ...) SQL> set timi off -- évite le chronométrage en fin de requête SQL> set autot off -- évite la production d'un plan en fin de requête SQL> set long 9999999 -- permet l'affichage de longs de 9999999 octets SQL> set longc 9999999 -- permet l'affichage de longs de 9999999 caractères* SQL> clear computes -- supprime les agrégations automatiques de colonnes SQL> clear columns -- supprime les formatages de colonnes SQL> clear breaks -- supprime les sauts SQL> TTITLE off -- supprime les titres de haut de requête (ou de pages) SQL> BTITLE off -- supprime les titres de bas de requête SQL> REPHEADER off -- supprime les en-tête de rapports SQL> REPFOOTER off -- supprime les pieds de rapports SQL> spool <fichier de sortie> SQL> -- instructions SQL> spool off
Concatener des constantes
SQL> def NOM_FIC=monfic SQL> spool &&MON_FIC..txt SQL> -- instructions SQL> spool off
Impossible de se connecter mais analyse requise
$ sqlplus -prelim SQL*Plus: Release 11.2.0.3.0 Production on Mer. Oct. 10 08:36:15 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Entrez le nom utilisateur : / as sysdba SQL>
Cette connexion est dite préliminaire et ne permet que d’utiliser oradebug ou de faire un shutdown abort
Voilà déjà de quoi s’amuser. Avec ces quelques infos/trucs vous pouvez administrer efficacement tout un parc de bases de données sans passer par des interfaces lourdes et (bien souvent) inutiles. Reste à connaitre le SQL et le catalogue Oracle mais après tout vous êtes DBA non ?
Un petit exercice pratique ? Créer un fichier CSV
La question revient souvent sur les forums : comment produire un fichier CSV depuis SQLPlus …
La solution a été donnée sur un forum, elle est copiée ici dans une version un peu remaniée et très légèrement plus générique
L’appel est simple depuis sqlplus: @sql2csv <nom du fichier contenant la(les) requête(s) à exporter en csv>
-- merci à skuatamad (http://www.developpez.net/forums/u253956/skuatamad/) -- -- 1 paramètre : le nom du script sql à exécuter -- En sortie un fichier CSV -- store set settings_sqlplus replace set echo off set feedback off set linesize 32767 set pages 0 emb on newp none set sqlprompt '' set trimspool on set long 9999999 set longc 9999999 set colsep ';' set heading on set recsep off set headsep on set underline off spool &&1..csv @&&1 spool off undef 1 @settings_sqlplus
Par exemple avec cette requête sql stockée dans le fichier sessions.sql :
select sid
, serial#
, process
, sql_id
, last_call_et
, status
from v$session ;
On obtient génère le fichier csv de la manière suivante:
SQL> @sessions
SID SERIAL# PROCESS SQL_ID LAST_CALL_ET STATUS
---------- ---------- ------------ ------------- ------------ --------
146 10 4312 4gd6b1r53yt88 60116 ACTIVE
151 2 4308 60116 ACTIVE
157 7 3836 60126 ACTIVE
159 46 6640:5200 g8730nuf9c1xf 0 ACTIVE
160 1 3796 60133 ACTIVE
161 1 3792 4gd6b1r53yt88 60133 ACTIVE
162 1 3788 60133 ACTIVE
163 1 3784 60133 ACTIVE
164 1 3780 60133 ACTIVE
165 1 3776 60133 ACTIVE
166 1 3772 60133 ACTIVE
SID SERIAL# PROCESS SQL_ID LAST_CALL_ET STATUS
---------- ---------- ------------ ------------- ------------ --------
167 1 3768 60133 ACTIVE
168 1 3764 60133 ACTIVE
169 1 3760 60133 ACTIVE
170 1 3756 60133 ACTIVE
15 rows selected.
SQL> @sql2csv sessions
Wrote file settings_sqlplus
SID; SERIAL#;PROCESS ;SQL_ID ;LAST_CALL_ET;STATUS
146; 10;4312 ;4gd6b1r53yt88; 60140;ACTIVE
151; 2;4308 ; ; 60140;ACTIVE
157; 7;3836 ; ; 60150;ACTIVE
159; 46;6640:5200 ;g8730nuf9c1xf; 0;ACTIVE
160; 1;3796 ; ; 60157;ACTIVE
161; 1;3792 ;4gd6b1r53yt88; 60157;ACTIVE
162; 1;3788 ; ; 60157;ACTIVE
163; 1;3784 ; ; 60157;ACTIVE
164; 1;3780 ; ; 60157;ACTIVE
165; 1;3776 ; ; 60157;ACTIVE
166; 1;3772 ; ; 60157;ACTIVE
167; 1;3768 ; ; 60157;ACTIVE
168; 1;3764 ; ; 60157;ACTIVE
169; 1;3760 ; ; 60157;ACTIVE
170; 1;3756 ; ; 60157;ACTIVE
SQL> ho type sessions.csv
SID; SERIAL#;PROCESS ;SQL_ID ;LAST_CALL_ET;STATUS
146; 10;4312 ;4gd6b1r53yt88; 60140;ACTIVE
151; 2;4308 ; ; 60140;ACTIVE
157; 7;3836 ; ; 60150;ACTIVE
159; 46;6640:5200 ;g8730nuf9c1xf; 0;ACTIVE
160; 1;3796 ; ; 60157;ACTIVE
161; 1;3792 ;4gd6b1r53yt88; 60157;ACTIVE
162; 1;3788 ; ; 60157;ACTIVE
163; 1;3784 ; ; 60157;ACTIVE
164; 1;3780 ; ; 60157;ACTIVE
165; 1;3776 ; ; 60157;ACTIVE
166; 1;3772 ; ; 60157;ACTIVE
167; 1;3768 ; ; 60157;ACTIVE
168; 1;3764 ; ; 60157;ACTIVE
169; 1;3760 ; ; 60157;ACTIVE
170; 1;3756 ; ; 60157;ACTIVE