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

    export / import (transfer) APEX users

    snmdla

      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.

       

         begin

         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      => '');

         end;

         /

       

      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