-----------script sulle macchine: check_tbs.sh----------------
#!/usr/bin/ksh

##############################################################################################
# La shell esegue in base alla lista dei sid definiti la verifica della v$instance           #
##############################################################################################

LISTA_SID=`cat /home/oracle/sid_to_check.par`

for ISTANZA in $LISTA_SID
 do
  echo "#########################################################################################################################"
  echo "Check istanza $ISTANZA"
  export ORACLE_SID=$ISTANZA
  #sqlplus -S '/ as sysdba' @query_tbs.sql

  echo "Fine check istanza $ISTANZA"
  echo ""
  echo "#########################################################################################################################"
  echo ""
done
---------------------------------------------------------------------------------

-------query_tbs.sql------------
#### Sopra a soglia ####
set line 200
set pages 200
select  a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free,b.largest, a.MAX_BYTES, round(((a.BYTES-b.BYTES)/a.MAX_BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES, SUM (decode(maxbytes,0,bytes,maxbytes)) MAX_BYTES from dba_data_files group by TABLESPACE_NAME )a,
(select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
AND ((a.BYTES-b.BYTES)/a.MAX_BYTES)*100 > 92
ORDER BY percent_used DESC;

#### Tutti ####
set line 200
set pages 200
select  a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free,b.largest, a.MAX_BYTES, round(((a.BYTES-b.BYTES)/a.MAX_BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES, SUM (decode(maxbytes,0,bytes,maxbytes)) MAX_BYTES from dba_data_files group by TABLESPACE_NAME )a,
(select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
ORDER BY percent_used DESC;

#### Solo un TBS ####
	select  a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free,b.largest, a.MAX_BYTES, round(((a.BYTES-b.BYTES)/a.MAX_BYTES)*100,2) percent_used
	from (select TABLESPACE_NAME,sum(BYTES) BYTES, SUM (decode(maxbytes,0,bytes,maxbytes)) MAX_BYTES from dba_data_files group by TABLESPACE_NAME )a,
	(select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b
	where a.TABLESPACE_NAME=b.TABLESPACE_NAME
	AND a.TABLESPACE_NAME='TBS_NEWCONS_INDEX';
-------------------------------

##### Crescita tablespace #####

(TABLESPACE_MAXSIZE in database blocks, se il blocco è 8KB l'output è in GB)
 
set pages 5000
select TABLESPACE_SIZE*8/1024/1024,TABLESPACE_MAXSIZE*8/1024/1024,TABLESPACE_USEDSIZE*8/1024/1024,RTIME 
from DBA_HIST_TBSPC_SPACE_USAGE where TABLESPACE_ID=1  order by SNAP_ID;

TABLESPACE_SIZE*8/1024/1024 TABLESPACE_MAXSIZE*8/1024/1024 TABLESPACE_USEDSIZE*8/1024/1024 RTIME
--------------------------- ------------------------------ ------------------------------- -------------------------
                 403.34166                     415.999802                      402.280441 11/05/2018 11:00:33
                 415.841644                    415.999802                      402.38736  11/05/2018 12:00:50

 
# Per vedere l'ID:
select * from v$tablespace where NAME='SYSAUX';
 
 
------ script su racnimt3: monitor_tbs.sh ------------
#!/usr/bin/ksh

##############################################################################################
# La shell esegue in base alla lista dei server definiti la verifica dei tablespace          #
##############################################################################################

LISTA_PC=`cat /home/JuriS/pc_to_check.par`

for PC in $LISTA_PC
 do
  echo "----------------------------------------------------------------------------------------------------"
  echo "Check server $PC"
  ssh  oracle@$PC "df -g"

  echo "Fine check server $PC"
  echo ""
  echo "----------------------------------------------------------------------------------------------------"
  echo ""
done
---------------------------------------------------------------------------------


# elenco datafile di un tablespace
select * from dba_data_files where tablespace_name='PRON_BLOB_2018';

set feed off
set linesize 120
set pagesize 200
col TABLESPACE_NAME for a20
col "USED (MB)" for a20
col "FREE (MB)" for a20
col "TOTAL (MB)" for a20
col PER_FREE for a20
---------------------------------------
SELECT tablespace_name, file_name, bytes  FROM dba_temp_files WHERE tablespace_name = 'TEMP';

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';

##### Aumenta max size:
alter database datafile '/u02/sys/system04.dbf' AUTOEXTEND ON NEXT 128M MAXSIZE 15G;
#ASM
alter database datafile '+DATA/bda/datafile/audit_data.15508.940496949' AUTOEXTEND ON NEXT 128M MAXSIZE 12G;
##### Aggiungi datafile
ALTER TABLESPACE SIUDETD2015_DATI  ADD DATAFILE '/oracle/oradata/NETAProd/dati29/siudetd2015_dati19.dbf'
   SIZE 500M    AUTOEXTEND ON NEXT 100M MAXSIZE 32G;
# ASM: alter tablespace SYSTEM ADD DATAFILE '+DATA' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE 12G;

alter tablespace AUDIT_DATA ADD DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 2G;

################################################################################################
#######                               SPAZIO   RECUPERABILE                             ########
################################################################################################
set pages 250
set lines 250
set verify off
column file_name format a50 word_wrapped
column smallest format 999,999,990 heading "Smallest|Size|Poss."
column currsize format 999,999,990 heading "Current|Size"
column savings  format 999,999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/