ORACLE - Commandes SQL utiles - Common SQL scripts

Liste des connexions actives :
Active Connections list

select userid, max(nvl(logoff$time, sysdate)) last_logoff from sys.aud$ group by userid; 

Liste des sessions actives :
Active Sessions list

select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,20) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid; 

Liste des users créés sur une instance
Instance users list

select USERNAME,CREATED
from DBA_USERS
where ACCOUNT_STATUS = 'OPEN'
and USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','MGMT_VIEW')
order by USERNAME;

Information concernant l'instance :
Instance infos

SELECT NAME,PLATFORM_NAME,DB_UNIQUE_NAME  FROM V$DATABASE

Version d'ORACLE :
Oracle Version

select * from v$version;

Création d'un répertoire de base de données
Database folder creation

Ceci peut être utile par exemple pour créer un autre répertoire que celui par défaut du DATA_PUMP_DIR

SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';

Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;

Grant succeeded.

Lister les répertoires 

select * from dba_directories;

Taille d'un schéma (User) en Mo
Mo Used for a schema (User)

select  a.owner username, round(sum(a.bytes)/1024/1024,2) "Taille (Mo)" from dba_segments a, (select tablespace_name, sum(bytes) total_space from dba_data_files group by tablespace_name) b where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2') and a.tablespace_name = b.tablespace_name and a.owner='Mon_User' group by a.tablespace_name, a.owner, b.total_space/1024/1024

Ajouter un champ à une table :
Add a Table Column

ALTER TABLE [ schema. ]table ADD { ( column [ datatype ] ) } ;

exemple :

ALTER TABLE MATABLE ADD (MONCHAMP NUMBER(15,2));

Connaitre le nom du champ et de la table sur lesquels une contrainte est définie
Constraint : Table and column name referent

select * from ALL_CONS_COLUMNS where CONSTRAINT_NAME like '......%'

Connaitre le Paramétrage du Character Set de l'Instance :

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

Bonnes pratiques pour créer les TABLESPACES :

CREATE DIRECTORY DUMPS_MOI AS 'I:\DUMPS_MOI';
GRANT read, write ON DIRECTORY DUMPS_MOI TO system;


CREATE TABLESPACE V9_DATA DATAFILE 
  'I:\oracle\PERF11R2\V9_DATA1.DBF' SIZE 33554416K AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED,
  'I:\oracle\PERF11R2\V9_DATA2.DBF' SIZE 33554416K AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED,
  'I:\oracle\PERF11R2\V9_DATA3.DBF' SIZE 10240M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TEMPORARY TABLESPACE V9_TEMPORAIRE TEMPFILE 
  'I:\oracle\PERF11R2\V9_TEMPORAIRE' SIZE 228M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE UNDO TABLESPACE V9_ROLLBACK DATAFILE 
  'I:\oracle\PERF11R2\V9_ROLLBACK' SIZE 360M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K

FLASHBACK ON;