script to monitor tablespace usage, in this example warn me if tablespace usage is more than 85% and free space in in lower than 30GB

with add_datafile_view as
(select rec.tbs_cnt,round(sum(df.bytes)/1024/1024/1024,2) as free_space,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
  'alter tablespace '||rec.tablespace_name|| ' add datafile '''||
  substr(d.file_name,1,length (d.file_name) -4) ||'_'||
  to_char (rec.tbs_cnt +1) ||'.ora'' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE unlimited'                           AS ADD_DATAFILE
  from dba_data_files d,
  (
SELECT COUNT(*) AS tbs_cnt,min(dd.file_id) as min_file_id,
  dd.tablespace_name,
  ROUND (SUM(dd.bytes   /1024/1024/1024))                                                                             AS tot_size,
  ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024) )                                                 AS max_size,
   ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024)  -ROUND (SUM(dd.bytes )/1024/1024/1024))        AS free_gb,
  100                * ROUND (SUM(dd.bytes/1024/1024/1024)/ SUM( NVL(NULLIF( dd.maxbytes , 0),dd.bytes) /1024/1024/1024),3) AS usage_pct
FROM  DBA_DATA_FILES dd, dba_tablespaces dt
               WHERE   DD.TABLESPACE_NAME = Dt.TABLESPACE_NAME
                       AND DD.STATUS = 'AVAILABLE'
                       and dt.status <>'READ ONLY'
                       and dt.contents='PERMANENT'
GROUP BY dd.tablespace_name
)rec,    dba_Free_space df
  where d.file_id = rec.min_file_id
    and df.tablespace_name=rec.tablespace_name
    group by rec.tbs_cnt,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
      'alter tablespace '||rec.tablespace_name|| ' add datafile '''||
  substr(d.file_name,1,length (d.file_name) -4) ||'_'||
  to_char (rec.tbs_cnt +1) ||'.ora'' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE unlimited'
  order by rec.usage_pct desc)
 
select * from sys.add_datafile_view where free_space < 30  and USAGE_PCT > 85;

same thing but for asm :

with add_datafile_view as (
select rec.tbs_cnt,round(sum(df.bytes)/1024/1024/1024,2) as free_space,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
  'alter tablespace '||rec.tablespace_name|| ' add datafile ' as add_datafile
  from   dba_Free_space df,
  (
SELECT COUNT(*) AS tbs_cnt,min(dd.file_id) as min_file_id,
  dd.tablespace_name,
  ROUND (SUM(dd.bytes   /1024/1024/1024))                                                                             AS tot_size,
  ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024) )                                                 AS max_size,
   ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024)  -ROUND (SUM(dd.bytes )/1024/1024/1024))        AS free_gb,
  100                * ROUND (SUM(dd.bytes/1024/1024/1024)/ SUM( NVL(NULLIF( dd.maxbytes , 0),dd.bytes) /1024/1024/1024),3) AS usage_pct
FROM  DBA_DATA_FILES dd, dba_tablespaces dt
               WHERE   DD.TABLESPACE_NAME = Dt.TABLESPACE_NAME
                       AND DD.STATUS = 'AVAILABLE'
                       and dt.status <>'READ ONLY'
                       and dt.contents='PERMANENT'
GROUP BY dd.tablespace_name
--HAVING 100* ROUND (SUM(dd.bytes/1024/1024/1024)/ SUM( NVL(NULLIF( dd.maxbytes , 0),dd.bytes) /1024/1024/1024),3) >75
--and ROUND (SUM(NVL(NULLIF( dd.maxbytes , 0),dd.bytes)/1024/1024/1024) ) -ROUND (SUM(dd.bytes/1024/1024/1024)) < 100
) rec
  where df.tablespace_name=rec.tablespace_name
    group by rec.tbs_cnt,rec.tablespace_name,rec.tot_size,rec.max_size,rec.free_gb,rec.usage_pct,
      'alter tablespace '||rec.tablespace_name|| ' add datafile '''
  order by rec.usage_pct desc )
  select * from sys.add_datafile_view where free_space < 30  and USAGE_PCT > 85;

check scheduler state ( last running job status )

col log_date format a35
col STATUS format a10
col JOB_NAME format a40
col JOB_ERR_LOG format a100
select log_date,status,job_name,job_err_log from (
SELECT
  log_date
  ,STATUS
  ,OWNER ||'.'||JOB_NAME as job_name
  ,dbms_lob.substr(ADDITIONAL_INFO,400,1) as job_err_log
  ,rank() over (partition by OWNER ||'.'||JOB_NAME order by log_date desc) rnk
FROM
  dba_scheduler_job_run_details
WHERE 1=1
  and owner not like 'SYS%'
GROUP BY
  STATUS
  ,OWNER ||'.'||JOB_NAME,log_date
   , dbms_lob.substr(ADDITIONAL_INFO,400,1) ) t where rnk=1 and status !='SUCCEEDED';

check invalid objects

col object_type format a15
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

check unusable index

select ' alter index '||OWNER||'.'|| index_name||' rebuild online ;' as rebuild_script from DBA_INDexes where (status  not in ( 'USABLE','VALID','N/A') or (DOMIDX_OPSTATUS<>'VALID' or DOMIDX_STATUS<>'VALID' or FUNCIDX_STATUS ='DISABLED')) and owner not like '%SYS%'
union all
select ' alter index '||INDEX_owner||'.'|| index_name||' rebuild PARTITION '|| PARTITION_NAME ||' online ;' from  DBA_IND_PARTITIONS     where status  not in ( 'USABLE','VALID','N/A')
union all
select  'alter index '||INDEX_owner||'.'|| index_name||' rebuild SUBPARTITION '|| SUBPARTITION_NAME ||' online ;' from  DBA_IND_SUBPARTITIONS     where status  not in ( 'USABLE','VALID','N/A');