Everyday Task Reference

How to Compile a package Body

ALTER PACKAGE <package_name> COMPILE BODY;

SQL > alter package DBMS_REPCAT_MIGRATION compile body;

Package body altered.

Usage of tar command in Unix

You cannot use gzip command to compress multiple files into a single file. gzip command will compress each file individually and create individual .gz file. gzip command is good for zipping single file.

tar command can be used to combine multiple files and create a single file (called a tarball).

Command to place all your files in a single tar file (Not compressed)
tar -cvf mydb_datapump.tar mydb_datapump*.dmp


Command to place all your files in a single tar file and then compress it
tar -cvzf mydb_datapump.tar.gz mydb_datapump*.dmp

Command to extract all your files from a single tar file (Not zipped)
tar -xvf mydb_datapump.tar


Command to extract all your files from a single tar and zipped file
tar -xvzf mydb_datapump.tar.gz

Command to ONLY List out all the files from a tar file (No Extract)
tar -tzf mydb_datapump.tar.gz

Unix Shell Script to Generate DataPump file with Date & Time on the DumpFile Name

The script will also zip the dump files at the end and delete any dump files, which are older than 30 days.


---------------------------------------------------------------------------------------
$ cat expfull_dp_prod.sh
export ORACLE_SID=proddb

. /home/oracle/.bashrc

export BACKUP_DIR=/u04/datapump/proddb
export v_date_time=`date '+%m%d%Y_%H%M%S'`

expdp dumpfile=expfull_dp_proddb_%U_${v_date_time}.dmp parfile=/u04/datapump/proddb/expfull_dp_proddb.par

# Create a TARBALL compressed file from all the dump files.
cd ${BACKUP_DIR}
tar -cvzf ${BACKUP_DIR}/expfull_dp_proddb_${v_date_time}.dmp.tar.gz expfull_dp_proddb*${v_date_time}.dmp

## Deleting files older that 30 days
find ${BACKUP_DIR} -name "expfull_dp_proddb*.dmp*" -mtime +30 -exec rm -f {} \;

cat ${BACKUP_DIR}/expfull_dp_proddb.log | mailx -s "${ORACLE_SID} - Full Export Datapump Completed" "email@email.com"
---------------------------------------------------------------------------------------
Par file
---------------------------------------------------------------------------------------
$ cat /u04/datapump/proddb/expfull_dp_proddb.par
userid=system/password
directory=data_pump_dir
logfile=expfull_dp_proddb.log
full=y
parallel=4
LOGTIME=ALL
CONSISTENT=Y
COMPRESSION=ALL
REUSE_DUMPFILES=Y
$
---------------------------------------------------------------------------------------