1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
| SET LINESIZE 140
SET PAGESIZE 100
SET TRIMS ON
COLUMN tablespace_name FORMAT a20 HEAD "Tablespace ORACLE"
COLUMN status FORMAT a10 HEAD "Statut"
COLUMN file_name FORMAT a55 HEAD "Fichier OS"
COLUMN bytes FORMAT '99990' HEAD "Taille"
COLUMN included_in_database_backup FORMAT a4 HEAD "Arch"
COLUMN increment_by FORMAT '99999990' HEAD "Increment"
COLUMN maxbytes FORMAT '999990' HEAD "Maximum"
BREAK ON REPORT
COMPUTE SUM LABEL "Total" OF bytes ON REPORT
SELECT tablespace_name,
STATUS,
file_name,
ROUND (bytes/(1024*1024), 0) bytes,
included_in_database_backup,
ROUND (increment_by/(1024*1024), 0) increment_by,
ROUND (maxbytes/(1024*1024), 0) maxbytes
FROM ( SELECT '1.0' ordre_tri,
'CONTROL FILES' tablespace_name,
NULL status,
NULL file_name,
NULL bytes,
NULL included_in_database_backup,
NULL increment_by,
NULL maxbytes
FROM dual
UNION ALL
SELECT '1.1' ordre_tri,
NULL tablespace_name,
status,
name file_name,
NULL bytes,
NULL included_in_database_backup,
NULL increment_by,
NULL maxbytes
FROM v$controlfile
UNION ALL
SELECT '2.0' ordre_tri,
'REDO LOG FILES' tablespace_name,
NULL status,
NULL file_name,
NULL bytes,
NULL included_in_database_backup,
NULL increment_by,
NULL maxbytes
FROM dual
UNION ALL
SELECT '2.1' ordre_tri,
' ' || lf.group# || '_' || l.members tablespace_name,
l.status,
lf.member file_name,
l.bytes bytes,
NULL included_in_database_backup,
NULL increment_by,
NULL maxbytes
FROM v$logfile lf,
v$log l
WHERE lf.group# = l.group#
UNION ALL
SELECT '3.0' ordre_tri,
'DATA FILES' tablespace_name,
NULL status,
NULL file_name,
NULL bytes,
NULL included_in_database_backup,
NULL increment_by,
NULL maxbytes
FROM dual
UNION ALL
SELECT '3.1' ordre_tri,
' ' || ddf.tablespace_name,
ddf.status,
ddf.file_name,
ddf.bytes bytes,
vtbs.included_in_database_backup,
ddf.increment_by,
ddf.maxbytes
FROM dba_data_files ddf,
v$tablespace vtbs
WHERE ddf.tablespace_name = vtbs.name
)
ORDER BY ordre_tri, tablespace_name, file_name
; |
Partager