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)
- Windows : clé de registre
HKLM\Software\Oracle(valeurINST_LOC), par défautC:\Program Files\Oracle\Inventory. - Linux / AIX : répertoire indiqué dans le fichier
oraInst.loc(sous/etc). - Solaris : fichier
oraInst.locsous/var/opt/oracle. - Fichier d'inventaire :
ContentsXML/inventory.xml; emplacement habituel$ORACLE_BASE/oraInventory(Linux/Unix).
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
- Espace disque et tablespaces
- Journaux système
- Enregistrements d'audit
- Sessions
- Sauvegardes
- Jobs batch
- Enterprise Manager