0 Replies Latest reply: Jul 25, 2013 2:07 AM by snmdla RSS

    export / import (transfer) APEX users


      As a Web search did not give me a apt HOW-TO, I'm writing this post:


      From time to time you will need to transfer user definitions from an APEX installation to another.


      If you don't have the option to transfer the whole workspace, you can go this way:


      1st do an export of the whole workspace:


      Application Builder / Tasks / Export

      Export Workspace <Export Workspace>


      The resulting export file will contain one

      "wwv_flow_fnd_user_api.create_fnd_user" statement per user, e.g.



         wwv_flow_fnd_user_api.create_fnd_user (

           p_user_id      => '6103225899908067',

           p_user_name    => 'XYZ',

           p_first_name   => '',

           p_last_name    => '',

           p_description  => '',

           p_email_address=> 'some.user@some.domain',

           p_web_password => 'YXZYXZYXZYXZYXZYXZYXZYXZYZXZYXZY',

           p_web_password_format => 'HEX_ENCODED_DIGEST_V2',

           p_group_ids                    => '',

           p_developer_privs              => '',

           p_default_schema               => 'ASCHEMA,

           p_account_locked               => 'N',

           p_account_expiry               => to_date('201304240000','YYYYMMDDHH24MI'),

           p_failed_access_attempts       => 0,

           p_change_password_on_first_use => 'Y',

           p_first_password_use_occurred  => 'N',

           p_allow_app_building_yn        => 'N',

           p_allow_sql_workshop_yn        => 'N',

           p_allow_websheet_dev_yn        => 'N',

           p_allow_team_development_yn    => 'Y',

           p_allow_access_to_schemas      => '');




      Extract those statements into a SQL script, which can then be imported

      into the SQL Workbench and run there in the Workspace context.


      For existing users the script execution will report a message like


         User was not created for the following reason: ORA-00001: unique constraint (APEX_0x0x00.WWV_FLOW_FND_USER_PK) violated


      This can be savely ignored.


      Lastly, consider deleting the export file, as this contains the user

      password hashes.


      HTH, Tom