set termout off
set linesize 90
set pagesize 20
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>DB HEALTH Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#800000'" -
TABLE "WIDTH='70%' ALIGN='CENTER' BORDER='5'"
spool myreport9.html
set lines 1000 pages 200
col NAME for a50
col VALUE for a50
select NAME,VALUE from v$parameter where VALUE!='NULL' and (ISSYS_MODIFIABLE in
('FALSE') or ISBASIC='TRUE');
set lines 1000 pages 200
SELECT TO_DATE(TO_CHAR (snpshot.begin_interval_time,'DD-MM-YYYY'),'DD-MM-YYYY')
daywise
, dbinfo.name
, dhts.tsname tablespacename
, max(round((dhtsu.tablespace_size* dtblspc.block_size )/(1024*1024*1024),2))
maximum_allocsize_GB
, max(round((dhtsu.tablespace_usedsize* dtblspc.block_size )/(1024*1024*1024),2))
maximum_usedsize_GB
FROM DBA_HIST_TBSPC_SPACE_USAGE dhtsu
, DBA_HIST_TABLESPACE_STAT dhts
, DBA_HIST_SNAPSHOT snpshot
, DBA_TABLESPACES dtblspc
, v$database dbinfo
WHERE begin_interval_time > TRUNC(SYSDATE) - 7
AND dhtsu.tablespace_id= dhts.ts#
AND dhtsu.snap_id = snpshot.snap_id
AND dhts.tsname = dtblspc.tablespace_name
AND dhts.tsname NOT IN ('SYSAUX','SYSTEM','UNDOTBS1','UNDOTBS2','USERS')
GROUP BY TO_DATE(TO_CHAR (snpshot.begin_interval_time,'DD-MM-YYYY'),'DD-MM-YYYY')
, dhts.tsname,dbinfo.name
ORDER BY dhts.tsname
, daywise;
set lines 500
set pagesize 300
set echo off
set feedback off
col OUTPUT_DEVICE for a15
COLUMN start_time FORMAT a25
COLUMN end_time FORMAT a25
COLUMN elapsed_time FORMAT a30
COLUMN status format a25
COLUMN input_type format a15
COLUMN output_device_type format a50
COLUMN input_size format a15
COLUMN output_size format a15
col ELAPSED_TIME for a10
SELECT
TO_CHAR(r.start_time, 'mm/dd/yyyy HH24:MI:SS') start_time
, TO_CHAR(r.end_time, 'mm/dd/yyyy HH24:MI:SS') end_time
, r.time_taken_display elapsed_time
, r.status status
, r.input_type input_type
, r.output_device_type output_device
, r.input_bytes_display input_size
, r.output_bytes_display output_size
FROM
(select
start_time
, end_time
, time_taken_display
, status
, input_type
, output_device_type
, input_bytes_display
, output_bytes_display
from v$rman_backup_job_details
order by start_time DESC
) r
where rownum<10;
col TABLESPACE_NAME for a18
select tablespace_name,round(Total_Space,2)Total_space,round((total_space -
free_space),2) Used_Space, round(Free_Space,2) Free_Space,
round((total_space - free_space)*100/Total_Space,0) "Used%" from (
select a.tablespace_name, sum(a.bytes)/1024/1024 total_space, b.remaning_space
free_space from dba_data_files a,
(select tablespace_name, sum(bytes)/1024/1024 remaning_space from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name group by
a.tablespace_name,b.remaning_space) order by 5;
spool off;
exit
