Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

reg: deletion of dump files

chandra_1986May 6 2021

HI Experts,

have written the below script:-EXP_TAB_cmprss.sh to take the export backup through script.

Need to add in the start of script to delete export dump 3 days old and then start export backup,
script should not execute without deleting old backups,
if it fails to delete old backup , script should terminate and send us alert.

# $Header: EXP_TAB_cmprss.sh
# *====================================================================================+

# | |
# +====================================================================================+
# |
# | FILENAME
# |
# |
# | DESCRIPTION
# | Daily Export backup script of a list of table
# | PLATFORM
# | Linux/Solaris

# +===========================================================================+
#!/bin/bash
echo Set Oracle Database Env
export ORACLE_SID=$1
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin
export TIMESTAMP=`date +%a%d%b%Y`
export EXP_DIR=/export/home/oracle

echo =======
echo Export command
echo =======
echo $ORACLE_HOME
$ORACLE_HOME/bin/expdp \'/ as sysdba\' directory=DB_DUMPS dumpfile=expdp_tab_${TIMESTAMP}_%U.dmp logfile=expdp_log_${TIMESTAMP}.log
tables=DBATEST.ORDER_TAB PARALLEL=5 COMPRESSION=ALL

echo SEND MAIL TO STAKE HOLDERS
echo =======
mailx -s "$ORACLE_SID $TIMESTAMP Export backup logfile" support@dbclass.com < $EXP_DIR/expdp_log_${TIMESTAMP}.log
echo Export completed at $TIMESTAMP
exit

in EXP_DIR we have below files generated after running script

-rwxrwxrwx 1 tuser tuser 1.4K May 6 12:25 EXP_TAB_cmprss.sh
-rwxrwxrwx 1 t2n5463 odba 3.2G May 6 13:13 expdp_tab_Thu06May2021_05.dmp
-rwxrwxrwx 1 t2n5463 odba 3.1G May 6 13:13 expdp_tab_Thu06May2021_03.dmp
-rwxrwxrwx 1 t2n5463 odba 22G May 6 13:13 expdp_tab_Thu06May2021_02.dmp
-rwxrwxrwx 1 t2n5463 odba 2.9G May 6 13:13 expdp_tab_Thu06May2021_04.dmp
-rwxrwxrwx 1 t2n5463 odba 25G May 6 13:13 expdp_tab_Thu06May2021_01.dmp
-rw-r--r-- 1 t2n5463 odba 8.7K May 6 13:13 expdp_log_Thu06May2021.log

Any suggestion how can we add inside the script to , add in the start of script to delete export dump 3 days old and then start export backup,
script should not execute without deleting old backups,
if it fails to delete old backup , script should terminate and send us alert.

Comments

Paulzip

You only have to worry about objects that reference the invalid objects (directly or through other dependencies), not objects that the invalid objects reference (caveated that they are not referenced by another invalid object).

E.g. Dropping PLAP/TEMP_DIS_CONSTR/PROCEDURE, won't affect SYS/ALL_CONS_COLUMNS/VIEW

1 person found this helpful
BEDE

That depends...

Some of those invalid packages/procedures/functions/views may need to have their code changed so that they may be used later on. So, I don't think it is the case to rush into such spring cleaning. First use dbms_utility.compile_schema(x_schema,false) to see what can recompile, then send all the developers you know to be working on that database a list of the invalid objects, eventually including also the errors, with the warning that, if in within two weeks they won't fix them, then they will be dropped.

Yet, if your concern is space, tables are those that take most of the space.

1 person found this helpful
4089142

Thanks so much! That's exactly what I did. First I tried to see what invalid objects I could recompile (except I used @utlrp.sql). Then, I sent all the developers a list of the invalid objects with each error included so that they can look through and fix the code if need be.

4089142

Thanks for the clear and concise answer Paulzip! This is exactly what I needed to know!

1 - 4

Post Details

Added on May 6 2021
#plsql, #sqldeveloper
6 comments
1,854 views