Search This Blog

Total Pageviews

Monday 31 October 2011

Oracle growth details script

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

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 report





set 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

Oracle DBA

anuj blog Archive