Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> copy users sql scripts

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US