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

, TYPE
, can_purge
, ( SUM (SPACE)

* (SELECT VALUE

FROM v$parameter
WHERE name = 'db_block_size')

/ 1024
/ 1024)
taille_mo

FROM dba_recyclebin

GROUP BY owner

, TYPE
, can_purge

ORDER BY 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,

DIRECTORY_NAME,
DIRECTORY_PATH

FROM DBA_DIRECTORIES
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_USERS
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
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
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
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
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
WHERE L.GROUP# = LF.GROUP#
UNION
SELECT 'Control' FILE_TYPE

, TO_NUMBER (NULL) GRP
, NAME
, STATUS
, TO_NUMBER (NULL) MBYTES

FROM V$CONTROLFILE
UNION
SELECT 'Spfile' FILE_TYPE

, TO_NUMBER (NULL ) GRP
, VALUE name
, NULL status
, TO_NUMBER (NULL) MBYTES

FROM v$parameter
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)

AS
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

);
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