LLI Wiki — My Digital Knowledge Base

Requêtes d'administration Oracle : aide-mémoire

Requêtes et commandes courantes pour administrer Oracle : instance, utilisateurs, fichiers de contrôle, orapwd et sys_context.

Aide-mémoire de requêtes et commandes d'administration Oracle courantes : session, instance, utilisateurs, fichiers de contrôle, fichier de mots de passe et contexte sys_context.

Langue (NLS_LANG)

select * from v$diag_info;
ALTER SESSION SET NLS_LANGUAGE=ENGLISH;

Linux / Unix :

echo $NLS_LANG
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

Sous Windows, la valeur NLS_LANG se trouve dans le registre (regedit, clé HKLM\Software\Oracle, sous la clé ORACLE_HOME concernée). Elle peut être remplacée par exemple par AMERICAN_AMERICA.WE8ISO8859P1.

Erreurs de compilation

show errors;
select * from all_errors;

Utilisateurs et schémas

select user from dual;
select username from sys.all_users;
select username from dba_users order by 1;

Changer de schéma courant et vérifier :

ALTER SESSION SET CURRENT_SCHEMA = <schema_name>;
select sys_context('userenv', 'current_schema') from dual;

Identifier la base, le service et l'instance

Information Requête Où la retrouver
Service name select sys_context('userenv','service_name') from dual; tnsnames.ora
Instance name select instance_name from v$instance; init.ora
Nom de base select ora_database_name from dual;
SID de la session select sys_context('USERENV','SID') from dual; listener.ora
Nom global select * from global_name;
Service (paramètre) select value from v$parameter where name like '%service_name%';

Autres commandes utiles :

ALTER SYSTEM SWITCH LOGFILE;
select log_mode from v$database;
select owner, count(*) from dba_tables group by owner;
lsnrctl status
tnsping servername

Les fichiers tnsnames.ora et listener.ora se trouvent dans $ORACLE_HOME/network/admin.

Caractéristiques de la base

SELECT NAME, DBID, LOG_MODE, FLASHBACK_ON, OPEN_MODE, CREATED
  FROM v$database;

SELECT PROPERTY_NAME, PROPERTY_VALUE, DESCRIPTION
  FROM DATABASE_PROPERTIES
 ORDER BY PROPERTY_NAME;

SELECT INSTANCE_NAME, VERSION FROM V$INSTANCE;

Fonctionnalités détectées (par exemple Partitioning) :

SELECT NAME, DETECTED_USAGES
  FROM DBA_FEATURE_USAGE_STATISTICS
 ORDER BY NAME;

Fichiers de log et de trace

select value from v$diag_info;
show parameter background
show parameter background_dump_dest

select name from v$parameter where name = 'background_dump_destination';
find / -name 'al*.log' 2>/dev/null
ls -ltr /oracle/app/oracle/diag/rdbms/<DB>/<DB>/trace/al*

Lister tables et colonnes

SELECT table_name FROM user_tables;
SELECT table_name FROM all_tables;      -- tables accessibles à l'utilisateur
SELECT table_name FROM dba_tables;      -- toutes les tables (droits DBA)

SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'APP_SCHEMA';

select owner, table_name, column_name from ALL_TAB_COLUMNS
 where owner = 'APP_SCHEMA';

Instances installées sur un serveur Linux

cat /etc/oratab | grep -v '^#\|^\s*$' | cut -d: -f 1

Lister les fichiers init*.ora et spfile*.ora de chaque ORACLE_HOME :

for ORACLEHOME in `cat /etc/oratab | grep -v '^#\|^\s*$' | cut -d: -f 2 | uniq`
do
    ls -1 $ORACLEHOME/dbs/init*.ora   | sed -n 's/init\(.*\).ora/\1/p'
    ls -1 $ORACLEHOME/dbs/spfile*.ora | sed -n 's/spfile\(.*\).ora/\1/p'
done

Inventaire Oracle (oraInventory)

Créer un utilisateur

create user app_user identified by "<PASSWORD>";
grant connect to app_user;
grant resource to app_user;
grant unlimited tablespace to app_user;

Créer un schéma avec son tablespace :

select * from dba_temp_files;
select name from v$tablespace;
select * from dba_data_files;

create tablespace app_ts datafile '<path_to_datafile>' size 100M
  autoextend on next 100M maxsize 2000M
  extent management local segment space management auto;

create user app_user identified by "<PASSWORD>"
  default tablespace app_ts temporary tablespace temp;

Verrouiller, déverrouiller, expirer :

ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user PASSWORD EXPIRE;

Fichier de mots de passe (connexion distante)

show parameter remote_login_passwordfile
alter system set remote_login_passwordfile=exclusive scope=spfile;
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<PASSWORD> entries=10

Sous Windows :

orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=<PASSWORD>

Démarrage par étapes

startup nomount;
alter database mount;
alter database open;

startup mount;
alter database open;

startup restrict;
alter system disable restricted session;

Fichiers de contrôle

select value from v$parameter where name like '%control_file%';

-- sauvegarde binaire
alter database backup controlfile to '<path>/control.ctl';
-- sauvegarde texte (script)
alter database backup controlfile to trace as '<path>/control_trace.sql';

Contexte de session avec sys_context

select dbid from v$database;
select sys_context('userenv','sid') from dual;
select sys_context('userenv','os_user') from dual;       -- utilisateur OS du client
select sys_context('userenv','host') from dual;          -- machine cliente
select sys_context('userenv','db_domain') from dual;
select sys_context('userenv','ip_address') from dual;    -- IP du client
select sys_context('userenv','instance_name') from dual;
select sys_context('userenv','db_name') from dual;
select sys_context('userenv','session_user') from dual;
select sid, serial# from v$session
 where audsid = sys_context('userenv','sessionid');

Exemple de trigger de connexion qui interdit la connexion directe d'un compte et impose un database link donné via DBLINK_INFO :

CREATE OR REPLACE TRIGGER dbl_logon_trg
AFTER LOGON ON DATABASE
DECLARE
  v_username VARCHAR2(30)  := sys_context('USERENV','SESSION_USER');
  v_dbl_info VARCHAR2(200) := sys_context('USERENV','DBLINK_INFO');
BEGIN
  IF v_username = 'ETL_USER' THEN
    IF v_dbl_info IS NULL THEN
      write_log('failed: direct login', v_username, v_dbl_info);
      raise_application_error(-20101, 'Direct login not allowed.');
    ELSIF v_dbl_info NOT LIKE 'SOURCE_GLOBAL_NAME=SRC_DB, DBLINK_NAME=ETL_DBL%' THEN
      write_log('failed: wrong dblink', v_username, v_dbl_info);
      raise_application_error(-20102, 'Login from wrong link not allowed.');
    ELSE
      write_log('successful login', v_username, v_dbl_info);
    END IF;
  END IF;
END dbl_logon_trg;

Droits utiles :

grant create view to system;
grant create materialized view to system;
select granted_role from dba_role_privs where grantee in ('SYSTEM');

Tâches quotidiennes

Mastodon LLIMastodon SocialFosstodon