TiwikiSe connecter
Pour se connecter à une base de donnée sur linux en sysdba, il faut se connecter avec un compte linux memnbre du groupe dba (sur windows, c’est le groupe ora_dba). Par ex:
su - oracle
Ensuite il faut charger les bons paramètres Oracle (variable $PATH, $ORACLE_HOME,…):
export ORACLE_SID=<SID>
. oraenv
Commandes Oracle de base (à faire dans sqlplus)
Voir les paramètres d’une base de données:
show parameters
Filtrer sur un type de paramètre:
show parameters listener
Pour killer une session user:
select SID,SERIAL#,USERNAME,PROGRAM from v$session where type='USER';
alter system kill session "sid","serial#"
Exécuter un script sql:
@<nom du sript(.sql)>
Exécuter une commande shell dans sql:
!<nom de la commande>
Commandes linux utiles
Créer une nouvelle base
dbca
Checker les listeners sur linux:
ps -ef | grep tns ou ps -ef | grep lsnr
oracle 3766 1 0 11:43 ? 00:00:00 /oracle/product/rdbms/11.2.0.4/bin/tnslsnr LISTENER_DB01 -inherit
Checker les bases démarées sur linux:
ps -ef | grep pmon
oracle 2392 1 0 09:45 ? 00:00:00 ora_pmon_DB03
oracle 3070 1 0 11:04 ? 00:00:00 ora_pmon_DB01
Checker les process liés à une base:
ps -ef | grep <SID>
[oracle@vm-oracle-01 admin]$ ps -ef | grep DB01
oracle 3070 1 0 11:04 ? 00:00:00 ora_pmon_DB01
oracle 3072 1 0 11:04 ? 00:00:01 ora_psp0_DB01
oracle 3074 1 3 11:04 ? 00:02:12 ora_vktm_DB01
oracle 3078 1 0 11:04 ? 00:00:00 ora_gen0_DB01
oracle 3080 1 0 11:04 ? 00:00:00 ora_diag_DB01
oracle 3082 1 0 11:04 ? 00:00:00 ora_dbrm_DB01
oracle 3084 1 0 11:04 ? 00:00:01 ora_dia0_DB01
oracle 3086 1 0 11:04 ? 00:00:00 ora_mman_DB01
oracle 3088 1 0 11:04 ? 00:00:00 ora_dbw0_DB01
oracle 3090 1 0 11:04 ? 00:00:00 ora_lgwr_DB01
oracle 3092 1 0 11:04 ? 00:00:00 ora_ckpt_DB01
oracle 3094 1 0 11:04 ? 00:00:00 ora_smon_DB01
oracle 3096 1 0 11:04 ? 00:00:00 ora_reco_DB01
oracle 3098 1 0 11:04 ? 00:00:01 ora_mmon_DB01
oracle 3100 1 0 11:04 ? 00:00:00 ora_mmnl_DB01
oracle 3108 1 0 11:04 ? 00:00:00 ora_qmnc_DB01
oracle 3122 1 0 11:04 ? 00:00:00 ora_cjq0_DB01
oracle 3135 1 0 11:04 ? 00:00:00 ora_q000_DB01
oracle 3137 1 0 11:04 ? 00:00:00 ora_q001_DB01
oracle 3270 1 0 11:14 ? 00:00:00 ora_smco_DB01
oracle 3766 1 0 11:43 ? 00:00:00 /oracle/product/rdbms/11.2.0.4/bin/tnslsnr LISTENER_DB01 -inherit
oracle 3817 1 0 11:46 ? 00:00:00 oracleDB01 (LOCAL=NO)
oracle 4051 1 0 12:04 ? 00:00:00 ora_w000_DB01
oracle 4086 2320 0 12:08 pts/0 00:00:00 grep --color=auto DB01
Commandes sur le listener:
lsnrctl start|stop|status <listener>
Requêtes SQL utiles
Décrire les champs d’une table :
desc dba_directories
Voir les répertoires systèmes déclarés dans Oracle :
select * from dba_directories;
Voir les droits sur un répertoire :
SELECT * FROM dba_tab_privs WHERE table_name='<nom du rep>';
Créer un utilisateur et lui donner le droit de se connecter à la base :
create user testtsn identified by <password>;
GRANT CREATE SESSION to testtsn;
GRANT connect to testtsn;
Voir tous les utilisateurs de la base :
select * from dba_users where username;
Voir les tables d’un user :
SELECT table_name FROM user_tables;
Lister le nombre de tous les objets de chaque utilisateur de la base :
select owner,object_type,count(*) from dba_objects group by owner,object_type order by 1,2;
Voir les droits d’un user sur les tables :
select * from dba_tab_privs where grantee = upper('TESTTSN');
Voir les droits systèmes d’un user :
select * from dba_sys_privs where grantee = 'TESTTSN';
Lister toutes les fonctions, procédures et packages :
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE');
Lister les triggers
select trigger_name from dba_triggers ;
Sélectionner le tablespace d’un user :
SELECT username, default_tablespace, temporary_tablespace FROM dba_users WHERE username ='<username>';
Afficher le utl_file_dir :
select * from v$parameter where name = 'utl_file_dir';
Déverrouiller un compte oracle :
ALTER USER <username> ACCOUNT UNLOCK;
Récupérer le code SQL de tous les index d’une table :
select index_name,dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where table_name###### <nom table> and owner<username>;
Les tables systèmes
| System Table | Description |
|---|---|
| ALL_ARGUMENTS | Arguments in object accessible to the user |
| ALL_CATALOG | All tables, views, synonyms, sequences accessible to the user |
| ALL_COL_COMMENTS | Comments on columns of accessible tables and views |
| ALL_CONSTRAINTS | Constraint definitions on accessible tables |
| ALL_CONS_COLUMNS | Information about accessible columns in constraint definitions |
| ALL_DB_LINKS | Database links accessible to the user |
| ALL_ERRORS | Current errors on stored objects that user is allowed to create |
| ALL_INDEXES | Descriptions of indexes on tables accessible to the user |
| ALL_IND_COLUMNS | COLUMNs comprising INDEXes on accessible TABLES |
| ALL_LOBS | Description of LOBs contained in tables accessible to the user |
| ALL_OBJECTS | Objects accessible to the user |
| ALL_OBJECT_TABLES | Description of all object tables accessible to the user |
| ALL_SEQUENCES | Description of SEQUENCEs accessible to the user |
| ALL_SNAPSHOTS | Snapshots the user can access |
| ALL_SOURCE | Current source on stored objects that user is allowed to create |
| ALL_SYNONYMS | All synonyms accessible to the user |
| ALL_TABLES | Description of relational tables accessible to the user |
| ALL_TAB_COLUMNS | Columns of user’s tables, views and clusters |
| ALL_TAB_COL_STATISTICS | Columns of user’s tables, views and clusters |
| ALL_TAB_COMMENTS | Comments on tables and views accessible to the user |
| ALL_TRIGGERS | Triggers accessible to the current user |
| ALL_TRIGGER_COLS | Column usage in user’s triggers or in triggers on user’s tables |
| ALL_TYPES | Description of types accessible to the user |
| ALL_UPDATABLE_COLUMNS | Description of all updatable columns |
| ALL_USERS | Information about all users of the database |
| ALL_VIEWS | Description of views accessible to the user |
| DATABASE_COMPATIBLE_LEVEL | Database compatible parameter set via init.ora |
| DBA_DB_LINKS | All database links in the database |
| DBA_ERRORS | Current errors on all stored objects in the database |
| DBA_OBJECTS | All objects in the database |
| DBA_ROLES | All Roles which exist in the database |
| DBA_ROLE_PRIVS | Roles granted to users and roles |
| DBA_SOURCE | Source of all stored objects in the database |
| DBA_TABLESPACES | Description of all tablespaces |
| DBA_TAB_PRIVS | All grants on objects in the database |
| DBA_TRIGGERS | All triggers in the database |
| DBA_TS_QUOTAS | Tablespace quotas for all users |
| DBA_USERS | Information about all users of the database |
| DBA_VIEWS | Description of all views in the database |
| DICTIONARY | Description of data dictionary tables and views |
| DICT_COLUMNS | Description of columns in data dictionary tables and views |
| GLOBAL_NAME | global database name |
| NLS_DATABASE_PARAMETERS | Permanent NLS parameters of the database |
| NLS_INSTANCE_PARAMETERS | NLS parameters of the instance |
| NLS_SESSION_PARAMETERS | NLS parameters of the user session |
| PRODUCT_COMPONENT_VERSION | version and status information for component products |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| SESSION_PRIVS | Privileges which the user currently has set |
| SESSION_ROLES | Roles which the user currently has enabled. |
| SYSTEM_PRIVILEGE_MAP | Description table for privilege type codes. Maps privilege type numbers to type names |
| TABLE_PRIVILEGES | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| TABLE_PRIVILEGE_MAP | Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names |