copy users sql scripts

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 27 Jun 2007 08:24:54 -0500
Message-ID: <ad3aa4c90706270624g48cfa7a0xe2d1bdc7bbe6cd61@mail.gmail.com>

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_at_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.'

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 27 2007 - 08:24:54 CDT