Requêtes Oracle SQL utiles : dates, dictionnaire, administration et performance
Aide-mémoire de requêtes Oracle courantes pour développeurs et DBA.
Aide-mémoire compact de requêtes Oracle SQL courantes pour développeurs et DBA : dates, dictionnaire de données, administration, performance et utilitaires. Remplacez les noms génériques (TABLE_NAME, USER_NAME, MYSCHEMA...) par les vôtres.
Dates et heures
-- Premier jour du mois courant (remplacer SYSDATE par n'importe quelle date)
SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL;
-- Dernier jour du mois courant (années bissextiles gérées)
SELECT TRUNC (LAST_DAY (SYSDATE)) FROM DUAL;
-- Premier et dernier jour de l'année courante
SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 FROM DUAL;
-- Nombre de jours du mois courant
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL;
-- Jours restants dans le mois courant
SELECT LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL;
-- Jours écoulés depuis une date donnée (ici : date d'embauche)
SELECT TRUNC (SYSDATE) - TRUNC (e.hire_date) FROM employees e;
-- Secondes écoulées depuis minuit / secondes restantes aujourd'hui
SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL;
SELECT (TRUNC (SYSDATE + 1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL;
Dates de début et de fin de chaque mois restant de l'année courante :
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
FROM XMLTABLE (
'for $i in 0 to xs:int(D) return $i'
PASSING XMLELEMENT (
d,
FLOOR (
MONTHS_BETWEEN (
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
SYSDATE)))
COLUMNS i INTEGER PATH '.');
Dictionnaire de données
-- Une table existe-t-elle dans le schéma courant ? (scripts de création rejouables)
SELECT table_name FROM user_tables WHERE table_name = 'TABLE_NAME';
-- Une colonne existe-t-elle ?
SELECT column_name AS FOUND
FROM user_tab_cols
WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';
-- DDL d'un objet ('VIEW', 'INDEX'... en premier argument pour les autres types)
SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;
-- Schéma courant, et changement de schéma
SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;
ALTER SESSION SET CURRENT_SCHEMA = new_schema;
Administration
-- Version et compatibilité
SELECT * FROM v$version;
SELECT VALUE FROM v$system_parameter WHERE name = 'compatible';
-- Profil et tablespaces par défaut de chaque utilisateur
SELECT username, profile, default_tablespace, temporary_tablespace FROM dba_users;
-- Jeu de caractères de la base
SELECT * FROM nls_database_parameters;
-- Index insensible à la casse sur des données sensibles à la casse
CREATE TABLE tab (col1 VARCHAR2 (10));
CREATE INDEX idx1 ON tab (UPPER (col1));
ANALYZE TABLE tab COMPUTE STATISTICS;
Fichiers de données et tablespaces :
-- Redimensionner un datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 2G;
-- Ajouter un datafile à un tablespace
ALTER TABLESPACE data01 ADD DATAFILE '/u01/oradata/ORCL/data01.dbf' SIZE 1000M AUTOEXTEND OFF;
-- État de l'autoextend
SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;
Tailles :
-- Taille allouée de la base (Go)
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
-- Espace occupé par les données (Go)
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;
-- Taille d'un schéma (Mo)
SELECT SUM (bytes / 1024 / 1024) "size" FROM dba_segments WHERE owner = '&owner';
-- Nombre d'objets par propriétaire
SELECT owner, COUNT (owner) number_of_objects
FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;
Sessions et performance
Dernier texte SQL exécuté par chaque session :
SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
s.sid || '/' || s.serial# sid,
s.status "Status",
p.spid,
sql_text sqltext
FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
WHERE t.address = s.sql_address
AND p.addr = s.paddr(+)
AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;
CPU consommé par les sessions actives :
SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;
Progression des opérations longues :
SELECT a.sid, a.serial#, b.username, opname OPERATION, target OBJECT,
TRUNC (elapsed_seconds, 5) "ET (s)",
TO_CHAR (start_time, 'HH24:MI:SS') start_time,
ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid
AND b.username NOT IN ('SYS', 'SYSTEM')
AND totalwork > 0
ORDER BY elapsed_seconds;
Identifiants de session, de processus serveur et de processus client de la session courante :
SELECT b.sid, b.serial#, a.spid processid, b.process clientpid
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
V$SESSION.SID et SERIAL# identifient la session, V$PROCESS.SPID est l'identifiant du processus serveur et V$SESSION.PROCESS celui du processus client (sous Windows, processus:thread).
Date du dernier DDL d'une table :
SELECT CREATED, TIMESTAMP, last_ddl_time
FROM all_objects
WHERE OWNER = 'MYSCHEMA' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'EMPLOYEE_TABLE';
Top SQL par lectures disque par exécution :
SELECT *
FROM (SELECT ROWNUM,
SUBSTR (a.sql_text, 1, 200) sql_text,
TRUNC (a.disk_reads / DECODE (a.executions, 0, 1, a.executions)) reads_per_execution,
a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE ROWNUM < 10;
Connexions :
-- Connexions en cours
SELECT osuser, username, machine, program FROM v$session ORDER BY osuser;
-- Sessions par programme
SELECT program application, COUNT (program) number_of_sessions
FROM v$session GROUP BY program ORDER BY number_of_sessions DESC;
-- Sessions par utilisateur
SELECT username, COUNT (username) number_of_sessions
FROM v$session GROUP BY username ORDER BY number_of_sessions DESC;
Utilitaires
-- Nombre en toutes lettres (anglais)
SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL; -- one thousand five hundred twenty-six
-- Rechercher une chaîne dans le code source PL/SQL
SELECT * FROM dba_source
WHERE UPPER (text) LIKE '%FOO_SOMETHING%' AND owner = 'USER_NAME';
-- Chaîne séparée par des virgules vers des lignes
WITH csv AS (SELECT 'AA,BB,CC,DD,EE,FF' AS csvdata FROM DUAL)
SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) IS NOT NULL;
-- Dernière ligne d'une table sans clé fiable
SELECT * FROM employees WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);
-- Une table contient-elle des lignes ? (moins coûteux que COUNT(*))
SELECT 1 FROM TABLE_NAME WHERE ROWNUM = 1;
-- Nombre aléatoire entre 1 et 101
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;
Multiplier les valeurs de toutes les lignes (signe géré séparément, valeur absolue via EXP(SUM(LN()))) :
WITH tbl AS (SELECT -2 num FROM DUAL UNION SELECT -3 FROM DUAL UNION SELECT -4 FROM DUAL),
sign_val AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
FROM tbl WHERE num < 0)
SELECT EXP (SUM (LN (ABS (num)))) * val
FROM tbl, sign_val
GROUP BY val;
Générer des données de test aléatoires :
SELECT LEVEL empl_id,
MOD (ROWNUM, 50000) dept_id,
TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender,
TO_DATE (ROUND (DBMS_RANDOM.VALUE (1, 28)) || '-' ||
ROUND (DBMS_RANDOM.VALUE (1, 12)) || '-' ||
ROUND (DBMS_RANDOM.VALUE (1900, 2010)), 'DD-MM-YYYY') dob,
DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
FROM DUAL
CONNECT BY LEVEL < 10000;
Remarques
- Les vues
dba_*etv$*nécessitent les privilèges adéquats. ANALYZEest déprécié pour collecter les statistiques ; préférerDBMS_STATSdans du code neuf.- La formule de différence de jours basée sur
MONTHS_BETWEEN * 30est approximative ; soustraire directement deux dates est exact.