/* OWB 9.0.4 and OWB 9.2 compliant.

Assmption: user dumps have been created using all defaults (i.e. grants
have also been exported). Usernames of runtime repository user and
target user that was exported will be the same as usernames that will be
used for import. If this prerequisite is not fulfilled, then grants will
have to be given differently; also location registration would have to
be updated. OWBRT_SYS has also been exported. If the user still exists,
then restore is obviously not needed.

*/

sqlplus /nolog

connect sys/<pw>@<db name> as sysdba

/* see comment in assumption!!! */
create user <owb_runtime_repos_user> identified by <owb_runtime_repos_user_pw>
default tablespace <default_tablespace>
temporary tablespace <temporary_tablespace>
/

grant CONNECT to <owb_runtime_repos_user>;
grant RESOURCE to <owb_runtime_repos_user>;
grant AQ_ADMINISTRATOR_ROLE to <owb_runtime_repos_user>;
grant AQ_USER_ROLE to <owb_runtime_repos_user>;
grant JAVADEBUGPRIV to <owb_runtime_repos_user>;
grant JAVAIDPRIV to <owb_runtime_repos_user>;
grant JAVASYSPRIV to <owb_runtime_repos_user>;
grant JAVAUSERPRIV to <owb_runtime_repos_user>;
grant JAVA_ADMIN to <owb_runtime_repos_user>;
grant JAVA_DEPLOY to <owb_runtime_repos_user>;

grant alter session to <owb_runtime_repos_user>;
grant unlimited tablespace to <owb_runtime_repos_user>;
grant administer database trigger to <owb_runtime_repos_user>;

grant execute on dbms_lock to <owb_runtime_repos_user>;
grant execute on dbms_aq to <owb_runtime_repos_user>;
grant execute on dbms_aqadm to <owb_runtime_repos_user>;

/* see comment in assumption!!! */
create user <owb_runtime_access_user> identified by <owb_runtime_access_user_pw>
default tablespace <default_tablespace>
temporary tablespace <temporary_tablespace>
/

grant connect to <owb_runtime_access_user> ;

/* see comment in assumption!!! */
create user <owb_target_user> identified by <owb_target_user_pw>
default tablespace <default_tablespace>
temporary tablespace <temporary_tablespace>
/

grant CONNECT to <owb_target_user>;
grant RESOURCE to <owb_target_user>;
grant SELECT_CATALOG_ROLE to <owb_target_user>;
grant HS_ADMIN_ROLE to <owb_target_user>;
grant AQ_USER_ROLE to <owb_target_user>;

grant CREATE VIEW to <owb_target_user>;
grant CREATE TABLE to <owb_target_user>;
grant ALTER SESSION to <owb_target_user>;
grant CREATE SESSION to <owb_target_user>;
grant CREATE SYNONYM to <owb_target_user>;
grant CREATE TRIGGER to <owb_target_user>;
grant CREATE ANY TYPE to <owb_target_user>;
grant CREATE SEQUENCE to <owb_target_user>;
grant CREATE SNAPSHOT to <owb_target_user>;
grant CREATE DIMENSION to <owb_target_user>;
grant CREATE INDEXTYPE to <owb_target_user>;
grant CREATE PROCEDURE to <owb_target_user>;
grant DROP ANY DIRECTORY to <owb_target_user>;
grant DROP PUBLIC SYNONYM to <owb_target_user>;
grant CREATE ANY DIRECTORY to <owb_target_user>;
grant CREATE DATABASE LINK to <owb_target_user>;
grant GLOBAL QUERY REWRITE to <owb_target_user>;
grant UNLIMITED TABLESPACE to <owb_target_user>;
grant CREATE PUBLIC SYNONYM to <owb_target_user>;
grant SELECT ANY DICTIONARY to <owb_target_user>;

grant execute on dbms_aq to <owb_target_user>;
grant execute on dbms_aqadm to <owb_target_user>;

/* Create OWBRT_SYS only if user does not exist anymore. */
create user owbrt_sys identified by owbrt_sys
default tablespace <default_tablespace>
temporary tablespace <temporary_tablespace>
/

grant connect to owbrt_sys ;
grant resource to owbrt_sys ;

grant INSERT ANY TABLE to owbrt_sys;
grant SELECT ANY TABLE to owbrt_sys;
grant DROP PUBLIC SYNONYM to owbrt_sys;
grant CREATE ANY PROCEDURE to owbrt_sys;
grant UNLIMITED TABLESPACE to owbrt_sys;
grant CREATE PUBLIC SYNONYM to owbrt_sys;
grant EXECUTE ANY PROCEDURE to owbrt_sys;

create role wb_d_<owb_runtime_repos_user>;
revoke wb_d_<owb_runtime_repos_user> from sys;
create role wb_r_<owb_runtime_repos_user>;
revoke wb_r_<owb_runtime_repos_user> from sys;
create role wb_u_<owb_runtime_repos_user>;
revoke wb_u_<owb_runtime_repos_user> from sys;
create role wb_a_<owb_runtime_repos_user>;
revoke wb_a_<owb_runtime_repos_user> from sys;

grant wb_u_<owb_runtime_repos_user> to wb_d_<owb_runtime_repos_user>;
grant wb_u_<owb_runtime_repos_user> to wb_r_<owb_runtime_repos_user>;
grant wb_u_<owb_runtime_repos_user> to wb_a_<owb_runtime_repos_user>;

grant wb_d_<owb_runtime_repos_user> to <owb_runtime_access_user>;
grant wb_r_<owb_runtime_repos_user> to <owb_runtime_access_user>;
grant wb_a_<owb_runtime_repos_user> to <owb_runtime_access_user>;
grant wb_u_<owb_runtime_repos_user> to <owb_runtime_access_user>;

alter user <owb_runtime_access_user> default role connect ;

/* Import dump files in the following order: OWBRT_SYS, <owb_runtime_repos_user>, <owb_runtime_access_user>, <owb_target_user>. */
imp owbrt_sys/owbrt_sys@<db name>

/* Next import will generate a number of compilation warnings due to invalid view definitions. */
imp <owb_runtime_repos_user>/<owb_runtime_repos_user_pw>@<db name>
/* Before continuing, make sure all objects in schema <owb_runtime_repos_user> are valid, either using dbms_utility.compile_schema or via alter <object_type> <object name> compile (body; in case of package bodies). All objects should validate correctly. */

imp <owb_runtime_access_user>/<owb_runtime_access_user_pw>@<db name>

/* If you created external tables that use directory definitions you may have to create those definitions beforehand or grant the privileges to the user (if the directories already exist). Otherwise you get import warnings.
If you have advanced queues in the target schema, you will have to grant AQ_ADMINISTRATOR_ROLE to <owb_target_user> (connect as sys) before you start the import. IMP-01031 Insufficient privileges is the error message you will get otherwise. As the AQ_ADMINISTRATOR_ROLE is not a standard role, consider to revoke it again after the import.
If you have used the XML toolkit or other Java procedures, grant JAVASYSPRIV to <owb_target_user> (connect as sys) before you start the import. IMP-01031 Insufficient privileges is the error message you will get otherwise. Consider revoking after the import has finished.
If you explicitly granted other system or object privileges to the target schema then you will have to restore those also.
*/
imp <owb_target_user>/<owb_target_user_pw>@<db name>

/* Runtime platform service should be available. Consider checking using <owb home>\owb\rtp\sql\service_doctor.sql. */