OraMatt: YABAOracle

Use these 2 scripts together and you should be good to go…

Here’s the DataPump Export Script

#!/bin/bash
   #===================================================================================
   #
   #         FILE: expdp_helper.sh
   #
   #        USAGE: Run from command line
   #
   #  DESCRIPTION: DataPump Export of database
   #      OPTIONS:  
   # REQUIREMENTS: 
   #       AUTHOR: Matt D
   #      CREATED: August 5, 2011
   #	  UPDATED: June 21, 2012
   #      UPDATED: August 3, 2012	
   #          -- Changed it to do only schema level export
   #      VERSION: 2.0
   #
   #
   #
   #
   #
   #
   #===================================================================================
clear screen

# Get the directory path to store the dump files
echo ---------------------------------------
echo "Please enter the path for the dump files > "
read EXPDIR
# Check for directory & create if not there
if [ ! -d ${EXPDIR} ] ; 
	then
	echo "Looks like ${EXPDIR} doesn't exist..."
	echo "I'll try and make it for you."
	mkdir -p ${EXPDIR}
fi

	

# Get the source database name
echo ---------------------------------------
echo "Please enter the TNS Name for the source database > "
read EXP_DBNAME

# Get the system password
echo ---------------------------------------
echo "Please enter the password for the database system user > "
read SYSPASS

# Get the user name 
echo ---------------------------------------
echo "Please enter the USERNAME for the export > "
read EXP_USERNAME

# Get the user password
echo ---------------------------------------
echo "Please enter the password for the database user > "
read EXP_USERPASS



# Create the directory on the OS and directory object in the database
# Create a file to recreate the tablespaces 
mkdir -p $EXPDIR

export ORACLE_SID=${EXP_DBNAME}
sqlplus -s system/${SYSPASS}@${EXP_DBNAME}<<EOF
drop directory dbexport;
create directory dbexport as '${EXPDIR}';
grant read,write on directory dbexport to public;
grant exp_full_database to ${EXP_USERNAME};
spool ${EXPDIR}/create_tablespaces.sql
select 'create bigfile tablespace '||tablespace_name|| ';' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','APEX') AND tablespace_name NOT LIKE 'UNDO%';
spool off;
exit;
EOF



# Create a shell script to perform the export
echo "export ORACLE_SID=${EXP_DBNAME}" >${EXP_USERNAME}_EXPORT.sh
echo "expdp ${EXP_USERNAME}/${EXP_USERPASS}@${EXP_DBNAME} DIRECTORY=dbexport SCHEMAS=${EXP_USERNAME} JOB_NAME=${EXP_USERNAME}_EXPORT FILESIZE=250M DUMPFILE=${EXP_USERNAME}_EXPORT_%U.dmp COMPRESSION=ALL LOGFILE=${EXP_USERNAME}_export.log PARALLEL=32" >>${EXP_USERNAME}_EXPORT.sh
chmod 755 ${EXP_USERNAME}_EXPORT.sh
chmod 755 ${EXP_USERNAME}_EXPORT.sh


# Tell user to run the DataPump Export
echo "Please run ${EXP_USERNAME}_EXPORT.sh to export the database"

Here’s the DataPump Import Script

#!/bin/bash
   #===================================================================================
   #
   #         FILE: impdp_helper.sh
   #
   #        USAGE: Run from the command line
   #
   #  DESCRIPTION: DataPump Import of database
   #      OPTIONS:  
   # REQUIREMENTS: 
   #       AUTHOR: Matt D
   #      CREATED: June 22, 2012
   #      UPDATED: August 3, 2012
   #      VERSION: 1.5
   #
   #
   #
   #
   #
   #
   #===================================================================================
clear screen

# Get the directory where the dump files are stored
echo ---------------------------------------
echo "Please enter the path for where the dump files are > "
read IMPDIR


# Get the target database name
echo ---------------------------------------
echo "Please enter the TNS Name for the target database > "
read DBNAME

# Get the system password
echo ---------------------------------------
echo "Please enter the password for the database system user > "
read SYSPASS

# Get the user name 
echo ---------------------------------------
echo "Please enter the username for the import > "
read USERNAME

# Get the user password
echo ---------------------------------------
echo "Please enter the password for the database user > "
read USERPASS


# Check to see if the dump are where you said they were
if [ -d ${IMPDIR} ]
then
    echo "Bingo...dump directory exists"
	FILES=`ls ${IMPDIR}/*.dmp  |wc -l`
		if [ "$FILES" = "0" ]
			then
				echo "NO DUMP files in ${IMPDIR}"
				echo "exiting"
				exit 1
		fi		
	DUMPFILES=`ls *01.dmp |sed 's/01/%U/g'`
	echo $DUMPFILES
fi


# Set up the target database for the import
export ORACLE_SID=${DBNAME}
sqlplus -s system/${SYSPASS}@${DBNAME}<<EOF
spool ${USERNAME}_import_setup.log
drop directory dbimport;
create directory dbimport as '${IMPDIR}';
grant read,write on directory dbimport to public;
grant imp_full_database to ${USERNAME} identified by ${USERNAME};
grant resource to ${USERNAME};
alter user ${USERNAME} quota unlimited on USERS;
-- create target tablespaces
@${IMPDIR}/create_tablespaces.sql
exit;
EOF



# Create a shell script to perform the import
echo "export ORACLE_SID=${DBNAME}" >${USERNAME}_IMPORT.sh
echo "impdp ${USERNAME}/${USERPASS}@${DBNAME} DIRECTORY=dbimport SCHEMAS=${USERNAME} JOB_NAME=${USERNAME}_IMPORT DUMPFILE=${DUMPFILES} LOGFILE=${USERNAME}_IMPORT.log PARALLEL=32" >>${USERNAME}_IMPORT.sh
chmod 755 ${USERNAME}_IMPORT.sh
chmod 755 ${USERNAME}_IMPORT.sh


# Tell user to run the DataPump Export
echo "Please run ${USERNAME}_IMPORT.sh to export the database"