cheng01120

Recreate temp files

#########################################################################
# #
# 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;
/

posted by cheng01120 @ 7:28 PM