brief technical note on images export import to new apex 41 database from hpux 11gr2 to linux 11gr2

We created a database on our new platform, installed apex in it with apexins.sql

Then we made a full export, we imported everything except apex_040100 user
 

Next we created workspaces and applications in the new environment.

We exported these workspace and applications with scripting from the internet.

the apexexport utility in apex41 gave empty files as a result.

INFO APEXEXport $ cd ..apex41/apex/utilities $ export CLASSPATH=.:${ORACLE_HOME}/oui/jlib/classes12.jar $ read wachtwoord # exporteer eerst de WORKSPACES middels test_export $ java  oracle.apex.APEXExport -db  HOSTNAME:1521:DATABASE_SERVICE -user system -password $wachtwoord -instance

Gave me the export of the applications
But the workspace files were empty, THIS is a bug.

https://apexplained.wordpress.com/2012/03/20/workspace-application-and-page-export-in-plsql/

I used to export the workspaces.
I installed the procedures and then used :

REM desc PRO_EXPORT_WORKSPACE
REM PROCEDURE PRO_EXPORT_WORKSPACE
REM  Argument Name                  Type                    In/Out Default?
REM  ------------------------------ ----------------------- ------ --------
REM  P_WORKSPACE_ID                 NUMBER                  IN
REM  P_INCLUDE_TEAM_DEV             BOOLEAN                 IN
REM  P_ORA_DIR                      VARCHAR2                IN
REM
REM MY_ORA_DIR
REM
col workspace_id format 999999999999999999
declare
cursor c_ws is
select workspace_id from apex_workspaces where workspace_id > 11 ;
r_ws c_ws%rowtype ;
begin
 for r_ws in c_ws
 loop
   PRO_EXPORT_WORKSPACE( r_ws.workspace_id , TRUE,'MY_ORA_DIR') ;
 end loop ;
end;
/

We exported the source data with datapump to file dp_EVERYTHING_%U.dmp

expdp full=y DIRECTORY=EXP_DB DUMPFILE=dp_EVERYTHING%u LOGFILE=EXP_DB:dp_EVERYTHING.log FILESIZE=20G PARALLEL=4 FLASHBACK_TIME=SYSTIMESTAMP

We created a database directory called IMP_DB in the target database.

$ cat images_impdp.par

DUMPFILE=dp_EVERYTHING%U.dmp
parallel=4
directory=IMP_DB
table_exists_action=append
tables=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$

$ cat images_impdp_WWV_FLOW_REPOSITORIES_TO_DIKPATER.par

DUMPFILE=dp_EVERYTHING%U.dmp
parallel=4
directory=IMP_DB
tables=APEX_040100.WWV_FLOW_CSS_REPOSITORY,APEX_040100.WWV_FLOW_IMAGE_REPOSITORY,APEX_040100.WWV_FLOW_HTML_REPOSITORY

remap_schema=(APEX_040100:DIKPATER)
remap_tablespace=(SYSAUX:USERS)

SQL> alter table flows_files."WWV_FLOW_FILE_OBJECTS$" disable all triggers ;
$ impdp parfile=images_impdp.par

SQL> alter table flows_files."WWV_FLOW_FILE_OBJECTS$" enable all triggers ;

Now we handle :

 apex_040100.WWV_FLOW_IMAGE_REPOSITORY
 apex_040100.WWV_FLOW_CSS_REPOSITORY
 apex_040100.WWV_FLOW_HTML_REPOSITORY

!!! create a user DIKPATER ( in my case) to hold the 3 REPOSITORY tables temporary.
!!! with tablespace quota ...and create table

$ impdp parfile=images_impdp_WWV_FLOW_REPOSITORIES_TO_DIKPATER.par

SQL> select * from all_objects where lower(object_name)like '%wwv_flow_%repository%'

and owner = 'APEX_040100' and object_type = 'TABLE'

SQL> select * from apex_040100.WWV_FLOW_CSS_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_CSS_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_CSS_REPOSITORY
select * from dikpater.WWV_FLOW_CSS_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_CSS_REPOSITORY
enable all triggers ;

SQL> select * from APEX_040100.WWV_FLOW_IMAGE_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_IMAGE_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_IMAGE_REPOSITORY
select * from dikpater.WWV_FLOW_IMAGE_REPOSITORY
where (flow_id, image_name, security_group_id) not in ( select flow_id, image_name, security_group_id from apex_040100.WWV_FLOW_IMAGE_REPOSITORY) ;
SQL> alter table apex_040100.WWV_FLOW_IMAGE_REPOSITORY
enable all triggers ;

SQL> alter table apex_040100.WWV_FLOW_HTML_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_HTML_REPOSITORY
select * from dikpater.WWV_FLOW_HTML_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_HTML_REPOSITORY
enable all triggers

Now the images are imported and ready to use.
I found many hits on the internet about missing images after import, they suggest just to import WWV_FLOW_FILE_OBJECTS$ alone, but in my opinion it is not enough.
I cannot find if this is a supported method, but maybe this can be helpfull.

Regards.

Dik Pater