set heading off
set feedback off
set verify off
set pages 0
column nl newline
set lines 132
set scan off
PROMPT 
PROMPT    Generating script to migrate Oracle database users...

set termout off

spool migrate_repos_users.sql
PROMPT rem
PROMPT rem   PURPOSE : Migrates Oracle database users from an Oracle Designer Release 2.1.2/6.0 
PROMPT rem             Repository to a Designer 6.5 Repository
PROMPT rem
PROMPT rem
PROMPT rem   ****************************************************************************
PROMPT rem   * To execute this script on your Designer 6.5 Repository                   *
PROMPT rem   *                                                                          *
PROMPT rem   * Syntax, from within SQL*Plus connected as the SYS user type:             *
PROMPT rem   * SQL> @migrate_repos_users.sql                                            *
PROMPT rem   *                                                                          *
PROMPT rem   ****************************************************************************
PROMPT
PROMPT 
PROMPT spool migrate_repos_users.log
PROMPT
PROMPT prompt
PROMPT prompt
PROMPT prompt Migrating Oracle database users to the Designer 6.5 Repository...
PROMPT 
PROMPT -- Select the available tablespaces
PROMPT prompt
PROMPT prompt Selecting available tablespaces...
PROMPT prompt
PROMPT select tablespace_name, contents, status from dba_tablespaces;;
PROMPT prompt
PROMPT -- Prompt for a default tablespace for the user
PROMPT accept default_tbsp prompt 'Please enter the default tablespace for the user: ' 
PROMPT 
PROMPT -- Prompt for a temporary tablespace for the user
PROMPT prompt Please enter the value for temporary tablespace
PROMPT accept temporary_tbsp default &&default_tbsp prompt '(defaulted to &&DEFAULT_TBSP): '   
PROMPT
PROMPT set termout off
PROMPT set verify off
PROMPT set heading off
PROMPT

select
 ' ' nl,
 ' ' nl,
 '-- Create user ' || '''' || su.usr_oracle_username || '''' nl, 
 'prompt ' nl,
 'prompt ' nl,
 'prompt Creating user ' || '''' || su.usr_oracle_username || '''' || '...'  nl, 
 'create user ' || su.usr_oracle_username  ||  ' identified by ' || 'changeme' nl,
 'default tablespace' || ' &&' || 'default_tbsp'  nl,
 'temporary tablespace' || ' &&' || 'temporary_tbsp'  nl,
 '-- *** Un-comment line below to specify a Database Profile *** ' nl,
 '-- profile dbusr_profile ' nl,
 '/' nl, 
 ' ' nl,
 'prompt Granting roles...'  nl,
 'grant CONNECT to ' || su.usr_oracle_username  nl,
 '/'  nl,
 'grant RESOURCE to ' || su.usr_oracle_username nl,
 '/'  nl,
 ' '  nl,
 'prompt Granting system privileges...' nl,
 'grant CREATE TABLE to ' || su.usr_oracle_username nl,
 '/'  nl,
 'grant CREATE VIEW to ' || su.usr_oracle_username nl,
 '/'  nl,
 'grant CREATE PROCEDURE to ' || su.usr_oracle_username nl,
 '/'  nl,
 'grant CREATE SYNONYM to ' || su.usr_oracle_username nl,
 '/'  nl, 
 'grant CREATE SEQUENCE to ' || su.usr_oracle_username nl,
 '/'  nl,
 'grant SELECT on SYS.V_$NLS_PARAMETERS to ' || su.usr_oracle_username nl,
 '/'  nl,
 'grant SELECT on DBA_ROLLBACK_SEGS to ' || su.usr_oracle_username nl,
 '/'  nl,
 'grant SELECT on DBA_SEGMENTS to ' || su.usr_oracle_username nl,
 '/' nl 
from sdw_users su
where  nvl(su.usr_sdd_owner,'N') != 'Y'
/
PROMPT set termout on
PROMPT set verify on
PROMPT spool off
PROMPT prompt
PROMPT prompt
PROMPT prompt Completed migration of Oracle database users.
PROMPT prompt 
PROMPT prompt  **********************************************************************************
PROMPT prompt  *                                                                                *
PROMPT prompt  * Please check log file 'migrate_repos_users.log' to ensure there were no errors *
PROMPT prompt  * whilst creating the Oracle database users on the Designer 6.5 Repository       *
PROMPT prompt  *                                                                                *
PROMPT prompt  **********************************************************************************
PROMPT prompt
PROMPT prompt

set termout on
set heading on
set verify on
set feedback on
set scan on
spool off
PROMPT
PROMPT    Completed generating script.
PROMPT 
PROMPT    ****************************************************************************
PROMPT    * Now execute the generated script on your Designer 6.5 Repository         *
PROMPT    *                                                                          *
PROMPT    * Syntax, from within SQL*Plus connected as the SYS user type:             *
PROMPT    * SQL> @migrate_repos_users.sql                                            *
PROMPT    *                                                                          *
PROMPT    ****************************************************************************
PROMPT
PROMPT





