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 sousbuffer 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 ? 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 sousbuffer 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 OWNER -------------------SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS

OBJECT_NAME -------------------I_JOB_NEXT C_OBJ# C_TS# C_USER# I_SYSAUTH1 I_OBJ1 OBJ$ PROPS$ FILE$ I_FILE1 I_LINK1 I_USER1 SYSAUTH$ I_USER# I_IND1

OBJECT_TYPE BLOCKS PERCENT_CACHE ------------------ ---------- ------------INDEX 38 39,58 CLUSTER 34 35,42 CLUSTER 4 4,17 CLUSTER 4 4,17 INDEX 3 3,13 INDEX 2 2,08 TABLE 2 2,08 TABLE 2 2,08 TABLE 1 1,04 INDEX 1 1,04 INDEX 1 1,04 INDEX 1 1,04 TABLE 1 1,04 INDEX 1 1,04 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 ...

9 9 9

382270 382530 382790

260 Free 260 Free 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

/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 off set heading off set feedback off prompt --------------------------------prompt - Coalesce tous les tablespaces prompt --------------------------------set term off spool tbs_coalesce.tmp SELECT 'set echo on' FROM dual; SELECT 'set feedback on' FROM dual; SELECT 'alter tablespace '||tablespace_name||' coalesce;' FROM dba_tablespaces WHERE contents not in ('TEMPORARY','UNDO'); spool off set 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 off set feedback off set linesize 512 prompt ---------------------------------prompt - plus grandes consommation CPU -prompt ---------------------------------column column column column column column column

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

SELECT s.sid sid,

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 value FROM v$session s, v$sesstat t, v$statname n WHERE s.sid = t.sid AND t.statistic# = n.statistic# AND n.name = 'CPU used by this session' AND t.value > 0 AND audsid > 0 ORDER BY t.value desc; Informations sur la base Ce script SQL permet d'obtenir les informations de la base set echo off set feedback off set linesize 128 column NAME Format a40 column DATAGUARD_BROKER Format a20 column GUARD_STATUS Format a20 prompt ------------------------prompt - Infos base de données -

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 off set feedback off set 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$CONTROLFILE ORDER 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 off set feedback off set linesize 512 prompt prompt prompt prompt

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

column count(username) heading 'Nbre utilisateurs' SELECT default_tablespace, count(username) FROM dba_users WHERE username not in ('PUBLIC','_NEXT_USER') GROUP BY default_tablespace; SELECT temporary_tablespace, count(username) FROM dba_users WHERE

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 off set feedback off set linesize 256 prompt -------------------------prompt - Directories de la base prompt -------------------------column CHEMIN format a80 column NOM format a40 SELECT OWNER SCHEMA, DIRECTORY_NAME NOM, DIRECTORY_PATH CHEMIN FROM DBA_DIRECTORIES ORDER BY OWNER, DIRECTORY_NAME; Liste des segments non extensibles Ce script SQL affiche la liste des segments non extensibles de la base set echo off set feedback off set 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_by FROM 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

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) c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name AND ((c.autoextensible = 0) OR ((c.autoextensible > 0) AND (a.next_extent > c.morebytes))) AND a.next_extent > b.free ORDER 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 off set feedback off set linesize 512 prompt ----------------------------------------------------prompt - Classement des Tablespaces par consommation d'E/S prompt ----------------------------------------------------column column column column column column

file_name ts_name stat_reads stat_writes stat_breads stat_bwrites

format format format format format format

a60 a32 999,999,999,999 999,999,999,999 999,999,999,999 999,999,999,999

heading heading heading heading heading heading

"Data-File Name" "Tablespace Name" "Physical Reads" "Physical Writes" "Physical Blk-Reads" "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_bwrites FROM v$tablespace t, v$datafile f, v$filestat s WHERE 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

set echo off set feedback off set linesize 512 prompt --------------------------------------------prompt - Classement des attentes d' E/S de la base prompt --------------------------------------------column event format a30 column segment_type format a10 column segment_name format a20 SELECT event, segment_type, segment_name, file_id,block_id, blocks FROM dba_extents, v$session_wait WHERE p1text='file#' AND p2text='block#' AND p1=file_id AND p2 between block_id AND block_id + blocks ORDER 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 off set feedback off set linesize 512 prompt ----------------------prompt - Verrous sur la base prompt ----------------------column column column column column column column column

sid format 999 heading "SID" username format a10 heading "Utilisateur" machine format a20 heading "Nom Machine" object_name format a20 heading "Nom objet" type format a4 heading "Type" lmode format a20 heading "Mode de verrouillage" request format 9999999 heading "Request Mode" 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',

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 block FROM v$lock l, v$session s, dba_objects o WHERE l.sid = s.sid AND 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 set set set

echo off feedback off linesize 512 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, ISADJUSTED FROM V$PARAMETER ORDER BY NAME; Liste des profils de la base Ce script SQL affiche la liste des profils de la base set echo off set feedback off set linesize 512 prompt --------------------------------prompt - Liste des profiles de la base prompt ---------------------------------

SELECT DISTINCT PROFILE FROM 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 OFF SET FEEDBACK OFF SET 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_trans FROM dba_rollback_segs r, v$rollname n, v$rollstat s WHERE r.segment_name = n.name AND 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 off set feedback off set linesize 512 prompt ---------------------------------prompt - liste des REDO LOGS de la base prompt ---------------------------------column member format a60 column 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_TIME FROM V$LOG A, V$LOGFILE B

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 off set feedback off set linesize 512 prompt ---------------------------------------prompt - Liste des roles attribues de la base prompt ---------------------------------------break on GRANTED_ROLE skip 1 column ADMIN format a8 column DEFAUT format a8 SELECT GRANTED_ROLE ROLE, GRANTEE UTILISATEUR, ADMIN_OPTION ADMIN, DEFAULT_ROLE DEFAUT FROM DBA_ROLE_PRIVS WHERE 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 off set feedback off set linesize 512 prompt -------------------------------------------------prompt - Liste des roles attribues aux roles de la base prompt -------------------------------------------------break on GRANTED_ROLE skip 1 column ADMIN format a8 column DEFAUT format a8 SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION ADMIN, DEFAULT_ROLE DEFAUT FROM SYS.DBA_ROLE_PRIVS WHERE GRANTEE IN (SELECT ROLE FROM DBA_ROLES) ORDER BY GRANTED_ROLE, ADMIN_OPTION; Liste des rôles utilisateurs de la base

Ce script SQL affiche la liste des rôles utilisateurs de la base set echo off set feedback off set linesize 512 prompt ------------------------------------------prompt - liste des roles utilisateurs de la base prompt ------------------------------------------SELECT ROLE, PASSWORD_REQUIRED FROM SYS.DBA_ROLES WHERE 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 off set feedback off set linesize 512 prompt ------------------------------------------------------prompt - Liste des roles utilisateurs attribues dans la base prompt ------------------------------------------------------break on GRANTED_ROLE skip 1 column ADMIN format a8 column DEFAUT format a8 SELECT GRANTED_ROLE ROLE, GRANTEE UTILISATEUR, ADMIN_OPTION ADMIN, DEFAULT_ROLE DEFAUT FROM SYS.DBA_ROLE_PRIVS

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 off set feedback off set 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_ROLE FROM SYS.DBA_ROLE_PRIVS WHERE 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' ) 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_REQUIRED FROM DBA_ROLES ORDER BY ROLE; Liste des évènements de la session Ce script SQL affiche la liste des évènements de la session set echo off set feedback off set 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_WAIT FROM V$SESSION_EVENT A, V$SESSION B WHERE A.SID = B.SID ORDER BY

1; Liste des sessions actives de la base Ce script SQL affiche la liste des sessions actives de la base set echo off set feed off set linesize 512 prompt -----------------------------------------prompt - Liste des sessions actives de la base -prompt -----------------------------------------column machine format a30 column osuser format a30 column module format a20 SELECT machine, process, osuser, username, schemaname, status, lockwait, sid, serial#, module, action FROM v$session WHERE username is not null AND osuser is not null ORDER 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 off set feedback off set linesize 512 prompt -------------------------------prompt - Occupation memoire de la SGA prompt -------------------------------column column column column column column

dummy area name pool bytes sum(bytes)

noprint format a20 heading 'Main SGA Areas' format a20 format a20 format 999,999,999,999 format 999,999,999,999

break on report compute sum of sum(bytes) on report SELECT 1 dummy,

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

v$sgastat WHERE pool is null AND name = 'fixed_sga' GROUP BY name ORDER 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$sgastat WHERE pool = 'shared pool' AND name in ('library cache','dictionary cache','free memory','sql area') GROUP BY name UNION ALL SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes) FROM v$sgastat WHERE pool = 'shared pool' AND name not in ('library cache','dictionary cache','free memory','sql area') GROUP BY pool ORDER 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 off set feedback off set linesize 512 prompt -------------------------------prompt - Liste des evenements systeme prompt -------------------------------column event format a32 column column column column

total_waits total_timeouts time_waited average_wait

SELECT EVENT EVENEMENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED,

format999,999,999,999 format999,999,999,999 format999,999,999,999 format999,999,999,999

heading heading heading heading

"Total Attentes" "Total Timeouts" "Temps attendu" "Moyenne d'attente"

AVERAGE_WAIT FROM V$SYSTEM_EVENT ORDER BY 4 DESC; Informations sur les tablespaces Ce script SQL affiche les informations des tablespaces set set set set

echo off feedback off verify off linesize 512

set term off COLUMN block_size NOPRINT new_value block_size SELECT value block_size FROM v$parameter WHERE name='db_block_size'; set term on prompt -----------------------------prompt - Infos sur les tablespaces -prompt -----------------------------col col col col col col col col col col col col

Tablespace_name Megs_Alloc Megs_Free Megs_Used Pct_Free Pct_Used Init_Ext Next_Ext Min_Ext Max_Ext Num_Segs Num_Exts

Heading Heading Heading Heading Heading Heading Heading Heading Heading Heading Heading Heading

'Tablespace' 'Mo alloués' 'Mo libres' 'Mo utilisés' 'Pct Free' 'Pct Used' 'Init Ext' 'Next Ext' 'Min Ext' 'Max Ext' 'Nbre Segments' '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_Exts FROM ( 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,

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) d WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name AND 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 off set feed off PROMPT ------------------------------------PROMPT - Liste des déclencheurs de la base PROMPT ------------------------------------SELECT owner UTILISATEUR, trigger_name TRIGGER, status STATUT FROM all_triggers WHERE 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 off set feedback off set linesize 512 prompt ------------------------------------prompt - Liste des utilisateurs de la base prompt ------------------------------------column count(b.object_id) heading 'Nbre d'objets' SELECT

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 b WHERE a.username = b.owner(+) GROUP BY a.username, a.account_status, a.default_tablespace, a.temporary_tablespace ORDER 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 off set feedback off set 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_TEXT FROM V$ROLLNAME A, V$ROLLSTAT B, V$SESSION C, V$SQLTEXT D, V$TRANSACTION E WHERE A.USN = B.USN AND B.USN = E.XIDUSN AND C.TADDR = E.ADDR AND C.SQL_ADDRESS = D.ADDRESS AND C.SQL_HASH_VALUE = D.HASH_VALUE ORDER 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 off set heading off set feedback off PROMPT -------------------------------------------PROMPT - Mise Hors ligne des segments de rollback PROMPT --------------------------------------------

set term off spool 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_segs WHERE segment_name != 'SYSTEM' AND status = 'ONLINE'; spool off set 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 off set heading off set feedback off PROMPT -------------------------------------------PROMPT - Remise en ligne des segments de rollback PROMPT -------------------------------------------set term off spool 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_segs WHERE segment_name != 'SYSTEM' AND status = 'OFFLINE'; spool off set 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 off set heading off set feedback off prompt ----------------------------------prompt - Mise hors ligne des tablespaces prompt ----------------------------------set term off spool tbs_hors_ligne.tmp

SELECT 'set echo on' FROM dual; SELECT 'set feedback on' FROM dual; SELECT 'alter tablespace '||tablespace_name||' offline;' FROM dba_tablespaces WHERE tablespace_name not in ('SYSTEM','OUTLN') AND status = 'ONLINE'; spool off set 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 off set heading off set feedback off prompt --------------------------------prompt - Mise en ligne des tablespaces prompt --------------------------------set term off spool tbs_en_ligne.tmp SELECT 'set echo on' FROM dual; SELECT 'set feedback on' FROM dual; SELECT 'alter tablespace '||tablespace_name||' online;' FROM dba_tablespaces WHERE tablespace_name not in ('SYSTEM','OUTLN') AND status = 'OFFLINE'; spool off set 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 off set head off set 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;'

FROM all_triggers WHERE owner not in ('SYS','SYSTEM') AND status = 'ENABLED'; spool off set feed on set 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 off set head off set 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_triggers WHERE owner not in ('SYS','SYSTEM') AND status = 'DISABLED'; spool off set feed on set 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 off set feedback off set 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_extents GROUP BY tablespace_name, owner, segment_type ORDER BY tablespace_name, owner, segment_type;

Infos de l'instance et de la base Ce script SQL affiche les information sur l'instance et la base set echo off set feedback off set heading off prompt ---------------------------------prompt - Instance et version de la base prompt ---------------------------------SELECT 'Block Size = '||value FROM v$parameter where name = 'db_block_size' UNION SELECT 'Version = '||banner FROM v$version where rownum = 1 UNION SELECT '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 COL COL COL

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

SELECT size_for_estimate,

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('%¶m_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 SET SET SET SET

LINESIZE 500 PAGESIZE 1000 FEEDBACK OFF VERIFY OFF 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

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 set set set set

echo off heading off feedback off linesize 512 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

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 off set feedback off set linesize 512 prompt ------------------------------prompt - Liste des tables de la base prompt ------------------------------break on OWNER SELECT * FROM DBA_TABLES WHERE 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 off set feedback off set linesize 512 prompt ------------------------------prompt - Liste des tables sans index prompt ------------------------------break on OWNER skip 1 SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') MINUS SELECT OWNER, TABLE_NAME FROM ALL_INDEXES WHERE 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 off set feedback off

set linesize 512 prompt ----------------------------------prompt - Liste des tables très indexées -prompt ----------------------------------SELECT OWNER, TABLE_NAME, COUNT (*) "Nbre" FROM ALL_INDEXES WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') GROUP BY OWNER, TABLE_NAME HAVING 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 off set feedback off set 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_INCREASE FROM SYS.DBA_TAB_PARTITIONS WHERE 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 off set feedback off set linesize 512 prompt ----------------------------------

prompt - Liste des clusters de la base -prompt ---------------------------------column tab_column_name format a32 break 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_INCREASE FROM DBA_CLUSTERS A, DBA_CLU_COLUMNS B WHERE A.OWNER = B.OWNER AND A.CLUSTER_NAME = B.CLUSTER_NAME AND 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 off set feedback off set linesize 512 prompt ---------------------------------prompt - Liste des contexts de la base -prompt ---------------------------------SELECT NAMESPACE, SCHEMA, PACKAGE FROM DBA_CONTEXT ORDER 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

set feedback off set linesize 512 prompt --------------------------------prompt - Liste des DB Links de la base prompt --------------------------------column host format a32 column 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.STATUS FROM DBA_DB_LINKS A, SYS.USER$ U, SYS.LINK$ B, DBA_OBJECTS C WHERE 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 off set feedback off set 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", REVISION FROM DBA_DIMENSIONS ORDER 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

set echo off set feedback off set linesize 512 prompt -----------------------------------------prompt - Liste des clés étrangères non indexées prompt -----------------------------------------column column_name format a32 break on OWNER skip 1 on TABLE_NAME SELECT ACC.OWNER, ACC.TABLE_NAME, ACC.CONSTRAINT_NAME, ACC.COLUMN_NAME, ACC.POSITION FROM ALL_CONS_COLUMNS ACC, ALL_CONSTRAINTS AC WHERE ACC.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') AND AC.TABLE_NAME = ACC.TABLE_NAME AND ACC.OWNER = AC.OWNER AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME AND 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 off set feedback off set linesize 512

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, status FROM user_constraints WHERE 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 off set feedback off set linesize 512 prompt -----------------------------prompt - Liste des index de la base prompt -----------------------------break on OWNER skip 1 SELECT * FROM DBA_INDEXES WHERE 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 off set feedback off set linesize 512 prompt ------------------------------------------prompt - Liste des colonnes indexées de la base -prompt ------------------------------------------column COLUMN_NAME format a32 break break break break break break

on on on on on on

INDEX_OWNER TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS

SELECT A.OWNER INDEX_OWNER,

A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.INDEX_TYPE, A.UNIQUENESS, B.COLUMN_POSITION, B.COLUMN_NAME FROM DBA_INDEXES A, DBA_IND_COLUMNS B, DBA_TABLES C WHERE A.OWNER = B.INDEX_OWNER AND A.OWNER = C.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_NAME = C.TABLE_NAME AND A.INDEX_NAME = B.INDEX_NAME AND 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 off set feedback off set 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,

IP.AVG_DATA_BLOCKS_PER_KEY, IP.SAMPLE_SIZE, IP.LAST_ANALYZED FROM DBA_INDEXES IX, DBA_IND_PARTITIONS IP WHERE 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.OWNER AND 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 off set feedback off set linesize 512 prompt ------------------------------prompt - Liste des index similaires -prompt ------------------------------column column column column

column_name format a32 table_name format a32 index_name format a32 table_owner format a32

break on TABLE_OWNER on TABLE NAME skip 1 SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME FROM ALL_IND_COLUMNS WHERE COLUMN_POSITION = 1 AND 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

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 off set feedback off set linesize 512 prompt ----------------------------prompt - Liste des jobs programmés prompt ----------------------------column interval format a40 column 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), ' ') WHAT FROM DBA_JOBS ORDER BY JOB; Liste des librairies de la base Ce script SQL permet d'afficher la liste des librairies de la base set echo off set feedback off set linesize 512 prompt ---------------------------------prompt - Liste des Libraries de la base prompt ---------------------------------column file_spec format a60 break on OWNER skip 1

SELECT OWNER, LIBRARY_NAME, FILE_SPEC, DECODE (DYNAMIC, 'Y', 'YES', 'N', 'NO') "DEC", STATUS FROM DBA_LIBRARIES WHERE 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 off set feedback off set linesize 512 prompt ----------------------------------prompt - Liste des Operateurs de la base prompt ----------------------------------SELECT OWNER, OPERATOR_NAME, NUMBER_OF_BINDS FROM DBA_OPERATORS ORDER 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 off set feedback off set linesize 512 prompt --------------------------------prompt - Liste des Outlines de la base prompt --------------------------------column version format a10 SELECT OWNER, NAME, CATEGORY, USED, TIMESTAMP, VERSION, SQL_TEXT FROM DBA_OUTLINES ORDER BY OWNER, NAME; Liste des clés primaires de l'utilisateur connecté

Ce script SQL permet d'afficher la liste des clés primaires de l'utilisateur connecté set echo off set feedback off set linesize 512 prompt -------------------------------------------prompt - Liste des clés primaires du user conncté prompt -------------------------------------------SELECT table_name, constraint_name, constraint_type, status FROM user_constraints WHERE 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 off set feedback off set linesize 512 prompt ----------------------------------prompt - Liste des séquences de la base -prompt ----------------------------------column CYCLE format a8 column 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_NUMBER FROM DBA_SEQUENCES WHERE 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 off set feedback off set linesize 512

prompt ---------------------------------prompt - Liste des Snapshots de la base prompt ---------------------------------column snapshot format a62 column source format a62 column 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, QUERY FROM DBA_SNAPSHOTS ORDER 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 off set feedback off set 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_SNAPSHOTS FROM DBA_SNAPSHOT_LOGS

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 off set feedback off set 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, SCALE FROM DBA_TYPE_ATTRS WHERE 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 set set set col col col col col

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

delete from plan_table where statement_id = 'X'; explain plan set statement_id = 'X' into plan_table for ; 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';

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;