i have an archiving script currently implemented in our system. the script works, but i've noticed that the processing time becomes longer than the previous run each time it is run. the files being deleted are roughly of the same size... pls. help
# *** Verify parameters here.
if [[ $1 = '' ]]
then :
echo
echo "Parameter requires for Table Name."
echo "archive_summ_data.sh failed."
echo
echo "Syntax : Archive_summ_data.sh [Table Name] [Dateineffective of previous month]"
echo
exit 1
fi
if [[ $2 = '' ]]
then :
echo
echo "Parameter requires for Start Date."
echo "archive_summ_data.sh failed."
echo
echo "Syntax : Archive_summ_data.sh [Table Name] [Dateineffective of previous month]"
echo
exit 1
fi
echo
echo "archive_summ_data.sh started..."
echo
sqlplus -s pro/prol << EOF > $1.im.log
WHENEVER OSERROR EXIT OSCODE ROLLBACK ;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK ;
PROMPT
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "Time Start" from v_system_date;
PROMPT
PROMPT
PROMPT Creating Temp SUMMARY_DEFINITION table
PROMPT
create table ${1}_summary_definition tablespace ARCHIVE_SUMMARY_DATA
as
select a.*
from summary_definition a,
report_line_summary b,
iaa_period c
where a.dailysummary = b.dailysummary
and b.iaaperiod = c.iaaperiod
and c.dateineffective = TO_DATE('$2','dd-mon-yyyy')
/
insert into ${1}_summary_definition
select a.*
from summary_definition a,
ws_summary b
where a.wssummary = b.wssummary
and b.agreement = -1
and a.summarydate <= TO_DATE('$2','dd-mon-yyyy')
/
PROMPT
select count(*) from ${1}_summary_definition
/
PROMPT
PROMPT Temp SUMMARY_DEFINITION table created
PROMPT
PROMPT
PROMPT Creating Temp DAILY_SUMMARY table
PROMPT
create table ${1}_daily_summary tablespace ARCHIVE_SUMMARY_DATA
as
select a.*
from daily_summary a,
report_line_summary b,
iaa_period c
where a.dailysummary = b.dailysummary
and b.iaaperiod = c.iaaperiod
and c.dateineffective = TO_DATE('$2','dd-mon-yyyy')
/
insert into ${1}_daily_summary
select a.*
from daily_summary a,
summary_definition b,
ws_summary c
where a.dailysummary = b.dailysummary
and b.wssummary = c.wssummary
and c.agreement = -1
and b.summarydate <= TO_DATE('$2','dd-mon-yyyy')
/
PROMPT
select count(*) from ${1}_daily_summary
/
PROMPT
PROMPT Temp DAILY_SUMMARY table created
PROMPT
PROMPT
PROMPT Creating Temp REPORT_LINE_SUMMARY table
PROMPT
create table ${1}_report_line_summary tablespace ARCHIVE_SUMMARY_DATA
as
select * from report_line_summary
/
PROMPT
select count(*) from ${1}_report_line_summary
/
PROMPT
PROMPT Temp REPORT_LINE_SUMMARY table created
PROMPT
PROMPT
PROMPT Creating Temp ELEMENT_SUMMARY table
PROMPT
create table ${1}_element_summary tablespace ARCHIVE_SUMMARY_DATA
as
select a.*
from element_summary a,
report_line_summary b,
iaa_period c
where a.dailysummary = b.dailysummary
and b.iaaperiod = c.iaaperiod
and c.dateineffective = TO_DATE('$2','dd-mon-yyyy')
/
PROMPT
select count(*) from ${1}_element_summary
/
PROMPT
PROMPT Temp ELEMENT_SUMMARY table created
PROMPT
PROMPT
PROMPT Deleting Records from SUMMARY_DEFINITION table
PROMPT
delete from summary_definition
/
PROMPT
PROMPT Records Deleted.
PROMPT
PROMPT
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "Time Finish" from v_system_date;
PROMPT
PROMPT
PROMPT Checking Table sizes and Tablespace Free Space
PROMPT
set pagesize 30
set feedback off
set heading on
col "Table Name" format a40
select segment_name "Table Name",sum(bytes/1024/1024) "Spaced Used (Mb)" from user_segments
where tablespace_name = 'ARCHIVE_SUMMARY_DATA'
group by segment_name order by 1
/
select tablespace_name , sum(bytes/1024/1024) "Free Space Available (Mb)"
from dba_free_space
where tablespace_name = 'ARCHIVE_SUMMARY_DATA'
group by tablespace_name
/
PROMPT
PROMPT
exit
EOF
exit
# *** Verify parameters here.
if [[ $1 = '' ]]
then :
echo
echo "Parameter requires for Table Name."
echo "archive_summ_data.sh failed."
echo
echo "Syntax : Archive_summ_data.sh [Table Name] [Dateineffective of previous month]"
echo
exit 1
fi
if [[ $2 = '' ]]
then :
echo
echo "Parameter requires for Start Date."
echo "archive_summ_data.sh failed."
echo
echo "Syntax : Archive_summ_data.sh [Table Name] [Dateineffective of previous month]"
echo
exit 1
fi
echo
echo "archive_summ_data.sh started..."
echo
sqlplus -s pro/prol << EOF > $1.im.log
WHENEVER OSERROR EXIT OSCODE ROLLBACK ;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK ;
PROMPT
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "Time Start" from v_system_date;
PROMPT
PROMPT
PROMPT Creating Temp SUMMARY_DEFINITION table
PROMPT
create table ${1}_summary_definition tablespace ARCHIVE_SUMMARY_DATA
as
select a.*
from summary_definition a,
report_line_summary b,
iaa_period c
where a.dailysummary = b.dailysummary
and b.iaaperiod = c.iaaperiod
and c.dateineffective = TO_DATE('$2','dd-mon-yyyy')
/
insert into ${1}_summary_definition
select a.*
from summary_definition a,
ws_summary b
where a.wssummary = b.wssummary
and b.agreement = -1
and a.summarydate <= TO_DATE('$2','dd-mon-yyyy')
/
PROMPT
select count(*) from ${1}_summary_definition
/
PROMPT
PROMPT Temp SUMMARY_DEFINITION table created
PROMPT
PROMPT
PROMPT Creating Temp DAILY_SUMMARY table
PROMPT
create table ${1}_daily_summary tablespace ARCHIVE_SUMMARY_DATA
as
select a.*
from daily_summary a,
report_line_summary b,
iaa_period c
where a.dailysummary = b.dailysummary
and b.iaaperiod = c.iaaperiod
and c.dateineffective = TO_DATE('$2','dd-mon-yyyy')
/
insert into ${1}_daily_summary
select a.*
from daily_summary a,
summary_definition b,
ws_summary c
where a.dailysummary = b.dailysummary
and b.wssummary = c.wssummary
and c.agreement = -1
and b.summarydate <= TO_DATE('$2','dd-mon-yyyy')
/
PROMPT
select count(*) from ${1}_daily_summary
/
PROMPT
PROMPT Temp DAILY_SUMMARY table created
PROMPT
PROMPT
PROMPT Creating Temp REPORT_LINE_SUMMARY table
PROMPT
create table ${1}_report_line_summary tablespace ARCHIVE_SUMMARY_DATA
as
select * from report_line_summary
/
PROMPT
select count(*) from ${1}_report_line_summary
/
PROMPT
PROMPT Temp REPORT_LINE_SUMMARY table created
PROMPT
PROMPT
PROMPT Creating Temp ELEMENT_SUMMARY table
PROMPT
create table ${1}_element_summary tablespace ARCHIVE_SUMMARY_DATA
as
select a.*
from element_summary a,
report_line_summary b,
iaa_period c
where a.dailysummary = b.dailysummary
and b.iaaperiod = c.iaaperiod
and c.dateineffective = TO_DATE('$2','dd-mon-yyyy')
/
PROMPT
select count(*) from ${1}_element_summary
/
PROMPT
PROMPT Temp ELEMENT_SUMMARY table created
PROMPT
PROMPT
PROMPT Deleting Records from SUMMARY_DEFINITION table
PROMPT
delete from summary_definition
/
PROMPT
PROMPT Records Deleted.
PROMPT
PROMPT
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "Time Finish" from v_system_date;
PROMPT
PROMPT
PROMPT Checking Table sizes and Tablespace Free Space
PROMPT
set pagesize 30
set feedback off
set heading on
col "Table Name" format a40
select segment_name "Table Name",sum(bytes/1024/1024) "Spaced Used (Mb)" from user_segments
where tablespace_name = 'ARCHIVE_SUMMARY_DATA'
group by segment_name order by 1
/
select tablespace_name , sum(bytes/1024/1024) "Free Space Available (Mb)"
from dba_free_space
where tablespace_name = 'ARCHIVE_SUMMARY_DATA'
group by tablespace_name
/
PROMPT
PROMPT
exit
EOF
exit