49
Comment savoir quels objets sont actuellement dans le buffer cache ? C'est intéressant de le savoir dans la mesure où si certains objets se retrouvent en permanence dans le buffer cache, il peut être intéressant de les placer dans le sous- buffer KEEP où ils auront tendance à persister plus longtemps Ceci améliorera donc les performances des requêtes qui utilisent ces objets puisque les blocs seront trouvés directement dans le buffer (donc en mémoire). La requête suivante affiche le contenu du buffer cache à l'instant t ainsi que le pourcentage du buffer cache occupé par l'objet : SELECT b.owner, b.object_name, b.object_type, COUNT(*) blocks, ROUND(100 * RATIO_TO_REPORT t (COUNT t(*)) OVER(), 2) percent_cache FROM V$BH a, DBA_OBJECTS b WHERE b.object_id = a.objd GROUP BY b.owner, b.object_name, b.object_type ORDER BY 5 DESC Comment afficher la disposition des segments dans un tablespace [haut] Le script fourni affiche la disposition des segments (tables, indexes...) dans un tablespace donné, plus précisémment dans chaque datafile de ce tablespace. SELECT file_id, block_id, blocks, owner||'.'||segment_name segment FROM SYS.DBA_EXTENTS WHERE tablespace_name = UPPER('<nom du tablespace>') UNION SELECT file_id, block_id, blocks, 'Free' FROM SYS.DBA_FREE_SPACE WHERE tablespace_name = UPPER('<nom du tablespace>') ORDER BY 1,2,3 Comment savoir quels objets sont actuellement dans le buffer cache ?

Scripts DBA

Embed Size (px)

Citation preview

Page 1: Scripts DBA

Comment savoir quels objets sont actuellement dans le buffer cache ?

C'est intéressant de le savoir dans la mesure où si certains objets se retrouvent en permanence dans le buffer cache, il peut être intéressant de les placer dans le sous-buffer KEEP où ils auront tendance à persister plus longtempsCeci améliorera donc les performances des requêtes qui utilisent ces objets puisque les blocs seront trouvés directement dans le buffer (donc en mémoire).

La requête suivante affiche le contenu du buffer cache à l'instant t ainsi que le pourcentage du buffer cache occupé par l'objet :

SELECT b.owner, b.object_name, b.object_type, COUNT(*) blocks, ROUND(100 * RATIO_TO_REPORT t (COUNT t(*)) OVER(), 2) percent_cache FROM V$BH a, DBA_OBJECTS b WHERE b.object_id = a.objd GROUP BY b.owner, b.object_name, b.object_type ORDER BY 5 DESC

Comment afficher la disposition des segments dans un tablespace [haut] Le script fourni affiche la disposition des segments (tables, indexes...) dans un tablespace donné, plus précisémment dans chaque datafile de ce tablespace.

SELECT file_id, block_id, blocks, owner||'.'||segment_name segment FROM SYS.DBA_EXTENTS WHERE tablespace_name = UPPER('<nom du tablespace>') UNION SELECT file_id, block_id, blocks, 'Free' FROM SYS.DBA_FREE_SPACE WHERE tablespace_name = UPPER('<nom du tablespace>') ORDER BY 1,2,3

Comment savoir quels objets sont actuellement dans le buffer cache ?

C'est intéressant de le savoir dans la mesure où si certains objets se retrouvent en permanence dans le buffer cache, il peut être intéressant de les placer dans le sous-buffer KEEP où ils auront tendance à persister plus longtempsCeci améliorera donc les performances des requêtes qui utilisent ces objets puisque les blocs seront trouvés directement dans le buffer (donc en mémoire).

La requête suivante affiche le contenu du buffer cache à l'instant t ainsi que le pourcentage du buffer cache occupé par l'objet :

Page 2: Scripts DBA

SELECT b.owner, b.object_name, b.object_type, COUNT(*) blocks, ROUND(100 * RATIO_TO_REPORT t (COUNT t(*)) OVER(), 2) percent_cache FROM V$BH a, DBA_OBJECTS b WHERE b.object_id = a.objd GROUP BY b.owner, b.object_name, b.object_type ORDER BY 5 DESC OWNER OBJECT_NAME OBJECT_TYPE BLOCKS PERCENT_CACHE -------------------- -------------------- ------------------ ---------- ------------- SYS I_JOB_NEXT INDEX 38 39,58 SYS C_OBJ# CLUSTER 34 35,42 SYS C_TS# CLUSTER 4 4,17 SYS C_USER# CLUSTER 4 4,17 SYS I_SYSAUTH1 INDEX 3 3,13 SYS I_OBJ1 INDEX 2 2,08 SYS OBJ$ TABLE 2 2,08 SYS PROPS$ TABLE 2 2,08 SYS FILE$ TABLE 1 1,04 SYS I_FILE1 INDEX 1 1,04 SYS I_LINK1 INDEX 1 1,04 SYS I_USER1 INDEX 1 1,04 SYS SYSAUTH$ TABLE 1 1,04 SYS I_USER# INDEX 1 1,04 SYS I_IND1 INDEX 1 1,04

Comment afficher la disposition des segments dans un tablespace

Le script fourni affiche la disposition des segments (tables, indexes...) dans un tablespace donné, plus précisémment dans chaque datafile de ce tablespace.

SELECT file_id, block_id, blocks, owner||'.'||segment_name segment FROM SYS.DBA_EXTENTS WHERE tablespace_name = UPPER('<nom du tablespace>') UNION SELECT file_id, block_id, blocks, 'Free' FROM SYS.DBA_FREE_SPACE WHERE tablespace_name = UPPER('<nom du tablespace>') ORDER BY 1,2,3 FILE_ID BLOCK_ID BLOCKS SEGMENT ---------- ---------- ---------- --------------------------------------------------- 4 2 130 Free 4 132 260 U91295.OL_LI_DM_IDX2 4 392 260 U91295.OLD_LI_DAILYMAIL 4 652 260 U91295.OL_LI_DM_IDX2 4 912 260 U91295.OLD_LI_DAILYMAIL 4 1172 260 U91295.OLD_LI_DAILYMAIL ...

Page 3: Scripts DBA

9 382270 260 Free 9 382530 260 Free 9 382790 260 PPWEB.MMA_5_NORMALE On voit donc qu'au début du tablespace (à partir du bloc 2), il y a un espace libre de 130 blocs...

Quel est l'intérêt à part d'un point de vue académique ?Il arrive qu'on ait un tablespace qui ne contiennent quasimment rien et pour lequel on voudrait diminuer la taille d'un datafile (alter database datafile '...' resize ...). Mais, la commande échoue car l'espace n'est pas libre à la fin du datafile. Avec le script fourni, on voit quel segment occupe la position à la fin du datafile et on peut donc le déplacer dans un autre tablespace, puis faire le resize pour gagner de la place.

Dans l'exemple ci-dessus, la table PPWEB.MMA_5_NORMALE occupe la fin du datafile 9 ce qui nous empêche de faire un resize sur le datafile 9. On peut faire un alter table PPWEB.MMA_5_NORMALE move tablespace pour déplacer cette table dans un autre tablespace le temps de faire le resize du datafile 9.

C'est bien me direz-vous mais comment savoir l'espace maximum qu'on peut rogner sur un datafile ?C'est ce que nous renvoie la requête suivante :

SELECT a.file_id, a.file_name file_name, CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest, CEIL( blocks * blksize / 1024 / 1024 ) currsize, CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings, 'alter database datafile ''' || file_name || ''' resize ' || CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd FROM DBA_DATA_FILES a, ( SELECT file_id, MAX( block_id + blocks - 1 ) hwm FROM DBA_EXTENTS GROUP BY file_id ) b, ( SELECT TO_NUMBER( value ) blksize FROM V$PARAMETER WHERE name = 'db_block_size' ) WHERE a.file_id = b.file_id(+) AND CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0 ORDER BY 5 desc FILE_ID ---------- FILE_NAME -------------------------------------------------------------------------------- SMALLEST CURRSIZE SAVINGS ---------- ---------- ---------- CMD -------------------------------------------------------------------------------- 3 /bnp/oradbf/CAPDEV/metro/RSK/temp/temp1RSKDEV.dbf 3 1500 1497 alter database datafile '/bnp/oradbf/CAPDEV/metro/RSK/temp/temp1RSKDEV.dbf' resi ze 3m; 13

Page 4: Scripts DBA

/bnp/oradbf/RSKDEV/data2/GRMQUA_WIP/data0001/wct_grmqua0001.dbf 1 200 199 alter database datafile '/bnp/oradbf/RSKDEV/data2/GRMQUA_WIP/data0001/wct_grmqua 0001.dbf' resize 1m; 11 /bnp/oradbf/RSKDEV/data2/GRMDEV_STAGING/data0001/wct_grmdev0001.dbf 8 200 192 Si on regarde le premier datafile : sa taille actuelle est de 1500Mo (CURRSIZE), on peut le réduire jusqu'à 3Mo (SMALLEST) et donc gagner 1497Mo (SAVINGS). La colonne CMD nous donne la commande SQL à utiliser.

Coalescer tous les tablespaces de la base

Ce script SQL permet de coalescer tous les tablespace de la base.

set echo offset heading offset feedback off

prompt ---------------------------------prompt - Coalesce tous les tablespaces -prompt ---------------------------------

set term offspool tbs_coalesce.tmp

SELECT 'set echo on' FROM dual;SELECT 'set feedback on' FROM dual;

SELECT 'alter tablespace '||tablespace_name||' coalesce;'FROM dba_tablespacesWHERE contents not in ('TEMPORARY','UNDO');

spool offset term on

@tbs_coalesce.tmp

Liste des plus grandes consommations CPU

Ce script SQL permet d'afficher les opérations les plus gourmandes en terme de CPU

set echo offset feedback offset linesize 512

prompt ----------------------------------prompt - plus grandes consommation CPU --prompt ----------------------------------

column sid format 999 heading "SID"column username format a20 heading "Utilisateur"column command format a20 heading "Commande"column osuser format a20 heading "Utilisateur OS"column process format a20 heading "Processus OS"column machine format a20 heading "Machine"column value format 99,999 heading "Temps CPU"

SELECT s.sid sid,

Page 5: Scripts DBA

s.username username,UPPER(DECODE(command, 1,'Create Table',2,'Insert',3,'Select', 4,'Create Cluster',5,'Alter Cluster',6,'Update', 7,'Delete', 8,'Drop Cluster', 9,'Create Index', 10,'Drop Index', 11,'Alter Index', 12,'Drop Table', 13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table', 16,'Drop Sequence', 17,'Grant', 18,'Revoke', 19,'Create Synonym', 20,'Drop Synonym', 21,'Create View', 22,'Drop View', 23,'Validate Index', 24,'Create Procedure', 25,'Alter Procedure', 26,'Lock Table', 27,'No Operation', 28,'Rename', 29,'Comment', 30,'Audit', 31,'NoAudit', 32,'Create Database Link', 33,'Drop Database Link', 34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment', 37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace', 40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions', 43,'Alter User', 44,'Commit', 45,'Rollback', 46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction', 49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User', 52,'Create Role', 53,'Drop User', 54,'Drop Role', 55,'Set Role', 56,'Create Schema', 57,'Create Control File', 58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger', 61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index', 64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile', 67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure', 70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log', 73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot', 76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table', 86,'Truncate Cluster', 88,'Alter View', 91,'Create Function', 92,'Alter Function', 93,'Drop Function', 94,'Create Package', 95,'Alter Package', 96,'Drop Package', 97,'Create Package Body', 98,'Alter Package Body', 99,'Drop Package Body')) command,s.osuser osuser,s.machine machine,s.process process,t.value valueFROMv$session s,v$sesstat t,v$statname nWHERE s.sid = t.sidANDt.statistic# = n.statistic#ANDn.name = 'CPU used by this session'ANDt.value > 0ANDaudsid > 0ORDER BYt.value desc;

Informations sur la base

Ce script SQL permet d'obtenir les informations de la base

set echo offset feedback offset linesize 128

column NAME Format a40column DATAGUARD_BROKER Format a20column GUARD_STATUS Format a20

prompt -------------------------prompt - Infos base de données -

Page 6: Scripts DBA

prompt -------------------------

SELECT NAME, To_char(CREATED,'DD/MM/YYYY') CREATION, LOG_MODE, DATABASE_ROLE, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

Liste des fichiers de contrôle de la base

Ce script SQL permet d'afficher la liste des fichiers de contrôle de la base

set echo offset feedback offset linesize 160

prompt -----------------------------------prompt - Fichiers de controle de la base -prompt -----------------------------------

column Fichier format a80

SELECT NAME Fichier, DECODE (STATUS, '', 'VALID', 'INVALID') "Statut"FROM V$CONTROLFILEORDER BY NAME;

Liste des tablespaces par défaut

Ce script SQL permet d'afficher la liste des tablespaces par défaut ainsi que le nombre d'utilisateurs qui y sont associés

set echo offset feedback offset linesize 512

prompt ----------------------------------------prompt - Liste des tablespaces par défaut -prompt - et le nombre d'utilisateurs associés -prompt ----------------------------------------

column count(username) heading 'Nbre utilisateurs'

SELECT default_tablespace, count(username)FROM dba_usersWHERE username not in ('PUBLIC','_NEXT_USER')GROUP BY default_tablespace;

SELECT temporary_tablespace, count(username)FROM dba_usersWHERE

Page 7: Scripts DBA

username not in ('PUBLIC','_NEXT_USER')GROUP BY temporary_tablespace;

Liste des directories de la base

C script SQL affiche la liste des directories (répertoires) de la base

set echo offset feedback offset linesize 256

prompt --------------------------prompt - Directories de la base -prompt --------------------------

column CHEMIN format a80column NOM format a40

SELECT OWNER SCHEMA, DIRECTORY_NAME NOM, DIRECTORY_PATH CHEMINFROM DBA_DIRECTORIESORDER BY OWNER, DIRECTORY_NAME;

Liste des segments non extensibles

Ce script SQL affiche la liste des segments non extensibles de la base

set echo offset feedback offset linesize 512

prompt -------------------------------------prompt - Liste des segments non extensible -prompt -------------------------------------

column segment_name format a32

SELECT a.owner, decode(partition_name, null, segment_name, segment_name || ':' || partition_name) name, a.SEGMENT_TYPE, a.tablespace_name, a.bytes, a.initial_extent, a.next_extent, a.PCT_INCREASE, a.extents, a.max_extents, b.free, b.remain, decode(c.autoextensible, 0, 'NO', 'YES') autoextensible, decode(c.autoextensible, 0, 0, c.morebytes) max_can_grow_by, decode(c.autoextensible, 0, 0, c.totalmorebytes) sum_can_grow_byFROM dba_segments a, (SELECT df.tablespace_name, nvl(max(fs.bytes), 0) free, nvl(sum(fs.bytes), 0) remain FROM dba_data_files df, dba_free_space fs WHERE df.file_id = fs.file_id (+) GROUP BY

Page 8: Scripts DBA

df.tablespace_name) b, (SELECT tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', maxbytes - bytes, 0)) totalmorebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible FROM dba_data_files GROUP BY tablespace_name) cWHERE a.tablespace_name = b.tablespace_nameAND a.tablespace_name = c.tablespace_nameAND ((c.autoextensible = 0) OR ((c.autoextensible > 0) AND (a.next_extent > c.morebytes)))AND a.next_extent > b.freeORDER BY 5 desc, 3;

Classement des tablespace par consommation d'E/S

Ce script SQL affiche la liste des tablespaces et leur consommation en entrées/sorties

set echo offset feedback offset linesize 512

prompt -----------------------------------------------------prompt - Classement des Tablespaces par consommation d'E/S -prompt -----------------------------------------------------

column file_name format a60 heading "Data-File Name"column ts_name format a32 heading "Tablespace Name"column stat_reads format 999,999,999,999 heading "Physical Reads"column stat_writes format 999,999,999,999 heading "Physical Writes"column stat_breads format 999,999,999,999 heading "Physical Blk-Reads"column stat_bwrites format 999,999,999,999 heading "Physical Blk-Writes"

break on ts_name

SELECT t.name ts_name, f.name file_name, s.phyrds stat_reads, s.phyblkrd stat_breads, s.phywrts stat_writes, s.phyblkwrt stat_bwritesFROM v$tablespace t, v$datafile f, v$filestat sWHERE t.ts# = f.ts# AND f.file# = s.file#ORDER BY s.phyrds desc, s.phywrts desc;

Classement des évènement d'attente d'E/S de la base

Ce script SQL affiche la liste des évènements d'attente d'entrées/sorties de la base

Page 9: Scripts DBA

set echo offset feedback offset linesize 512

prompt ---------------------------------------------prompt - Classement des attentes d' E/S de la base -prompt ---------------------------------------------

column event format a30column segment_type format a10column segment_name format a20

SELECT event, segment_type, segment_name, file_id,block_id, blocksFROM dba_extents, v$session_waitWHERE p1text='file#'AND p2text='block#'AND p1=file_id AND p2 between block_id AND block_id + blocksORDER BY segment_type, segment_name;

Liste des verrous de la base

Ce script SQL affiche la liste des verrous présents dans la base

set echo offset feedback offset linesize 512

prompt -----------------------prompt - Verrous sur la base -prompt -----------------------

column sid format 999 heading "SID"column username format a10 heading "Utilisateur"column machine format a20 heading "Nom Machine"column object_name format a20 heading "Nom objet"column type format a4 heading "Type"column lmode format a20 heading "Mode de verrouillage"column request format 9999999 heading "Request Mode"column block format 9999999 heading "Lock Blocking"

SELECT s.sid sid, s.username username, s.machine machine, l.type type, o.object_name object_name, DECODE(l.lmode, 0,'None', 1,'Null', 2,'Row Share', 3,'Row Exlusive',

Page 10: Scripts DBA

4,'Share', 5,'Sh/Row Exlusive', 6,'Exclusive') lmode, DECODE(l.request, 0,'None', 1,'Null', 2,'Row Share', 3,'Row Exlusive', 4,'Share', 5,'Sh/Row Exlusive', 6,'Exclusive') request, l.block blockFROM v$lock l, v$session s, dba_objects oWHERE l.sid = s.sidAND username != 'SYSTEM'AND o.object_id(+) = l.id1;

Liste des paramètres de la base (init.ora)

Ce script affiche la liste des paramètres de la base

set echo offset feedback offset linesize 512set pagesize 1000

prompt --------------------------------------prompt - Paramètres fichier d'init INIT.ORA -prompt --------------------------------------

column value format a80

SELECT NAME, DECODE (TYPE, 1, 'Boolean', 2, 'String', 3, 'INTEGER', 4, 'FILE', 5, 'RESERVED', 6, 'BIG INTEGER') TYPE, VALUE, DESCRIPTION, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISDEFAULT, ISMODIFIED, ISADJUSTEDFROM V$PARAMETERORDER BY NAME;

Liste des profils de la base

Ce script SQL affiche la liste des profils de la base

set echo offset feedback offset linesize 512

prompt ---------------------------------prompt - Liste des profiles de la base -prompt ---------------------------------

Page 11: Scripts DBA

SELECT DISTINCT PROFILEFROM DBA_PROFILES;

Liste des segments de rollback de la base

Ce script SQL affiche la liste des segments de rollback de la base

SET ECHO OFFSET FEEDBACK OFFSET LINESIZE 256

PROMPT --------------------------------PROMPT - Rollback Segments de la base -PROMPT --------------------------------

column UTILISATEUR format a20

SELECT r.segment_name SEGMENT, r.owner UTILISATEUR, r.tablespace_name TABLESPACE, r.status STATUT, round(r.initial_extent/1024/1024) "Init (Mo)", round(r.next_extent/1024/1024) "Next (Mo)", s.extents "NBR EXTENTS", ROUND(s.rssize/1024/1024)"Taile (Mo)", s.xactsactive_transFROM dba_rollback_segs r, v$rollname n, v$rollstat sWHERE r.segment_name = n.nameAND n.usn = s.usn;

Liste des REDO LOGS de la base

Ce script SQL affiche la liste des REDO LOGS de la base

set echo offset feedback offset linesize 512

prompt ----------------------------------prompt - liste des REDO LOGS de la base -prompt ----------------------------------

column member format a60column archived format a10

SELECT A.GROUP#, B.MEMBER, A.THREAD#, A.SEQUENCE#, A.BYTES, A.MEMBERS, A.ARCHIVED, A.STATUS, A.FIRST_CHANGE#, A.FIRST_TIMEFROM V$LOG A, V$LOGFILE B

Page 12: Scripts DBA

WHERE A.GROUP# = B.GROUP#ORDER BY A.GROUP#;

Liste des rôles attribués de la base

Ce script SQL affiche la liste des rôles attribués de la base

set echo offset feedback offset linesize 512

prompt ----------------------------------------prompt - Liste des roles attribues de la base -prompt ----------------------------------------

break on GRANTED_ROLE skip 1column ADMIN format a8column DEFAUT format a8

SELECT GRANTED_ROLE ROLE, GRANTEE UTILISATEUR, ADMIN_OPTION ADMIN, DEFAULT_ROLE DEFAUTFROM DBA_ROLE_PRIVSWHERE GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)ORDER BY GRANTED_ROLE, ADMIN_OPTION;

Liste des rôles attribués aux rôles de la base

Ce script SQL affiche la liste des rôles attribués aux rôles de la base

set echo offset feedback offset linesize 512

prompt --------------------------------------------------prompt - Liste des roles attribues aux roles de la base -prompt --------------------------------------------------

break on GRANTED_ROLE skip 1column ADMIN format a8column DEFAUT format a8

SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION ADMIN, DEFAULT_ROLE DEFAUTFROM SYS.DBA_ROLE_PRIVSWHERE GRANTEE IN (SELECT ROLE FROM DBA_ROLES)ORDER BY GRANTED_ROLE, ADMIN_OPTION;

Liste des rôles utilisateurs de la base

Page 13: Scripts DBA

Ce script SQL affiche la liste des rôles utilisateurs de la base

set echo offset feedback offset linesize 512

prompt -------------------------------------------prompt - liste des roles utilisateurs de la base -prompt -------------------------------------------

SELECT ROLE, PASSWORD_REQUIREDFROM SYS.DBA_ROLESWHERE ROLE NOT IN ('AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','CTXAPP','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE','IMP_FULL_DATABASE','JAVADEBUGPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','OEM_MONITOR','OLAP_DBA','RECOVERY_CATALOG_OWNER','RESOURCE','SELECT_CATALOG_ROLE','SNMPAGENT','WKADMIN','WKUSER');

Liste des rôles utilisateurs attribués dans la base

Ce script SQL affiche la liste des rôles utilisateurs attribués dans la base

set echo offset feedback offset linesize 512

prompt -------------------------------------------------------prompt - Liste des roles utilisateurs attribues dans la base -prompt -------------------------------------------------------

break on GRANTED_ROLE skip 1column ADMIN format a8column DEFAUT format a8

SELECT GRANTED_ROLE ROLE, GRANTEE UTILISATEUR, ADMIN_OPTION ADMIN, DEFAULT_ROLE DEFAUTFROM SYS.DBA_ROLE_PRIVS

Page 14: Scripts DBA

WHERE GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)AND GRANTED_ROLE NOT IN ('AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','CTXAPP','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE','IMP_FULL_DATABASE','JAVADEBUGPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','OEM_MONITOR','OLAP_DBA','RECOVERY_CATALOG_OWNER','RESOURCE','SELECT_CATALOG_ROLE','SNMPAGENT','WKADMIN','WKUSER')ORDER BY GRANTED_ROLE, ADMIN_OPTION;

Liste des rôles utilisateurs attribués aux rôles de la base

Ce script SQL affiche la liste des rôles utilisateurs attribués aux rôles de la base

set echo offset feedback offset linesize 512

prompt ---------------------------------------------------------------prompt - Liste des rôles utilisateurs attribués aux rôles de la base -prompt ---------------------------------------------------------------

break on GRANTED_ROLE skip 1

SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLEFROM SYS.DBA_ROLE_PRIVSWHERE GRANTED_ROLE NOT In ('AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','CTXAPP','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE','IMP_FULL_DATABASE',

Page 15: Scripts DBA

'JAVADEBUGPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','OEM_MONITOR','OLAP_DBA','RECOVERY_CATALOG_OWNER','RESOURCE','SELECT_CATALOG_ROLE','SNMPAGENT','WKADMIN','WKUSER')AND GRANTEE IN (SELECT ROLE FROM SYS.DBA_ROLES)ORDER BY GRANTED_ROLE, ADMIN_OPTION;

Liste des rôles de la base

Ce script SQL affiche la liste des rôles de la base

set echo off feedback off linesize 512 pagesize 50

prompt ------------------------------prompt - Liste des roles de la base -prompt ------------------------------

SELECT ROLE, PASSWORD_REQUIREDFROM DBA_ROLESORDER BY ROLE;

Liste des évènements de la session

Ce script SQL affiche la liste des évènements de la session

set echo offset feedback offset linesize 512

prompt -------------------------prompt - Evenements de session -prompt -------------------------

column event format a32

SELECT B.USERNAME, B.SID, B.SERIAL#, A.EVENT, A.TOTAL_WAITS, A.TOTAL_TIMEOUTS, A.TIME_WAITED, A.AVERAGE_WAITFROM V$SESSION_EVENT A, V$SESSION BWHERE A.SID = B.SIDORDER BY

Page 16: Scripts DBA

1;

Liste des sessions actives de la base

Ce script SQL affiche la liste des sessions actives de la base

set echo offset feed offset linesize 512

prompt ------------------------------------------prompt - Liste des sessions actives de la base --prompt ------------------------------------------

column machine format a30column osuser format a30column module format a20

SELECT machine, process, osuser, username, schemaname, status, lockwait, sid, serial#, module, actionFROM v$sessionWHERE username is not nullAND osuser is not nullORDER BY machine, osuser, username, schemaname, status, module;

Occupation mémoire de la SGA

Ce script SQL affiche l'occupation mémoire de la SGA

set echo offset feedback offset linesize 512

prompt --------------------------------prompt - Occupation memoire de la SGA -prompt --------------------------------

column dummy noprintcolumn area format a20 heading 'Main SGA Areas'column name format a20column pool format a20column bytes format 999,999,999,999column sum(bytes) format 999,999,999,999

break on reportcompute sum of sum(bytes) on report

SELECT 1 dummy,

Page 17: Scripts DBA

'DB Buffer Cache' area, name, sum(bytes)FROM v$sgastatWHERE pool is null AND name = 'db_block_buffers'GROUP BY nameUNION ALLSELECT 2, 'Shared Pool', pool, sum(bytes) FROM v$sgastatWHERE pool = 'shared pool'GROUP BY poolUNION ALLSELECT 3, 'Large Pool', pool, sum(bytes) FROM v$sgastatWHERE pool = 'large pool'GROUP BY poolUNION ALLSELECT 4, 'Java Pool', pool, sum(bytes) FROM v$sgastatWHERE pool = 'java pool'GROUP BY poolUNION ALLSELECT 5, 'Redo Log Buffer', name, sum(bytes)FROM v$sgastatWHERE pool is null AND name = 'log_buffer'GROUP BY nameUNION ALLSELECT 6, 'Fixed SGA', name, sum(bytes)FROM

Page 18: Scripts DBA

v$sgastatWHERE pool is null AND name = 'fixed_sga'GROUP BY nameORDER BY 4 desc;

column area format a20 heading 'Shared Pool Areas'

prompt ----------------------------prompt -- Detail du pool partagé --prompt ----------------------------

SELECT 'Shared Pool' area, name, sum(bytes) FROM v$sgastatWHERE pool = 'shared pool' AND name in ('library cache','dictionary cache','free memory','sql area')GROUP BY nameUNION ALLSELECT 'Shared Pool' area, 'miscellaneous', sum(bytes) FROM v$sgastatWHERE pool = 'shared pool' AND name not in ('library cache','dictionary cache','free memory','sql area')GROUP BY poolORDER BY 3 desc;

Liste des évènements système

Ce script SQL affiche la liste des évènements système de la base

set echo offset feedback offset linesize 512

prompt --------------------------------prompt - Liste des evenements systeme -prompt --------------------------------

column event format a32

column total_waits format999,999,999,999 heading "Total Attentes"column total_timeouts format999,999,999,999 heading "Total Timeouts"column time_waited format999,999,999,999 heading "Temps attendu"column average_wait format999,999,999,999 heading "Moyenne d'attente"

SELECT EVENT EVENEMENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED,

Page 19: Scripts DBA

AVERAGE_WAITFROM V$SYSTEM_EVENTORDER BY 4 DESC;

Informations sur les tablespaces

Ce script SQL affiche les informations des tablespaces

set echo offset feedback offset verify offset linesize 512

set term offCOLUMN block_size NOPRINT new_value block_sizeSELECT value block_size FROM v$parameterWHERE name='db_block_size';

set term on

prompt ------------------------------prompt - Infos sur les tablespaces --prompt ------------------------------

col Tablespace_name Heading 'Tablespace'col Megs_Alloc Heading 'Mo alloués'col Megs_Free Heading 'Mo libres'col Megs_Used Heading 'Mo utilisés'col Pct_Free Heading 'Pct Free'col Pct_Used Heading 'Pct Used'col Init_Ext Heading 'Init Ext'col Next_Ext Heading 'Next Ext'col Min_Ext Heading 'Min Ext'col Max_Ext Heading 'Max Ext'col Num_Segs Heading 'Nbre Segments'col Num_Exts Heading 'Nbre Extents'

SELECT c.tablespace_name, round(a.bytes/1048576) Megs_Alloc, round(b.bytes/1048576) Megs_Free, round((a.bytes-b.bytes)/1048576) Megs_Used, round(b.bytes/a.bytes * 100) Pct_Free, round((a.bytes-b.bytes)/a.bytes * 100) Pct_Used, round(c.initial_extent/1048576) Init_Ext, round(c.next_extent/1048576) Next_Ext, round(a.minbytes/1048576) Min_Ext, round(a.maxbytes/1048576) Max_Ext, nvl(d.num_segs,0) Num_segs, nvl(d.num_exts,0) Num_ExtsFROM ( SELECT tablespace_name, sum(a.bytes) bytes, min(a.bytes) minbytes, max(a.bytes) maxbytes FROM sys.dba_data_files a GROUP BY tablespace_name) a, ( SELECT a.tablespace_name,

Page 20: Scripts DBA

nvl(sum(b.bytes),0) bytes FROM sys.dba_data_files a, sys.dba_free_space b WHERE a.tablespace_name = b.tablespace_name (+) AND a.file_id = b.file_id (+) GROUP BY a.tablespace_name) b, sys.dba_tablespaces c, ( SELECT tablespace_name, count(distinct segment_name) num_segs, count(extent_id) num_exts FROM sys.dba_extents GROUP BY tablespace_name) dWHERE a.tablespace_name = b.tablespace_name(+)AND a.tablespace_name = c.tablespace_nameAND a.tablespace_name = d.tablespace_name(+)ORDER BY c.tablespace_name;

Liste des déclencheurs (triggers) de la base

Ce script SQL affiche la liste des déclencheurs de la base

set echo offset feed off

PROMPT -------------------------------------PROMPT - Liste des déclencheurs de la base -PROMPT -------------------------------------

SELECT owner UTILISATEUR, trigger_name TRIGGER, status STATUTFROM all_triggersWHERE owner not in ('SYS','SYSTEM')ORDER BY owner, trigger_name;

Liste des utilisateurs de la base

Ce script SQL affiche la liste des utilisateurs de la base

set echo offset feedback offset linesize 512

prompt -------------------------------------prompt - Liste des utilisateurs de la base -prompt -------------------------------------

column count(b.object_id) heading 'Nbre d'objets'

SELECT

Page 21: Scripts DBA

a.username UTILISATEUR, a.account_status STATUT, a.default_tablespace "TABLESPACE PAR DEFAUT", a.temporary_tablespace "TABLESPACE TEMPORAIRE", count(b.object_id)FROM dba_users a, dba_objects bWHERE a.username = b.owner(+)GROUP BY a.username, a.account_status, a.default_tablespace, a.temporary_tablespaceORDER BY a.account_status DESC, a.username;

Liste des activités du segment de rollback de la base

Ce script SQL afiche la liste des activités du segment de rollback de la base

set echo offset feedback offset linesize 512

prompt ---------------------------------------------------------prompt - Liste des activités du segment de rollback de la base -prompt ---------------------------------------------------------

SELECT A.NAME, B.XACTS, C.SID, C.SERIAL#, C.USERNAME, D.SQL_TEXTFROM V$ROLLNAME A, V$ROLLSTAT B, V$SESSION C, V$SQLTEXT D, V$TRANSACTION EWHERE A.USN = B.USNAND B.USN = E.XIDUSNAND C.TADDR = E.ADDRAND C.SQL_ADDRESS = D.ADDRESSAND C.SQL_HASH_VALUE = D.HASH_VALUEORDER BY A.NAME, C.SID, D.PIECE;

Mise hors ligne des segments de rollback

Ce script SQL permet de placer hors ligne tous les segments de rollback

set echo offset heading offset feedback off

PROMPT --------------------------------------------PROMPT - Mise Hors ligne des segments de rollback -PROMPT --------------------------------------------

Page 22: Scripts DBA

set term offspool rbs_hors_ligne.tmp

SELECT 'set echo on' FROM dual;SELECT 'set feedback on' FROM dual;

SELECT 'alter rollback segment '||segment_name||' offline;'FROM dba_rollback_segsWHERE segment_name != 'SYSTEM'AND status = 'ONLINE';

spool offset term on

@rbs_hors_ligne.tmp

Remise en ligne des segments de rollback

Ce script SQL permet de remettre en ligne tous les segments de rollback

set echo offset heading offset feedback off

PROMPT --------------------------------------------PROMPT - Remise en ligne des segments de rollback -PROMPT --------------------------------------------

set term offspool rbs_en_ligne.tmp

SELECT 'set echo on' FROM dual;SELECT 'set feedback on' FROM dual;

SELECT 'alter rollback segment '||segment_name||' online;'FROM dba_rollback_segsWHERE segment_name != 'SYSTEM'AND status = 'OFFLINE';

spool offset term on

@rbs_en_ligne.tmp

Mise hors ligne des tablespaces de la base

Ce script SQL permet de mettre hors ligne tous les tablespaces

set echo offset heading offset feedback off

prompt -----------------------------------prompt - Mise hors ligne des tablespaces -prompt -----------------------------------

set term offspool tbs_hors_ligne.tmp

Page 23: Scripts DBA

SELECT 'set echo on' FROM dual;SELECT 'set feedback on' FROM dual;

SELECT 'alter tablespace '||tablespace_name||' offline;'FROM dba_tablespacesWHERE tablespace_name not in ('SYSTEM','OUTLN')AND status = 'ONLINE';

spool offset term on

@tbs_hors_ligne.tmp

Remise en ligne des tablespaces de la base

Ce script SQL permet de remettre en lignes tous les tablespaces de la base

set echo offset heading offset feedback off

prompt ---------------------------------prompt - Mise en ligne des tablespaces -prompt ---------------------------------

set term offspool tbs_en_ligne.tmp

SELECT 'set echo on' FROM dual;SELECT 'set feedback on' FROM dual;

SELECT 'alter tablespace '||tablespace_name||' online;'FROM dba_tablespacesWHERE tablespace_name not in ('SYSTEM','OUTLN')AND status = 'OFFLINE';

spool offset term on

@tbs_en_ligne.tmp

Désactivation de tous les déclencheurs (triggers) de la base

Ce script SQL permet de désactiver tous les déclencheurs de la base

set echo offset head offset feed off

PROMPT -------------------------------------------------PROMPT - Desactivation de tous les triggers de la base -PROMPT -------------------------------------------------

spool triggers_hors_ligne.tmp

SELECT 'alter trigger '||owner||'.'||trigger_name||' disable;'

Page 24: Scripts DBA

FROM all_triggersWHERE owner not in ('SYS','SYSTEM')AND status = 'ENABLED';

spool offset feed onset echo on@triggers_hors_ligne.tmp

Activation de tous les déclencheurs (triggers) de la base

Ce script SQL permet d'activer tous les déclencheurs de la base

set echo offset head offset feed off

PROMPT -------------------------------------------------PROMPT - Re-activation de tous les triggers de la base -PROMPT -------------------------------------------------

spool triggers_en_ligne.tmp

SELECT 'alter trigger '||owner||'.'||trigger_name||' enable;'FROM all_triggersWHERE owner not in ('SYS','SYSTEM')AND status = 'DISABLED';

spool offset feed onset echo on@triggers_en_ligne.tmp

Liste des types d'objets des tablespaces

Ce script SQL permet d'afficher la liste des types d'objets de chaque tablespace

set echo offset feedback offset linesize 512

prompt ----------------------------------prompt - Types d'objets des tablespaces -prompt ----------------------------------

column count(*) heading 'Nbre d''objets'column sum(bytes) heading 'Octets'

SELECT tablespace_name TABLESPACE, owner UTILISATEUR, segment_type "TYPE SEGMENT", count(*), sum(bytes)FROM sys.dba_extentsGROUP BY tablespace_name, owner, segment_typeORDER BY tablespace_name, owner, segment_type;

Page 25: Scripts DBA

Infos de l'instance et de la base

Ce script SQL affiche les information sur l'instance et la base

set echo offset feedback offset heading off

prompt ----------------------------------prompt - Instance et version de la base -prompt ----------------------------------

SELECT 'Block Size = '||value FROM v$parameter where name = 'db_block_size'UNIONSELECT 'Version = '||banner FROM v$version where rownum = 1UNIONSELECT 'Instance = '||name FROM v$database;

Occupation des tablespaces

Affichage de l'occupation des tablespaces

col tablespace_name format a30 head "Tablespace" col taille format 99,999,999 head "Taille|(en Mo)" col libre format 99,999,999 head "Disponible|(en Mo)" col pctf format 990 head "%|free" break on report compute sum of taille libre on report compute avg of pctf on report SELECT a.tablespace_name tablespace_name , 100-(100-round(b.total_bytes*100/sum(c.user_bytes),2)) pctf, (round(b.total_bytes/1024/1024,2)) libre, (round(sum(c.bytes)/1024/1024,2)) taille FROM dba_tablespaces a, dba_data_files c, dba_free_space_coalesced b WHERE a.tablespace_name = b.tablespace_name AND c.tablespace_name = a.tablespace_name GROUP BY a.tablespace_name, a.status, a.contents, a.allocation_type, b.percent_extents_coalesced, b.total_extents, b.total_bytes ORDER BY 2 ;

clear breaks clear compute

Estimation des lectures sur disque selon la valeur du DB_CACHE_SIZE

Estimation des lectures sur disque selon la valeur du DB_CACHE_SIZE (bouton conseil du cache tampon dans OEM)

COL size_for_estimate FORMAT 999,999,999,999 HEADING 'Cache Size (MB)' COL buffers_for_estimate FORMAT 999,999,999 HEADING 'Buffers' COL estd_physical_read_factor FORMAT 990.90 HEADING 'Estd Phys|Read Factor' COL estd_physical_reads FORMAT 999,999,999 HEADING 'Estd Phys| Reads'

SELECT size_for_estimate,

Page 26: Scripts DBA

size_factor, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = ( SELECT value FROM v$parameter WHERE name = 'db_block_size' ) AND advice_status = 'ON';

Affichage de la liste des paramètres cachés (facultatifs)

SELECT nam.ksppinm NAME, val.KSPPSTVL VALUE FROM x$ksppi nam, x$ksppsv val WHERE nam.indx = val.indx AND nam.ksppinm like lower('%&param_nam%' ) ORDER BY 1 ;

Liste des sessions les plus consommatrices

Liste des sessions les plus consommatrices (modifier le ORDER BY selon le tri souhaité)

SET LINESIZE 500 SET PAGESIZE 1000 SET FEEDBACK OFF SET VERIFY OFF SET SERVEROUTPUT ON

BEGIN

Dbms_Output.Enable(1000000); Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') || Lpad('Reads/Execution',16,' ') || Lpad('Buffer Gets',12,' ') || Lpad('Disk Reads',12,' ') || Lpad('Executions',12,' ') || Lpad('Sorts',12,' ') || Lpad('Address',10,' ')); Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' || Lpad('-',15,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',9,'-')); FOR cur_rec IN (SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text, Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address

Page 27: Scripts DBA

FROM v$sqlarea a ORDER BY 2 DESC) WHERE ROWNUM <= &top) LOOP Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') || Lpad(cur_rec.reads_per_execution,16,' ') || Lpad(cur_rec.buffer_gets,12,' ') || Lpad(cur_rec.disk_reads,12,' ') || Lpad(cur_rec.executions,12,' ') || Lpad(cur_rec.sorts,12,' ') || Lpad(cur_rec.address,10,' ')); END LOOP; END; /

Liste des sessions par consommation de ressource

Liste des sessions par consommation de ressource

SELECT ses.sid , DECODE(ses.action,NULL,'online','batch') "User" , MAX(DECODE(sta.statistic#,9,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s" , MAX(DECODE(sta.statistic#,40,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s" , 60*24*(sysdate-ses.logon_time) "Minutes" FROM V$SESSION ses , V$SESSTAT sta WHERE ses.status = 'ACTIVE' AND sta.sid = ses.sid AND sta.statistic# IN (9,40) GROUP BY ses.sid, ses.action, ses.logon_time ORDER BY SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) ) / greatest(3600*24*(sysdate-ses.logon_time),1) DESC

Afficher le nombre de lignes de chaque table de l'utilisateur courant

Ce script SQL permet d'afficher la liste des tables de l'utilisateur courant ainsi que le nombre de lignes

set echo offset heading offset feedback offset linesize 512set pagesize 10000

prompt ------------------------------------------------------prompt - Compte les lignes de chaque table du user courant --prompt ------------------------------------------------------

SET TERM OFF

spool compte_lignes_tables.tmp

SELECT 'select '''||table_name||' = ''||count(*) from ' || table_name||' having count(*) > 0;'FROM user_tables WHERE table_name not like 'SYS_IOT_OVER_%' ORDER BY table_name;

spool off

Page 28: Scripts DBA

SET TERM ON

@compte_lignes_tables.tmp

Liste de toutes les tables de la base

Ce script SQL affiche la liste de toutes les tables de la base

set echo offset feedback offset linesize 512

prompt -------------------------------prompt - Liste des tables de la base -prompt -------------------------------

break on OWNER

SELECT *FROM DBA_TABLESWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY OWNER, TABLE_NAME;

Liste des tables non indexées de la base

Ce script SQL affiche la liste de toutes les tables de la base qui ne possédent aucun index

set echo offset feedback offset linesize 512

prompt -------------------------------prompt - Liste des tables sans index -prompt -------------------------------

break on OWNER skip 1

SELECT OWNER, TABLE_NAMEFROM ALL_TABLESWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')MINUSSELECT OWNER, TABLE_NAMEFROM ALL_INDEXESWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP');

Liste des tables sur indexées de la base

Ce script SQL affiche la liste des tables de la base sur lesquelles il existe plus de 6 index

set echo offset feedback off

Page 29: Scripts DBA

set linesize 512

prompt -----------------------------------prompt - Liste des tables très indexées --prompt -----------------------------------

SELECT OWNER, TABLE_NAME, COUNT (*) "Nbre"FROM ALL_INDEXESWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')GROUP BY OWNER, TABLE_NAMEHAVING COUNT (*) > ('6');

Liste des tables partitionnées de la base

Ce script SQL affiche la liste de toutes les tables partitionnées de la base

set echo offset feedback offset linesize 512

prompt ---------------------------------------------prompt - Liste des tables partitionnées de la base -prompt ---------------------------------------------

break on TABLE_OWNER on TABLE_NAME skip 1

SELECT TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME, PARTITION_NAME, PARTITION_POSITION, LOGGING, HIGH_VALUE, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENT, PCT_INCREASEFROM SYS.DBA_TAB_PARTITIONSWHERE TABLE_OWNER NOT IN ('SYS','SYSTEM')ORDER BY TABLE_OWNER, TABLE_NAME, PARTITION_POSITION;

Liste des clusters de la base

Ce script SQL permet d'afficher tous les clusters de la base

set echo offset feedback offset linesize 512

prompt ----------------------------------

Page 30: Scripts DBA

prompt - Liste des clusters de la base --prompt ----------------------------------

column tab_column_name format a32break on OWNER skip 1 on TABLESPACE_NAME on CLUSTER_NAME on TABLE_NAME

SELECT A.OWNER, A.TABLESPACE_NAME, A.CLUSTER_NAME, B.TABLE_NAME, B.TAB_COLUMN_NAME, B.CLU_COLUMN_NAME, A.CLUSTER_TYPE, A.AVG_BLOCKS_PER_KEY, A.KEY_SIZE, A.FUNCTION, A.HASHKEYS, A.PCT_FREE, A.PCT_USED, A.INI_TRANS, A.MAX_TRANS, A.INITIAL_EXTENT, A.NEXT_EXTENT, A.MIN_EXTENTS, A.MAX_EXTENTS, A.PCT_INCREASEFROM DBA_CLUSTERS A, DBA_CLU_COLUMNS BWHERE A.OWNER = B.OWNERAND A.CLUSTER_NAME = B.CLUSTER_NAMEAND A.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY 1, 2, 3, 4;

Liste de tous les context de la base

Ce script SQL permet d'afficher la liste de tous les context de la base

set echo offset feedback offset linesize 512

prompt ----------------------------------prompt - Liste des contexts de la base --prompt ----------------------------------

SELECT NAMESPACE, SCHEMA, PACKAGEFROM DBA_CONTEXTORDER BY NAMESPACE;

Liste de tous les DB LINK de la base

Ce script SQL permet d'afficher la liste de tous les DB LINK de la base

set echo off

Page 31: Scripts DBA

set feedback offset linesize 512

prompt ---------------------------------prompt - Liste des DB Links de la base -prompt ---------------------------------

column host format a32column db_link format a32

break on OWNER skip 1

SELECT A.OWNER, A.HOST, A.DB_LINK, A.USERNAME, A.CREATED, DECODE (B.FLAG, 0, 'NO', 1, 'YES') "DEC", B.AUTHUSR, C.STATUSFROM DBA_DB_LINKS A, SYS.USER$ U, SYS.LINK$ B, DBA_OBJECTS CWHERE A.DB_LINK = B.NAME AND A.OWNER = U.NAME AND B.OWNER# = U.USER# AND A.DB_LINK = C.OBJECT_NAME AND A.OWNER = C.OWNER AND C.OBJECT_TYPE = 'DATABASE LINK'ORDER BY 1, 2, 3;

Liste de toutes les dimensions de la base

Ce script SQL permet d'afficher la liste de toutes les dimensions de la base

set echo offset feedback offset linesize 512

prompt -----------------------------------prompt - Liste des Dimensions de la base -prompt -----------------------------------

break on OWNER

SELECT OWNER, DIMENSION_NAME, DECODE (INVALID, 'Y', 'YES', 'N', 'NO') "DEC", REVISIONFROM DBA_DIMENSIONSORDER BY OWNER, DIMENSION_NAME;

Liste des clés étrangères non indexées de la base

Ce script SQL permet d'afficher la liste des clés étrangères non indexées de la base

Page 32: Scripts DBA

set echo offset feedback offset linesize 512

prompt ------------------------------------------prompt - Liste des clés étrangères non indexées -prompt ------------------------------------------

column column_name format a32break on OWNER skip 1 on TABLE_NAME

SELECT ACC.OWNER, ACC.TABLE_NAME, ACC.CONSTRAINT_NAME, ACC.COLUMN_NAME, ACC.POSITIONFROM ALL_CONS_COLUMNS ACC, ALL_CONSTRAINTS ACWHERE ACC.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND AC.TABLE_NAME = ACC.TABLE_NAMEAND ACC.OWNER = AC.OWNERAND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAMEAND AC.CONSTRAINT_TYPE = 'R'AND (ACC.OWNER, ACC.TABLE_NAME, ACC.COLUMN_NAME, ACC.POSITION) IN ( SELECT ACC.OWNER, ACC.TABLE_NAME, ACC.COLUMN_NAME, ACC.POSITION FROM ALL_CONS_COLUMNS ACC, ALL_CONSTRAINTS AC WHERE AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND AC.CONSTRAINT_TYPE = 'R' MINUS SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMNS )ORDER BY ACC.OWNER, ACC.CONSTRAINT_NAME, ACC.COLUMN_NAME, ACC.POSITION;

Liste des clés étrangères de l'utilisateur connecté

Ce script SQL permet d'afficher la liste des clés étrangères de l'utilisateur connecté

set echo offset feedback offset linesize 512

Page 33: Scripts DBA

prompt -------------------------------------------------------prompt - Liste des clés étrangères de l'utilisateur connecté -prompt -------------------------------------------------------

SELECT table_name, constraint_name, r_owner, r_constraint_name, delete_rule, statusFROM user_constraintsWHERE constraint_type = 'R'ORDER BY table_name, constraint_name;

Liste de tous les index de la base

Ce script SQL permet d'afficher la liste de tous les index de la base

set echo offset feedback offset linesize 512

prompt ------------------------------prompt - Liste des index de la base -prompt ------------------------------

break on OWNER skip 1

SELECT *FROM DBA_INDEXESWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY OWNER, TABLE_OWNER, TABLE_NAME;

Liste des colonnes indexées de la base

Ce script SQL permet d'afficher la liste des colonnes indexées de la base

set echo offset feedback offset linesize 512

prompt -------------------------------------------prompt - Liste des colonnes indexées de la base --prompt -------------------------------------------

column COLUMN_NAME format a32

break on INDEX_OWNERbreak on TABLE_OWNERbreak on TABLE_NAMEbreak on INDEX_NAMEbreak on INDEX_TYPEbreak on UNIQUENESS

SELECT A.OWNER INDEX_OWNER,

Page 34: Scripts DBA

A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.INDEX_TYPE, A.UNIQUENESS, B.COLUMN_POSITION, B.COLUMN_NAMEFROM DBA_INDEXES A, DBA_IND_COLUMNS B, DBA_TABLES CWHERE A.OWNER = B.INDEX_OWNERAND A.OWNER = C.OWNERAND A.TABLE_NAME = B.TABLE_NAMEAND A.TABLE_NAME = C.TABLE_NAMEAND A.INDEX_NAME = B.INDEX_NAMEAND A.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND A.TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY A.OWNER, A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, B.COLUMN_POSITION;

Liste des index partitionnés de la base

Ce script SQL permet d'afficher la liste des index partitionnés de la base

set echo offset feedback offset linesize 512

prompt -------------------------------------------prompt - Liste des index partitionnés de la base -prompt -------------------------------------------

break on INDEX_OWNER skip 1 on TABLE_NAME on INDEX_NAME

SELECT IP.INDEX_OWNER, IX.TABLE_OWNER || '.' || IX.TABLE_NAME "OWNER.TABLE", IP.INDEX_NAME, IP.PARTITION_NAME, IP.PARTITION_POSITION, IP.BLEVEL, IP.CLUSTERING_FACTOR, IP.DISTINCT_KEYS, IP.NUM_ROWS, IP.PCT_FREE, IP.INI_TRANS, IP.MAX_TRANS, IP.INITIAL_EXTENT, IP.NEXT_EXTENT, IP.MIN_EXTENT, IP.MAX_EXTENT, IP.PCT_INCREASE, IP.STATUS, IP.LEAF_BLOCKS, IP.AVG_LEAF_BLOCKS_PER_KEY,

Page 35: Scripts DBA

IP.AVG_DATA_BLOCKS_PER_KEY, IP.SAMPLE_SIZE, IP.LAST_ANALYZEDFROM DBA_INDEXES IX, DBA_IND_PARTITIONS IPWHERE IX.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND IX.TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND IX.PARTITIONED = 'YES'AND IP.INDEX_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND IP.INDEX_OWNER = IX.OWNERAND IP.INDEX_NAME = IX.INDEX_NAME ORDER BY 1, 2, 3;

Liste des index similaires de la base

Ce script SQL permet d'afficher la liste des index similaires de la base

set echo offset feedback offset linesize 512

prompt -------------------------------prompt - Liste des index similaires --prompt -------------------------------

column column_name format a32column table_name format a32column index_name format a32column table_owner format a32

break on TABLE_OWNER on TABLE NAME skip 1

SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAMEFROM ALL_IND_COLUMNSWHERE COLUMN_POSITION = 1AND TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')AND (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN ( SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME FROM ( SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COUNT (*) TCOUNT FROM

Page 36: Scripts DBA

ALL_IND_COLUMNS WHERE COLUMN_POSITION = 1 AND TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') HAVING COUNT (*) > 1 GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME ) )ORDER BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME, INDEX_NAME;

Liste des job programmés

Ce script SQL permet d'afficher la liste des job programmés

set echo offset feedback offset linesize 512

prompt -----------------------------prompt - Liste des jobs programmés -prompt -----------------------------

column interval format a40column what format a40

SELECT JOB, LOG_USER, PRIV_USER, SCHEMA_USER, LAST_DATE, THIS_DATE, NEXT_DATE, TOTAL_TIME, DECODE (BROKEN, 'Y', 'YES', 'N', 'NO') "JOB_BROKEN", INTERVAL, FAILURES, TRANSLATE(WHAT,chr(10), ' ') WHATFROM DBA_JOBSORDER BY JOB;

Liste des librairies de la base

Ce script SQL permet d'afficher la liste des librairies de la base

set echo offset feedback offset linesize 512

prompt ----------------------------------prompt - Liste des Libraries de la base -prompt ----------------------------------

column file_spec format a60

break on OWNER skip 1

Page 37: Scripts DBA

SELECT OWNER, LIBRARY_NAME, FILE_SPEC, DECODE (DYNAMIC, 'Y', 'YES', 'N', 'NO') "DEC", STATUSFROM DBA_LIBRARIESWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY OWNER, LIBRARY_NAME;

Liste des opérateurs de la base

Ce script SQL permet d'afficher la liste des opérateurs de la base

set echo offset feedback offset linesize 512

prompt -----------------------------------prompt - Liste des Operateurs de la base -prompt -----------------------------------

SELECT OWNER, OPERATOR_NAME, NUMBER_OF_BINDSFROM DBA_OPERATORSORDER BY OWNER, OPERATOR_NAME;

Liste des Outlines de la base

Ce script SQL permet d'afficher la liste des Outlines de la base

set echo offset feedback offset linesize 512

prompt ---------------------------------prompt - Liste des Outlines de la base -prompt ---------------------------------

column version format a10

SELECT OWNER, NAME, CATEGORY, USED, TIMESTAMP, VERSION, SQL_TEXTFROM DBA_OUTLINESORDER BY OWNER, NAME;

Liste des clés primaires de l'utilisateur connecté

Page 38: Scripts DBA

Ce script SQL permet d'afficher la liste des clés primaires de l'utilisateur connecté

set echo offset feedback offset linesize 512

prompt --------------------------------------------prompt - Liste des clés primaires du user conncté -prompt --------------------------------------------

SELECT table_name, constraint_name, constraint_type, statusFROM user_constraintsWHERE constraint_type = 'P'ORDER BY table_name, constraint_name;

Liste des séquences de la base

Ce script SQL permet d'afficher la liste des séquences de la base

set echo offset feedback offset linesize 512

prompt -----------------------------------prompt - Liste des séquences de la base --prompt -----------------------------------

column CYCLE format a8column ORDRE format a8

break on SEQUENCE_OWNER skip 1

SELECT SEQUENCE_OWNER, SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, DECODE (CYCLE_FLAG, 'Y', 'YES', 'N', 'NO') CYCLE, DECODE (ORDER_FLAG, 'Y', 'YES', 'N', 'NO') ORDRE, CACHE_SIZE, LAST_NUMBERFROM DBA_SEQUENCESWHERE SEQUENCE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY 1, 2;

Liste des Snapshots de la base

Ce script SQL permet d'afficher la liste des Snapshots de la base

set echo offset feedback offset linesize 512

Page 39: Scripts DBA

prompt ----------------------------------prompt - Liste des Snapshots de la base -prompt ----------------------------------

column snapshot format a62column source format a62column master_link format a32

SELECT OWNER, NAME|| '.'|| TABLE_NAME "SNAPSHOT", MASTER_VIEW, MASTER_OWNER|| '.'|| MASTER "SOURCE", MASTER_LINK, CAN_USE_LOG, UPDATABLE, LAST_REFRESH, REFRESH_GROUP, TYPE, UPDATE_TRIG, UPDATE_LOG, ERROR, MASTER_ROLLBACK_SEG, QUERYFROM DBA_SNAPSHOTSORDER BY 1, 3, 5;

Liste des Snapshots logs de la base

Ce script SQL permet d'afficher la liste des logs des snapshots de la base

set echo offset feedback offset linesize 512

prompt ----------------------------------prompt - Liste Snapshot Logs de la base -prompt ----------------------------------

break on LOG_OWNER skip 1

SELECT LOG_OWNER, MASTER, LOG_TABLE, LOG_TRIGGER, ROWIDS, PRIMARY_KEY, FILTER_COLUMNS, CURRENT_SNAPSHOTSFROM DBA_SNAPSHOT_LOGS

Page 40: Scripts DBA

ORDER BY 1, 2;

Liste des types objet de la base

Ce script SQL permet d'afficher la liste des types objet de la base

set echo offset feedback offset linesize 512

prompt ------------------------------------prompt - Liste des types objet de la base -prompt ------------------------------------

break on OWNER on TYPE_NAME

SELECT OWNER, TYPE_NAME, ATTR_NAME, ATTR_NO, ATTR_TYPE_NAME, LENGTH, PRECISION, SCALEFROM DBA_TYPE_ATTRSWHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')ORDER BY OWNER, TYPE_NAME, ATTR_NO;

Affichage du plan d'exécution d'une requête

Attention : lancer %ORACLE_HOME%/rdbms/admin/utlxplan.sql si PLAN_TABLE n'existe pas.

set echo off set pagesize 1000 set feedback off set lines 15000 col OPERATION format A45 col TYPE_ACCES format A20 col NOM_OBJET format A30 col ORDRE format A8 col ETAT format A20

delete from plan_table where statement_id = 'X'; explain plan set statement_id = 'X' into plan_table for <la requête à analyser> ; select LPAD(' ',2*(LEVEL-1))||operation "OPERATION", options "TYPE_ACCES", DECODE(TO_CHAR(id),'0','COST= '|| NVL(TO_CHAR(position),'Indefini'), object_name) "NOM_OBJET", id || '-' || NVL(parent_id,0) || '-' || NVL(position,0) "ORDRE", ' COUT=' || COST ||','||'Card=' || CARDINALITY "Cout Op" From plan_table start with id = 0 and statement_id = 'X' connect by prior id = parent_id and statement_id = 'X';

Page 41: Scripts DBA

Liste des paramètres d'un programme stocké

Liste des paramètres d'un programme stockée

SELECT a.owner, a.object_name, o.object_type, a.argument_name parameter_name, a.position, a.data_type, a.default_value, a.default_length, a.in_out, a.data_length, a.data_precision, a.data_scale, a.type_link, a.type_name, a.type_owner, a.type_subname FROM all_arguments a, all_objects o WHERE a.object_id = o.object_id AND o.object_owner = &1 AND o.object_name = &2 ORDER BY a.object_name, a.overload, a.SEQUENCE;