copy users sql scripts

  • From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>, nileshkum@xxxxxxxxx
  • Date: Wed, 27 Jun 2007 08:24:54 -0500

Below is a script written several years ago to copy users from one instance
to another..  After using these scripts, you can uese exp/imp to copy
tables.  Obviously in 10g you can use datapump for this.

/******************************************************************************************/
/* The move_users script needs the source database and the target database
defined        */
/* then it creates the following output
scripts:                                          */
/*
*/
/* CREATE_PROFILES.SQL:  This script creates a profile in the target
database that is not */
/*                       in the source
database.                                          */
/* ALTER_PROFILES.SQL:   This script moves changes in profiles from the
source database   */
/*                       to the target
database.                                          */
/* CREATE_USERS.SQL:     This script creates a user in the target database
that is not in */
/*                       the source
database.                                             */
/* CHANGE_USERS.SQL:     This script moves changes in the user
(PASSWORD,                 */
/*                       DEFAULT_TABLESPACE, A.TEMPORARY_TABLESPACE,
A.PROFILE) from the  */
/*                       source database to the target
database.                          */
/* ADD_ROLES.SQL         This script grants roles to users in the target
database that    */
/*                       were in the source
database.                                     */
/* CREATE_ROLES.SQL      This script creates roles in the target database
that where      */
/*                       in the source
database.                                          */
/******************************************************************************************/


SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
DEFINE sourceDB = '@SRCINST'
DEFINE targetDB = '@'DESTINST

/*******************************CREATE_PROFILES.SQL****************************************/
SPOOL CREATE_PROFILES.SQL

SELECT 'CREATE PROFILE '||PROFILE||' LIMIT '||RESOURCE_NAME||' '||LIMIT||';'
FROM   DBA_PROFILES&sourceDB.
WHERE  PROFILE IN
(
 SELECT PROFILE FROM DBA_PROFILES&sourceDB.
 MINUS
 SELECT PROFILE FROM DBA_PROFILES&targetDB.
)
AND ROWNUM < 2;

SPOOL OFF

/*******************************ALTER_PROFILE.SQL******************************************/

SPOOL ALTER_PROFILE.SQL

SELECT 'ALTER PROFILE '||B.PROFILE||' LIMIT '||A.RESOURCE_NAME||'
'||A.LIMIT||';'
FROM   DBA_PROFILES&sourceDB. A,
      DBA_PROFILES&targetDB. B
WHERE  A.limit != B.limit
AND    A.profile = B.profile
AND    A.RESOURCE_NAME = B.RESOURCE_NAME;

SPOOL OFF

/*******************************CREATE_USERS.SQL*******************************************/

SPOOL CREATE_USERS.SQL
SELECT 'CREATE USER '||USERNAME||' IDENTIFIED BY VALUES '''||PASSWORD||
      ''' DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE
'
      ||TEMPORARY_TABLESPACE||' PROFILE '|| PROFILE||';'
FROM   DBA_USERS&sourceDB.
WHERE  USERNAME IN
   (
     SELECT USERNAME FROM DBA_USERS&sourceDB.
     MINUS
     SELECT USERNAME FROM DBA_USERS&targetDB.
   );

SPOOL OFF

/*******************************CHANGE_USERS.SQL*******************************************/

SPOOL CHANGE_USERS.SQL
SELECT 'ALTER USER '||b.USERNAME||' IDENTIFIED BY VALUES '''||A.PASSWORD||
      ''' DEFAULT TABLESPACE '||A.DEFAULT_TABLESPACE||' TEMPORARY
TABLESPACE '||
      A.TEMPORARY_TABLESPACE||' PROFILE '||A.PROFILE||';'
FROM   DBA_USERS&sourceDB. A,
      DBA_USERS&targetDB. B
where  A.USERNAME = B.USERNAME
AND    A.PASSWORD||A.DEFAULT_TABLESPACE||A.TEMPORARY_TABLESPACE||A.PROFILE!=
      B.PASSWORD||B.DEFAULT_TABLESPACE||B.TEMPORARY_TABLESPACE||B.PROFILE;

SPOOL OFF

/****************************CREATE_ROLES.SQL**********************************************/

SPOOL CREATE_ROLES.SQL

SELECT 'CREATE ROLE '||ROLE||';'
FROM   DBA_ROLES&sourceDB.
WHERE  ROLE IN
(
 SELECT ROLE FROM DBA_ROLES&sourceDB.
 MINUS
 SELECT ROLE FROM DBA_ROLES&targetDB.
);

SPOOL OFF

/*******************************ADD_ROLES.SQL**********************************************/

SPOOL ADD_ROLES.SQL

SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';'
FROM   DBA_ROLE_PRIVS&sourceDB.
WHERE  (GRANTEE, GRANTED_ROLE) IN
(
 SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS&sourceDB.
 MINUS
 SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS&targetDB.
);

SPOOL OFF

/******************************************************************************************/

SPOOL ADD_SYS.SQL

SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';'
FROM   DBA_SYS_PRIVS&sourceDB.
WHERE  (GRANTEE, PRIVILEGE) IN
(
 SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS&sourceDB.
 MINUS
 SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS&targetDB.
);

SPOOL OFF

/******************************************************************************************/
/******************************************************************************************/
/**************************************************************************/
move db_links

select 'create public database link '||db_link||' using '''||db_link||''';'
from dba_db_links@oltpqa
where USERNAME is null
and   db_link not in (select db_link from dba_db_links);


/*
@CREATE_PROFILES.SQL
@ALTER_PROFILES.SQL
@CREATE_USERS.SQL
@CHANGE_USERS.SQL
@ADD_ROLES.SQL
@ADD_SYS.SQL
*/

UNDEFINE sourceDB
UNDEFINE targetDB

SET FEEDBACK ON
@LOC

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Other related posts:

  • » copy users sql scripts