LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon