{"id":4701,"date":"2022-05-16T07:00:00","date_gmt":"2022-05-16T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4701"},"modified":"2023-11-02T17:30:44","modified_gmt":"2023-11-02T14:30:44","slug":"pos-mporoyme-na-lamvanoyme-email-kathe-fora-3","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-lamvanoyme-email-kathe-fora-3\/","title":{"rendered":"How can we get email whenever Tablespace needs Datafile in Oracle database"},"content":{"rendered":"

In Oracle databases tables are stored in logical entities called Tablespaces<\/strong>. Each Tablespace consists of physical files named Datafiles<\/strong>. In Oracle, however, each datafile can have a maximum size of up to 32GB, because of this, if we have not created enough datafiles, while we have disk space, a tablespace and subsequently the tables it contains may not have space.<\/p>\n\n\n\n

In this article we will see a way so that when a tablespace is estimated by its usage that it is approaching a few days before its limit it informs us with a warning email. This will contain the name of the server and instance, along with the name of the tablespace, the space it has left and the command we need to run to create the datafile in that tablespace.<\/p>\n\n\n\n

However, we will see that with the following script we can completely automate the process and even create the datafile by itself.<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

In previous article<\/a> we had seen how we can set one up Linux servers<\/strong> (Oracle Linux based on RHEL specifically) so that we receive an email whenever an error occurs in the Alert log of the database instance. Accordingly we will now look at the steps to receive email when a tablespace needs a datafile soon.<\/p>\n\n\n\n

Create an SMTP relay with Google's gmail<\/h2>\n\n\n\n

If we have our own relay<\/strong> on the exchange server with configured smtp that will send the emails, we don't need this step.<\/p>\n\n\n\n

But now we will see what we do in case we want to send the emails through Gmail.<\/p>\n\n\n\n

First we need to connect to gmail account security<\/a>.<\/p>\n\n\n\n

There we should activate 2 factor authentication and then create an application password.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:<\/p>\n\n\n\n

[oracle@dp-gr ~]$ sudo vi \/etc\/mail.rc <\/pre>\n\n\n\n
account gmail {\n    set smtp-use-starttls\n    set ssl-verify=ignore\n    set smtp-auth=login\n    set smtp=smtp:\/\/smtp.gmail.com:587\n    set from=\"info@dataplatform.gr\"\n    set smtp-auth-user=dataplatform.gr@gmail.com\n    set smtp-auth-password=\"edw_vazoume_to_app_password\"\n    set ssl-verify=ignore\n    set nss-config-dir=\/etc\/pki\/nssdb\n}<\/pre>\n\n\n\n
We are ready to test that we can send email:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ echo \"Test\" | \/usr\/bin\/mail -A gmail -s \"dokimastiko\" ToEmailMas@outlook.com<\/pre>\n\n\n\n
How will we find the Tablespace that Datafile will soon need<\/h2>\n\n\n\n
We create the following sql query that generates the warning message for the tablespace, when with similar usage the estimate is that the datafile will be needed in less than 20 days or one reaches almost zero:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ vi check_tablespaces.sql<\/code><\/pre>\n\n\n\n
set linesize 7000;\nset heading off;\nset feedback off;\nset echo off;\nset pagesize 0;\n\n\n\nSELECT 'Warning: Low Tablespace space on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance)||' to tablespace '|| TABLESPACE_NAME ||'.     Space is enough for estimated ' || to_char(round(DAYS_EST_W_EXT,2),'999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') || ' days!!!     There is left free with extends '||to_char(round(FREE_LEFT_MB_W_EXT,2),'999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')|| 'MB.   You can execute this command as sysdba user to create the new datafile:      ' || FILE_CREATION\nFROM(SELECT T.TABLESPACE_NAME,\n       (SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) FREE_LEFT_MB_W_EXT,\n       (SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) \/ decode(max(t2.MB_PER_DAY),0,0.01,max(t2.MB_PER_DAY)) days_est_w_ext,                               \n       (select 'ALTER tablespace ' || d.tablespace_name || ' add datafile ''' || d.file_name ||'_adf_' || md.file_id ||'.dbf'' size 1g autoextend on next 64m maxsize 32767m;'\n       FROM (select tablespace_name,ROW_NUMBER() OVER (PARTITION BY tablespace_name order by file_id) rn,  substr(file_name,0,(length(file_name)-4)) file_name from dba_data_files ) D,\n            (select (max(file_id)+1) file_id from dba_data_files) MD\n       where tablespace_name = T.TABLESPACE_NAME and RN=1\n        ) FILE_CREATION\n  FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES \/ 1048576) FREESPACE\n          FROM DBA_FREE_SPACE\n         GROUP BY TABLESPACE_NAME, FILE_ID) F,\n       (SELECT FILE_ID,\n               FILE_NAME,\n               BYTES \/ 1048576 TOTALSPACE,\n               TABLESPACE_NAME,\n               DECODE(MAXBYTES, 0, BYTES, MAXBYTES) \/ 1024 \/ 1024 AS F_MAXSIZE\n          FROM DBA_DATA_FILES) T,\n       (select name, case when count(*)=1 then null else case when sum(used_mb - p_used_mb) >=0 then sum(used_mb - p_used_mb) \/ (count(*) - 1) else 0 end end mb_per_day          \n          from (select SN.begin_interval_time,\n                       name,\n                       round(tablespace_usedsize * TSO.block_size \/ 1024 \/ 1024, 2) USED_MB,\n                       nvl(LAG(round(tablespace_usedsize * TSO.block_size \/ 1024 \/ 1024, 2)) OVER(PARTITION by TS.name ORDER BY\n                                Sn.begin_interval_time),\n                           round(tablespace_usedsize * TSO.block_size \/ 1024 \/ 1024, 2)) P_USED_MB\n                  from V$TABLESPACE               TS,\n                       DBA_HIST_TBSPC_SPACE_USAGE TH,\n                       DBA_HIST_SNAPSHOT          SN,\n                      DBA_TABLESPACES            TSO\n                 where TS.ts# = TH.tablespace_id\n                   and TH.snap_id = SN.snap_id\n                   and SN.instance_number = 1\n                   and TS.NAME = TSO.tablespace_name\n                   and to_char(SN.begin_interval_time, 'hh24:mi') = '07:00'\n                 order by 2, 1)\n         group by name) T2\nWHERE T.FILE_ID = F.FILE_ID(+)\n   AND T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)\n   AND T.TABLESPACE_NAME = T2.NAME(+)\nGROUP BY T.TABLESPACE_NAME \n)\nWHERE \n(\nROWNUM=1\nAND TO_NUMBER(DAYS_EST_W_EXT) < 20 --change this if you need to change the days estimation threshold\nAND FREE_LEFT_MB_W_EXT<10240 --minimum free mb left threshold\n) \nOR\n(\nROWNUM=1\nAND FREE_LEFT_MB_W_EXT<128 --ignore estimation threshold if space is very low\n)\n;\n\n\/*\n\nselect ' ' from dual;\n\nselect 'The below command was executed:' from dual;\nspool create_dbf.txt\n\n SELECT FILE_CREATION\nFROM(SELECT T.TABLESPACE_NAME,\n       to_char(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0)), '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') FREE_LEFT_MB_W_EXT,\n       (SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) \/ decode(max(t2.MB_PER_DAY),0,0.01,max(t2.MB_PER_DAY)) days_est_w_ext,                               \n       (select 'ALTER tablespace ' || d.tablespace_name || ' add datafile ''' || d.file_name ||'_adf_' || md.file_id ||'.dbf'' size 128mb autoextend on next 64m maxsize 32767m;'\n       FROM (select tablespace_name,ROW_NUMBER() OVER (PARTITION BY tablespace_name order by file_id) rn,  substr(file_name,0,(length(file_name)-4)) file_name from dba_data_files ) D,\n            (select (max(file_id)+1) file_id from dba_data_files) MD\n       where tablespace_name = T.TABLESPACE_NAME and RN=1\n        ) FILE_CREATION\n  FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES \/ 1048576) FREESPACE\n          FROM DBA_FREE_SPACE\n         GROUP BY TABLESPACE_NAME, FILE_ID) F,\n       (SELECT FILE_ID,\n               FILE_NAME,\n               BYTES \/ 1048576 TOTALSPACE,\n               TABLESPACE_NAME,\n               DECODE(MAXBYTES, 0, BYTES, MAXBYTES) \/ 1024 \/ 1024 AS F_MAXSIZE\n          FROM DBA_DATA_FILES) T,\n       (select name, case when count(*)=1 then null else case when sum(used_mb - p_used_mb) >=0 then sum(used_mb - p_used_mb) \/ (count(*) - 1) else 0 end end mb_per_day          \n          from (select SN.begin_interval_time,\n                       name,\n                       round(tablespace_usedsize * TSO.block_size \/ 1024 \/ 1024, 2) USED_MB,\n                       nvl(LAG(round(tablespace_usedsize * TSO.block_size \/ 1024 \/ 1024, 2)) OVER(PARTITION by TS.name ORDER BY\n                                Sn.begin_interval_time),\n                           round(tablespace_usedsize * TSO.block_size \/ 1024 \/ 1024, 2)) P_USED_MB\n                  from V$TABLESPACE               TS,\n                       DBA_HIST_TBSPC_SPACE_USAGE TH,\n                       DBA_HIST_SNAPSHOT          SN,\n                       DBA_TABLESPACES            TSO\n                 where TS.ts# = TH.tablespace_id\n                   and TH.snap_id = SN.snap_id\n                   and SN.instance_number = 1\n                   and TS.NAME = TSO.tablespace_name\n                   and to_char(SN.begin_interval_time, 'hh24:mi') = '07:00'\n                 order by 2, 1)\n         group by name) T2\nWHERE T.FILE_ID = F.FILE_ID(+)\n   AND T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)\n   AND T.TABLESPACE_NAME = T2.NAME(+)\nGROUP BY T.TABLESPACE_NAME \n)\nWHERE \n(\nROWNUM=1\nAND TO_NUMBER(DAYS_EST_W_EXT) < 20 --change this if you need to change the days estimation threshold\nAND FREE_LEFT_MB_W_EXT<10240 --minimum free mb left threshold\n) \nOR\n(\nROWNUM=1\nAND FREE_LEFT_MB_W_EXT<128 --ignore estimation threshold if space is very low\n)\n;\nspool off;\n\n@create_dbf.txt\n\n*\/\n\n\nquit;\n\/\n<\/pre>\n\n\n\n
*The code that is commented \/* to *\/ is to automate the process. We will refer in detail below.<\/em><\/p>\n\n\n\n
How do we email when a Tablespace Datafile is needed soon?<\/h2>\n\n\n\n
Now we will create an executable that will run the above query, exporting the results to a text file. In case the file is more than one line, then it will send email with the title \u201cOracle DB Alert ORCL\u201d<\/em> in the emails we have defined, having as text a warning that contains the name of the server and the instance, together with the name of the tablespace, the remaining space and the command that we need to execute in order to create the datafile in this tablespace.<\/p>\n\n\n\n
The reason the process only completes if the file contains more than one line is so that it doesn't send an email if there is no tablespace that needs a datafile and the text file is empty.<\/p>\n\n\n\n
So we create the executable with the following code:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ vi check_tablespaces.sh<\/code><\/pre>\n\n\n\n
#!\/bin\/bash\nsqlplus -S \"\/as sysdba\" @\/home\/oracle\/check_tablespaces.sql > \/home\/oracle\/check_tablespaces.log\ncnt=$(wc -l <\/home\/opc\/check_tablespaces.log)\necho $cnt\nif [ $cnt -ge \"1\" ]\nthen\n        \/usr\/bin\/mail -A gmail -s \"Oracle DB Tablespace Warning on ORCL\" \\\n                -r info@dataplatform.gr \\\n                dataplatform.gr@gmail.com \\\n         < \/home\/opc\/check_tablespaces.log\n\nfi<\/pre>\n\n\n\n
We can alternatively run it remotely on another machine with the user SYS<\/code> via ezconnect<\/a><\/strong>:<\/p>\n\n\n\n
#!\/bin\/bash\nsqlplus -S \"sys\/Kwdikos@hostname:1521\/ORCL as sysdba\" @\/home\/oracle\/check_tablespaces.sql > \/home\/oracle\/check_tablespaces.log\ncnt=$(wc -l <\/home\/opc\/check_tablespaces.log)\necho $cnt\nif [ $cnt -ge \"1\" ]\nthen\n        \/usr\/bin\/mail -A gmail -s \"Oracle DB Alert ORCL\" \\\n                -r info@dataplatform.gr \\\n                dataplatform.gr@gmail.com \\\n         < \/home\/opc\/check_tablespaces.log\n\nfi<\/pre>\n\n\n\n
We don't forget to have given execute permission before executing it:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ chmod +x check_tablespaces.sh\n[oracle@dp-gr ~]$ .\/check_tablespaces.sh<\/code><\/pre>\n\n\n\n
How do we schedule it to be checked every morning?<\/h2>\n\n\n\n
Since our executable is ready, we can add a line to the crontab in which we will set it to run every morning:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ crontab -e<\/code><\/pre>\n\n\n\n
#crontab\n00 08 * * * . ~\/.bash_profile && (\/home\/oracle\/check_tablespaces.sh  >> \/dev\/null 2>&1)<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
Now when the tablespace exceeds the threshold in the days we have set for it, we will receive an email. <\/p>\n\n\n\n
This email will contain a message of the type:<\/p>\n\n\n\n
Warning: Low Tablespace space on ORCL@dp-gr to tablespace EXAMPLE.     Space is enough for estimated 19 days!!!     There is left free with extends       1.085,98MB.   You can execute this command as sysdba user to create the new datafile:      ALTER tablespace EXAMPLE add datafile '\/home\/oracle\/oracle\/app\/oracle\/oradata\/orcl\/example01_adf_7.dbf' size 1g autoextend on next 64m maxsize 32767m;<\/code><\/pre>\n\n\n\n
*for example I had put 34,000 days as shown in the image below.<\/em><\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
How do we completely automate the process so that it creates the Datafile by itself<\/h2>\n\n\n\n
If in the same sql query we remove the tag as a comment from the code, i.e. the \/*<\/code> and *\/<\/code> . Then during its execution it will not only inform us, but it will also create the datafile in the tablespace itself which will be needed soon.<\/p>\n\n\n\n
Then he will inform us with the following email:<\/p>\n\n\n\n
*for example I had put 34,000 days as shown in the image below.<\/em><\/p>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n