• Dropper un schema
DROP USER "&user" cascade;
  • Lister les schemas d'une base
SELECT DISTINCT owner FROM dba_objects WHERE owner LIKE '%<string_to_search>%';
  • Lister les usernames d'une base
SELECT username FROM all_users WHERE username LIKE '%<string_to_search>%';
  • Killer une session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  • Lister les comptes verrouillé
SET linesize 300
COL "USERNAME" FORMAT A15
COL "ACCOUNT_STATUS" FORMAT A20
COL "CREATED" FORMAT A10
SELECT username, account_status, created, lock_date, expiry_date, profile
FROM dba_users
WHERE account_status != 'OPEN';
 
SET linesize 300
COL "USERNAME" FORMAT A15
COL "ACCOUNT_STATUS" FORMAT A20
COL "CREATED" FORMAT A10
SELECT username, account_status, profile, created, lock_date,EXPIRY_DATE, round(EXPIRY_DATE - SYSDATE) nb_jour_avant_expiration
FROM   dba_users
ORDER BY nb_jour_avant_expiration;
  • Changer le MDP d'un utilisateur
ALTER USER <user> IDENTIFIED BY <new_password>;
  • Deverrouiller un compte utilisateur
ALTER USER <user> account UNLOCK;
  • Gestion des profils Oracle
COL "PROFILE" FORMAT A15
COL "RESOURCE_NAME" FORMAT A30
COL "RESOURCE_TYPE" FORMAT A15
COL "LIMIT" FORMAT A15
SELECT * FROM dba_profiles
  • Connaitre mon user et schema actuel
SELECT sys_context('USERENV','SESSION_USER') CURRENT_USER, sys_context('USERENV','SESSION_SCHEMA') current_schema FROM dual;
  • Lister les sessions active
SET echo off
SET feed off
SET linesize 512
 
COLUMN machine format a20
COLUMN osuser format a10
COLUMN module format a30
 
SELECT machine,process,osuser,username,schemaname,STATUS,lockwait,sid,serial#,module,action FROM v$session
WHERE username IS NOT NULL
AND osuser IS NOT NULL
AND STATUS != 'INACTIVE'
ORDER BY machine,osuser,username,schemaname,STATUS,module;
  • Afficher les verrous
SET echo off
    SET feedback off
    SET linesize 512
 
    prompt -----------------------
    prompt - Verrous sur la base -
    prompt -----------------------
 
    COLUMN sid format 999 heading "SID"
    COLUMN username format a10 heading "Utilisateur"
    COLUMN machine format a20 heading "Nom Machine"
    COLUMN object_name format a20 heading "Nom objet"
    COLUMN TYPE format a4 heading "Type"
    COLUMN lmode format a20 heading "Mode de verrouillage"
    COLUMN request format 9999999 heading "Request Mode"
    COLUMN block format 9999999 heading "Lock Blocking"
 
    SELECT
    s.sid sid,
    s.username username,
    s.machine machine,
    l.type TYPE,
    o.object_name object_name,
    DECODE(l.lmode,
    0,'None',
    1,'Null',
    2,'Row Share',
    3,'Row Exlusive',
    4,'Share',
    5,'Sh/Row Exlusive',
    6,'Exclusive') lmode,
    DECODE(l.request,
    0,'None',
    1,'Null',
    2,'Row Share',
    3,'Row Exlusive',
    4,'Share',
    5,'Sh/Row Exlusive',
    6,'Exclusive') request,
    l.block block
    FROM
    v$lock l,
    v$session s,
    dba_objects o
    WHERE
    l.sid = s.sid
    AND
    username != 'SYSTEM'
    AND
    o.object_id(+) = l.id1; 
  • Lister les roles d'un utilisateur
COL "Utilisateur" FORMAT A25
COL "Roles" FORMAT A25		  
SELECT grantee "Utilisateur",
granted_role "Roles",
admin_option
FROM dba_role_privs
WHERE grantee = '&1'
UNION
   SELECT  grantee "Utilisateur",
   granted_role "Roles",
   admin_option
   FROM dba_role_privs
   WHERE  grantee IN (SELECT granted_role
                      FROM DBA_ROLE_PRIVS
                      WHERE grantee = '&1');
  • Lister les roles d'un utilisateur (sans droits DBA et schéma courant)
SELECT * FROM SESSION_PRIVS ;
  • Lister les privilèges d'un role
COL "Roles" FORMAT A25
COL "Privilege Systeme" FORMAT A40
SELECT grantee "Roles" ,
Privilege "Privilege Systeme",
admin_option
FROM dba_sys_privs
WHERE grantee IN (SELECT granted_role
                  FROM dba_role_privs
                  WHERE grantee = '&1'
                  UNION
                  SELECT granted_role
                  FROM dba_role_privs
                  WHERE grantee IN (SELECT granted_role
                  FROM DBA_ROLE_PRIVS
                  WHERE grantee = '&1'))
                  ORDER BY 1 DESC;