Segment tablespace growth details script
object growth over last N days, sorted by growth desc
set feedback on
select * from (select c.TABLESPACE_NAME,c.segment_name "Object Name",b.object_type,
sum(space_used_delta)/1024/1024 "Growth (MB)"
from dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner ='ANUJ_BIG_TABLE'
group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
order by 3 asc;
set pages 80
set feedback off
column "OBJECT_NAME" justify left format A30
column "SUBOBJECT_NAME" justify left format A30
column "OBJECT_TYPE" justify left format A30
column "Tablespace Name" justify left format A30
set line 5000
SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",
(SELECT sum(bytes)/(1024*1024) FROM dba_segments
WHERE segment_name=o.object_name) "Total Size(MB)"
FROM DBA_OBJECTS o,( SELECT TS#,OBJ#,SUM(SPACE_USED_DELTA) growth FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0 ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND o.OWNER='SCOTT' ---------------
ORDER BY 6 DESC
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
set line 5000
column "SEGMENT_NAME" justify left format A30
column "TABLESPACE_NAME" justify left format A30
select * from (select c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, 'MM/DD/YY') mydate,
sum(space_used_delta)/1024/1024 "Space used (MB)", avg(c.bytes)/1024/1024 "Total Object Size (MB)",
round(sum(space_used_delta)/sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate)-10
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = 'ANUJ_BIG_TABLE'
group by c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, 'MM/DD/YY')
order by c.TABLESPACE_NAME,c.segment_name,to_date(mydate, 'MM/DD/YY'));
TABLESPACE_NAME SEGMENT_NAME MYDATE Space used (MB) Total Object Size (MB) Percent of Total Disk Usage
------------------------------ ------------------------------ -------- --------------- ---------------------- ---------------------------
USERS ANUJ_BIG_TABLE 10/31/11 -.47 2.44 -19.34
Search This Blog
Total Pageviews
Monday 31 October 2011
Oracle SQL space advisor
SYS@rac1> connect anuj/v123
Connected.
ANUJ@rac1> create table anuj_big_table (id number, name char(200));
Table created.
ANUJ@rac1> insert into anuj_big_table select rownum,'a' from dual connect by rownum<100000;
99999 rows created.
ANUJ@rac1> commit;
Commit complete.
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
22 24 0
ANUJ@rac1> delete from anuj_big_table where mod(id,10)<>0;
90000 rows deleted.
ANUJ@rac1> commit;
Commit complete.
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
4 24 0
Change folowing
my_task_name
attr1 owner
attr2 table name
DECLARE
my_task_id NUMBER;
obj_id NUMBER;
my_task_name VARCHAR2(100);
my_task_desc VARCHAR2(500);
BEGIN
my_task_name := 'tableBIG_TABLE';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1
---------
dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc => my_task_desc);
---------
-- Step 2
---------
dbms_advisor.create_object ( task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'ANUJ',
attr2 => 'ANUJ_BIG_TABLE',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter( task_name => my_task_name, parameter => 'recommend_all',value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/
PL/SQL procedure successfully completed.
SELECT 'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f ,dba_advisor_objects o ,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e.advisor_name = 'Segment Advisor'
AND e. execution_start > sysdate - 1
ORDER BY f.task_name;
ADVICE
--------------------------------------------------------------------------------
Task Name : tableBIG_TABLE
Start Run Time : 27-sep-17 02:11
Segment Name : ANUJ_BIG_TABLE
Segment Type : TABLE
Partition Name :
Message : Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perf
orm shrink, estimated savings is 20101729 bytes.
More Info : Allocated Space:25165824: Used Space:5064095: Reclaimable Spa
ce :20101729:
------------------------------------------------------
set pagesize 200 linesize 200
SELECT
'Segment Advice --------------------------' || chr(10)||
'TABLESPACE_NAME : ' || tablespace_name || chr(10)||
'SEGMENT_OWNER : ' || segment_owner || chr(10)||
'SEGMENT_NAME : ' || segment_name || chr(10)||
'ALLOCATED_SPACE : ' || allocated_space || chr(10)||
'RECLAIMABLE_SPACE:' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations || chr(10)||
'SOLUTION 1: ' || c1|| chr(10) ||
'SOLUTION 2: ' || c2|| chr(10) ||
'SOLUTION 3: ' || c3 Advice
FROM TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));
ADVICE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Segment Advice --------------------------
TABLESPACE_NAME : USERS
SEGMENT_OWNER : ANUJ
SEGMENT_NAME : ANUJ_BIG_TABLE
ALLOCATED_SPACE : 25165824
RECLAIMABLE_SPACE:20101729
RECOMMENDATIONS : Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perform shrink, estimated savings is 20101729 bytes.
SOLUTION 1: alter table "ANUJ"."ANUJ_BIG_TABLE" shrink space
SOLUTION 2: alter table "ANUJ"."ANUJ_BIG_TABLE" shrink space COMPACT
SOLUTION 3: alter table "ANUJ"."ANUJ_BIG_TABLE" enable row movement
col BENEFIT_TYPE FOR a120
SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='tableBIG_TABLE';
BENEFIT_TYPE
------------------------------------------------------------------------------------------------------------------------
Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perform shrink, estimated savings is 20101729 bytes.
SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc;
SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE';
TASK_ID
----------
11520
SELECT
segment_owner,
segment_name,
ROUND(allocated_space/(1024*1024)) AS "Allocated (MB)",
ROUND(used_space/(1024*1024)) AS "Used (MB)",
ROUND(reclaimable_space/(1024*1024)) AS "Reclaimable (MB)",
ROUND(reclaimable_space*100/allocated_space) AS "Percentage gain"
FROM TABLE(dbms_space.asa_recommendations())
WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE');
SET pages 50
SELECT recommendations,c3,c2,c1 FROM TABLE(dbms_space.asa_recommendations()) WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE');
set linesize 200 pagesize 200
col TASK_NAME for a50
col EXECUTION_START FOR a30
col EXECUTION_END FOR a25
SELECT * FROM (SELECT task_name,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start, TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status, ROUND((execution_end-execution_start)*24*60,1) AS "Duration (min)"
FROM dba_advisor_tasks
WHERE advisor_name='Segment Advisor'
ORDER BY task_id DESC)
WHERE rownum<=10;
TASK_NAME EXECUTION_START EXECUTION_END STATUS Duration (min)
-------------------------------------------------- ------------------------------ ------------------------- ----------- --------------
tableBIG_TABLE1 27-sep-2017 02:42:46 27-sep-2017 02:42:47 COMPLETED 0
SYS_AUTO_SPCADV932072319092017 19-sep-2017 23:07:32 19-sep-2017 23:07:46 COMPLETED .2
SET pages 100 lines 200
SELECT * FROM TABLE(dbms_space.object_growth_trend('ANUJ','ANUJ_BIG_TABLE','TABLE'))
ORDER BY timepoint;
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
28-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
29-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
30-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
31-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
01-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
02-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
03-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
04-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
05-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
06-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
07-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
08-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
09-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
10-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
11-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
12-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
13-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
14-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
15-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
16-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
17-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
18-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
19-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
20-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
21-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
22-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
23-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
24-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
25-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
26-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
27-SEP-17 03.15.51.119584 AM 5064095 25165824 GOOD
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE ENABLE ROW MOVEMENT;
Table altered.
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE;
Table altered.
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE COMPACT;
Table altered.
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE CASCADE;
Table altered.
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
2 2 0
Table Size ---
with segment_rollup as (
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs ), ranked_tables as ( select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
from segment_rollup r, dba_segments s
where s.owner =r.segment_owner
and s.segment_name =r.segment_name
and r.owner=upper('&schema_name')
group by r.owner, r.table_name
)
select rank, round(blocks*8/1024) mb, table_name from ranked_tables
where 1=1
and table_name= 'ANUJ_BIG_TABLE'
-- and rank<=20;
RANK MB TABLE_NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
2 24 ANUJ_BIG_TABLE
SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc;
set pagesize 200 linesize 200
col JOB_START_TIME for a40
col JOB_DURATION for a30
select job_start_time,job_status,job_duration,job_error from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
and job_start_time > sysdate - 1
order by job_start_time;
JOB_START_TIME JOB_STATUS JOB_DURATION JOB_ERROR
---------------------------------------- ------------------------------ ------------------------------ ----------
26-SEP-17 10.00.08.476056 PM EST5EDT SUCCEEDED +000 00:00:29 0
26-SEP-17 11.03.26.726343 PM EST5EDT SUCCEEDED +000 00:00:33 0
26-SEP-17 11.14.22.829939 PM EST5EDT SUCCEEDED +000 00:00:32 0
26-SEP-17 11.23.30.956927 PM EST5EDT SUCCEEDED +000 00:00:20 0
26-SEP-17 11.24.25.594535 PM EST5EDT SUCCEEDED +000 00:00:19 0
26-SEP-17 11.33.32.428398 PM EST5EDT SUCCEEDED +000 00:00:21 0
26-SEP-17 11.34.26.762093 PM EST5EDT SUCCEEDED +000 00:00:19 0
26-SEP-17 11.43.33.801698 PM EST5EDT SUCCEEDED +000 00:00:22 0
26-SEP-17 11.44.28.435450 PM EST5EDT SUCCEEDED +000 00:00:20 0
select ss.DATAOBJ#,sn.END_INTERVAL_TIME,ss.DB_BLOCK_CHANGES_DELTA,ss.PHYSICAL_WRITES_DELTA from DBA_HIST_SEG_STAT ss,DBA_HIST_SNAPSHOT sn,DBA_HIST_SEG_STAT_OBJ so
where so.OWNER='ANUJ'
and so.OBJECT_NAME='ANUJ_BIG_TABLE'
and so.OBJECT_TYPE='TABLE'
and ss.OBJ#=so.OBJ#
and ss.DATAOBJ#=so.DATAOBJ#
and ss.snap_id=sn.snap_id
order by ss.snap_id,ss.DATAOBJ#;
DATAOBJ# END_INTERVAL_TIME DB_BLOCK_CHANGES_DELTA PHYSICAL_WRITES_DELTA
---------- --------------------------------------------------------------------------- ---------------------- ---------------------
142685 27-SEP-17 02.00.29.291 AM 115552 2962
142685 27-SEP-17 02.00.29.358 AM 115552 2962
EXEC DBMS_ADVISOR.DELETE_TASK('tableBIG_TABLE');
PL/SQL procedure successfully completed.
Oracle Tablespace space report
Tablespace space report
-- Tablespace space reportset linesize 300 pagesize 300 col tablespace_name for a25 col force_logging for a12 col bigfile for a8 col encrypted for a10 col allocation_type for a15 col PDB_NAME for a15 col "TABLESPACE_SIZE(GB)" for a20 select a.tablespace_name,a.CON_ID,nvl(PDB_NAME,'CDB$ROOT' ) PDB_NAME, round(a.used_percent, 2) as "USED _%",b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management, to_char(round((a.tablespace_size * b.block_size)/1024/1024/1024, 2)) as "TABLESPACE_SIZE(GB)", round((a.used_space * b.block_size)/1024/1024/1024, 2) as "USED_SPACE(GB)", round((a.tablespace_size * b.block_size)/1024/1024/1024, 2) - round((a.used_space * b.block_size)/1024/1024/1024, 2) Free_GB, round(a.used_percent, 2) as "USED_%" from cdb_tablespace_usage_metrics a , cdb_tablespaces b ,dba_pdbs c where a.tablespace_name = b.tablespace_name -- and a.tablespace_name like '%' -- and CONTENTS='TEMPORARY' -- and CONTENTS='UNDO' -- and CONTENTS='PERMANENT' and a.con_id=b.con_id and a.con_id=c.con_id(+) order by "USED_%" desc ;
DBA_TABLESPACE_USAGE_METRICS Returns Incorrect Information After applying 12.1.0.2.170418 (25397136) Bundle patch (Doc ID 2289448.1)
set pagesize 10000 linesize 300 tab off col tablespace_name format A22 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)" col used_mb format 999,999,990.00 heading "Used Space|(Mb)" col Free_mb format 999,999,990.00 heading "Free Space|(Mb)" col used_pct format 999 heading "Used|%" col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)" col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)" col max_used_pct format 999 heading "Max Used|(%)*" col max_used_pct1 format 999 heading "Max Used1|(%)****" col Max_Free for 999,999,990.00 heading "Max_Free|(MB)" BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb Max_Free ON REPORT select tablespace_name, status, ts_type, files, segments, Allocated_MB, Used_MB, Free_MB, Used_pct, max_ext_mb, Max_Free, Max_used_pct, --- <<<<<<<<<from dba_tablespace_usage_metrics Sometime this may be wrong due to bug (Used_MB/max_ext_mb)*100 Max_used_pct1 from ( WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics), ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt files, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB, ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB, ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, 0, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB, ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB, ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' UNION ALL -- UNDO TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB, ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Max_Free , ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 12 desc )
Tablespace report prompt - LM/DM - Local/Dictionary Managed |- SYS/UNI - SYStem/UNIform Extent Management (LM only) |ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only) set pagesize 10000 linesize 300 tab off col tablespace_name format A22 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)" col used_mb format 999,999,990.00 heading "Used Space|(Mb)" col Free_mb format 999,999,990.00 heading "Free Space|(Mb)" col used_pct format 999 heading "Used|%" col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)" col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)" col max_used_pct format 999 heading "Max Used|(%)*" col Max_Free for 999,999,990.00 heading "Max_Free|(MB)" BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb Max_Free ON REPORT WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics), ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt files, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB, ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB, ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, 0, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB, ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB, ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' UNION ALL -- UNDO TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB, ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB, ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb, ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Max_Free , ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 12 desc / Allocated Size Used Space Free Space Used Max Size Max_Free Max Used Tablespace STATUS TS Type FILES Segments (Mb) (Mb) (Mb) % (Mb) (MB) (%)* ---------------------- --------- ------------- ----- -------- --------------- --------------- --------------- ---- --------------- --------------- -------- UNDOTBS1 ONLINE UNDO-SYS-MSSM 9 307 214,771.94 212,140.88 2,631.06 99 294,911.86 82,770.98 73 R80DBFS_TS ONLINE LM-SYS-ASSM 1 6 131,072.00 130,018.19 1,053.81 99 307,200.00 177,181.81 42 R80DATA ONLINE LM-SYS-ASSM 1 44210 12,001,380.00 11,867,990.56 133,389.44 99 33,554,431.98 21,686,441.41 35 OOOETL_DATA ONLINE LM-SYS-ASSM 1 58 2,068,580.00 2,038,569.44 30,010.56 99 33,554,431.98 31,515,862.54 6 SYSAUX ONLINE LM-SYS-ASSM 1 5053 2,470.00 1,745.63 724.38 71 32,767.98 31,022.36 5 R80DATA3 ONLINE LM-SYS-ASSM 70 5136 1,570,025.78 95,078.53 1,474,947.25 6 2,293,758.91 2,198,680.38 4 SYSTEM ONLINE LM-SYS-MSSM 1 1688 32,750.00 885.25 31,864.75 3 32,767.98 31,882.73 3 R80INDEX ONLINE LM-SYS-ASSM 1 15655 338,020.00 332,294.94 5,725.06 98 33,554,431.98 33,222,137.04 1 R80ARC ONLINE LM-SYS-ASSM 4 333 2,524.00 325.13 2,198.88 13 131,071.94 130,746.81 0 USERS ONLINE LM-SYS-ASSM 1 59 81.25 4.69 76.56 6 32,767.98 32,763.30 0 TEMP_BIGFILE ONLINE TEMP-UNI-MSSM 1 0 7,605,500.00 209.00 7,605,291.00 0 33,554,431.98 33,554,222.98 0 TEMP ONLINE TEMP-UNI-MSSM 1 0 32,767.00 0.00 32,767.00 0 32,767.00 32,767.00 0 ----- -------- --------------- --------------- --------------- --------------- --------------- AVERAGE ==========> 8 6042 1,999,995.16 1,223,271.85 776,723.31 11,447,978.46 10,224,706.61 TOTAL SUM ==========> 92 72505 23,999,941.97 14,679,262.22 9,320,679.75 137,375,741.56 122,696,479.34 12 rows selected. ========================= set pause off set feed off set verify off set pagesize 55 set linesize 255 clear screen col tn format a10 heading 'Name' trunc col fn format a50 heading 'Located in file_ID + File Name' col bts format a15 heading 'Size' -- col bts format 999999999999 heading 'Size' col used format a10 heading 'Used' col ex format 9999 heading 'NrExt' col rs format 999,999,999 heading 'RBSsize' col init format 999,999 heading 'Init' col next format 999,999 heading 'Next' col mi format 999 heading 'Min' col ma format 9999999999 heading 'Max' col pct format 990 heading '%Inc' col st format a4 heading 'Stat' col sn format a15 heading 'Segm Name' col ts format a15 heading 'In Table Space' create or replace view free_view as select file_id, sum(bytes) free_bytes from sys.dba_free_space group by file_id; clear screen prompt Tablespace Datafiles select d.tablespace_name tn, f.file_id||' '||file_name fn, to_char(f.bytes/1024,'999999,999')||'K' bts, to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used from sys.dba_tablespaces d, sys.dba_data_files f, free_view s where d.tablespace_name = f.tablespace_name and f.file_id = s.file_id(+) order by d.tablespace_name; prompt prompt Tablespace definitions define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')" col gr format a30 heading 'Percent full' select d.tablespace_name tn, d.initial_extent init, d.next_extent next, d.pct_increase pct, d.min_extents mi, max_extents ma, decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st, rpad(&part1,29,' ')||'|' gr from sys.dba_tablespaces d, sys.dba_data_files f, free_view s where d.tablespace_name = f.tablespace_name and f.file_id = s.file_id order by d.tablespace_name; drop view free_view; set feed on =================================== Tablespace Datafiles Name Located in file_ID + File Name Size Used ---------- -------------------------------------------------- --------------- ---------- ANUJTEST 6 /opt/app/oracle/oradata/orcl/anujtest.dbf 10,240K 8,192K DROP1 11 /opt/app/oracle/oradata/orcl/drop.dbf 2,048K 1,024K EXAMPLE 5 /opt/app/oracle/oradata/orcl/example01.dbf 102,400K 80,640K PERFSTAT 12 /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf 1024,000K 117,760K RMAN 10 /opt/app/oracle/oradata/orcl/rman.dbf 51,200K 7,104K SYSAUX 2 /opt/app/oracle/oradata/orcl/sysaux01.dbf 849,920K 743,488K SYSTEM 1 /opt/app/oracle/oradata/orcl/system01.dbf 849,920K 832,960K TEST 9 /opt/app/oracle/oradata/orcl/test.dbf 20,480K 1,024K TSAPEXF 7 /opt/app/oracle/oradata/orcl/tsapexf01.dbf 51,200K 1,024K TSAPEXU 8 /opt/app/oracle/oradata/orcl/tsapexu01.dbf 112,640K 99,840K UNDOTBS1 3 /opt/app/oracle/oradata/orcl/undotbs01.dbf 332,800K 12,800K USERS 4 /opt/app/oracle/oradata/orcl/users01.dbf 604,160K 542,208K Tablespace definitions Name Init Next %Inc Min Max Stat Percent full ---------- -------- -------- ---- ---- ----------- ---- ------------------------------ ANUJTEST 65,536 1 2147483645 OnL |********************** | DROP1 65,536 1 2147483645 OnL |************* | EXAMPLE 65,536 1 2147483645 OnL |********************* | PERFSTAT 524,288 524,288 0 1 2147483645 OnL |** | RMAN 65,536 1 2147483645 OnL |*** | SYSAUX 65,536 1 2147483645 OnL |************************ | SYSTEM 65,536 1 2147483645 OnL |*************************** | TEST 65,536 1 2147483645 OnL | | TSAPEXF 65,536 1 2147483645 OnL | TSAPEXU 65,536 1 2147483645 OnL |************************ | UNDOTBS1 65,536 1 2147483645 OnL | | USERS 65,536 1 2147483645 OnL |************************* |
==================
for PDB
set pagesize 9999 line 9999 col TS_Name format a35 col PDBNAME format a15 col TS_Name format a35 col TYPE format a12 col LOGGING format a10 SELECT CON_ID, PDBNAME, TS#, TS_NAME, TYPE, TS_SIZE_M, FREE_SIZE_M, USED_SIZE_M, USED_PER, MAX_SIZE_G, USED_PER_MAX, BLOCK_SIZE, LOGGING, TS_DF_COUNT FROM (WITH wt1 AS (SELECT ts.CON_ID, (SELECT np.NAME FROM V$CONTAINERS np WHERE np.CON_ID = tS.con_id) PDBNAME, (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(tS.TABLESPACE_NAME) AND a.CON_ID = tS.con_id) TS#, ts.TABLESPACE_NAME, df.all_bytes, decode(df.TYPE, 'D', nvl(fs.FREESIZ, 0), 'T', df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ, df.MAXSIZ, ts.BLOCK_SIZE, ts.LOGGING, ts.FORCE_LOGGING, ts.CONTENTS, ts.EXTENT_MANAGEMENT, ts.SEGMENT_SPACE_MANAGEMENT, ts.RETENTION, ts.DEF_TAB_COMPRESSION, df.ts_df_count FROM cdb_tablespaces ts, (SELECT d.CON_ID, 'D' TYPE, TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM cdb_data_files d GROUP BY d.CON_ID, TABLESPACE_NAME UNION ALL SELECT d.CON_ID, 'T', TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) FROM cdb_temp_files d GROUP BY d.CON_ID, TABLESPACE_NAME) df, (SELECT d.CON_ID, TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM cdb_free_space d GROUP BY d.CON_ID, TABLESPACE_NAME UNION ALL SELECT d.CON_ID, tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes FROM gv$sort_usage a, cdb_tablespaces d WHERE a.tablespace = d.tablespace_name AND a.CON_ID = d.CON_ID GROUP BY d.CON_ID, tablespace_name) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.CON_ID = df.CON_ID AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+) AND ts.CON_ID = fs.CON_ID(+)) SELECT T.CON_ID, (CASE WHEN T.PDBNAME = LAG(T.PDBNAME, 1) OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN NULL ELSE T.PDBNAME END) PDBNAME, TS#, t.TABLESPACE_NAME TS_Name, CONTENTS type, round(t.all_bytes / 1024 / 1024) ts_size_M, round(t.freesiz / 1024 / 1024) Free_Size_M, round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per, round(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_Size_g, round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / MAXSIZ, 3) USED_per_MAX, round(t.BLOCK_SIZE) BLOCK_SIZE, t.LOGGING, t.ts_df_count FROM wt1 t UNION ALL SELECT DISTINCT T.CON_ID, '' PDBNAME, to_number('') TS#, 'ALL TS:' TS_Name, ' ' type, round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M, round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m, round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per, round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size, to_number('') "USED,% of MAX Size", to_number('') BLOCK_SIZE, '' LOGGING, to_number('') ts_df_count FROM wt1 t GROUP BY rollup(CON_ID,PDBNAME) ) ORDER BY CON_ID,TS# ;
====
from
https://github.com/carlos-sierra/cscripts/blob/master/cdb_tablespace_usage_metrics.sql
SET HEA ON LIN 2490 PAGES 0 TAB OFF FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF LONG 240000 LONGC 2400 SERVEROUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
SET RECSEP OFF;
--
CLEAR BREAK COMPUTE;
COL pdb_tablespace_name1 FOR A35 HEA 'PDB|TABLESPACE_NAME';
COL pdb_tablespace_name2 FOR A35 HEA 'PDB|TABLESPACE_NAME';
COL used_space_gbs1 FOR 999,990.000 HEA 'USED_SPACE|(GB)';
COL used_space_gbs2 FOR 999,990.000 HEA 'USED_SPACE|(GB)';
COL max_size_gbs1 FOR 999,990.000 HEA 'MAX_SIZE|(GB)';
COL max_size_gbs2 FOR 999,990.000 HEA 'MAX_SIZE|(GB)';
COL used_percent1 FOR 990.000 HEA 'USED|PERCENT';
COL used_percent2 FOR 990.000 HEA 'USED|PERCENT';
--
BREAK ON REPORT;
COMPUTE SUM LABEL 'TOTAL' OF used_space_gbs1 max_size_gbs1 used_space_gbs2 max_size_gbs2 ON REPORT;
--
COL output_file_name NEW_V output_file_name NOPRI;
SELECT 'cdb_tablespace_usage_metrics_'||LOWER(name)||'_'||LOWER(REPLACE(SUBSTR(host_name, 1 + INSTR(host_name, '.', 1, 2), 30), '.', '_'))||'_'||TO_CHAR(SYSDATE, 'yyyymmdd"T"hh24miss') output_file_name FROM v$database, v$instance;
--
WITH
t AS (
SELECT c.name||'('||c.con_id||')' pdb,
m.tablespace_name,
ROUND(m.used_percent, 3) used_percent, -- as per maximum size (considering auto extend)
ROUND(m.used_space * t.block_size / POWER(10, 9), 3) used_space_gbs,
ROUND(m.tablespace_size * t.block_size / POWER(10, 9), 3) max_size_gbs,
ROW_NUMBER() OVER (ORDER BY c.name, m.tablespace_name) row_number1,
ROW_NUMBER() OVER (ORDER BY m.used_percent DESC, m.used_space * t.block_size DESC, m.tablespace_size * t.block_size DESC) row_number2
FROM cdb_tablespace_usage_metrics m,
cdb_tablespaces t,
v$containers c
WHERE t.con_id = m.con_id
AND t.tablespace_name = m.tablespace_name
AND t.status = 'ONLINE'
AND t.contents = 'PERMANENT'
-- AND t.tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND c.con_id = m.con_id
AND c.open_mode = 'READ WRITE'
)
SELECT t1.pdb||CHR(10)||' '||
t1.tablespace_name pdb_tablespace_name1,
t1.used_percent used_percent1,
t1.used_space_gbs used_space_gbs1,
t1.max_size_gbs max_size_gbs1,
'|'||CHR(10)||'|' "|",
t2.used_percent used_percent2,
t2.used_space_gbs used_space_gbs2,
t2.max_size_gbs max_size_gbs2,
t2.pdb||CHR(10)||' '||
t2.tablespace_name pdb_tablespace_name2
FROM t t1, t t2
WHERE t1.row_number1 = t2.row_number2
ORDER BY
t1.row_number1
/
Oracle database check script
Database_check2.sql
--- Author: Francisco Munoz Alvarez
--- Date : 20/07/2008
--- Script: Checklist v.1.3
COLUMN y new_value sid NOPRINT
SELECT name||'_'||TO_CHAR(sysdate, 'ddmonyy_hh24mi') y FROM v$database;
SPOOL checklist_&sid..txt
alter system checkpoint;
alter system check datafiles;
Set Linesize 200
Set Pagesize 45
Set SQLPROMPT 'Sql>'
Set Desc Linenum On
Set Arraysize 1
Set Long 2000
Set Serveroutput On size 800000 ;
Set Heading Off
Set Feedback Off
Set Verify Off
Column Var_Date new_value Var_Date noprint
Select To_Char(Sysdate, 'DD-MM-YYYY HH24:MI') Var_Date from v$database ;
-- Prompt ---
Select
' ****** &Var_Date ************** Base : ' || Name || ' ************** '
from
v$database;
Set Heading On
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column username heading "Utilis." format A20
column terminal heading "Terminal" format A11
column program heading "Programme" format A27 word_wrapped
select s.sid, p.spid, substr(s.username,1,20) username, s.terminal, p.Program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = (select sid from v$mystat where rownum=1)
;
Set Heading Off
Set Termout Off
Column Var_Prompt new_value Var_Prompt noprint
Select ''''||Rpad(Lpad(Initcap(Substr(Name,1,4)),3,'*'),4,'*')||'>''' Var_Prompt from v$database ;
Set SQLPROMPT &Var_Prompt
Set Termout On
Prompt
-- Prompt ---
Set Heading On
Set Feedback On
-- prompt
prompt
prompt Oracle Instance Information
prompt
Set Heading Off
Set Feedback Off
Set Verify Off
column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(Select ' Host_Name '||Lpad(Host_Name,18) status_02 from V$Instance)
, (Select ' Cpu_Count '||Lpad(value,8) status_01 from V$PARAMETER where name='cpu_count' and value is not null)
Union
Select status_01||' | '||status_02 status
From
(Select ' Instance_Name '||Lpad(Instance_Name,12) Status_01 from V$Instance)
, (Select ' Database_Status '||Lpad(Database_Status,12) Status_02 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Startup_Time '||To_Char(Startup_Time, 'DD-MM-YYYY HH24:MI') Status_02 from V$Instance)
, (Select ' Status '||Lpad(Status,12) Status_01 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Version '||Lpad(Version,12) Status_01 from V$Instance)
, (Select ' Instance_Role '||Lpad(Instance_Role,16) Status_02 from V$Instance)
;
select ' Database log mode '||log_mode "Parameter" from V$DATABASE
union
select ' Archive destination '||value from V$PARAMETER where (name='log_archive_dest' or name = 'log_archive_dest_1') and value is not null
;
select ' Spfile '||value from V$PARAMETER where name='spfile' and value is not null
union
select ' Background Dump Dest '||value from V$PARAMETER where name='background_dump_dest' and value is not null
;
col OWNER format a15
col SEGMENT format a20
col PARTITION format a12
prompt Check Redo Size
select distinct ' Redo size (Kb) '|| Lpad(To_Char(round(bytes/1024)),'16') Status from v$log;
Prompt
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_SGA Is
select ' SGA (Mb) '||Lpad(To_Char(Round(sum (value)/1024/1024)),8) status_02 from v$sga;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(40);
--
X Varchar2(100);
Nb_Tf Number(8);
SGA Varchar2(40);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_SGA;
Fetch Cur_SGA Into SGA;
Close Cur_SGA;
--
If X Is Not Null Then
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_temp_files) dtft_size';
--
Else
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
--
End If;
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 40);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line('-- '||W_Temp||' | '||SGA||' --');
--
Dbms_Sql.Close_Cursor(Curs);
--
End;
/
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_Df Is
Select Count(*)
From dba_data_files
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Df;
Fetch Cur_Df Into Nb_Df;
Close Cur_Df;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From dba_temp_files';
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5)||' | Nb. Tempfiles '||Lpad(W_Temp,5)||' --' );
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5));
End If;
--
End;
/
prompt
prompt
prompt
prompt Instance CheckList
prompt
Select status_01||' | '||status_02 status
From
(Select ' Instance Status '||Lpad('OK',12) Status_01 from V$Instance)
, (Select ' Listener Status '||Lpad('OK',12) Status_02 from V$Instance);
prompt
prompt
prompt
prompt Performance Memory CheckList
prompt
prompt
declare
cursor c1 is
select count(*)
from v$session
where serial# != 1
and osuser is not null;
---
cursor c2 is
select count(*)
from v$session
where SERIAL# != 1
and osuser is not null
and status='ACTIVE';
---
cursor c3 is
select ((1-a.value/(b.value+c.value))*100)
from v$sysstat a, v$sysstat b , v$sysstat c
where a.name = 'physical reads'
and b.name = 'db block gets'
and c.name = 'consistent gets';
---
cursor c4 is
select ((sum(pins)/(sum(pins)+sum(reloads))*100))
from v$librarycache;
---
cursor c5 is
select ((1-(sum(getmisses)/sum(gets)))*100)
from v$rowcache;
---
cursor c6 is
select (count(*)/24)
from sys.v_$log_history
where first_time > sysdate - 1;
---
cursor c7 is
select count(*)
From dba_jobs Where broken != 'N';
---
cursor c8 is
select count(*)
From v$shared_pool_reserved where request_failures != 0;
---
cursor c9 is
select round(100*retries.value/entries.value,4)
from gv$sysstat retries, gv$sysstat entries
where retries.name = 'redo buffer allocation retries' AND
entries.name = 'redo entries' and
entries.inst_id = retries.inst_id;
---
cursor c10 is
select nvl(total_waits,0)
from gv$system_event
where event like '%log buffer space%';
---
cursor c11 is
select round(100*SUM(getmisses)/SUM(gets),3)
from gv$rowcache;
---
A Number(8);
B Number(8);
C Number(8);
D Number(8);
E Number(8);
F Number(8);
G Number(8);
H Number(8);
I Number(8,4);
J Number(8);
K Number(8,3);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(5);
FF Varchar2(5);
GG Varchar2(5);
HH Varchar2(5);
II Varchar2(5);
JJ Varchar2(5);
KK Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into E;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
Open c7;
Fetch c7 Into G;
Close c7;
Open c8;
Fetch c8 Into H;
Close c8;
Open c9;
Fetch c9 Into I;
Close c9;
Open c10;
Fetch c10 Into J;
Close c10;
Open c11;
Fetch c11 Into K;
Close c11;
if A <= 700 then
AA := 'OK';
else
AA := 'NO';
end if;
if B <= 15 then
BB := 'OK';
else
BB := 'NO';
end if;
if C >= 80 then
CC := 'OK';
else
CC := 'NO';
end if;
if D >= 99 then
DD := 'OK';
else
DD := 'NO';
end if;
if E >= 80 then
EE := 'OK';
else
EE := 'NO';
end if;
if F <= 5 then
FF := 'OK';
else
FF := 'NO';
end if;
if G = 0 then
GG := 'OK';
else
GG := 'NO';
end if;
if H = 0 then
HH := 'OK';
else
HH := 'NO';
end if;
if I <= 0.0010 then
II := 'OK';
else
II := 'NO';
end if;
if J = 0 then
JJ := 'OK';
elsif J is null then
JJ := 'OK';
J := 0;
else
JJ := 'NO';
end if;
if K <= 0.015 then
KK := 'OK';
else
KK := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Total Sessions < 700 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Active sessions number <15 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Data Buffer Hit Ratio > 80 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'L.Buffer Reload Pin Ratio > 99'||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
dbms_OutPut.Put_Line('-- '||'Row Cache Miss Ratio < 0.015 '||Lpad(KK,5)||' - '||Lpad(To_Char(K),5));
dbms_OutPut.Put_Line('-- '||'Dict.Buffer Hit Ratio > 80 '||Lpad(EE,5)||' - '||Lpad(To_Char(E),5));
dbms_OutPut.Put_Line('-- '||'Log Buffer Waits = 0 '||Lpad(JJ,5)||' - '||Lpad(To_Char(J),5));
dbms_OutPut.Put_Line('-- '||'Log Buffer Retries < 0.0010 '||Lpad(II,5)||' - '||Lpad(To_Char(I),5));
dbms_OutPut.Put_Line('-- '||'Switch number (Daily Avg) < 5 '||Lpad(FF,5)||' - '||Lpad(To_Char(F),5));
dbms_OutPut.Put_Line('-- '||'Jobs Broken = 0 '||Lpad(GG,5)||' - '||Lpad(To_Char(G),5));
dbms_OutPut.Put_Line('-- '||'Shared_Pool Failure = 0 '||Lpad(HH,5)||' - '||Lpad(To_Char(H),5));
end;
/
prompt
prompt
prompt Storage CheckList
prompt
prompt
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' V$Datafile Status '||Lpad('OK',12)
, 'SYSTEM', ' V$Datafile Status '||Lpad('OK',12)
, ' V$Datafile Status '||Lpad('NO',12)
) STATUS_01 from v$datafile)
, (select distinct
decode (status
, 'ONLINE', ' V$Tempfile Status '||Lpad('OK',14)
, 'SYSTEM', ' V$Tempfile Status '||Lpad('OK',14)
, ' V$Tempfile Status '||Lpad('NO',14)
) STATUS_02 from v$tempfile)
Union
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' Dba_Tablespaces Status '||Lpad('OK',7)
, 'READ ONLY', ' Dba_Tablespaces Status '||Lpad('OK',7)
, ' Dba_Tablespaces Status '||Lpad('NO',7)
) STATUS_01 from dba_tablespaces)
, (select distinct
decode (status
, 'CURRENT', ' V$Log Status '||Lpad('OK',19)
, 'ACTIVE', ' V$Log Status '||Lpad('OK',19)
, 'INACTIVE', ' V$Log Status '||Lpad('OK',19)
, ' V$Log Status '||Lpad('NO',19)
) STATUS_02 from v$log)
union
Select status_01||' | '||status_02 status
From
(select
distinct
decode (count(1)
, 0, ' V$Recover_File '||Lpad('OK',15)
, ' V$Recover_File '||Lpad('NO',15)
) STATUS_01 from v$recover_file)
, (select
distinct
decode (count(1)
, 0, ' V$Recovery_Log '||Lpad('OK',17)
, ' V$Recovery_Log '||Lpad('NO',17)
) STATUS_02 from v$recovery_log)
;
prompt
declare
cursor c1 is
select count(*)
from v$backup where status != 'NOT ACTIVE';
---
cursor c2 is
select count(*)
from ( select sum (bytes)/1048576 free, max (bytes)/1048576 fragmax, tablespace_name from sys.dba_free_space group by tablespace_name ) fsp, ( select sum(bytes)/1048576 alloc, tablespace_name from sys.dba_data_files group by tablespace_name) df, dba_tablespaces dt where fsp.tablespace_name (+) = df.tablespace_name and df.tablespace_name = dt.tablespace_name and dt.status = 'ONLINE' and (((alloc - nvl (free, 0)) / alloc) * 100 > 95);
---
cursor c3 is
select count(*)
From dba_objects where status != 'VALID'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
cursor c4 is
select count(*)
from dba_indexes where status = 'UNUSABLE';
---
cursor c5 is
select count(*)
from dba_triggers where status != 'ENABLED'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
cursor c6 is
select count(*)
From dba_constraints where status != 'ENABLED'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
A Number(8);
B Number(8);
C Number(8);
D Number(8);
E Number(8);
F Number(8);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(5);
FF Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into E;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
if A = 0 then
AA := 'OK';
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
else
BB := 'NO';
end if;
if C = 0 then
CC := 'OK';
else
CC := 'NO';
end if;
if D = 0 then
DD := 'OK';
else
DD := 'NO';
end if;
if E = 0 then
EE := 'OK';
else
EE := 'NO';
end if;
if F = 0 then
FF := 'OK';
else
FF := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Tablespace in Backup Mode = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Tablespace < 95% '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Objects Invalid = 0 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'Indexes unusable = 0 '||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
dbms_OutPut.Put_Line('-- '||'Trigger Disabled = 0 '||Lpad(EE,5)||' - '||Lpad(To_Char(E),5));
dbms_OutPut.Put_Line('-- '||'Constraint Disabled = 0 '||Lpad(FF,5)||' - '||Lpad(To_Char(F),5));
end;
/
declare
cursor c1 is
select count(*)
from dba_segments
where max_extents-extents < 10 AND
segment_type <> 'CACHE';
---
cursor c2 is
select count(*)
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%'
and next_extent * 2 > (
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name);
---
cursor c3 is
select count(*)
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP')
and tablespace_name = 'SYSTEM';
---
cursor c4 is
select count(*)
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columnsx
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and b.owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP')
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columnsx
from dba_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columnsx (+) like a.columnsx || '%'
and b.table_name is null;
A Number(8);
B Number(8);
C Number(8);
D Number(8);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
if A = 0 then
AA := 'OK';
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
else
BB := 'NO';
end if;
if C = 0 then
CC := 'OK';
else
CC := 'NO';
end if;
if D = 0 then
DD := 'OK';
else
DD := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Objects close max extents = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Objects can not extent = 0 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'User Objects on Systems = 0 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'FK Without Index = 0 '||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
end;
/
prompt
prompt
prompt Datagard CheckList
prompt
declare
cursor c1 is
select count(*)
FROM gv$dataguard_status
WHERE severity IN ('Error','Fatal','Warning');
---
cursor c2 is
select count(*)
FROM v$archive_gap;
---
cursor c3 is
select max(sequence#) from v$archived_log where applied = 'YES';
---
cursor c4 is
select max(sequence#) from v$archived_log;
---
cursor c5 is
select value from v$parameter where name = 'log_archive_dest_2';
---
cursor c6 is
select to_number(substr(version,1,3)) from v$instance;
---
A Number(8);
B Number(8);
C Number(10);
D Number(10);
E Number(10);
F Number(8,2);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(50);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into EE;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
if A = 0 then
AA := 'OK';
elsif A is null then
AA := 'OK';
A := 0;
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
elsif B is null then
BB := 'OK';
B := 0;
else
BB := 'NO';
end if;
if D is null then
D := 0;
end if;
if C is null then
C := 0;
end if;
E := ( D - C);
if E <= 5 then
CC := 'OK';
else
CC := 'NO';
end if;
if EE is not null and F >= 9 then
dbms_OutPut.Put_Line('-- '||'Datagard Errors = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Datagard Gap = 0 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Archives not Aplied < 5 '||Lpad(CC,5)||' - '||Lpad(To_Char(E),5));
else
dbms_OutPut.Put_Line('-- '||'No Datagard Available or Database version is not supported for this check!');
end if;
end;
/
prompt
prompt
prompt Installed options :
prompt
select ' - ' || parameter || ' option'
from sys.v_$option
where value = 'TRUE'
;
set head on
Prompt
set feedback on
prompt
prompt V$Recovery_Log Status
prompt
column Thread# format 9999999 heading "Thread"
column Sequence# format 9999999 heading "Sequence"
column archive_name format A60 wrap heading "Archive|Name"
column Time heading "Time"
select *
from v$recovery_log
order by thread#
;
prompt
prompt V$Recover_File Status
prompt
column file# format 9999999 heading "File#"
column online format A10 heading "Online"
column online_status format A10 heading "Online|Status"
column error format A20 wrap heading "Error"
column change format 99999 heading "Change"
column Time heading "Time"
select * from v$recover_file
order by file# ;
prompt
prompt V$Log Status
prompt
Declare
--
Cursor Cur_Req Is
select 'X'
from v$log
where status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Log Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on redogroup -
skip 1
column redogroup format 99999 heading "Group"
column redothread format 99999 heading "Thread"
column redosequence format 999999 heading "Sequence"
column file_name format A55 heading "RedoLog Name"
column KB format 99999999B heading "Size|(Kb)"
column status format A5 heading "Stat." Trunc
select
lf.group# redogroup
, l.thread# redothread
, l.sequence# redosequence
, lf.member file_name
, l.bytes/1024 Kb
, l.status status
from
v$logfile lf
, v$log l
where
l.group# = lf.group#
and
l.status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
order by
lf.group#
, l.thread#
, l.sequence#
, lf.member
;
clear breaks
prompt
prompt V$Tempfile Status
prompt
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$tempfile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Tempfile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on status skip 1
column name format a60 wrap heading "DataFile Name"
select
name
, status
from V$tempfile
where status not in ('ONLINE', 'SYSTEM')
Order By Status , Name
;
clear breaks
prompt
prompt V$Datafile Status
prompt
Declare
--
Cursor Cur_Req Is
select
'X'
from v$datafile
where status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Datafile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on status skip 1
column name format a60 wrap heading "DataFile Name"
select
name
, status
from V$datafile
where status not in ('ONLINE', 'SYSTEM')
Order By Status , Name
;
clear breaks
prompt -----------------------------
prompt Dba_Tablespaces Status
prompt ---------------------------
Declare
--
Cursor Cur_Req Is
select
'X'
from dba_tablespaces
where status not in ('ONLINE', 'READ ONLY')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Dba_Tablespaces Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column tblsp format a20 wrap heading "Tablespace Name"
column contents format a10 heading "Content"
clear breaks
break on contents -
skip 1
select
contents
, tablespace_name tblsp
, status
from dba_tablespaces
where status not in ('ONLINE', 'READ ONLY')
order by contents
;
prompt
prompt Tablespaces Backup Mode
prompt
Declare
--
Cursor Cur_Req Is
select 'X'
from v$backup bck
, dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database);
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Tablespaces in Backup Mode ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column tablespace_name format a18 wrap heading "Tablespace Name"
column file_name format a50 wrap heading "DataFile Name"
column status format a12 wrap heading "Status"
column change# format 999999999999 heading "Change"
column time format a18 wrap heading "Time"
select
df.tablespace_name
, df.file_name
, bck.status
, bck.change#
, To_Char(bck.time,'DD-MM-YYYY HH24:MI') time
from
v$backup bck
, dba_data_files df
where
bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database)
order by df.tablespace_name, df.file_name;
prompt
prompt Lock list
prompt
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where
s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Lock list ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column username heading "Utilis." format A15
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column state heading "Etat" format A15
column Lmode_H heading "Lock mode" format A15
column terminal heading "Terminal" format A10
column command heading "C" format 99
Column serial# heading "Serial#" format 99999
column ctime heading "Duration" format 999999
column logon heading "Date Connexion" format A16
column object_name heading "Object Name" format A18
select
s.sid
, s.serial#
, p.spid
, substr(s.username,1,15) username
, s.terminal
, l.type
, to_char(s.logon_time,'DD-MM-YYYY HH24:MI') logon
, decode(l.lmode, 1,'null' , 2,'Row share'
, 3,'Row Exclusive' , 4,'Share'
, 5,'Share Row Excl.' , 6,'Exclusive') Lmode_H
, o.object_name
, Round(ctime/60) ctime
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where
s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
prompt
prompt
prompt Tablespaces
prompt
Set Heading Off
Set Termout Off
Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint
Select value Var_DB_BLOCK_SIZE
from v$parameter
where Upper(name) = Upper ('db_block_size')
;
Set Termout On
Set Heading On
clear breaks
break on contents -
skip 1
compute Sum of alloc used free nbfrag on contents
column tblsp format a20 wrap heading "Tablespace Name"
column Alloc format 999,999 heading "Alloc|(Mb)"
column Free format 999,999 heading "Free|(Mb)"
column used format 999,999 heading "Used|(Mb)"
column pused format 990.9 heading "%|Used|Space"
column fragmax format 99,999.9 heading "Largest|Free|Ext.(Mb)"
column nbfrag format 99999 heading "Nb|frag"
column contents format a10 heading "Content"
column pct_ext_coal format 999 heading "% Ext.|Coal."
column ext_manage format a7 wrap heading "Ext.|M."
column autoext format a7 wrap heading "Auto|Ext."
select
contents
, nvl (dt.tablespace_name, nvl (fsp.tablespace_name, 'Unknown')) tblsp
, alloc
, alloc - nvl (free, 0) Used
, nvl (free, 0) Free
, ((alloc - nvl (free, 0)) / alloc) * 100 pused
, nbfrag
, fragmax
, dfsc.pct_ext_coal pct_ext_coal
, dt.ext_manage
, df.inc autoext
from
( select sum (bytes)/1048576 free
, max (bytes)/1048576 fragmax
, tablespace_name
, count(*) nbfrag
from sys.dba_free_space
group by tablespace_name
) fsp
, ( select sum(bytes)/1048576 alloc
, tablespace_name
, Decode(((inc * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes') inc
from sys.dba_data_files sddf
, sys.filext$ aut
where sddf.file_id = aut.file# (+)
group by tablespace_name
, Decode(((inc * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes')
Union
select sum(bytes)/1048576 alloc
, tablespace_name
, Decode(((increment_by * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes') inc
from sys.dba_temp_files sddf
group by tablespace_name
, Decode(((increment_by * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes')
) df
, ( select contents
, tablespace_name
, initial_extent/1024 initial_ext
, next_extent/1024 next_ext
, pct_increase
, max_extents
, min_extents
, Substr(extent_management,1,5) ext_manage
from dba_tablespaces
) dt
, ( select percent_extents_coalesced pct_ext_coal
, tablespace_name
from dba_free_space_coalesced
) dfsc
where fsp.tablespace_name (+) = dt.tablespace_name
and df.tablespace_name (+) = dt.tablespace_name
and dfsc.tablespace_name (+) = dt.tablespace_name
order by contents , pused desc
;
Prompt
PROMPT
PROMPT ******************************************** INVALID OBJECTS BY TYPE
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL of quantity ON REPORT
SELECT object_type, COUNT(*) quantity
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY object_type;
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT ******************************************** INVALID OBJECTS BY TYPE AND OWNER
SELECT owner, object_type, COUNT(*) quantity
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;
PROMPT
PROMPT ******************************************** MODIFIED OBJECTS IN THE LAST 7 DAYS
col objet format a40
SELECT owner||'.'||object_name objet, object_type, created, last_ddl_time modified, ROUND(sysdate - last_ddl_time,2) days
FROM dba_objects
WHERE sysdate - last_ddl_time < 7 AND
subobject_name IS NULL
and object_name not like 'WRH$%'
ORDER BY created DESC;
PROMPT
PROMPT ******************************************** UNUSABLE INDEXES
SELECT owner||'.'||index_name indice FROM dba_indexes
WHERE status = 'UNUSABLE'
ORDER BY 1;
col partition format a45
SELECT index_owner||'.'||index_name||'.'||partition_name partition
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
ORDER BY 1;
PROMPT
PROMPT ******************************************** DISABLED TRIGGERS
col owntab format a45
col triggering_event format a20
SELECT owner||'.'||trigger_name "TRIGGER", triggering_event, table_owner||'.'||table_name owntab , status
FROM dba_triggers
WHERE status = 'DISABLED';
PROMPT
PROMPT ******************************************** DISABLED CONSTRAINTS
col owntab format a45
SELECT constraint_name constraint, constraint_type, owner||'.'||table_name owntab , status
FROM dba_constraints
WHERE status = 'DISABLED';
-- PROMPT
-- PROMPT ******************************************** ANALYZED TABLES
-- SELECT owner, NVL(TO_CHAR(TRUNC(last_analyzed),'dd-mm-yyyy'), 'Never Analyzed') last_analyzed, COUNT(*) quantity
-- FROM dba_tables
-- GROUP BY owner, TRUNC(last_analyzed)
-- ORDER BY 1,2;
-- PROMPT
-- PROMPT ******************************************** ANALYZED INDEXES
-- SELECT owner, NVL(TO_CHAR(TRUNC(last_analyzed),'dd-mm-yyyy'), 'Never Analyzed') last_analyzed, COUNT(*) quantity
-- FROM dba_indexes
-- GROUP BY owner, TRUNC(last_analyzed)
-- ORDER BY 1,2;
PROMPT
PROMPT ******************************************** TABLESPACES WITH LESS THAN 15% FREE
SELECT a.tblspc tablespace, a.fbytes TOTAL, NVL(u.ebytes,0) RESERVED, a.fbytes-NVL(u.ebytes,0) FREE, 100*NVL(u.ebytes,0)/a.fbytes "% RESERVED"
FROM (SELECT tablespace_name tblspc, SUM(bytes)/1024/1024 ebytes FROM dba_segments GROUP BY tablespace_name) u,
(SELECT tablespace_name tblspc, SUM(bytes)/1024/1024 fbytes FROM dba_data_files GROUP BY tablespace_name) a
WHERE u.tblspc(+) = a.tblspc AND
100*NVL(u.ebytes,0)/a.fbytes > 85.00
ORDER BY tablespace;
PROMPT
PROMPT ******************************************** OBJECTS CLOSE TO MAX EXTENTS
SELECT tablespace_name tablespace, segment_type tipo, owner||'.'||segment_name segment, extents, bytes/1024/1024 mb,
max_extents
FROM dba_segments
WHERE max_extents-extents < 10 AND
segment_type <> 'CACHE'
ORDER BY 1,2,3,4;
PROMPT
PROMPT ******************************************** OBJECTS THAT CAN.T EXTENT
prompt Objects that cannot extend (no space in TS)
column Sname form a40 heading 'Object Name'
column Stype form a15 heading 'Type'
column Size form 9,999 heading 'Size'
column Next form 99,999 heading 'Next'
column Tname form a15 heading 'TsName'
select a.owner||'.'||a.segment_name "Sname",
a.segment_type "Stype",
a.bytes/1024/1024 "Size",
a.next_extent/1024/1024 "Next",
a.tablespace_name "TName"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 2 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc
/
PROMPT
PROMPT ******************************************** OBJECTS IN THE SYSTEM TABLESPACE
select owner, substr(segment_name,1,25) segment , substr(partition_name,1,25) partition, segment_type
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';
PROMPT
PROMPT ******************************************** DATAGUARD STATE (CRITICAL MESSAGES)
SELECT inst_id, facility, severity, dest_id, error_code, callout, timestamp, message
FROM gv$dataguard_status
WHERE severity IN ('Error','Fatal','Warning')
ORDER BY timestamp;
--PROMPT
--PROMPT ******************************************** NO APLIED ARCHIVELOGS
--selecT sequence#,substr(a.name,1,46) archivelog, a.blocks, a.archived, a.applied, a.status, a.end_of_redo eor, a.standby_dest, a.first_time, a.completion_time completion, SYSDATE time
--From v$archived_log a
--WHERE a.sequence# between (select max(b.sequence#)
--from v$archived_log b where b.applied = 'YES') and (select max(c.sequence#) from v$archived_log c)
--/
PROMPT
PROMPT ******************************************** GAP SEQUENCE
SELECT * FROM v$archive_gap;
PROMPT
PROMPT ******************************************** DIFERENCE ON RECEPTION
SELECT thread#, MAX(next_time) next_time, SYSDATE time, ROUND((SYSDATE - MAX(next_time))*24,2) horas, ROUND((SYSDATE - MAX(next_time))*24*60,0) minutos
FROM v$archived_log
GROUP BY thread#;
PROMPT
PROMPT ******************************************** DIFERENCE TO APPLY
SELECT thread#, MAX(next_time) next_time, SYSDATE time, ROUND((SYSDATE - MAX(next_time))*24,2) horas, ROUND((SYSDATE - MAX(next_time))*24*60,0) minutos
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#;
PROMPT
PROMPT ******************************************** PROCESSES
col "group#" format a10
SELECT inst_id, process, pid, status, substr(to_char(group#),1,8) group#, thread#,
sequence#, block#, blocks, delay_mins, known_agents, active_agents, client_process
FROM gv$managed_standby;
PROMPT
set heading on
set echo off
set linesize 150
set pagesize 500
column day format a16 heading 'Day'
column d_0 format a3 heading '00'
column d_1 format a3 heading '01'
column d_2 format a3 heading '02'
column d_3 format a3 heading '03'
column d_4 format a3 heading '04'
column d_5 format a3 heading '05'
column d_6 format a3 heading '06'
column d_7 format a3 heading '07'
column d_8 format a3 heading '08'
column d_9 format a3 heading '09'
column d_10 format a3 heading '10'
column d_11 format a3 heading '11'
column d_12 format a3 heading '12'
column d_13 format a3 heading '13'
column d_14 format a3 heading '14'
column d_15 format a3 heading '15'
column d_16 format a3 heading '16'
column d_17 format a3 heading '17'
column d_18 format a3 heading '18'
column d_19 format a3 heading '19'
column d_20 format a3 heading '20'
column d_21 format a3 heading '21'
column d_22 format a3 heading '22'
column d_23 format a3 heading '23'
column Total format 9999
column status format a8
column member format a40
column archived heading 'Archived' format a8
column bytes heading 'Bytes|(MB)' format 9999
col group# format 999
Ttitle 'Log Info' skip 2
select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type
from v$log l, v$logfile f
where l.group# = f.group#
/
Ttitle off
prompt =========================================================================================================================
Ttitle 'Log Switch on hour basis' skip 2
select to_char(FIRST_TIME,'DY, DD-MON-YYYY') day,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0))) d_21,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0))) d_22,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0))) d_23,
count(trunc(FIRST_TIME)) Total
from v$log_history
group by to_char(FIRST_TIME,'DY, DD-MON-YYYY')
order by to_date(substr(to_char(FIRST_TIME,'DY, DD-MON-YYYY'),5,15) )
/
Ttitle off
prompt
spool off
--- Author: Francisco Munoz Alvarez
--- Date : 20/07/2008
--- Script: Checklist v.1.3
COLUMN y new_value sid NOPRINT
SELECT name||'_'||TO_CHAR(sysdate, 'ddmonyy_hh24mi') y FROM v$database;
SPOOL checklist_&sid..txt
alter system checkpoint;
alter system check datafiles;
Set Linesize 200
Set Pagesize 45
Set SQLPROMPT 'Sql>'
Set Desc Linenum On
Set Arraysize 1
Set Long 2000
Set Serveroutput On size 800000 ;
Set Heading Off
Set Feedback Off
Set Verify Off
Column Var_Date new_value Var_Date noprint
Select To_Char(Sysdate, 'DD-MM-YYYY HH24:MI') Var_Date from v$database ;
-- Prompt ---
Select
' ****** &Var_Date ************** Base : ' || Name || ' ************** '
from
v$database;
Set Heading On
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column username heading "Utilis." format A20
column terminal heading "Terminal" format A11
column program heading "Programme" format A27 word_wrapped
select s.sid, p.spid, substr(s.username,1,20) username, s.terminal, p.Program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = (select sid from v$mystat where rownum=1)
;
Set Heading Off
Set Termout Off
Column Var_Prompt new_value Var_Prompt noprint
Select ''''||Rpad(Lpad(Initcap(Substr(Name,1,4)),3,'*'),4,'*')||'>''' Var_Prompt from v$database ;
Set SQLPROMPT &Var_Prompt
Set Termout On
Prompt
-- Prompt ---
Set Heading On
Set Feedback On
-- prompt
prompt
prompt Oracle Instance Information
prompt
Set Heading Off
Set Feedback Off
Set Verify Off
column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(Select ' Host_Name '||Lpad(Host_Name,18) status_02 from V$Instance)
, (Select ' Cpu_Count '||Lpad(value,8) status_01 from V$PARAMETER where name='cpu_count' and value is not null)
Union
Select status_01||' | '||status_02 status
From
(Select ' Instance_Name '||Lpad(Instance_Name,12) Status_01 from V$Instance)
, (Select ' Database_Status '||Lpad(Database_Status,12) Status_02 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Startup_Time '||To_Char(Startup_Time, 'DD-MM-YYYY HH24:MI') Status_02 from V$Instance)
, (Select ' Status '||Lpad(Status,12) Status_01 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Version '||Lpad(Version,12) Status_01 from V$Instance)
, (Select ' Instance_Role '||Lpad(Instance_Role,16) Status_02 from V$Instance)
;
select ' Database log mode '||log_mode "Parameter" from V$DATABASE
union
select ' Archive destination '||value from V$PARAMETER where (name='log_archive_dest' or name = 'log_archive_dest_1') and value is not null
;
select ' Spfile '||value from V$PARAMETER where name='spfile' and value is not null
union
select ' Background Dump Dest '||value from V$PARAMETER where name='background_dump_dest' and value is not null
;
col OWNER format a15
col SEGMENT format a20
col PARTITION format a12
prompt Check Redo Size
select distinct ' Redo size (Kb) '|| Lpad(To_Char(round(bytes/1024)),'16') Status from v$log;
Prompt
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_SGA Is
select ' SGA (Mb) '||Lpad(To_Char(Round(sum (value)/1024/1024)),8) status_02 from v$sga;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(40);
--
X Varchar2(100);
Nb_Tf Number(8);
SGA Varchar2(40);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_SGA;
Fetch Cur_SGA Into SGA;
Close Cur_SGA;
--
If X Is Not Null Then
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_temp_files) dtft_size';
--
Else
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
--
End If;
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 40);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line('-- '||W_Temp||' | '||SGA||' --');
--
Dbms_Sql.Close_Cursor(Curs);
--
End;
/
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_Df Is
Select Count(*)
From dba_data_files
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Df;
Fetch Cur_Df Into Nb_Df;
Close Cur_Df;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From dba_temp_files';
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5)||' | Nb. Tempfiles '||Lpad(W_Temp,5)||' --' );
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5));
End If;
--
End;
/
prompt
prompt
prompt
prompt Instance CheckList
prompt
Select status_01||' | '||status_02 status
From
(Select ' Instance Status '||Lpad('OK',12) Status_01 from V$Instance)
, (Select ' Listener Status '||Lpad('OK',12) Status_02 from V$Instance);
prompt
prompt
prompt
prompt Performance Memory CheckList
prompt
prompt
declare
cursor c1 is
select count(*)
from v$session
where serial# != 1
and osuser is not null;
---
cursor c2 is
select count(*)
from v$session
where SERIAL# != 1
and osuser is not null
and status='ACTIVE';
---
cursor c3 is
select ((1-a.value/(b.value+c.value))*100)
from v$sysstat a, v$sysstat b , v$sysstat c
where a.name = 'physical reads'
and b.name = 'db block gets'
and c.name = 'consistent gets';
---
cursor c4 is
select ((sum(pins)/(sum(pins)+sum(reloads))*100))
from v$librarycache;
---
cursor c5 is
select ((1-(sum(getmisses)/sum(gets)))*100)
from v$rowcache;
---
cursor c6 is
select (count(*)/24)
from sys.v_$log_history
where first_time > sysdate - 1;
---
cursor c7 is
select count(*)
From dba_jobs Where broken != 'N';
---
cursor c8 is
select count(*)
From v$shared_pool_reserved where request_failures != 0;
---
cursor c9 is
select round(100*retries.value/entries.value,4)
from gv$sysstat retries, gv$sysstat entries
where retries.name = 'redo buffer allocation retries' AND
entries.name = 'redo entries' and
entries.inst_id = retries.inst_id;
---
cursor c10 is
select nvl(total_waits,0)
from gv$system_event
where event like '%log buffer space%';
---
cursor c11 is
select round(100*SUM(getmisses)/SUM(gets),3)
from gv$rowcache;
---
A Number(8);
B Number(8);
C Number(8);
D Number(8);
E Number(8);
F Number(8);
G Number(8);
H Number(8);
I Number(8,4);
J Number(8);
K Number(8,3);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(5);
FF Varchar2(5);
GG Varchar2(5);
HH Varchar2(5);
II Varchar2(5);
JJ Varchar2(5);
KK Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into E;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
Open c7;
Fetch c7 Into G;
Close c7;
Open c8;
Fetch c8 Into H;
Close c8;
Open c9;
Fetch c9 Into I;
Close c9;
Open c10;
Fetch c10 Into J;
Close c10;
Open c11;
Fetch c11 Into K;
Close c11;
if A <= 700 then
AA := 'OK';
else
AA := 'NO';
end if;
if B <= 15 then
BB := 'OK';
else
BB := 'NO';
end if;
if C >= 80 then
CC := 'OK';
else
CC := 'NO';
end if;
if D >= 99 then
DD := 'OK';
else
DD := 'NO';
end if;
if E >= 80 then
EE := 'OK';
else
EE := 'NO';
end if;
if F <= 5 then
FF := 'OK';
else
FF := 'NO';
end if;
if G = 0 then
GG := 'OK';
else
GG := 'NO';
end if;
if H = 0 then
HH := 'OK';
else
HH := 'NO';
end if;
if I <= 0.0010 then
II := 'OK';
else
II := 'NO';
end if;
if J = 0 then
JJ := 'OK';
elsif J is null then
JJ := 'OK';
J := 0;
else
JJ := 'NO';
end if;
if K <= 0.015 then
KK := 'OK';
else
KK := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Total Sessions < 700 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Active sessions number <15 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Data Buffer Hit Ratio > 80 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'L.Buffer Reload Pin Ratio > 99'||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
dbms_OutPut.Put_Line('-- '||'Row Cache Miss Ratio < 0.015 '||Lpad(KK,5)||' - '||Lpad(To_Char(K),5));
dbms_OutPut.Put_Line('-- '||'Dict.Buffer Hit Ratio > 80 '||Lpad(EE,5)||' - '||Lpad(To_Char(E),5));
dbms_OutPut.Put_Line('-- '||'Log Buffer Waits = 0 '||Lpad(JJ,5)||' - '||Lpad(To_Char(J),5));
dbms_OutPut.Put_Line('-- '||'Log Buffer Retries < 0.0010 '||Lpad(II,5)||' - '||Lpad(To_Char(I),5));
dbms_OutPut.Put_Line('-- '||'Switch number (Daily Avg) < 5 '||Lpad(FF,5)||' - '||Lpad(To_Char(F),5));
dbms_OutPut.Put_Line('-- '||'Jobs Broken = 0 '||Lpad(GG,5)||' - '||Lpad(To_Char(G),5));
dbms_OutPut.Put_Line('-- '||'Shared_Pool Failure = 0 '||Lpad(HH,5)||' - '||Lpad(To_Char(H),5));
end;
/
prompt
prompt
prompt Storage CheckList
prompt
prompt
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' V$Datafile Status '||Lpad('OK',12)
, 'SYSTEM', ' V$Datafile Status '||Lpad('OK',12)
, ' V$Datafile Status '||Lpad('NO',12)
) STATUS_01 from v$datafile)
, (select distinct
decode (status
, 'ONLINE', ' V$Tempfile Status '||Lpad('OK',14)
, 'SYSTEM', ' V$Tempfile Status '||Lpad('OK',14)
, ' V$Tempfile Status '||Lpad('NO',14)
) STATUS_02 from v$tempfile)
Union
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' Dba_Tablespaces Status '||Lpad('OK',7)
, 'READ ONLY', ' Dba_Tablespaces Status '||Lpad('OK',7)
, ' Dba_Tablespaces Status '||Lpad('NO',7)
) STATUS_01 from dba_tablespaces)
, (select distinct
decode (status
, 'CURRENT', ' V$Log Status '||Lpad('OK',19)
, 'ACTIVE', ' V$Log Status '||Lpad('OK',19)
, 'INACTIVE', ' V$Log Status '||Lpad('OK',19)
, ' V$Log Status '||Lpad('NO',19)
) STATUS_02 from v$log)
union
Select status_01||' | '||status_02 status
From
(select
distinct
decode (count(1)
, 0, ' V$Recover_File '||Lpad('OK',15)
, ' V$Recover_File '||Lpad('NO',15)
) STATUS_01 from v$recover_file)
, (select
distinct
decode (count(1)
, 0, ' V$Recovery_Log '||Lpad('OK',17)
, ' V$Recovery_Log '||Lpad('NO',17)
) STATUS_02 from v$recovery_log)
;
prompt
declare
cursor c1 is
select count(*)
from v$backup where status != 'NOT ACTIVE';
---
cursor c2 is
select count(*)
from ( select sum (bytes)/1048576 free, max (bytes)/1048576 fragmax, tablespace_name from sys.dba_free_space group by tablespace_name ) fsp, ( select sum(bytes)/1048576 alloc, tablespace_name from sys.dba_data_files group by tablespace_name) df, dba_tablespaces dt where fsp.tablespace_name (+) = df.tablespace_name and df.tablespace_name = dt.tablespace_name and dt.status = 'ONLINE' and (((alloc - nvl (free, 0)) / alloc) * 100 > 95);
---
cursor c3 is
select count(*)
From dba_objects where status != 'VALID'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
cursor c4 is
select count(*)
from dba_indexes where status = 'UNUSABLE';
---
cursor c5 is
select count(*)
from dba_triggers where status != 'ENABLED'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
cursor c6 is
select count(*)
From dba_constraints where status != 'ENABLED'
and owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP');
---
A Number(8);
B Number(8);
C Number(8);
D Number(8);
E Number(8);
F Number(8);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(5);
FF Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into E;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
if A = 0 then
AA := 'OK';
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
else
BB := 'NO';
end if;
if C = 0 then
CC := 'OK';
else
CC := 'NO';
end if;
if D = 0 then
DD := 'OK';
else
DD := 'NO';
end if;
if E = 0 then
EE := 'OK';
else
EE := 'NO';
end if;
if F = 0 then
FF := 'OK';
else
FF := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Tablespace in Backup Mode = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Tablespace < 95% '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Objects Invalid = 0 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'Indexes unusable = 0 '||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
dbms_OutPut.Put_Line('-- '||'Trigger Disabled = 0 '||Lpad(EE,5)||' - '||Lpad(To_Char(E),5));
dbms_OutPut.Put_Line('-- '||'Constraint Disabled = 0 '||Lpad(FF,5)||' - '||Lpad(To_Char(F),5));
end;
/
declare
cursor c1 is
select count(*)
from dba_segments
where max_extents-extents < 10 AND
segment_type <> 'CACHE';
---
cursor c2 is
select count(*)
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%'
and next_extent * 2 > (
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name);
---
cursor c3 is
select count(*)
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP')
and tablespace_name = 'SYSTEM';
---
cursor c4 is
select count(*)
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columnsx
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and b.owner not in ('PUBLIC', 'SYS', 'SYSTEM','OUTLN','WMSYS','MDSYS','CTXSYS','DBSNMP')
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columnsx
from dba_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columnsx (+) like a.columnsx || '%'
and b.table_name is null;
A Number(8);
B Number(8);
C Number(8);
D Number(8);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
if A = 0 then
AA := 'OK';
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
else
BB := 'NO';
end if;
if C = 0 then
CC := 'OK';
else
CC := 'NO';
end if;
if D = 0 then
DD := 'OK';
else
DD := 'NO';
end if;
dbms_OutPut.Put_Line('-- '||'Objects close max extents = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Objects can not extent = 0 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'User Objects on Systems = 0 '||Lpad(CC,5)||' - '||Lpad(To_Char(C),5));
dbms_OutPut.Put_Line('-- '||'FK Without Index = 0 '||Lpad(DD,5)||' - '||Lpad(To_Char(D),5));
end;
/
prompt
prompt
prompt Datagard CheckList
prompt
declare
cursor c1 is
select count(*)
FROM gv$dataguard_status
WHERE severity IN ('Error','Fatal','Warning');
---
cursor c2 is
select count(*)
FROM v$archive_gap;
---
cursor c3 is
select max(sequence#) from v$archived_log where applied = 'YES';
---
cursor c4 is
select max(sequence#) from v$archived_log;
---
cursor c5 is
select value from v$parameter where name = 'log_archive_dest_2';
---
cursor c6 is
select to_number(substr(version,1,3)) from v$instance;
---
A Number(8);
B Number(8);
C Number(10);
D Number(10);
E Number(10);
F Number(8,2);
---
AA Varchar2(5);
BB Varchar2(5);
CC Varchar2(5);
DD Varchar2(5);
EE Varchar2(50);
---
begin
Open c1;
Fetch c1 Into A;
Close c1;
Open c2;
Fetch c2 Into B;
Close c2;
Open c3;
Fetch c3 Into C;
Close c3;
Open c4;
Fetch c4 Into D;
Close c4;
Open c5;
Fetch c5 Into EE;
Close c5;
Open c6;
Fetch c6 Into F;
Close c6;
if A = 0 then
AA := 'OK';
elsif A is null then
AA := 'OK';
A := 0;
else
AA := 'NO';
end if;
if B = 0 then
BB := 'OK';
elsif B is null then
BB := 'OK';
B := 0;
else
BB := 'NO';
end if;
if D is null then
D := 0;
end if;
if C is null then
C := 0;
end if;
E := ( D - C);
if E <= 5 then
CC := 'OK';
else
CC := 'NO';
end if;
if EE is not null and F >= 9 then
dbms_OutPut.Put_Line('-- '||'Datagard Errors = 0 '||Lpad(AA,5)||' - '||Lpad(To_Char(A),5));
dbms_OutPut.Put_Line('-- '||'Datagard Gap = 0 '||Lpad(BB,5)||' - '||Lpad(To_Char(B),5));
dbms_OutPut.Put_Line('-- '||'Archives not Aplied < 5 '||Lpad(CC,5)||' - '||Lpad(To_Char(E),5));
else
dbms_OutPut.Put_Line('-- '||'No Datagard Available or Database version is not supported for this check!');
end if;
end;
/
prompt
prompt
prompt Installed options :
prompt
select ' - ' || parameter || ' option'
from sys.v_$option
where value = 'TRUE'
;
set head on
Prompt
set feedback on
prompt
prompt V$Recovery_Log Status
prompt
column Thread# format 9999999 heading "Thread"
column Sequence# format 9999999 heading "Sequence"
column archive_name format A60 wrap heading "Archive|Name"
column Time heading "Time"
select *
from v$recovery_log
order by thread#
;
prompt
prompt V$Recover_File Status
prompt
column file# format 9999999 heading "File#"
column online format A10 heading "Online"
column online_status format A10 heading "Online|Status"
column error format A20 wrap heading "Error"
column change format 99999 heading "Change"
column Time heading "Time"
select * from v$recover_file
order by file# ;
prompt
prompt V$Log Status
prompt
Declare
--
Cursor Cur_Req Is
select 'X'
from v$log
where status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Log Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on redogroup -
skip 1
column redogroup format 99999 heading "Group"
column redothread format 99999 heading "Thread"
column redosequence format 999999 heading "Sequence"
column file_name format A55 heading "RedoLog Name"
column KB format 99999999B heading "Size|(Kb)"
column status format A5 heading "Stat." Trunc
select
lf.group# redogroup
, l.thread# redothread
, l.sequence# redosequence
, lf.member file_name
, l.bytes/1024 Kb
, l.status status
from
v$logfile lf
, v$log l
where
l.group# = lf.group#
and
l.status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
order by
lf.group#
, l.thread#
, l.sequence#
, lf.member
;
clear breaks
prompt
prompt V$Tempfile Status
prompt
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$tempfile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Tempfile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on status skip 1
column name format a60 wrap heading "DataFile Name"
select
name
, status
from V$tempfile
where status not in ('ONLINE', 'SYSTEM')
Order By Status , Name
;
clear breaks
prompt
prompt V$Datafile Status
prompt
Declare
--
Cursor Cur_Req Is
select
'X'
from v$datafile
where status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Datafile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on status skip 1
column name format a60 wrap heading "DataFile Name"
select
name
, status
from V$datafile
where status not in ('ONLINE', 'SYSTEM')
Order By Status , Name
;
clear breaks
prompt -----------------------------
prompt Dba_Tablespaces Status
prompt ---------------------------
Declare
--
Cursor Cur_Req Is
select
'X'
from dba_tablespaces
where status not in ('ONLINE', 'READ ONLY')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Dba_Tablespaces Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column tblsp format a20 wrap heading "Tablespace Name"
column contents format a10 heading "Content"
clear breaks
break on contents -
skip 1
select
contents
, tablespace_name tblsp
, status
from dba_tablespaces
where status not in ('ONLINE', 'READ ONLY')
order by contents
;
prompt
prompt Tablespaces Backup Mode
prompt
Declare
--
Cursor Cur_Req Is
select 'X'
from v$backup bck
, dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database);
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Tablespaces in Backup Mode ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column tablespace_name format a18 wrap heading "Tablespace Name"
column file_name format a50 wrap heading "DataFile Name"
column status format a12 wrap heading "Status"
column change# format 999999999999 heading "Change"
column time format a18 wrap heading "Time"
select
df.tablespace_name
, df.file_name
, bck.status
, bck.change#
, To_Char(bck.time,'DD-MM-YYYY HH24:MI') time
from
v$backup bck
, dba_data_files df
where
bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database)
order by df.tablespace_name, df.file_name;
prompt
prompt Lock list
prompt
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where
s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Lock list ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column username heading "Utilis." format A15
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column state heading "Etat" format A15
column Lmode_H heading "Lock mode" format A15
column terminal heading "Terminal" format A10
column command heading "C" format 99
Column serial# heading "Serial#" format 99999
column ctime heading "Duration" format 999999
column logon heading "Date Connexion" format A16
column object_name heading "Object Name" format A18
select
s.sid
, s.serial#
, p.spid
, substr(s.username,1,15) username
, s.terminal
, l.type
, to_char(s.logon_time,'DD-MM-YYYY HH24:MI') logon
, decode(l.lmode, 1,'null' , 2,'Row share'
, 3,'Row Exclusive' , 4,'Share'
, 5,'Share Row Excl.' , 6,'Exclusive') Lmode_H
, o.object_name
, Round(ctime/60) ctime
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where
s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
prompt
prompt
prompt Tablespaces
prompt
Set Heading Off
Set Termout Off
Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint
Select value Var_DB_BLOCK_SIZE
from v$parameter
where Upper(name) = Upper ('db_block_size')
;
Set Termout On
Set Heading On
clear breaks
break on contents -
skip 1
compute Sum of alloc used free nbfrag on contents
column tblsp format a20 wrap heading "Tablespace Name"
column Alloc format 999,999 heading "Alloc|(Mb)"
column Free format 999,999 heading "Free|(Mb)"
column used format 999,999 heading "Used|(Mb)"
column pused format 990.9 heading "%|Used|Space"
column fragmax format 99,999.9 heading "Largest|Free|Ext.(Mb)"
column nbfrag format 99999 heading "Nb|frag"
column contents format a10 heading "Content"
column pct_ext_coal format 999 heading "% Ext.|Coal."
column ext_manage format a7 wrap heading "Ext.|M."
column autoext format a7 wrap heading "Auto|Ext."
select
contents
, nvl (dt.tablespace_name, nvl (fsp.tablespace_name, 'Unknown')) tblsp
, alloc
, alloc - nvl (free, 0) Used
, nvl (free, 0) Free
, ((alloc - nvl (free, 0)) / alloc) * 100 pused
, nbfrag
, fragmax
, dfsc.pct_ext_coal pct_ext_coal
, dt.ext_manage
, df.inc autoext
from
( select sum (bytes)/1048576 free
, max (bytes)/1048576 fragmax
, tablespace_name
, count(*) nbfrag
from sys.dba_free_space
group by tablespace_name
) fsp
, ( select sum(bytes)/1048576 alloc
, tablespace_name
, Decode(((inc * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes') inc
from sys.dba_data_files sddf
, sys.filext$ aut
where sddf.file_id = aut.file# (+)
group by tablespace_name
, Decode(((inc * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes')
Union
select sum(bytes)/1048576 alloc
, tablespace_name
, Decode(((increment_by * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes') inc
from sys.dba_temp_files sddf
group by tablespace_name
, Decode(((increment_by * &Var_DB_BLOCK_SIZE)/1024), Null, 'No', 'Yes')
) df
, ( select contents
, tablespace_name
, initial_extent/1024 initial_ext
, next_extent/1024 next_ext
, pct_increase
, max_extents
, min_extents
, Substr(extent_management,1,5) ext_manage
from dba_tablespaces
) dt
, ( select percent_extents_coalesced pct_ext_coal
, tablespace_name
from dba_free_space_coalesced
) dfsc
where fsp.tablespace_name (+) = dt.tablespace_name
and df.tablespace_name (+) = dt.tablespace_name
and dfsc.tablespace_name (+) = dt.tablespace_name
order by contents , pused desc
;
Prompt
PROMPT
PROMPT ******************************************** INVALID OBJECTS BY TYPE
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL of quantity ON REPORT
SELECT object_type, COUNT(*) quantity
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY object_type;
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT ******************************************** INVALID OBJECTS BY TYPE AND OWNER
SELECT owner, object_type, COUNT(*) quantity
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;
PROMPT
PROMPT ******************************************** MODIFIED OBJECTS IN THE LAST 7 DAYS
col objet format a40
SELECT owner||'.'||object_name objet, object_type, created, last_ddl_time modified, ROUND(sysdate - last_ddl_time,2) days
FROM dba_objects
WHERE sysdate - last_ddl_time < 7 AND
subobject_name IS NULL
and object_name not like 'WRH$%'
ORDER BY created DESC;
PROMPT
PROMPT ******************************************** UNUSABLE INDEXES
SELECT owner||'.'||index_name indice FROM dba_indexes
WHERE status = 'UNUSABLE'
ORDER BY 1;
col partition format a45
SELECT index_owner||'.'||index_name||'.'||partition_name partition
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
ORDER BY 1;
PROMPT
PROMPT ******************************************** DISABLED TRIGGERS
col owntab format a45
col triggering_event format a20
SELECT owner||'.'||trigger_name "TRIGGER", triggering_event, table_owner||'.'||table_name owntab , status
FROM dba_triggers
WHERE status = 'DISABLED';
PROMPT
PROMPT ******************************************** DISABLED CONSTRAINTS
col owntab format a45
SELECT constraint_name constraint, constraint_type, owner||'.'||table_name owntab , status
FROM dba_constraints
WHERE status = 'DISABLED';
-- PROMPT
-- PROMPT ******************************************** ANALYZED TABLES
-- SELECT owner, NVL(TO_CHAR(TRUNC(last_analyzed),'dd-mm-yyyy'), 'Never Analyzed') last_analyzed, COUNT(*) quantity
-- FROM dba_tables
-- GROUP BY owner, TRUNC(last_analyzed)
-- ORDER BY 1,2;
-- PROMPT
-- PROMPT ******************************************** ANALYZED INDEXES
-- SELECT owner, NVL(TO_CHAR(TRUNC(last_analyzed),'dd-mm-yyyy'), 'Never Analyzed') last_analyzed, COUNT(*) quantity
-- FROM dba_indexes
-- GROUP BY owner, TRUNC(last_analyzed)
-- ORDER BY 1,2;
PROMPT
PROMPT ******************************************** TABLESPACES WITH LESS THAN 15% FREE
SELECT a.tblspc tablespace, a.fbytes TOTAL, NVL(u.ebytes,0) RESERVED, a.fbytes-NVL(u.ebytes,0) FREE, 100*NVL(u.ebytes,0)/a.fbytes "% RESERVED"
FROM (SELECT tablespace_name tblspc, SUM(bytes)/1024/1024 ebytes FROM dba_segments GROUP BY tablespace_name) u,
(SELECT tablespace_name tblspc, SUM(bytes)/1024/1024 fbytes FROM dba_data_files GROUP BY tablespace_name) a
WHERE u.tblspc(+) = a.tblspc AND
100*NVL(u.ebytes,0)/a.fbytes > 85.00
ORDER BY tablespace;
PROMPT
PROMPT ******************************************** OBJECTS CLOSE TO MAX EXTENTS
SELECT tablespace_name tablespace, segment_type tipo, owner||'.'||segment_name segment, extents, bytes/1024/1024 mb,
max_extents
FROM dba_segments
WHERE max_extents-extents < 10 AND
segment_type <> 'CACHE'
ORDER BY 1,2,3,4;
PROMPT
PROMPT ******************************************** OBJECTS THAT CAN.T EXTENT
prompt Objects that cannot extend (no space in TS)
column Sname form a40 heading 'Object Name'
column Stype form a15 heading 'Type'
column Size form 9,999 heading 'Size'
column Next form 99,999 heading 'Next'
column Tname form a15 heading 'TsName'
select a.owner||'.'||a.segment_name "Sname",
a.segment_type "Stype",
a.bytes/1024/1024 "Size",
a.next_extent/1024/1024 "Next",
a.tablespace_name "TName"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 2 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc
/
PROMPT
PROMPT ******************************************** OBJECTS IN THE SYSTEM TABLESPACE
select owner, substr(segment_name,1,25) segment , substr(partition_name,1,25) partition, segment_type
from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';
PROMPT
PROMPT ******************************************** DATAGUARD STATE (CRITICAL MESSAGES)
SELECT inst_id, facility, severity, dest_id, error_code, callout, timestamp, message
FROM gv$dataguard_status
WHERE severity IN ('Error','Fatal','Warning')
ORDER BY timestamp;
--PROMPT
--PROMPT ******************************************** NO APLIED ARCHIVELOGS
--selecT sequence#,substr(a.name,1,46) archivelog, a.blocks, a.archived, a.applied, a.status, a.end_of_redo eor, a.standby_dest, a.first_time, a.completion_time completion, SYSDATE time
--From v$archived_log a
--WHERE a.sequence# between (select max(b.sequence#)
--from v$archived_log b where b.applied = 'YES') and (select max(c.sequence#) from v$archived_log c)
--/
PROMPT
PROMPT ******************************************** GAP SEQUENCE
SELECT * FROM v$archive_gap;
PROMPT
PROMPT ******************************************** DIFERENCE ON RECEPTION
SELECT thread#, MAX(next_time) next_time, SYSDATE time, ROUND((SYSDATE - MAX(next_time))*24,2) horas, ROUND((SYSDATE - MAX(next_time))*24*60,0) minutos
FROM v$archived_log
GROUP BY thread#;
PROMPT
PROMPT ******************************************** DIFERENCE TO APPLY
SELECT thread#, MAX(next_time) next_time, SYSDATE time, ROUND((SYSDATE - MAX(next_time))*24,2) horas, ROUND((SYSDATE - MAX(next_time))*24*60,0) minutos
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#;
PROMPT
PROMPT ******************************************** PROCESSES
col "group#" format a10
SELECT inst_id, process, pid, status, substr(to_char(group#),1,8) group#, thread#,
sequence#, block#, blocks, delay_mins, known_agents, active_agents, client_process
FROM gv$managed_standby;
PROMPT
set heading on
set echo off
set linesize 150
set pagesize 500
column day format a16 heading 'Day'
column d_0 format a3 heading '00'
column d_1 format a3 heading '01'
column d_2 format a3 heading '02'
column d_3 format a3 heading '03'
column d_4 format a3 heading '04'
column d_5 format a3 heading '05'
column d_6 format a3 heading '06'
column d_7 format a3 heading '07'
column d_8 format a3 heading '08'
column d_9 format a3 heading '09'
column d_10 format a3 heading '10'
column d_11 format a3 heading '11'
column d_12 format a3 heading '12'
column d_13 format a3 heading '13'
column d_14 format a3 heading '14'
column d_15 format a3 heading '15'
column d_16 format a3 heading '16'
column d_17 format a3 heading '17'
column d_18 format a3 heading '18'
column d_19 format a3 heading '19'
column d_20 format a3 heading '20'
column d_21 format a3 heading '21'
column d_22 format a3 heading '22'
column d_23 format a3 heading '23'
column Total format 9999
column status format a8
column member format a40
column archived heading 'Archived' format a8
column bytes heading 'Bytes|(MB)' format 9999
col group# format 999
Ttitle 'Log Info' skip 2
select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type
from v$log l, v$logfile f
where l.group# = f.group#
/
Ttitle off
prompt =========================================================================================================================
Ttitle 'Log Switch on hour basis' skip 2
select to_char(FIRST_TIME,'DY, DD-MON-YYYY') day,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0))) d_21,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0))) d_22,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0))) d_23,
count(trunc(FIRST_TIME)) Total
from v$log_history
group by to_char(FIRST_TIME,'DY, DD-MON-YYYY')
order by to_date(substr(to_char(FIRST_TIME,'DY, DD-MON-YYYY'),5,15) )
/
Ttitle off
prompt
spool off
Subscribe to:
Posts (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)