#########################################################################
#                                                                       #
#   This  script is designed with an  intention of  recreating the      #
#   temp  files automatically.This script will first create a temp      #
#   file  of its own then it  will drop   the existing  overloaded      #
#   tempfile. After that it will  recreate a new fresh tempfile in      #
#   same location  same  name  of  datafile even with same name of      #  
#   temp tablespace.No matter if there are more than one tempfiles      #
#   in that case it will consider only the tempfile with max size.      #
#   Now a condition is given in the code that it will recreate the      #
#   tempfile only if it has already grown up to or more than three      #
#   GB.If You  wish  to  recreate temp file with any size then use      #
#   different version of the code.                                      #
#                                                                       #
#                                                                       #
#########################################################################



# Creating temp table for sorting the max tempfile in case of more than one 
###########################################################################


create table temp_t as select * from dba_temp_files
/
truncate table temp_t
/
insert into temp_t 
select * 
  from dba_temp_files 
 where user_bytes=(
select max(user_bytes) 
  from dba_temp_files)
/



CREATE OR REPLACE PROCEDURE CREATE_TEMP_FOR_MORE
IS
        CNT             NUMBER(2);
        V_BYTES         NUMBER(10,3);
        V_TBS           VARCHAR2(30);
        V_FNAME         VARCHAR2(60);
        V_DATAFILE      VARCHAR2(20);
        V_STR           VARCHAR2(70);
        V_SQL1          VARCHAR2(300);
        V_SQL2          VARCHAR2(300);
        V_SQL3          VARCHAR2(300);
        V_SQL4          VARCHAR2(300);
        V_SQL5          VARCHAR2(300);
        V_SQL6          VARCHAR2(300);
BEGIN
        #Counting number of existing tempfile
        ##################################### 


        SELECT COUNT(*) 
          INTO CNT 
          FROM DBA_TEMP_FILES;


        IF CNT <> 1 THEN


                DBMS_OUTPUT.PUT_LINE('MORE THAN ONE TEMPFILES FOUND!! RECREATING THE MAX ONE ');


                #extracting the path
                #####################


                SELECT SUBSTR(FILE_NAME,1,LENGTH(FILE_NAME)-LENGTH(REVERSE(SUBSTR(REVERSE(FILE_NAME),1,INSTR(REVERSE(FILE_NAME),'\')-1))))
                  INTO V_STR 
                  FROM TEMP_T;


                #extracting the tablespace_name
                ###############################


                SELECT TABLESPACE_NAME 
                  INTO V_TBS
                  FROM TEMP_T;


                #extracting the total absolute path
                ####################################
                SELECT FILE_NAME  
                  INTO V_FNAME
                  FROM TEMP_T;


                #extracting datafile name
                ############################


                SELECT REVERSE(SUBSTR(REVERSE(FILE_NAME),1,INSTR(REVERSE(FILE_NAME),'\')-1))
                  INTO V_DATAFILE
                  FROM TEMP_T;


                # FOR CREATING TEMP TABLESPACE
                #################################


                V_SQL1:='CREATE TEMPORARY TABLESPACE TEMPTEMP TEMPFILE '''||V_STR||'TEMPTEMP.DBF'' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M';


                #FOR MAKING THE NEW TABLESPACE DEFAULT
                #######################################


                V_SQL2:='ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTEMP';


                #FOR DROPPING PREV TEMP TABLESPACE
                #######################################


                V_SQL3:='DROP TABLESPACE '||V_TBS||' INCLUDING CONTENTS AND DATAFILES';


                #FOR CREATING A NEW TEMP TABLESPACE WITH SAME NAME
                ###################################################


                V_SQL4:='CREATE TEMPORARY TABLESPACE '||V_TBS||' TEMPFILE '''||V_STR||V_DATAFILE||''' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M';


                #FOR MAKING IT AGAIN THE DEFAULT TEMP TABLESPACE
                #################################################


                V_SQL5:='ALTER DATABASE DEFAULT TEMPORARY TABLESPACE '||V_TBS;


                #FOR DROPPING THE TEMP TEMPORARY TABLESPACE
                ############################################


                V_SQL6:='DROP TABLESPACE TEMPTEMP INCLUDING CONTENTS AND DATAFILES';


                #checking the size of the tempfile
                ##################################


                SELECT USER_BYTES/1024/1024/1024 
                  INTO V_BYTES 
                  FROM TEMP_T;


                IF V_BYTES > 3  THEN


                        EXECUTE IMMEDIATE V_SQL1;
                        EXECUTE IMMEDIATE V_SQL2;
                        EXECUTE IMMEDIATE V_SQL3;
                        EXECUTE IMMEDIATE V_SQL4;
                        EXECUTE IMMEDIATE V_SQL5;
                        EXECUTE IMMEDIATE V_SQL6;


                        DBMS_OUTPUT.PUT_LINE('TEMPFILE RECREATED');
                ELSE
                        DBMS_OUTPUT.PUT_LINE('EVEN YOUR TEMFILE SIZE IS:'||V_BYTES||' GB');
                        DBMS_OUTPUT.PUT_LINE('RECREATION IS NOT REQUIRED RIGHT NOW!!');
                END IF;


                DROP TABLE TEMP_T;
                
        ELSE #THIS IS FOR ONLY ONE TEMP TABLE SPACE PRESENT IN THE DATABASE


                SELECT SUBSTR(FILE_NAME,1,LENGTH(FILE_NAME)-LENGTH(REVERSE(SUBSTR(REVERSE(FILE_NAME),1,INSTR(REVERSE(FILE_NAME),'\')-1))))
                  INTO V_STR 
                  FROM DBA_TEMP_FILES;


                SELECT TABLESPACE_NAME 
                  INTO V_TBS
                  FROM DBA_TEMP_FILES;


                SELECT FILE_NAME  
                  INTO V_FNAME
                  FROM DBA_TEMP_FILES;


                SELECT REVERSE(SUBSTR(REVERSE(FILE_NAME),1,INSTR(REVERSE(FILE_NAME),'\')-1))
                  INTO V_DATAFILE
                  FROM DBA_TEMP_FILES;


                #CREATING TEMP TABLESPACE
                ##########################


                V_SQL1:='CREATE TEMPORARY TABLESPACE TEMPTEMP TEMPFILE '''||V_STR||'TEMPTEMP.DBF'' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M';


                #FOR MAKING THE NEW TABLESPACE DEFAULT
                #######################################


                V_SQL2:='ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTEMP';


                #FOR DROPPING PREV TEMP TABLESPACE
                ####################################


                V_SQL3:='DROP TABLESPACE '||V_TBS||' INCLUDING CONTENTS AND DATAFILES';


                #FOR CREATING A NEW TEMP TABLESPACE WITH SAME NAME
                ###################################################


                V_SQL4:='CREATE TEMPORARY TABLESPACE '||V_TBS||' TEMPFILE '''||V_STR||V_DATAFILE||''' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M';


                #FOR MAKING IT AGAIN THE DEFAULT TEMP TABLESPACE
                ###################################################


                V_SQL5:='ALTER DATABASE DEFAULT TEMPORARY TABLESPACE '||V_TBS;


                #FOR DROPPING THE TEMP TEMPORARY TABLESPACE
                ############################################


                V_SQL6:='DROP TABLESPACE TEMPTEMP INCLUDING CONTENTS AND DATAFILES';


                SELECT USER_BYTES/1024/1024/1024 
                  INTO V_BYTES 
                  FROM DBA_TEMP_FILES;



                IF V_BYTES > 3  THEN


                        EXECUTE IMMEDIATE V_SQL1;
                        EXECUTE IMMEDIATE V_SQL2;
                        EXECUTE IMMEDIATE V_SQL3;
                        EXECUTE IMMEDIATE V_SQL4;
                        EXECUTE IMMEDIATE V_SQL5;
                        EXECUTE IMMEDIATE V_SQL6;
                        DBMS_OUTPUT.PUT_LINE('TEMPFILE RECREATED');
                ELSE
                        DBMS_OUTPUT.PUT_LINE('YOUR TEMFILE SIZE IS:'||V_BYTES||' GB');
                        DBMS_OUTPUT.PUT_LINE('RECREATION IS NOT REQUIRED RIGHT NOW!!');
                END IF; #END OF V_BYTES > 3
        END IF; #END OF TEMP TABLE SPACE 1
        
EXCEPTION
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(' PLEASE CHECK-->' || SQLCODE || ': ' || SQLERRM);
END;
/