
| auteur : SheikYerbouti |
Comment calculer l'espace disque occupé par les objets Oracle
En interrogeant la vue DBA_SEGMENTS :
SQL> COLUMN TABLE FORMAT A30
SQL> COLUMN Taille FORMAT A15
SQL> COLUMN TABLESPACE FORMAT A20
SQL>
SQL> SELECT segment_name "TABLE"
2 , SUM(BYTES) /1024 || 'Ko' "Taille"
3 , blocks "Blocs"
4 , tablespace_name "TABLESPACE"
5 FROM dba_segments
6 WHERE segment_type = 'TABLE'
7 AND OWNER = 'FD'
8 GROUP BY segment_name, tablespace_name, blocks
9 ORDER BY segment_name ;
TABLE Taille Blocs TABLESPACE
ADRESSE 64Ko 8 USERS
CONTACT 64Ko 8 USERS
DEPT 64Ko 8 USERS
EMP 128Ko 16 USERS
EMPLOYE 64Ko 8 USERS
FACTURE 64Ko 8 USERS
LOV_BLOCK 64Ko 8 USERS
LOV_COLONNE 64Ko 8 USERS
LOV_ELEMENT_COLONNE 64Ko 8 USERS
LOV_ITEM 64Ko 8 USERS
LOV_LOV 64Ko 8 USERS
LOV_MODULE 64Ko 8 USERS
NOMBRES 64Ko 8 USERS
TELEPHONE 64Ko 8 USERS
TEST 2048Ko 256 USERS
TEST_LOV 4096Ko 512 USERS
TEST_TYPES 64Ko 8 USERS
TRACE 64Ko 8 SYSTEM
UTIL_PREFS 64Ko 8 USERS
UTIL_PREFS_ORDER 64Ko 8 USERS
UTIL_PREFS_RECORD_ORDER 64Ko 8 USERS
21 ligne(s) sélectionnée(s). |
Afficher la place occupée par toutes les tables d'un schéma particulier :
SQL> SELECT SUM(BYTES) /1024 /1024 || 'Mo' "Taille"
2 , SUM(blocks) "Blocs"
3 FROM dba_segments
4 WHERE segment_type = 'TABLE'
5 AND OWNER = 'FD'
6 GROUP BY OWNER ;
Taille Blocs
7,25Mo 928 |
Afficher le cumul de volumétrie de tous les objets d'un schéma particulier :
SQL> SELECT SUM(BYTES) /1024 /1024 || 'Mo' "Taille"
2 , SUM(blocks) "Blocs"
3 FROM dba_segments
4 WHERE OWNER = 'FD' ;
Taille Blocs
12,625Mo 1616 |
Afficher l'espace occupé par l'ensemble des schémas :
SQL> SELECT SUM(BYTES) /1024 /1024 || 'Mo' "Taille"
2 , SUM(blocks) "Blocs"
3 FROM dba_segments ;
Taille Blocs
647,484375Mo 82878 |
|
| auteur : SheikYerbouti |
La place occupée par les unités de traitement est interrogeable depuis la vue : USER_OBJECT_SIZE
- NAME Contient le nom de l'objet
- TYPE Contient le type de l'objet (TYPE, TYPE BODY, TABLE, VIEW, SYNONYM,SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, JAVA SOURCE, JAVA CLASS, JAVA RESOURCE ou JAVA DATA)
- SOURCE_SIZE Contient la taille en octets du code source
- PARSED_SIZE Contient la taille en octets du code DIANA (incluant les références aux objets sous-jacents)
- CODE_SIZE Contient la taille du code chargé en mémoire à l'exécution
- ERROR_SIZE Contient la taille des messages d'erreur
taille des unités de traitement | SQL> SELECT *
2 FROM USER_OBJECT_SIZE
3 WHERE TYPE IN ('PACKAGE BODY','PACKAGE','FUNCTION','PROCEDURE')
4 ORDER BY TYPE,NAME ;
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE
LIGNESALAIRE FUNCTION 280 261 563 0
PWD_DECODE FUNCTION 459 234 702 0
RETOURNE_PARAM FUNCTION 885 239 990 0
SRV_LECTURE_PARAMETRE FUNCTION 458 342 697 0
TEST_XX FUNCTION 869 269 1119 0
PKG_CORRECTION PACKAGE 414 1493 362 0
TEST_RECORD PACKAGE 233 761 875 0
PKG_CORRECTION PACKAGE BODY 11408 0 12467 0
TEST_RECORD PACKAGE BODY 403 0 1376 0
DEBUG PROCEDURE 1153 475 1343 0
DISPLAY_IMAGE PROCEDURE 894 509 1277 0
ECRITURE_ERREUR PROCEDURE 755 1517 673 0 |
La première ligne indique que la fonction LIGNESALAIRE occupe 280 octets de code source et nécessite 563 octets en mémoire à l'exécution.
|
| auteur : Helyos |
Pour connaître les bases de données actuellement en cours d'utilisation sur votre poste il vous suffit :
Sous Windows d'aller dans le gestionnaire des services : Click droit sur le poste de travail => gérer => services et applications => services
puis de regarder tous les services commençant par OracleService et qui sont formatés de la sorte OracleService<SID> (où SID correspond au nom de l'instance associée)
Sous Linux Vous pouvez lancer la commande suivante : ps -ef | egrep pmon_ | grep -v grep
|
| auteur : SheikYerbouti |
Voici un script permettant d'afficher la taille allouée, utilisée et libre de chaque pool :
COL "Total octets alloués" FORMAT A20
COL "octets utilisés" FORMAT A20
COL "octets libres" FORMAT A20
SELECT
a.POOL "Pool"
, b.Octets || ' (' || ROUND(b.Octets/1024/1024) || ' Mo)' "Total octets alloués"
, (b.Octets-a.BYTES) || ' (' || ROUND((b.Octets-a.BYTES)/1024/1024) || ' Mo)' "octets utilisés"
, a.BYTES || ' (' || ROUND(a.BYTES/1024/1024) || 'Mo)' "octets libres"
FROM V$SGASTAT a,
( SELECT POOL, SUM(BYTES) Octets, SUM(BYTES/1024/1024) Mo
FROM V$SGASTAT
WHERE POOL IS NOT NULL
GROUP BY POOL
ORDER BY POOL) b
WHERE NAME = 'free memory'
AND a.POOL = b.POOL
ORDER BY a.POOL ; |
Ainsi que le résultat de l'exécution
SQL> COL "Total octets alloués" FORMAT A20
SQL> COL "octets utilisés" FORMAT A20
SQL> COL "octets libres" FORMAT A20
SQL> SELECT
2 a.POOL "Pool"
3 , b.Octets || ' (' || ROUND(b.Octets/1024/1024) || ' Mo)' "Total octets alloués"
4 , (b.Octets-a.BYTES) || ' (' || ROUND((b.Octets-a.BYTES)/1024/1024) || ' Mo)' "octets utilisés"
5 , a.BYTES || ' (' || ROUND(a.BYTES/1024/1024) || 'Mo)' "octets libres"
6 FROM V$SGASTAT a,
7 ( SELECT POOL, SUM(BYTES) Octets, SUM(BYTES/1024/1024) Mo
8 FROM V$SGASTAT
9 WHERE POOL IS NOT NULL
10 GROUP BY POOL
11 ORDER BY POOL) b
12 WHERE NAME = 'free memory'
13 AND a.POOL = b.POOL
14 ORDER BY a.POOL
15 /
Pool Total octets alloués octets utilisés octets libres
java pool 29360128 (28 Mo) 0 (0 Mo) 29360128 (28Mo)
large pool 8404644 (8 Mo) 304668 (0 Mo) 8099976 (8Mo)
shared pool 41943040 (40 Mo) 22455544 (21 Mo) 19487496 (19Mo) |
|
| auteur : lalystar |
Pour fixer la valeur de certains paramètres de sessions automatiquement à chaque connection d'un utilisateur donné,
le plus simple est d'utiliser un trigger au niveau de la base.
Le trigger doit être créé en tant que SYS ou bien par un utilisateur ayant le privilège ADMINISTER DATABASE TRIGGER.
create or replace trigger TG_DB_AFTER_LOGON after logon on database
begin
if user = 'SCOTT' then
execute immediate 'alter session set sort_area_size=100000';
end if;
end; |
Exemple d'utilisation :
SQL> connect sys/... as sysdba
Connected.
SQL> create or replace trigger TG_DB_AFTER_LOGON after logon on database
2 begin
3 if user = 'SCOTT' then
4 execute immediate 'alter session set sort_area_size=100000';
5 end if;
6 end;
7
8 /
Trigger created.
SQL> show parameter sort_area_size
NAME TYPE VALUE
sort_area_size integer 65536
SQL> connect scott/...
Connected.
SQL> show parameter sort_area_size;
NAME TYPE VALUE
sort_area_size integer 100000 |
Ce genre de trigger est utile en particulier pour fixer les paramètres régionaux (séparateur décimal, séparateur de milliers...)
indépendamment de la plateforme (Windows, Unix...) et de l'outil utilisé pour la connection (SQL*Plus, SQL*Loader, ...).
|
| auteur : Jaouad | Create database link dblink_name
connect to user identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SID)
)
)' |
|
| auteur : Jaouad |
Ici il s'agit d'utiliser les tables externes, fonctionnalité disponible à partir de la 9i :
create directory BDUMP as 'background_dump_dest'
create table alert_log(text varchar2(80))
organization external
(
type oracle_loader
default directory BDUMP
access parameters (records delimited by newline)
location ('alert_SID.log')
)
reject limit 1000;
SQL> select * from alert_log ; |
|
| auteur : Jaouad |
Via le script suivant :
set linesize 250
col file_name format a40
select b.file_name, a.file#, a.cnt
from (select file#, count(1) cnt
from v$bh
group by file#) a,
dba_data_files b
where a.file# = b.file_id; |
|
| auteur : Laurent Schneider |
La requête suivante permet d'afficher les directory, avec les propriétés suivantes :
select OWNER,
DIRECTORY_NAME,
DIRECTORY_PATH
from DBA_DIRECTORIES
order by OWNER,
DIRECTORY_NAME;
OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS WORK_DIR /app/oracle/product/10.2.0.2/work
SYS TMP /tmp
SYS DATA_PUMP_DIR /app/oracle/product/10.2.0.2/rdbms/log/
SYS ADMIN_DIR /app/oracle/product/10.2.0.2/md/admin |
|
| auteur : Laurent Schneider |
Via la requête suivante, à partir de la 9i :
SELECT COMP_NAME,
STATUS,
VERSION
FROM DBA_REGISTRY
ORDER BY COMP_NAME;
COMP_NAME STATUS VERSION
JServer JAVA Virtual Machine VALID 10.2.0.2.0
Oracle Database Catalog Views VALID 10.2.0.2.0
Oracle Database Java Packages VALID 10.2.0.2.0
Oracle Database Packages and Types VALID 10.2.0.2.0
Oracle Expression Filter VALID 10.2.0.2.0
Oracle Text VALID 10.2.0.2.0
Oracle Workspace Manager VALID 10.2.0.3.0
Oracle XDK VALID 10.2.0.2.0
Oracle XML Database VALID 10.2.0.2.0
Oracle interMedia VALID 10.2.0.2.0 |
|
| auteur : Laurent Schneider |
À partir de la version 10.1.0.5 cpu2006jan, via le requête suivante :
SELECT ACTION_TIME,
ACTION,
VERSION,
ID
FROM DBA_REGISTRY_HISTORY
ORDER BY to_timestamp(ACTION_TIME,'DD.MM.YYYY HH24:MI:SSXFF');
ACTION_TIME ACTION VERSION ID
14.02.2006 10:28 CPU 4751932
07.03.2006 11:30 UPGRADE 10.2.0.2.0 |
|
| auteur : Laurent Schneider |
La requête suivante nous permet de lister les objets invalides, avec les informations suivantes :
- Schéma
- Type d'objet
- Date de création
- Date de dernière modification/compilation
SELECT OWNER,
OBJECT_TYPE,
OBJECT_NAME,
CREATED,
LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
ORDER BY OWNER, OBJECT_NAME;
OWNER OBJECT_TYPE OBJECT_NAME CREATED LAST_DDL_T
PUBLIC SYNONYM DBA_HIST_FILESTATXS 2006-04-18 2006-06-13
PUBLIC SYNONYM DBA_HIST_SQLBIND 2006-04-18 2006-06-13
PUBLIC SYNONYM DBA_HIST_SQLSTAT 2006-04-18 2006-06-13 |
|
| auteur : Laurent Schneider |
À partir de la version 10g, la requête suivante permet de lister les objets dans la poubelle, avec les propriétés suivantes :
- Nom du tablespace
- Type d'objet
- Propriétaire
- Nom original
- Nom BIN$
- SCN
- Date de création
- Date d'effacement des objets dans la poubelle (Recycle bin)
SELECT
TS_NAME,
TYPE,
OWNER,
ORIGINAL_NAME,
OBJECT_NAME,
DROPSCN,
CREATETIME,
DROPTIME
FROM dba_recyclebin
ORDER BY owner, type, original_name, dropSCN;
TS_NAME TYPE OWNER ORIG OBJECT_NAME DROPSCN CREATETIME DROPTIME
USERS TABLE SCOTT T BIN$F22M89kvcArgQwow5W1wCg==$0 3537036 2006-06-30 2006-06-30 |
|
| auteur : Laurent Schneider |
La requête suivante nous permet de lister les jobs, avec les caractéristiques suivantes :
- Schéma
- Numéro du job
- Date de prochaine éxecution
- Code des jobs
SELECT SCHEMA_USER,
JOB,
NEXT_DATE,
WHAT
FROM DBA_JOBS;
SCHEMA JOB NEXT_DATE WHAT
SYS 1 2006-07-01 scott.p; |
|
| auteur : Laurent Schneider |
La requête suivante permet de lister les sessions, avec les propriétés suivantes :
- Utilisateur DB
- Utilisateur SE
- SID
- SERIAL#
- Processus OS
- Type de serveur
- Status
- Machine cliente
- Programme
- Heure du login
- Nom du dispatcher
- Nom du shared server
SELECT s.USERNAME,
s.OSUSER,
s.SID,
s.SERIAL#,
p.SPID,
s.SERVER,
s.STATUS,
s.MACHINE,
s.PROGRAM,
TO_CHAR(s.LOGON_TIME, 'hh24:mi:ss') LOGON_TIME,
d.name DISP,
ss.name SERV
FROM V$PROCESS p,
V$SESSION s,
V$DISPATCHER d,
V$CIRCUIT c,
V$SHARED_SERVER ss
WHERE p.ADDR = s.PADDR
AND s.SADDR=c.SADDR (+)
AND c.DISPATCHER=d.PADDR (+)
AND c.SERVER=ss.PADDR (+)
AND s.USERNAME IS NOT NULL
ORDER BY s.USERNAME, p.SPID;
USERNAME OSUSER SID SERIAL# SPID SERVER STATUS MACHINE PROGRAM LOGON DISP SERV
DBSNMP oracle 129 31 2465846 SHARED ACTIVE pclsc01 emagent 06:17 D000 S003
DBSNMP oracle 122 6 2728088 NONE INACTIVE pclsc01 emagent 06:17 D000
SYS oracle 113 2669 2920628 DEDICATED ACTIVE pclsc01 sqlplus 10:38 |
Concernant les "longues opérations", la requête suivante vous permettra d'obtenir :
- Numéros de sessions
- Opération
- Travail effectué
- Travail total
- Temps restant
SELECT SID,
SERIAL#,
OPNAME,
SOFAR,
TOTALWORK,
TIME_REMAINING
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;
SID SERIAL# OPNAME SOFAR TOTAL TIME_REM
120 4001 RMAN: full datafile backup 33692 66496 76 |
|
| auteur : Jaouad |
Le tablespace SYSAUX est apparue avec la version 10g, et a pour but de décharger le tablespace SYSTEM de certains segments.
SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants ;
OCCUPANT_NAME SPACE_USAGE_KBYTES
LOGMNR 6080
LOGSTDBY 896
STREAMS 512
XDB 49600
AO 21248
XSOQHIST 21248
... |
|
| auteur : Jaouad |
Lors d'une réorganisation de base de données, si l'on souhaite déplacer certains objets
du tablespace SYSAUX vers d'autres tablespaces, comment procéder ?
Cette requête va nous donner la procédure à utiliser pour le déplacement en fonction des objets
SQL> SELECT occupant_name, schema_name, move_procedure
2 FROM v$sysaux_occupants ;
OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE
LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
STREAMS SYS
XDB XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO SYS DBMS_AW.MOVE_AWMETA
XSOQHIST SYS DBMS_XSOQ.OlapiMoveProc
... |
|
| auteur : Jaouad |
Cette requête fontionne sur toutes les versions :
SELECT TO_DATE(TO_CHAR(next_time, 'dd/mm/yyyy'), 'dd/mm/yyyy') Jour,
ROUND(SUM(blocks*block_size/1024/1024)) Mo
FROM v$archived_log
GROUP BY TO_DATE(TO_CHAR(next_time, 'dd/mm/yyyy'), 'dd/mm/yyyy')
ORDER BY 1 DESC; |
|
| auteurs : LeoAnderson, Jaouad |
Depuis la version 8, le BUFFER CACHE est divisé en plusieurs segments : DEFAULT, KEEP et RECYCLE.
Contrairement à ce que leurs noms peuvent laisser penser,
ils sont gérés tous les 3 exactement de la même façon, par les même règles LRU (Last Recently Used).
En général, on utilise le buffer KEEP pour y stocker les blocks des tables que l'on interroge souvent;
et le buffer RECYCLE pour des données plus volatiles.
Par exemple, si on a une table de REFERENCE, une table de COMMANDES et une table IMAGE_PRODUIT, on aura intérêt à répartir les tables de la façon suivante :
- COMMANDES sur le buffer pool DEFAULT
- REFERENCE dans le buffer pool KEEP
- IMAGE_PRODUIT dans le buffer pool RECYCLE
En effet, si l'on interroge l'image (volume important), cela va nécessiter de sortir du pool de nombreux blocs de REFERENCE
qu'il faudra recharger ultérieurement alors que les blocs de l'image auront très peu de chance de resservir...
Les noms des buffers pools ne sont qu'une astuce mnémotechnique, car ils pourraient très bien s'appeller A, B et C, cela ne changerait rien !
Remarque : depuis la 9i, il est possible de définir des buffer pools de taille de block différente ( db_nk_cache_size) mais les buffer KEEP et RECYCLE auront forcément comme taille de bloc la taille DEFAULT.
Donc, on a 3 pools de taille par défault et jusqu'à 4 buffers de taille différente, ce qui fait 7 zones buffers indépendantes au maximum !
(mais je vous déconseille vivement d'implémenter un tel système, ça deviendra impossible à administrer/tuner !)
La requête est la suivante :
col object_type format a10
col object_name format a20
SELECT dba_objects.owner,object_name,object_type,object_type
FROM dba_objects, dba_indexes,dba_tables
WHERE dba_objects.object_name = dba_indexes.index_name
AND dba_objects.object_name = dba_tables.table_name
AND dba_tables.buffer_pool = 'KEEP'
AND dba_indexes.buffer_pool = 'KEEP' ; |
|
| auteur : Jaouad |
Via cette requête :
SQL> SELECT dbms_utility.port_string FROM dual;
PORT_STRING
IBMPC/WIN_NT-8.1.0 |
|
| auteur : Jaouad |
Lors d'une migration vers une base 10g, comment savoir si les pré requis ont été vérifiés avant d'effectuer la migration ?
Via un nouvel outil d'upgrade ( Upgrade information Tool ) :
Prendre le fichier utlu101i.sql ( migration vers une 10Gr1 ) présent dans le dossier $ORACLE_HOME\rdbms\admin\ et le faire tourner sur la base source.
Il est possible de faire une migration vers la 10g ( quelque que soit la release ) sans passer par d'autres versions si la base source est 806, 817, 927
|
| auteur : Jaouad |
Voici comment déterminer les sessions qui sont killed for ever :
SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1
FROM v$session
WHERE paddr = addr); |
SVRMGRL> SELECT spid, osuser, s.program
FROM v$process p, v$session s
WHERE p.addr=s.paddr; |
Pour les tuer :
|
Consultez les autres F.A.Q's
Les sources présentés sur cette pages sont libre de droits,
et vous pouvez les utiliser à votre convenance. Par contre cette page de présentation de ces sources constitue une oeuvre intellectuelle protégée par les droits d'auteurs.
Copyright ©2006
Developpez LLC. Tout droits réservés Developpez LLC.
Aucune reproduction, même partielle, ne peut être faite de ce site et de
l'ensemble de son contenu : textes, documents et images sans l'autorisation
expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à 3 ans
de prison et jusqu'à 300 000 E de dommages et intérets.
Cette page est déposée à la SACD.
|