***************************************************************************************
* Description: SQL for Checking Tablespace Utilization, and Adding Data File Procedure
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 10:55 AM EST, 01/16/2017
***************************************************************************************


<1> Querying tablespace utilization:
    |
    |__ SQL> column tbs format a25
             set pagesize 60
             column percfull format 9999
             select tbs, round(free_mb+free_gw) freesp , round(total_mb) totalsp,
             round((total_mb-free_mb-free_gw)*100/total_mb) percfull
             from ( select tablespace_name tbs,
             sum(decode(maxbytes,null,bytes,0,bytes,maxbytes))/1024/1024 total_mb,
             sum(decode(maxbytes,null,bytes,0,bytes,maxbytes)-bytes)/1024/1024 free_gw
             from dba_data_files group by tablespace_name
             ) a,
             ( select tablespace_name tbs1,sum(bytes)/1024/1024 free_mb
             from dba_free_space group by tablespace_name) b
             where a.tbs=b.tbs1
             order by 4;
		 

                    TBS                           FREESP    TOTALSP PERCFULL
                    ------------------------- ---------- ---------- --------
                    TSD_PERFSTAT                   16133      16136        0
                    UNDOTBS1                       22511      22528        0
                    EXAMPLE                        32745      32768        0
                    USERS                          13690      15383       11
                    SYSAUX                         27856      32768       15
                    SYSTEM                           739       2048       64
	
	
==============================================================================

				
<2> Checking detail of data files under each tablespace:
    |
    |__ SQL> set pagesize 80
             set linesize 500
             col FILE_NAME format a60
             col bytes format 999999999999
             col MAxbytes format 999999999999
             
             select FILE_NAME,AUTOEXTENSIBLE,BYTES/(1024*1024) MB, MAXBYTES/(1024*1024) MAxbytes from dba_data_files where tablespace_name = '&1';
             select FILE_NAME,AUTOEXTENSIBLE,BYTES/(1024*1024) MB, MAXBYTES/(1024*1024) MAxbytes from dba_temp_files where tablespace_name = '&1';

			 
                   FILE_NAME                                                    AUT         MB      MAXBYTES
                   ------------------------------------------------------------ --- ---------- -------------
                   /u02/oradata/nebfprd/system01.dbf                            YES        890          1024
                   /u01/oradata/nebfprd/system02.dbf                            YES        500          1024
				   
				   
==============================================================================
	
	
<3> Checking free space on ASM diskgroup or regular file system:
    |
    |__ SQL> select name,round((free_mb/1024),0) FREE_GB,round((total_mb/1024),0) TOTAL_GB,round(((total_mb/1024) - (free_mb/1024)),0) "USED_GB",
             round(free_mb/total_mb*100,0) "FREE_%" from v$asm_diskgroup;
			 
			 
                   NAME                              FREE_GB   TOTAL_GB    USED_GB     FREE_%
                   ------------------------------ ---------- ---------- ---------- ----------
                   GROUP2                                 56        245        189         23
                   GROUP1                                 67        300        233         22
			 
			 
==============================================================================


<4> SQL for adding data file or extending space:	
    |
    |__ SQL> alter tablespace INDX add datafile '+DATA' size 1G autoextend on next 128M;
             alter tablespace BLOBS add datafile 'C:\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\TCM1\TEMP01.DBF' size 1G autoextend on next 128M maxsize 5G;
             alter database datafile '/u01/oradata/nebfprd/system02.dbf' autoextend on maxsize 1G;
             alter database datafile '+DATA/gdsp/datafile/aia_collect_data.283.883257183' autoextend on maxsize 30G;
             alter database datafile 'D:\DATABASE\ORADATA\CCSPROD\CCS_DATA02.DBF' autoextend on;
             alter database tempfile 'C:\oracle\product\12.1.0\dbhome_1\database\tcm1\TEMP01.DBF' size 17GB autoextend on;
             alter database datafile '+DATA/gcoedms2/datafile/allgco_data.575.865911639' RESIZE 3G;
             alter database tempfile 'D:\DATABASE\ORADATA\CCSPROD\LAPPS_TEMP01.DBF' RESIZE 10G;