Shell script to export all APEX Workspace and Applications as SQL scripts

Environment Details:
Oracle 11gR2 , Application Express 5.1.4 Oracle Linux 6.4 64-bit, Java JDK 8u131
Go to the apex software directory
cd /u10test/oracle/R12/db/tech_st/11.2.0.4/apex/utilities
1.Script for Workspace Export
#!/bin/sh
# script to export all ApEx applications as sql scripts
CLASSPATH=$CLASSPATH:/u10test/oracle/R12/db/tech_st/11.2.0.4/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u01/apex_backup
cd /u10test/oracle/R12/db/tech_st/11.2.0.4/apex/utilities
usr/jdk/jdk1.7.0_80/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user system -password <SYSTEM password> -expWorkspace
mv f*.sql $BACKUPDIR
if [ -f $BACKUPDIR/apex_export.log ]
then
    cat $BACKUPDIR/apex_export.log|mail -s “APEX export – `hostname -s`” dba@test.com
fi
2.Script for Application Export
#!/bin/sh
# script to export all ApEx applications as sql scripts
CLASSPATH=$CLASSPATH:/u10test/oracle/R12/db/tech_st/11.2.0.4/oui/jlib/classes12.jar; export CLASSPATH
BACKUPDIR=/u01/apex_backup
cd /u10test/oracle/R12/db/tech_st/11.2.0.4/apex/utilities
usr/jdk/jdk1.7.0_80/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user system -password <SYSTEM password> -instance
mv f*.sql $BACKUPDIR
if [ -f $BACKUPDIR/apex_export.log ]
then
    cat $BACKUPDIR/apex_export.log|mail -s “APEX export – `hostname -s`” dba@test.com
fi
——————————————xxxxxxxxxxxxxxxxxxxx——————————-
IMPORT STEPS
1. On the target DB, create the DB schemas the APEX workspaces will be based upon and accomplish the standard DB import.
2. Accomplish the workspace import from sqlplus as the appropriate apex schema.  For example:
– connect sys as sysdba
alter user APEX_050100 identified by oracle123 account unlock;
– alter session set current_schema = APEX_050100;
– run the scripts to create the workspaces
3.cd APEX_APPLICATION/
[oracloud@testdb APEX_APPLICATION]$ ls -ltr
total 69436
-rwxr-xr-x. 1 root root 4857376 Jun 15 16:00 f100.sql
-rwxr-xr-x. 1 root root 1152903 Jun 15 16:00 f101.sql
-rwxr-xr-x. 1 root root 1591910 Jun 15 16:00 f102.sql
-rwxr-xr-x. 1 root root  221463 Jun 15 16:00 f103.sql
-rwxr-xr-x. 1 root root  384656 Jun 15 16:00 f104.sql
-rwxr-xr-x. 1 root root 3402983 Jun 15 16:00 f105.sql
ls -ltr|awk {‘print $9’}>hi.txt
 cat hi.txt|while read line
> do
> sqlplus “/ as sysdba”<<!
> alter session set current_schema = APEX_050100;
> @$line
> exit;
> !
> done
  • July 4, 2018 | 15 views
  • Comments