Le problème :
ORA-01502:
index 'idx_bd' or partition of such index is in unusable state
lister les indexes invalides :
SELECT owner, index_name, tablespace_name
FROM dba_indexes
WHERE status = 'UNUSABLE';
On peut aussi voir les indexes de partitions :
SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';
Générer le scripts pour reconstruire les indexes :
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';'
FROM dba_indexes
WHERE status = 'UNUSABLE';
Générer le scripts pour reconstruire les indexes de partition :
SELECT 'alter index '||owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM dba_ind_partitions
WHERE status = 'UNUSABLE';
alter index MON_IDX rebuild tablespace INDX;
lundi 28 septembre 2015
jeudi 24 septembre 2015
EXADATA
Introduction à Exadata
Cet article tente de présenter de manière simple la machine Exadata :
Exadata est une solution intégrée oracle combinant une couche hardware et une couche software fournissant une plateforme pour faire fonctionner des bases de données Oracle.
On peut voir Exadata comme une couche de stockage et une couche de bases de données reliées via un réseau InfiniBand. Le réseau InfiniBand est caractérisé par une très grande bande passante et une faible latence. La couche base de données est formée de multiple serveurs Sun sur lesquels tournent des bases Oracle 11gR2. Les serveurs de bases de données sont généralement configurés en RAC clusters. Les bases de données utilisent ASM pour la gestion du stockages. Les serveurs de stockage eux aussi sont formés de multiples serveurs Sun. Chaque serveur de stockage contient 12 disques et sur lequel tourne le logiciel oracle storage server(cellsrv). La communication entre différentes couches est effectué via iDB (protocole réseau) implémenté par InfiniBand.
Pour information iDB est utilisé pour interroger les données via des requêtes envoyées à cellsrv accompagnées de metadata sur ces requêtes. Dans certains cas cellsrv utilise ces metadata pour traiter les données avant de les retransmettre à la couche base de données. Ce traitement effectué par cellsrv est appelé Smart Scan.
 |
| Les composants Exadata |
Voici une vue de la machine Exadata en mode full Rack:
 |
| La machine Exadata |
Serveur de stockage (cells):
Cell Services(cellsrv) est le système installé sur les serveurs de stockage ou storage cells. C'est un programme multi threads qui offre le service I/O pour répondre au requêtes envoyées par les serveurs de bases de données. En fonction des requêtes, celle-ci peuvent être exécutées en retournant les données déjà traitées au serveur de bases de données ou en retournant l'ensemble des blocks. Pour information cellsrv implemente oracle ressource manager pour là gestion des ressources.
mardi 22 septembre 2015
Supervision requêtes consommatrices.
SI on souhaite mettre en place une supervision simple pour détecter les requêtes consommatrices et candidates au tuning alors voici une fonction simple pour cela :
- la fonction pourra être schedulé dans un job
- Elle devra permettre d'exclure des user que nous voulons pas superviser comme SYS et SYSTEM par exemple.
- déterminer le seuil (en seconde) d'alerte pour qualifier de long une exécution.
- la fonction de supervision devra loger dans une table temporaire. Les requêtes longues avec une rétention de 30 jours.
- on pourra recevoir un mail avec le reporting sur les requêtes capturées.
- un seul mail sera envoyé par heure s'il s'agit du même user et de la même requête.
- pour les sessions parallèle on envoi un unique mail.
- La supervision est compatible avec le RAC.
- création de la table qui va contenir les requêtes capturées :
dba_long_sqlstat:
CREATE TABLE "SYS"."DBA_LONG_SQLSTAT"
( "INST_ID" NUMBER,
"USERNAME" VARCHAR2(30 BYTE),
"OSUSER" VARCHAR2(30 BYTE),
"MACHINE" VARCHAR2(64 BYTE),
"PROGRAM" VARCHAR2(48 BYTE),
"SQL_ID" VARCHAR2(13 BYTE),
"LAST_CALL_ET" NUMBER,
"SQL_TEXT" CLOB
);
dba_hist_long_sqlstat(table d'historisation):
CREATE TABLE "SYS"."DBA_HIST_LONG_SQLSTAT"
( "CAPTURE_TIMESTAMP" DATE DEFAULT SYSDATE,
"INST_ID" NUMBER,
"USERNAME" VARCHAR2(30 BYTE),
"OSUSER" VARCHAR2(30 BYTE),
"MACHINE" VARCHAR2(64 BYTE),
"PROGRAM" VARCHAR2(48 BYTE),
"SQL_ID" VARCHAR2(13 BYTE),
"LAST_CALL_ET" NUMBER,
"SQL_TEXT" CLOB
);
création de la procédure :
create or replace procedure long_running_sql
is
v_db_name v$parameter.value%type;
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
begin
dbms_utility.exec_ddl_statement('truncate table dba_long_sqlstat');
insert into dba_long_sqlstat
select
s.inst_id, s.username, s.osuser, s.machine, s.program, s.sql_id,l_et,st.sql_fulltext
from
(
select distinct inst_id, username, osuser, machine, program, sql_id, max(last_call_et) as l_et
from gv$session
where
username is not null and
username not in ('SYS','SYSTEM','DBSNMP') and
status='ACTIVE'
and last_call_et > 180
group by
inst_id, username, osuser, machine, program, sql_id
) s
left outer join gv$sql st
on s.sql_id = st.sql_id;
commit;
select value into v_db_name from v$parameter where name = 'db_name';
for rec in
(
select * FROM dba_long_sqlstat WHERE ROWID IN
(SELECT MAX(ROWID) FROM
(SELECT inst_id, username, osuser, machine, program, sql_id, last_call_et, sql_text
FROM (
SELECT inst_id, username, osuser, machine, program, sql_id, last_call_et, sql_text, rank() over (partition by sql_id order by last_call_et desc) rnk
FROM dba_long_sqlstat
where sql_id not in (select sql_id from dba_hist_long_sqlstat where capture_timestamp between TRUNC(SYSDATE, 'HH24') and sysdate)
)
WHERE rnk = 1
)
group by sql_id)
)
loop
declare
v_from varchar2(80) := v_db_name;
v_recipient varchar2(80) := 'xxxx@gmail.com';
v_subject varchar2(80) := 'long running sql on '||v_db_name;
v_mail_host varchar2(30) := 'localhost';
v_mail_conn utl_smtp.connection;
crlf varchar2(2) := chr(13)||chr(10);
begin
v_mail_conn := utl_smtp.open_connection(v_mail_host,25);
utl_smtp.helo(v_mail_conn, v_mail_host);
utl_smtp.mail(v_mail_conn, v_from);
utl_smtp.rcpt(v_mail_conn, v_recipient);
utl_smtp.data(v_mail_conn, 'Content-Type: text/html;' || crlf ||
'To: ' || v_recipient || crlf ||
'Subject: '|| v_subject || crlf ||
'The following sql is running for '|| rec.last_call_et || ' seconds in database '|| v_db_name ||'<br><br></html>' ||
'DB User: '|| '<html><font color="darkblue">'|| rec.username || '</font><br></html>' ||
'On Instance: '|| '<html><font color="darkblue">'|| rec.inst_id || '</font><br></html>' ||
'OS User: '|| '<html><font color="darkblue">'|| rec.osuser || '</font><br></html>' ||
'Source Machine: '|| '<html><font color="darkblue">'|| rec.machine || '</font><br></html>' ||
'Program: '|| '<html><font color="darkblue">'|| rec.program || '</font><br><br></html>' ||
'SQL ID: '|| '<html><font color="red">'|| rec.sql_id || '</font><br><br></html>' ||
'<html><body><font face="courier" size="1" color="maroon">'|| rec.sql_text ||'</font></body></html>');
utl_smtp.quit(v_mail_conn);
end;
end loop;
insert into dba_hist_long_sqlstat(
inst_id,
username,
osuser,
machine,
program,
sql_id,
last_call_et,
sql_text)
select * from dba_long_sqlstat;
delete from dba_hist_long_sqlstat where capture_timestamp < (sysdate - 30);
commit;
end long_running_sql ;
Par exemple pour tourner toute les 2 minutes en semaine et dans la plage horaire pertinente pour nous.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'long_running_sql_check_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN long_running_sql; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;BYHOUR=12,13,14,15,16,17,18,19,20,21,22;BYDAY=MON,TUE,WED,THU,FRI',
end_date => NULL,
enabled => TRUE,
comments => 'sup requêtes en semaine ');
END;
Pour tester on peut se connecter bien évidemment avec un user qui n'est pas exclu de la supervision et on execute :
BEGIN
DBMS_LOCK.sleep(seconds => 500);
END;
/
A tester bien évidemment avant de le mettre en production.
lundi 21 septembre 2015
RMAN-20011
RMAN-20011: target database incarnation is not current in recovery catalog
en exécutant le crosscheck j'ai l'erreur suivante :
RMAN> crosscheck archivelog all;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 09/21/2015 17:06:56
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog
Option 1 - Reset PROD to Previous Incarnation
B. Connect to the Production Database with RMAN
Dans mon cas (base de test) comme je peux me permettre de le faire :-) j'ai fait :
en exécutant le crosscheck j'ai l'erreur suivante :
RMAN> crosscheck archivelog all;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 09/21/2015 17:06:56
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog
RMAN> list incarnation of database;
RMAN> show all ;
RMAN configuration parameters are:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 09/21/2015 17:10:56
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog
SOLUTION
Option 1 - Reset PROD to Previous Incarnation
OR
Option 2 - Re-register PROD with the Recovery Catalog
Finally change the cloned db's DBID:
Note 224266.1 How to Change the DBID and the DBNAME by using NID
Note 224266.1 How to Change the DBID and the DBNAME by using NID
Option 1 - Reset PROD to Previous Incarnation
A. Check the latest incarnation key for production database in rc_database_incarnation
SQL> select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME
from RC_DATABASE_INCARNATION
where dbid=2284119847;
==> Check the DBINC_KEY corresponding to the current incarnation of PROD database
from RC_DATABASE_INCARNATION
where dbid=2284119847;
==> Check the DBINC_KEY corresponding to the current incarnation of PROD database
B. Connect to the Production Database with RMAN
Be aware that database needs to be in "MOUNT" status to be able to reset the incarnation
$ rman catalog <un/pw@catalog_db> target /
RMAN>
RMAN> reset database to incarnation <dbinc_key>;
RMAN> resync catalog;
RMAN> list incarnation; # => Now the production DB incarnation should be the current one and
# the error RMAN-20011 should not raise anymore
RMAN>
RMAN> reset database to incarnation <dbinc_key>;
RMAN> resync catalog;
RMAN> list incarnation; # => Now the production DB incarnation should be the current one and
# the error RMAN-20011 should not raise anymore
Option 2 - Re-Register PROD with the Recovery Catalog
Warning: Once a database is uregistered from the recovery catalog all backup information will be coming from the controlfile. Therefore you may lose some bakcup information!! If you have other backups that needs to be added to the recovery catalog you must recatalog them afterwards.
A. Take a backup of the recovery catalog database/schema
B. Confirm the backups you currently have in the controlfile vs recovery catalog. Run the following in both nocatalog and catalog mode:
RMAN> list backup summary;
C. Connect to the clone database and recovery catalog and unregister the database:
RMAN> unregister database;
D. Connect to prod and the recovery catalog and register the prod database:
RMAN> register database;
RMAN> list incarnation of database;
RMAN> list backup summary;
RMAN> list incarnation of database;
RMAN> list backup summary;
E. Recatalog any additional backuppieces:
If on disk:
RMAN> catalog start with 'location of the backuppieces';
If on tape:
HOW TO CATALOG TAPE BACKUP PIECES (Doc ID 550082.1)
Dans mon cas (base de test) comme je peux me permettre de le faire :-) j'ai fait :
- unregister database;
- register database;
et ensuite j'ai lancé un full backup de la base.
samedi 19 septembre 2015
RMAN-03002: failure of Duplicate Db command
Problème :
Lors d'un Refresh la duplicate RMAN tombe en erreur :
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/database/oracle/MYDB/system/SYSTEM_TB10_001.dbf'
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4
released channel: ch5
released channel: ch6
released channel: ch7
released channel: ch8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/18/2015 16:24:55
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 83021 and starting SCN of 9284244923069 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 83020 and starting SCN of 9284244901184 found to restore
Recovery Manager complete.
Après analyse du problème voici la cause de l'erreur :
Les archivelogs de la base source n'avait pas encore été sauvegardés et par conséquent pas dans le catalogue RMAN.
Pour résoudre le problème il faut :
Lors d'un Refresh la duplicate RMAN tombe en erreur :
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/database/oracle/MYDB/system/SYSTEM_TB10_001.dbf'
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4
released channel: ch5
released channel: ch6
released channel: ch7
released channel: ch8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/18/2015 16:24:55
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 83021 and starting SCN of 9284244923069 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 83020 and starting SCN of 9284244901184 found to restore
Recovery Manager complete.
Action :
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /V/oracle/MYDB/arch
Oldest online log sequence 83017
Next log sequence to archive 83019
Current log sequence 83019
Après analyse du problème voici la cause de l'erreur :
Les archivelogs de la base source n'avait pas encore été sauvegardés et par conséquent pas dans le catalogue RMAN.
Pour résoudre le problème il faut :
- sauvegarder les archivelog de la base surce
- Relancer le dplicate de nouveau ou faire la restauration des archivelog et finir manuellement la duplcation(ne pas oublier alors les tâches comme la création d'un DBID pour la nouvelle base).
vendredi 18 septembre 2015
ADRCI Oracle 11G
Pour faire une purge des traces a partir de la version Oracle 11g:
adrci> purge -age 2
Pour plus d'information :
adrci> show homes
ADR Homes:
diag/rdbms/mytestdb/MYTESTDB1
diag/rdbms/mytestdb/MYTESTDB
On choisit
adrci> set homepath diag/rdbms/mytestdb/MYTESTDB1
Check the current retention policy:
adrci> show control
nous retourne des informations comme :
LONGP_POLICY (long term) positionné à 365 jours par défaut. utilisé pour les Incidents et mes alertes du Health Monitor.
SHORTP_POLICY (short term) positionné à 30 jours par défaut. utilisé pour la gestion des fichiers trace et core dump.
Pour changer la retention policy on utilise la commande suivante:
adrci> set control (SHORTP_POLICY = 240) : en nombre d'heure -> 10 jours
adrci> set control (LONGP_POLICY = 1095) : -> 1.5 Mois
Pour purger les anciens logs et traces on peux le faire manuellement ou de maniéré automatique :
adrci> purge -age 2
Pour plus d'information :
Automatic Diagnostic Repository (ADR)
$ adrci
la commande suivante liste les différents HOME ADR
adrci> show homes
ADR Homes:
diag/rdbms/mytestdb/MYTESTDB1
diag/rdbms/mytestdb/MYTESTDB
On choisit
adrci> set homepath diag/rdbms/mytestdb/MYTESTDB1
Check the current retention policy:
adrci> show control
nous retourne des informations comme :
LONGP_POLICY (long term) positionné à 365 jours par défaut. utilisé pour les Incidents et mes alertes du Health Monitor.
SHORTP_POLICY (short term) positionné à 30 jours par défaut. utilisé pour la gestion des fichiers trace et core dump.
Pour changer la retention policy on utilise la commande suivante:
adrci> set control (SHORTP_POLICY = 240) : en nombre d'heure -> 10 jours
adrci> set control (LONGP_POLICY = 1095) : -> 1.5 Mois
Pour purger les anciens logs et traces on peux le faire manuellement ou de maniéré automatique :
- Purge manuelle : On spécifie la période de rétention de maniéré explicite : 10080 minutes = 7 jours
- adrci> purge -age 10080 -type ALERT
- adrci> purge -age 10080 -type TRACE
- adrci> purge -age 10080 -type incident
- adrci> purge -age 10080 -type hm
- adrci> purge -age 10080 -type utscdmp
- adrci> purge -age 10080 -type cdump
- adrci> purge -age 10080 pour tout purger.
- adrci> purge (on applique la rétention actuellement définie)
ASM augmenter la taille d'un fichier
Pour augmenter la taille d'un fichier du tablespace SYSAUX :
SQL> ALTER DATABASE DATAFILE '+DATA/MYBD/datafile/sysaux.283.837344867' RESIZE 3072M;
SQL> ALTER DATABASE DATAFILE '+DATA/MYBD/datafile/sysaux.283.837344867' RESIZE 3072M;
SQL> select NAME ,BYTES from v$datafile;
NAME BYTES
-------------------------------------------------- ----------
+DATA/MYDB/datafile/sysaux.283.837344867 3221225472
mercredi 16 septembre 2015
Tuning oracle
Pour voir le plan d'exécution d'une requête on utilise la commande :
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM hr.employees JOIN hr.departments USING (department_id);
Explained.
Pour voir le plan vous pouvez utiliser la requête suivante:
SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
RTRIM (operation) || ' ' ||
RTRIM (options) || ' ' ||
object_name) query_plan,
cost,
cardinality
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0
SQL>
SQL> SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
2 RTRIM (operation) || ' ' ||
3 RTRIM (options) || ' ' ||
4 object_name) query_plan,
5 cost, cardinality
6 FROM plan_table
7 CONNECT BY PRIOR id = parent_id
8 START WITH id = 0 ;
Le résultat de la requête est le suivant :
QUERY_PLAN COST CARDINALITY
---------------------------------------------- ------ -----------
SELECT STATEMENT 4 106
NESTED LOOPS 4 106
TABLE ACCESS FULL EMPLOYEES 3 107
TABLE ACCESS BY INDEX ROWID DEPARTMENTS 1 1
INDEX UNIQUE SCAN DEPT_ID_PK 0 1
Une autre solution plus conviviale est l'utilisation de dbms_xplan
SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 4201152682
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 4 (0)|
| 1 | NESTED LOOPS | | 106 | 9328 | 4 (0)|
| 2 | TABLE ACCESS FULL |EMPLOYEES | 107 | 7276 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID|DEPARTMENTS | 1 | 20 | 1 (0)|
|* 4 | INDEX UNIQUE SCAN |DEPT_ID_PK | 1 | | 0 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID").
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM hr.employees JOIN hr.departments USING (department_id);
Explained.
Pour voir le plan vous pouvez utiliser la requête suivante:
SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
RTRIM (operation) || ' ' ||
RTRIM (options) || ' ' ||
object_name) query_plan,
cost,
cardinality
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0
SQL>
SQL> SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
2 RTRIM (operation) || ' ' ||
3 RTRIM (options) || ' ' ||
4 object_name) query_plan,
5 cost, cardinality
6 FROM plan_table
7 CONNECT BY PRIOR id = parent_id
8 START WITH id = 0 ;
Le résultat de la requête est le suivant :
QUERY_PLAN COST CARDINALITY
---------------------------------------------- ------ -----------
SELECT STATEMENT 4 106
NESTED LOOPS 4 106
TABLE ACCESS FULL EMPLOYEES 3 107
TABLE ACCESS BY INDEX ROWID DEPARTMENTS 1 1
INDEX UNIQUE SCAN DEPT_ID_PK 0 1
Une autre solution plus conviviale est l'utilisation de dbms_xplan
SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 4201152682
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 4 (0)|
| 1 | NESTED LOOPS | | 106 | 9328 | 4 (0)|
| 2 | TABLE ACCESS FULL |EMPLOYEES | 107 | 7276 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID|DEPARTMENTS | 1 | 20 | 1 (0)|
|* 4 | INDEX UNIQUE SCAN |DEPT_ID_PK | 1 | | 0 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID").
- V$SQL_PLAN contient les plans d’exécution des requêtes sql actuellement dans le cache.
- V$SQL_PLAN_STATISTICS on peut voir le nombre d'execution les I/O et les lignes traitées par chaque étape du plan d'exécution.
on peut requêter la vue V$SQL pour voir les requêtes qui consommatrices de CPU ou IO ou avec un temps d’exécution long.
par exemple on peut avoir le top 10 des requêtes les plus consommatrices :
SQL> SELECT sql_id,child_number,sql_text, elapsed_time
2 FROM (SELECT sql_id, child_number, sql_text, elapsed_time,
3 cpu_time,disk_reads,
4 RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
5 FROM v$sql)
6 WHERE elapsed_rank <= 10
7 /
mardi 15 septembre 2015
requêtes utiles pour DBA
Voir les processus Oracle sous Linux/Unix
ps -ef | egrep "smon|pmon|dbwr|lgwr" | grep -v grep
Voir les Bases declarées
cat /etc/oratab | grep -v "^#"
Voir les Bases en cours d'utilisation
ps -ef | egrep pmon_ | grep -v grep
Voir les variable d'environnement concernant Oracle
env | grep ORA
Uptime de la Base Oracle
Prompt Version de base Oracle
Prompt =============================
col Up FOR a40 head "Uptime"
col startup FOR a20 head "Startup time"
SELECT inst_id, TO_CHAR(startup_time, 'DD/MM/YYYY HH24:MI:SS') startup
,TRUNC(SYSDATE - startup_time)||' day(s) '
||trunc(MOD(((SYSDATE - startup_time)*24),24))||' hour(s) '
||round(MOD(((SYSDATE - startup_time)*24*60),60))||' min(s) '
||round(MOD(((SYSDATE - startup_time)*24*60*60),60))||' sec' AS Up
FROM gv$instance
ORDER BY inst_id;
Version de la Base Oracle
Prompt Version des composants de la Base Oracle
Prompt ========================================
COL COMP_ID FORMAT A15
COL COMP_NAME FORMAT A60
COL STATUS FORMAT A15
COL VERSION FORMAT A20
SELECT COMP_ID, COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY ORDER BY COMP_ID;
Avez-vous Diagnostic et Tuning Packs ?
Prompt Diagnostic et Tuning Packs
Prompt ================================================
col name FOR a35
col VALUE FOR a35
SELECT name, VALUE
FROM v$parameter
WHERE name = 'control_management_pack_access';
Utilisation de Diagnostic et Tuning Packs
SET pages 999
col c1 heading 'feature' format a45
col c2 heading 'times|used' format 999,999
col c3 heading 'first|used'
col c4 heading 'used|now'
SELECT
name c1,
detected_usages c2,
first_usage_date c3,
currently_used c4
FROM
dba_feature_usage_statistics
WHERE
first_usage_date IS NOT NULL;
Connaître le système d'exploitation hébergeant la Base Oracle
Prompt Système d exploitation hébergeant la Base Oracle
Prompt ================================================
COL OS FORMAT A100
SELECT dbms_utility.port_string OS FROM dual;
Identifier le "Character Set" de la Base Oracle
Prompt Character SET de la Base Oracle
Prompt ==============================
SELECT VALUE$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';
Pfile ou Spfile
La requête suivante permet de savoir si la base a été démarrée avec un pfile ou un spfile.
Prompt Utilisation de Pfile ou Spfile
Prompt ===============================
SELECT DECODE(COUNT(*), 1, 'spfile', 'pfile' ) FROM v$spparameter WHERE ROWNUM=1 AND isspecified='TRUE';
versio de la base
SQL> select * from PRODUCT_COMPONENT_VERSION
Voir si le paramètre est modifiable à chaud
savoir si le paramètre est modifiable pour le système, l'instance, ou la session :
Prompt Paramètre modifiable à chaud ou pas
Prompt ===================================
SET linesize 500
col name FOR a20
col VALUE FOR a60
SELECT NAME,DECODE(TYPE,1,'Boolean',2,'String',3,'Integer',4,'Parameter file',5,'Reserved',6,'Big integer') TYPE,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE
FROM v$parameter WHERE name LIKE '%&Parameter%';
MEMORY, SGA & GPA
Prompt SGA & PGA
Prompt ===============================
show parameter memory;
show parameter sga;
show parameter pga;
show parameter SIZE;
Prompt SGA & PGA
Prompt ===============================
SET linesize 500
col name FOR a30
col VALUE FOR a60
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%sga%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%pga%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%memory%'
ORDER BY name;
Répartition de la SGA :
SELECT name, ROUND(bytes/1024/1024,0) AS Mo, resizeable FROM v$sgainfo ORDER BY 1;
Memory advisor :
SELECT * FROM gv$pga_target_advice;
SELECT * FROM gv$sga_target_advice;
SELECT * FROM gv$memory_target_advice;
SELECT * FROM gv$shared_pool_advice;
SELECT * FROM gv$px_buffer_advice;
SELECT * FROM DBA_HIST_MEMORY_TARGET_ADVICE;
SELECT * FROM DBA_HIST_SGA_TARGET_ADVICE;
SELECT * FROM DBA_HIST_PGA_TARGET_ADVICE;
Les Archives Logs
Prompt Archive LOG, DATABASE Flash, DataGuard
Prompt ======================================
COL NAME FORMAT A30
SELECT DBID, NAME, LOG_MODE, FLASHBACK_ON, DATABASE_ROLE, DATAGUARD_BROKER, OPEN_MODE, PROTECTION_MODE, REMOTE_ARCHIVE, VERSION_TIME FROM V$DATABASE;
ARCHIVE LOG LIST;
SHOW PARAMETER ARCH;
Base en mode force_logging
Prompt MODE Force Logging
Prompt ==================
SELECT force_logging FROM v$database;
Les Incarnations de la Base Oracle
Prompt Incarnation de Base Oracle
Prompt ======================================
SELECT * FROM V$DATABASE_INCARNATION;
La RecycleBin
Paramétrage de la RecycleBin
Prompt Etat de la RecycleBin
Prompt =====================================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%recyclebin%';
Taille de la RecycleBin
Prompt Taille de la RecycleBin
Prompt =====================================
COL taille_mo FORMAT 999,999,999.999999
SELECT SUM(SPACE)*8192/1024/1024 taille_mo FROM dba_recyclebin ORDER BY owner;
Taille de la RecycleBin par User et Type
Prompt Taille de la RecycleBin par USER et TYPE
Prompt ========================================
SET LINES 250
COL owner FOR a20
COL TYPE FOR a20
COL can_purge FOR a10
COL taille_mo FOR 999,999,999.999
break ON owner skip 1 ON report
compute SUM label "Total User" OF taille_mo ON owner
compute SUM label "Total General" OF taille_mo ON report
SELECT owner
La Flash Recovery
Paramétrage de la Flash Recovery
Prompt Emplacement du fichier Flash Recovery
Prompt =====================================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%recovery%';
show parameter db_recovery_file_dest_size;
Utilisation de la Flash Recovery en %
Prompt Utilisation de la Flash Recovery
Prompt =====================================
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 42.25 0 525
BACKUPPIECE 0 0 1
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 ROWS selected.
Utilisation de la Flash Recovery en Mo
col name FOR a30
col SIZE FOR 999,999,999,999 head "Size MB"
col used FOR 999,999,999,999 head "Used MB"
SELECT name
, FLOOR(space_limit / 1024 / 1024) "size"
, CEIL(space_used / 1024 / 1024) "used"
FROM v$recovery_file_dest
ORDER BY name;
NAME SIZE MB Used MB
------------------------------ ---------------- ----------------
/fra/ 256,000 182,024
SELECT * FROM v$recovery_file_dest;
Le Undo Actif
Prompt Information d UNDO
Prompt =============================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE 'undo%';
L'Optimizer et les Statistiques
Prompt Information sur l Optimzer, les statistiques
Prompt ============================================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE 'optimizer%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE 'stat%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%trace%'
ORDER BY 1;
Les DBLINKS
Prompt Information sur les DBLINKS
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;
Les Directory Oracle
Prompt Information sur les DIRECTORY
Prompt =============================
COL OWNER FORMAT A20
COL DIRECTORY_NAME FORMAT A30
COL DIRECTORY_PATH FORMAT A100
break ON OWNER skip 1
SELECT OWNER,
ORDER BY OWNER,
Prompt Information sur les Outlines
Prompt =============================
col version FOR a10
col category FOR a10
break ON OWNER skip 1
SELECT
OWNER,
NAME,
CATEGORY,
USED,
TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi:ss'),
VERSION,
SQL_TEXT
FROM
DBA_OUTLINES
ORDER BY
OWNER,
NAME;
Les Traces
Prompt Information sur les traces
Prompt ==========================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%dump%' ORDER BY 1;
Lister les Sessions
SELECT SUM("Nb Act Sess") "Nb Act Sess",SUM("Nb Act Sess BackGrd") "Nb Act Sess BackGrd", SUM("Nb Inact Sess") "Nb Inact Sess", SUM("Nb Kill Sess") "Nb Kill Sess", SUM("Nb Tot Sess") "Nb Tot Sess"
FROM (
SELECT COUNT(*) "Nb Act Sess",0 "Nb Act Sess BackGrd", 0 "Nb Inact Sess", 0 "Nb Kill Sess", 0 "Nb Tot Sess" FROM v$session WHERE status='ACTIVE' AND TYPE <> 'BACKGROUND'
UNION ALL
SELECT 0, COUNT(*),0,0,0 FROM v$session WHERE status='ACTIVE' AND TYPE = 'BACKGROUND'
UNION ALL
SELECT 0,0,COUNT(*),0,0 FROM v$session WHERE status='INACTIVE'
UNION ALL
SELECT 0,0,0,COUNT(*),0 FROM v$session WHERE status='KILLED'
UNION ALL
SELECT 0,0,0,0,COUNT(*) FROM v$session
);
Lister les Users Oracle
Prompt Information sur les Disk ASM et les Disk GROUP ASM
Prompt ================================================================
col USER_ID FOR 999999
col USERNAME FOR a20
col DEFAULT_TABLESPACE FOR a20
col DEFAULT_TABLESPACE FOR a20
col PROFILE FOR a20
col ACCOUNT_STATUS FOR a16
SELECT USERNAME,
ORDER BY USERNAME;
Le Stockage avec ASM
Les disques ASM
Prompt Information sur les Disk ASM et les Disk GROUP ASM
Prompt ================================================================
SET LINESIZE 500
SET PAGESIZE 5000
col gprnb FOR 9999
col dsknb FOR 9999
COL NAME FOR A15
COL FAILGROUP FOR A15
COL PATH FOR A26
COL DATABASE_COMPATIBILITY FOR A15
COL TOTAL_MB FOR 99,999,999
COL FREE_MB FOR 99,999,999
COL DATABASE_COMPATIBILITY FOR a15
col header_status FOR a11
col re FOR 99
col we FOR 99
col repair FOR 99999
BREAK ON gprnb SKIP 1
COMPUTE SUM LABEL 'Totals MB :' OF TOTAL_MB ON gprnb
COMPUTE SUM LABEL 'Totals MB :' OF FREE_MB ON gprnb
SELECT g.GROUP_NUMBER gprnb , g.NAME,d.FAILGROUP, d.DISK_NUMBER dsknb, d.NAME, d.header_status,
g.TYPE, DECODE(g.TYPE,'EXTERN','Pas de Raid','NORMAL','Raid sur 2 Disk','HIGH','Raid sur 3 Disk') Typeraid,
g.STATE, d.PREFERRED_READ,
g.DATABASE_COMPATIBILITY, d.PATH, read_errs RE, write_errs wE, d.repair_timer repair, d.TOTAL_MB, d.FREE_MB,
ROUND(100*(g.total_mb-g.free_mb)/g.total_mb) pctocc, required_mirror_free_mb,
CASE WHEN g.USABLE_FILE_MB >0 THEN 'OK' ELSE 'Ajout disque requis' END AS CAPACITY_ASM
FROM V$ASM_DISK d,V$ASM_DISKGROUP g
WHERE d.GROUP_NUMBER=g.GROUP_NUMBER
ORDER BY 1,3;
Les Groupes de disques ASM
COL USABLE_FILE_MB FOR 999,999,999
SELECT name, TYPE, total_mb, free_mb, required_mirror_free_mb, usable_file_mb
FROM V$ASM_DISKGROUP;
Les Control File, Redo Logs, Tablespaces, Spfile
Prompt Information sur les Control FILE, les Redo logs, les Tablespaces
Prompt ================================================================
SET LINES 500
SET PAGESIZE 1500
COL FILE_TYPE FORMAT A45
COL STATUS FORMAT A18
COL GRP FORMAT 9999
COL NAME FORMAT A72
SELECT 'Data / '||b.NAME FILE_TYPE
WHERE a.ts#=b.ts#
AND b.name NOT LIKE 'UNDO%'
UNION
SELECT 'Undo/ '||b.NAME FILE_TYPE
WHERE a.ts#=b.ts# AND b.name LIKE 'UNDO%'
UNION
SELECT 'Temp/ '||b.NAME FILE_TYPE
WHERE a.ts#=b.ts#
UNION
SELECT 'Redo' FILE_TYPE
WHERE L.GROUP# = LF.GROUP#
UNION
SELECT 'Redo Standby' FILE_TYPE
WHERE L.GROUP# = LF.GROUP#
UNION
SELECT 'Control' FILE_TYPE
UNION
SELECT 'Spfile' FILE_TYPE
WHERE name = 'spfile'
ORDER BY 1,2,3;
Connaitre la taille de tous les Tablespaces la base de données
clear breaks
clear computes
clear columns
SET pagesize 50
SET linesize 120
SET heading ON
COLUMN tablespace_name heading 'Tablespace' justify left format a20 truncated
COLUMN tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
COLUMN tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
COLUMN tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
COLUMN tbusedpct heading 'Used % ' justify left format a8
COLUMN tbfreepct heading 'Free % ' justify left format a8
break ON report
compute SUM label 'Totals:' OF tbsize tbused tbfree ON report
SELECT t.tablespace_name, ROUND(a.bytes,2) tbsize,
NVL(ROUND(c.bytes,2),'0') tbfree,
NVL(ROUND(b.bytes,2),'0') tbused,
TO_CHAR(ROUND(100 * (NVL(b.bytes,0)/NVL(a.bytes,1)),2)) || '%' tbusedpct,
TO_CHAR(ROUND(100 * (NVL(c.bytes,0)/NVL(a.bytes,1)),2)) || '%' tbfreepct
FROM dba_tablespaces t,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) bytes
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) bytes
FROM dba_temp_files
GROUP BY tablespace_name ) a,
(SELECT e.tablespace_name, ROUND(SUM(e.bytes)/1024/1024,2) bytes
FROM dba_segments e
GROUP BY e.tablespace_name
UNION
SELECT tablespace_name, SUM(max_size) bytes
FROM v$sort_segment
GROUP BY tablespace_name) b,
(SELECT f.tablespace_name, ROUND(SUM(f.bytes)/1024/1024,2) bytes
FROM dba_free_space f
GROUP BY f.tablespace_name
UNION
SELECT tmp.tablespace_name, (SUM(bytes/1024/1024) - SUM(max_size)) bytes
FROM dba_temp_files tmp, v$sort_segment sort
WHERE tmp.tablespace_name = sort.tablespace_name
GROUP BY tmp.tablespace_name) c
WHERE
t.tablespace_name = a.tablespace_name (+)
AND t.tablespace_name = b.tablespace_name (+)
AND t.tablespace_name = c.tablespace_name (+)
ORDER BY t.tablespace_name;
Connaitre la taille de tous les fichiers la base de données
col FS_NAME format a50
col Taille_Mo format 999,999,999.99
col ALL_DATABASE format a12
BREAK ON ALL_DATABASE SKIP 1
compute SUM label 'Totals:' OF Taille_Mo ON ALL_DATABASE
SELECT '' "ALL_DATABASE",FS_NAME , FLOOR((SUM(VOL)/1024/1024)+0.5) Taille_Mo
FROM (
SELECT SUBSTR(F.FILE_NAME,1,INSTR(F.FILE_NAME ,'/',-2)-1) FS_NAME , SUM(BYTES) VOL
FROM DBA_DATA_FILES F
GROUP BY SUBSTR(F.FILE_NAME,1,INSTR(F.FILE_NAME ,'/',-2)-1)
UNION
SELECT SUBSTR(MEMBER,1,INSTR(MEMBER,'/',-1)-1) FS_NAME,SUM(BYTES) VOL
FROM V$LOGFILE M,V$LOG L
WHERE L.GROUP#=M.GROUP#
AND TYPE = 'ONLINE'
GROUP BY SUBSTR(MEMBER,1,INSTR(MEMBER,'/',-1)-1)
UNION
SELECT SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1) FS_NAME,0 VOL
FROM V$CONTROLFILE
WHERE IS_RECOVERY_DEST_FILE = 'NO'
GROUP BY SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1)
UNION
SELECT SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1) FS_NAME,SUM(BYTES) VOL
FROM V$TEMPFILE
GROUP BY SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1)
)
GROUP BY FS_NAME;
Connaitre la taille d'un objet dans la base de données
CREATE OR REPLACE PROCEDURE taille_reelle_object(OBJECT_OWNER_in IN VARCHAR2,
Procédure créée.
SQL> SET SERVEROUTPUT ON;
SQL> EXEC taille_reelle_object('SCOTT','X_PHYNUM','INDEX');
Espace utilisé en Mo: 481,77
Espace alloué en Mo: 536
Utilisation de DataGuard
Type de DataGuard
SELECT db_unique_name,dataguard_broker,database_role,protection_mode FROM v$database;
DB_UNIQUE_NAME DATAGUAR DATABASE_ROLE PROTECTION_MODE
------------------------------ -------- ---------------- --------------------
ORCL ENABLED PRIMARY MAXIMUM PERFORMANCE
Les informations de base via DGMGRL
se connecter à DGMGRL
$ dgmgrl /
Configuration globale :
show configuration verbose;
Configuration de chaque Database :
show DATABASE "<database_name>";
ou plus détaillé :
show DATABASE verbose "<database_name>";
--info incarnation base
select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME, CURRENT_INCARNATION
from <SCHEMA_RMAN>.rc_database_incarnation where name='<DBNAME>';
select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME
from rc_database_incarnation where dbid=<DB_ID>;
select CLIENT_NAME, STATUS from DBA_AUTOTASK_CLIENT;
info redolog ::
COL MEMBER FORMAT A100
COL STATUS FORMAT A40
select l.group#,l.status,lf.member,l.bytes/1024/1024 size_Mb from v$log l, v$logfile lf where l.group#=lf.group# ;
changer la taille des redo :
SQL> select banner from v$version;
BANNER
longops sur un sid
rêquete pour déterminer la taille a la quelle on peut réduire les datafiles pour récuperer de l'espace :
select file_name, hwm, blocks total_blocks, ROUND((blocks-hwm+1)*8192/1024/1024,2) shrink_possible, ROUND(a.blocks*p.value/1024/1024,2) taille, ROUND(a.blocks*p.value/1024/1024,2)-ROUND((blocks-hwm+1)*8192/1024/1024,0)-10 taille_possible
from dba_data_files a, v$parameter p,
( select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id
AND tablespace_name = 'GOJRCL_DATA_PROLIX'
AND upper(p.name) = upper('db_block_size');
--------------------------
purger l'alert log d'une base on line :
dans l'exemple ici la taille de l'alert log est de 4G :
cat alert_oibko121.log |gzip > alert_oibko121.log.20180129.gz; echo > alert_oibko121.log
ps -ef | egrep "smon|pmon|dbwr|lgwr" | grep -v grep
Voir les Bases declarées
cat /etc/oratab | grep -v "^#"
Voir les Bases en cours d'utilisation
ps -ef | egrep pmon_ | grep -v grep
Voir les variable d'environnement concernant Oracle
env | grep ORA
Uptime de la Base Oracle
Prompt Version de base Oracle
Prompt =============================
col Up FOR a40 head "Uptime"
col startup FOR a20 head "Startup time"
SELECT inst_id, TO_CHAR(startup_time, 'DD/MM/YYYY HH24:MI:SS') startup
,TRUNC(SYSDATE - startup_time)||' day(s) '
||trunc(MOD(((SYSDATE - startup_time)*24),24))||' hour(s) '
||round(MOD(((SYSDATE - startup_time)*24*60),60))||' min(s) '
||round(MOD(((SYSDATE - startup_time)*24*60*60),60))||' sec' AS Up
FROM gv$instance
ORDER BY inst_id;
Version de la Base Oracle
Prompt Version des composants de la Base Oracle
Prompt ========================================
COL COMP_ID FORMAT A15
COL COMP_NAME FORMAT A60
COL STATUS FORMAT A15
COL VERSION FORMAT A20
SELECT COMP_ID, COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY ORDER BY COMP_ID;
Avez-vous Diagnostic et Tuning Packs ?
Prompt Diagnostic et Tuning Packs
Prompt ================================================
col name FOR a35
col VALUE FOR a35
SELECT name, VALUE
FROM v$parameter
WHERE name = 'control_management_pack_access';
Utilisation de Diagnostic et Tuning Packs
SET pages 999
col c1 heading 'feature' format a45
col c2 heading 'times|used' format 999,999
col c3 heading 'first|used'
col c4 heading 'used|now'
SELECT
name c1,
detected_usages c2,
first_usage_date c3,
currently_used c4
FROM
dba_feature_usage_statistics
WHERE
first_usage_date IS NOT NULL;
Connaître le système d'exploitation hébergeant la Base Oracle
Prompt Système d exploitation hébergeant la Base Oracle
Prompt ================================================
COL OS FORMAT A100
SELECT dbms_utility.port_string OS FROM dual;
Identifier le "Character Set" de la Base Oracle
Prompt Character SET de la Base Oracle
Prompt ==============================
SELECT VALUE$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';
Pfile ou Spfile
La requête suivante permet de savoir si la base a été démarrée avec un pfile ou un spfile.
Prompt Utilisation de Pfile ou Spfile
Prompt ===============================
SELECT DECODE(COUNT(*), 1, 'spfile', 'pfile' ) FROM v$spparameter WHERE ROWNUM=1 AND isspecified='TRUE';
versio de la base
SQL> select * from PRODUCT_COMPONENT_VERSION
Voir si le paramètre est modifiable à chaud
savoir si le paramètre est modifiable pour le système, l'instance, ou la session :
Prompt Paramètre modifiable à chaud ou pas
Prompt ===================================
SET linesize 500
col name FOR a20
col VALUE FOR a60
SELECT NAME,DECODE(TYPE,1,'Boolean',2,'String',3,'Integer',4,'Parameter file',5,'Reserved',6,'Big integer') TYPE,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE
FROM v$parameter WHERE name LIKE '%&Parameter%';
MEMORY, SGA & GPA
Prompt SGA & PGA
Prompt ===============================
show parameter memory;
show parameter sga;
show parameter pga;
show parameter SIZE;
Prompt SGA & PGA
Prompt ===============================
SET linesize 500
col name FOR a30
col VALUE FOR a60
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%sga%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%pga%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%memory%'
ORDER BY name;
Répartition de la SGA :
SELECT name, ROUND(bytes/1024/1024,0) AS Mo, resizeable FROM v$sgainfo ORDER BY 1;
Memory advisor :
SELECT * FROM gv$pga_target_advice;
SELECT * FROM gv$sga_target_advice;
SELECT * FROM gv$memory_target_advice;
SELECT * FROM gv$shared_pool_advice;
SELECT * FROM gv$px_buffer_advice;
SELECT * FROM DBA_HIST_MEMORY_TARGET_ADVICE;
SELECT * FROM DBA_HIST_SGA_TARGET_ADVICE;
SELECT * FROM DBA_HIST_PGA_TARGET_ADVICE;
Les Archives Logs
Prompt Archive LOG, DATABASE Flash, DataGuard
Prompt ======================================
COL NAME FORMAT A30
SELECT DBID, NAME, LOG_MODE, FLASHBACK_ON, DATABASE_ROLE, DATAGUARD_BROKER, OPEN_MODE, PROTECTION_MODE, REMOTE_ARCHIVE, VERSION_TIME FROM V$DATABASE;
ARCHIVE LOG LIST;
SHOW PARAMETER ARCH;
Base en mode force_logging
Prompt MODE Force Logging
Prompt ==================
SELECT force_logging FROM v$database;
Les Incarnations de la Base Oracle
Prompt Incarnation de Base Oracle
Prompt ======================================
SELECT * FROM V$DATABASE_INCARNATION;
La RecycleBin
Paramétrage de la RecycleBin
Prompt Etat de la RecycleBin
Prompt =====================================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%recyclebin%';
Taille de la RecycleBin
Prompt Taille de la RecycleBin
Prompt =====================================
COL taille_mo FORMAT 999,999,999.999999
SELECT SUM(SPACE)*8192/1024/1024 taille_mo FROM dba_recyclebin ORDER BY owner;
Taille de la RecycleBin par User et Type
Prompt Taille de la RecycleBin par USER et TYPE
Prompt ========================================
SET LINES 250
COL owner FOR a20
COL TYPE FOR a20
COL can_purge FOR a10
COL taille_mo FOR 999,999,999.999
break ON owner skip 1 ON report
compute SUM label "Total User" OF taille_mo ON owner
compute SUM label "Total General" OF taille_mo ON report
SELECT owner
, TYPE
, can_purge
, ( SUM (SPACE)
, can_purge
, ( SUM (SPACE)
* (SELECT VALUE
FROM v$parameter
WHERE name = 'db_block_size')
WHERE name = 'db_block_size')
/ 1024
/ 1024)
taille_mo
/ 1024)
taille_mo
FROM dba_recyclebin
GROUP BY owner
, TYPE
, can_purge
ORDER BY owner;
, can_purge
La Flash Recovery
Paramétrage de la Flash Recovery
Prompt Emplacement du fichier Flash Recovery
Prompt =====================================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%recovery%';
show parameter db_recovery_file_dest_size;
Utilisation de la Flash Recovery en %
Prompt Utilisation de la Flash Recovery
Prompt =====================================
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 42.25 0 525
BACKUPPIECE 0 0 1
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 ROWS selected.
Utilisation de la Flash Recovery en Mo
col name FOR a30
col SIZE FOR 999,999,999,999 head "Size MB"
col used FOR 999,999,999,999 head "Used MB"
SELECT name
, FLOOR(space_limit / 1024 / 1024) "size"
, CEIL(space_used / 1024 / 1024) "used"
FROM v$recovery_file_dest
ORDER BY name;
NAME SIZE MB Used MB
------------------------------ ---------------- ----------------
/fra/ 256,000 182,024
SELECT * FROM v$recovery_file_dest;
Le Undo Actif
Prompt Information d UNDO
Prompt =============================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE 'undo%';
L'Optimizer et les Statistiques
Prompt Information sur l Optimzer, les statistiques
Prompt ============================================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE 'optimizer%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE 'stat%'
UNION
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%trace%'
ORDER BY 1;
Les DBLINKS
Prompt Information sur les DBLINKS
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;
Les Directory Oracle
Prompt Information sur les DIRECTORY
Prompt =============================
COL OWNER FORMAT A20
COL DIRECTORY_NAME FORMAT A30
COL DIRECTORY_PATH FORMAT A100
break ON OWNER skip 1
SELECT OWNER,
DIRECTORY_NAME,
DIRECTORY_PATH
FROM DBA_DIRECTORIESDIRECTORY_PATH
ORDER BY OWNER,
DIRECTORY_NAME;
Les outlines
Prompt Information sur les Outlines
Prompt =============================
col version FOR a10
col category FOR a10
break ON OWNER skip 1
SELECT
OWNER,
NAME,
CATEGORY,
USED,
TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi:ss'),
VERSION,
SQL_TEXT
FROM
DBA_OUTLINES
ORDER BY
OWNER,
NAME;
Les Traces
Prompt Information sur les traces
Prompt ==========================
COL NAME FORMAT A40
COL VALUE FORMAT A100
SELECT name, VALUE FROM v$parameter WHERE name LIKE '%dump%' ORDER BY 1;
Lister les Sessions
SELECT SUM("Nb Act Sess") "Nb Act Sess",SUM("Nb Act Sess BackGrd") "Nb Act Sess BackGrd", SUM("Nb Inact Sess") "Nb Inact Sess", SUM("Nb Kill Sess") "Nb Kill Sess", SUM("Nb Tot Sess") "Nb Tot Sess"
FROM (
SELECT COUNT(*) "Nb Act Sess",0 "Nb Act Sess BackGrd", 0 "Nb Inact Sess", 0 "Nb Kill Sess", 0 "Nb Tot Sess" FROM v$session WHERE status='ACTIVE' AND TYPE <> 'BACKGROUND'
UNION ALL
SELECT 0, COUNT(*),0,0,0 FROM v$session WHERE status='ACTIVE' AND TYPE = 'BACKGROUND'
UNION ALL
SELECT 0,0,COUNT(*),0,0 FROM v$session WHERE status='INACTIVE'
UNION ALL
SELECT 0,0,0,COUNT(*),0 FROM v$session WHERE status='KILLED'
UNION ALL
SELECT 0,0,0,0,COUNT(*) FROM v$session
);
Lister les Users Oracle
Prompt Information sur les Disk ASM et les Disk GROUP ASM
Prompt ================================================================
col USER_ID FOR 999999
col USERNAME FOR a20
col DEFAULT_TABLESPACE FOR a20
col DEFAULT_TABLESPACE FOR a20
col PROFILE FOR a20
col ACCOUNT_STATUS FOR a16
SELECT USERNAME,
USER_ID,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
ACCOUNT_STATUS,
PROFILE,
CREATED
FROM DBA_USERSDEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
ACCOUNT_STATUS,
PROFILE,
CREATED
ORDER BY USERNAME;
Le Stockage avec ASM
Les disques ASM
Prompt Information sur les Disk ASM et les Disk GROUP ASM
Prompt ================================================================
SET LINESIZE 500
SET PAGESIZE 5000
col gprnb FOR 9999
col dsknb FOR 9999
COL NAME FOR A15
COL FAILGROUP FOR A15
COL PATH FOR A26
COL DATABASE_COMPATIBILITY FOR A15
COL TOTAL_MB FOR 99,999,999
COL FREE_MB FOR 99,999,999
COL DATABASE_COMPATIBILITY FOR a15
col header_status FOR a11
col re FOR 99
col we FOR 99
col repair FOR 99999
BREAK ON gprnb SKIP 1
COMPUTE SUM LABEL 'Totals MB :' OF TOTAL_MB ON gprnb
COMPUTE SUM LABEL 'Totals MB :' OF FREE_MB ON gprnb
SELECT g.GROUP_NUMBER gprnb , g.NAME,d.FAILGROUP, d.DISK_NUMBER dsknb, d.NAME, d.header_status,
g.TYPE, DECODE(g.TYPE,'EXTERN','Pas de Raid','NORMAL','Raid sur 2 Disk','HIGH','Raid sur 3 Disk') Typeraid,
g.STATE, d.PREFERRED_READ,
g.DATABASE_COMPATIBILITY, d.PATH, read_errs RE, write_errs wE, d.repair_timer repair, d.TOTAL_MB, d.FREE_MB,
ROUND(100*(g.total_mb-g.free_mb)/g.total_mb) pctocc, required_mirror_free_mb,
CASE WHEN g.USABLE_FILE_MB >0 THEN 'OK' ELSE 'Ajout disque requis' END AS CAPACITY_ASM
FROM V$ASM_DISK d,V$ASM_DISKGROUP g
WHERE d.GROUP_NUMBER=g.GROUP_NUMBER
ORDER BY 1,3;
Les Groupes de disques ASM
COL USABLE_FILE_MB FOR 999,999,999
SELECT name, TYPE, total_mb, free_mb, required_mirror_free_mb, usable_file_mb
FROM V$ASM_DISKGROUP;
Les Control File, Redo Logs, Tablespaces, Spfile
Prompt Information sur les Control FILE, les Redo logs, les Tablespaces
Prompt ================================================================
SET LINES 500
SET PAGESIZE 1500
COL FILE_TYPE FORMAT A45
COL STATUS FORMAT A18
COL GRP FORMAT 9999
COL NAME FORMAT A72
SELECT 'Data / '||b.NAME FILE_TYPE
, TO_NUMBER (FILE# ) GRP
, a.NAME
, a.STATUS
, ROUND(a.BYTES / (1024 * 1024),0) MBYTES
FROM V$DATAFILE a,v$TABLESPACE b, a.NAME
, a.STATUS
, ROUND(a.BYTES / (1024 * 1024),0) MBYTES
WHERE a.ts#=b.ts#
AND b.name NOT LIKE 'UNDO%'
UNION
SELECT 'Undo/ '||b.NAME FILE_TYPE
, TO_NUMBER (FILE# ) GRP
, a.NAME
, a.STATUS
, ROUND(a.BYTES / (1024 * 1024),0) MBYTES
FROM V$DATAFILE a,v$TABLESPACE b, a.NAME
, a.STATUS
, ROUND(a.BYTES / (1024 * 1024),0) MBYTES
WHERE a.ts#=b.ts# AND b.name LIKE 'UNDO%'
UNION
SELECT 'Temp/ '||b.NAME FILE_TYPE
, TO_NUMBER (FILE# ) GRP
, a.NAME
, a.STATUS
, ROUND(a.BYTES / (1024 * 1024),0) MBYTES
FROM V$TEMPFILE a,v$TABLESPACE b, a.NAME
, a.STATUS
, ROUND(a.BYTES / (1024 * 1024),0) MBYTES
WHERE a.ts#=b.ts#
UNION
SELECT 'Redo' FILE_TYPE
, LF.GROUP# GRP
, LF.MEMBER name
, L.STATUS
, ROUND(L.BYTES / (1024 * 1024),0) MBYTES
FROM V$LOG L, V$LOGFILE LF, LF.MEMBER name
, L.STATUS
, ROUND(L.BYTES / (1024 * 1024),0) MBYTES
WHERE L.GROUP# = LF.GROUP#
UNION
SELECT 'Redo Standby' FILE_TYPE
, LF.GROUP# GRP
, LF.MEMBER name
, L.STATUS
, ROUND(L.BYTES / (1024 * 1024),0) MBYTES
FROM V$STANDBY_LOG L, V$LOGFILE LF, LF.MEMBER name
, L.STATUS
, ROUND(L.BYTES / (1024 * 1024),0) MBYTES
WHERE L.GROUP# = LF.GROUP#
UNION
SELECT 'Control' FILE_TYPE
, TO_NUMBER (NULL) GRP
, NAME
, STATUS
, TO_NUMBER (NULL) MBYTES
FROM V$CONTROLFILE, NAME
, STATUS
, TO_NUMBER (NULL) MBYTES
UNION
SELECT 'Spfile' FILE_TYPE
, TO_NUMBER (NULL ) GRP
, VALUE name
, NULL status
, TO_NUMBER (NULL) MBYTES
FROM v$parameter , VALUE name
, NULL status
, TO_NUMBER (NULL) MBYTES
WHERE name = 'spfile'
ORDER BY 1,2,3;
Connaitre la taille de tous les Tablespaces la base de données
clear breaks
clear computes
clear columns
SET pagesize 50
SET linesize 120
SET heading ON
COLUMN tablespace_name heading 'Tablespace' justify left format a20 truncated
COLUMN tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
COLUMN tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
COLUMN tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
COLUMN tbusedpct heading 'Used % ' justify left format a8
COLUMN tbfreepct heading 'Free % ' justify left format a8
break ON report
compute SUM label 'Totals:' OF tbsize tbused tbfree ON report
SELECT t.tablespace_name, ROUND(a.bytes,2) tbsize,
NVL(ROUND(c.bytes,2),'0') tbfree,
NVL(ROUND(b.bytes,2),'0') tbused,
TO_CHAR(ROUND(100 * (NVL(b.bytes,0)/NVL(a.bytes,1)),2)) || '%' tbusedpct,
TO_CHAR(ROUND(100 * (NVL(c.bytes,0)/NVL(a.bytes,1)),2)) || '%' tbfreepct
FROM dba_tablespaces t,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) bytes
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) bytes
FROM dba_temp_files
GROUP BY tablespace_name ) a,
(SELECT e.tablespace_name, ROUND(SUM(e.bytes)/1024/1024,2) bytes
FROM dba_segments e
GROUP BY e.tablespace_name
UNION
SELECT tablespace_name, SUM(max_size) bytes
FROM v$sort_segment
GROUP BY tablespace_name) b,
(SELECT f.tablespace_name, ROUND(SUM(f.bytes)/1024/1024,2) bytes
FROM dba_free_space f
GROUP BY f.tablespace_name
UNION
SELECT tmp.tablespace_name, (SUM(bytes/1024/1024) - SUM(max_size)) bytes
FROM dba_temp_files tmp, v$sort_segment sort
WHERE tmp.tablespace_name = sort.tablespace_name
GROUP BY tmp.tablespace_name) c
WHERE
t.tablespace_name = a.tablespace_name (+)
AND t.tablespace_name = b.tablespace_name (+)
AND t.tablespace_name = c.tablespace_name (+)
ORDER BY t.tablespace_name;
Connaitre la taille de tous les fichiers la base de données
col FS_NAME format a50
col Taille_Mo format 999,999,999.99
col ALL_DATABASE format a12
BREAK ON ALL_DATABASE SKIP 1
compute SUM label 'Totals:' OF Taille_Mo ON ALL_DATABASE
SELECT '' "ALL_DATABASE",FS_NAME , FLOOR((SUM(VOL)/1024/1024)+0.5) Taille_Mo
FROM (
SELECT SUBSTR(F.FILE_NAME,1,INSTR(F.FILE_NAME ,'/',-2)-1) FS_NAME , SUM(BYTES) VOL
FROM DBA_DATA_FILES F
GROUP BY SUBSTR(F.FILE_NAME,1,INSTR(F.FILE_NAME ,'/',-2)-1)
UNION
SELECT SUBSTR(MEMBER,1,INSTR(MEMBER,'/',-1)-1) FS_NAME,SUM(BYTES) VOL
FROM V$LOGFILE M,V$LOG L
WHERE L.GROUP#=M.GROUP#
AND TYPE = 'ONLINE'
GROUP BY SUBSTR(MEMBER,1,INSTR(MEMBER,'/',-1)-1)
UNION
SELECT SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1) FS_NAME,0 VOL
FROM V$CONTROLFILE
WHERE IS_RECOVERY_DEST_FILE = 'NO'
GROUP BY SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1)
UNION
SELECT SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1) FS_NAME,SUM(BYTES) VOL
FROM V$TEMPFILE
GROUP BY SUBSTR(NAME,1,INSTR(NAME,'/',-1)-1)
)
GROUP BY FS_NAME;
Connaitre la taille d'un objet dans la base de données
CREATE OR REPLACE PROCEDURE taille_reelle_object(OBJECT_OWNER_in IN VARCHAR2,
OBJECT_NAME_in IN varcahr2,
OBJECT_TYPE_in IN VARCHAR2)
OBJECT_TYPE_in IN VARCHAR2)
AS
SAMPLE_CONTROL_in NUMBER := NULL;
SPACE_USED_out NUMBER;
SPACE_ALLOCATED_out NUMBER;
CHAIN_PCENT_out NUMBER;
BEGIN
dbms_space.object_space_usage (
SAMPLE_CONTROL_in NUMBER := NULL;
SPACE_USED_out NUMBER;
SPACE_ALLOCATED_out NUMBER;
CHAIN_PCENT_out NUMBER;
BEGIN
dbms_space.object_space_usage (
OBJECT_OWNER => OBJECT_OWNER_in ,
OBJECT_NAME => OBJECT_NAME_in ,
OBJECT_TYPE => OBJECT_TYPE_in ,
SAMPLE_CONTROL => SAMPLE_CONTROL_in ,
SPACE_USED => SPACE_USED_out ,
SPACE_ALLOCATED => SPACE_ALLOCATED_out ,
CHAIN_PCENT => CHAIN_PCENT_out
OBJECT_NAME => OBJECT_NAME_in ,
OBJECT_TYPE => OBJECT_TYPE_in ,
SAMPLE_CONTROL => SAMPLE_CONTROL_in ,
SPACE_USED => SPACE_USED_out ,
SPACE_ALLOCATED => SPACE_ALLOCATED_out ,
CHAIN_PCENT => CHAIN_PCENT_out
);
dbms_output.put_line('Espace utilisé en Mo: ' || ROUND(SPACE_USED_out/1024/1024,2));
dbms_output.put_line('Espace alloué en Mo: ' || ROUND(SPACE_ALLOCATED_out/1024/1024,2));
END;
/
dbms_output.put_line('Espace utilisé en Mo: ' || ROUND(SPACE_USED_out/1024/1024,2));
dbms_output.put_line('Espace alloué en Mo: ' || ROUND(SPACE_ALLOCATED_out/1024/1024,2));
END;
/
Procédure créée.
SQL> SET SERVEROUTPUT ON;
SQL> EXEC taille_reelle_object('SCOTT','X_PHYNUM','INDEX');
Espace utilisé en Mo: 481,77
Espace alloué en Mo: 536
Utilisation de DataGuard
Type de DataGuard
SELECT db_unique_name,dataguard_broker,database_role,protection_mode FROM v$database;
DB_UNIQUE_NAME DATAGUAR DATABASE_ROLE PROTECTION_MODE
------------------------------ -------- ---------------- --------------------
ORCL ENABLED PRIMARY MAXIMUM PERFORMANCE
Les informations de base via DGMGRL
se connecter à DGMGRL
$ dgmgrl /
Configuration globale :
show configuration verbose;
Configuration de chaque Database :
show DATABASE "<database_name>";
ou plus détaillé :
show DATABASE verbose "<database_name>";
--info incarnation base
select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME, CURRENT_INCARNATION
from <SCHEMA_RMAN>.rc_database_incarnation where name='<DBNAME>';
select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME
from rc_database_incarnation where dbid=<DB_ID>;
select CLIENT_NAME, STATUS from DBA_AUTOTASK_CLIENT;
info redolog ::
COL MEMBER FORMAT A100
COL STATUS FORMAT A40
select l.group#,l.status,lf.member,l.bytes/1024/1024 size_Mb from v$log l, v$logfile lf where l.group#=lf.group# ;
changer la taille des redo :
SQL> select banner from v$version;
BANNER
longops sur un sid
select l.SID,l.serial#, l.username,
decode(trunc(l.elapsed_seconds/3600),0,null,trunc(l.elapsed_seconds/3600)||'h ')||decode(trunc((l.elapsed_seconds-(trunc(l.elapsed_seconds/3600)*3600))/60),0,null,
trunc((l.elapsed_seconds-(trunc(l.elapsed_seconds/3600)*3600))/60)||'mn ')||mod(l.elapsed_seconds,60)||'s' "Durée écoulée",
decode(trunc(l.time_remaining/3600),0,null,trunc(l.time_remaining/3600)||'h ')||decode(trunc((l.time_remaining-(trunc(l.time_remaining/3600)*3600))/60),0,null,
trunc((l.time_remaining-(trunc(l.time_remaining/3600)*3600))/60)||'mn ')||mod(l.time_remaining,60)||'s' "Durée restante",
l.opname "Opération", l.target, s.sql_text
from v$session_longops l, v$sqlarea s
where s.address=l.sql_address
and l.sid=469 and serial#=27497;
----------------------------rêquete pour déterminer la taille a la quelle on peut réduire les datafiles pour récuperer de l'espace :
select file_name, hwm, blocks total_blocks, ROUND((blocks-hwm+1)*8192/1024/1024,2) shrink_possible, ROUND(a.blocks*p.value/1024/1024,2) taille, ROUND(a.blocks*p.value/1024/1024,2)-ROUND((blocks-hwm+1)*8192/1024/1024,0)-10 taille_possible
from dba_data_files a, v$parameter p,
( select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id
AND tablespace_name = 'GOJRCL_DATA_PROLIX'
AND upper(p.name) = upper('db_block_size');
--------------------------
purger l'alert log d'une base on line :
dans l'exemple ici la taille de l'alert log est de 4G :
cat alert_oibko121.log |gzip > alert_oibko121.log.20180129.gz; echo > alert_oibko121.log
résultat :
4.0K alert_oibko121.log
53M alert_oibko121.log.20180129.gz
24K oibko121_cjq0_11163.trc
Inscription à :
Articles (Atom)