RE: moving a schema from prod to test (or vice versa) in 8i

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 27 Feb 2004 14:32:58 -0500
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5951@exchsen0a1ma>

Ben,

The way that I've done this in the past is as follows:

In the target database (new database):
1). Create any roles you need. Grant system privs and access to sys objects to those roles as needed.
2). Create any database links that you might need.2). Create tablespaces. These would be the same tablespace names that you currently have in your source database.
3). Create the users that you need. Grant the privs/roles to those users as required.
4). Perform schema exports from the source database. 5). Perform schema import into the target database.

By performing steps 1-3 before steps 4 & 5, you take care of any import errors you might experience during the import process. You might need to try this a couple of times noting any errors you get, and adjust your steps accoringly.

Oracle export/import is a great tool for doing just what you are looking to do.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Ben [mailto:poelsb_at_post.queensu.ca] Sent: Friday, February 27, 2004 2:24 PM
To: Oracle-L_at_Freelists. Org
Subject: moving a schema from prod to test (or vice versa) in 8i - no dbms_metadata

I have often needed to copy a schema to another database so I am working on a script to accomplish this. This could be an entirely new schema on the target database or may require a drop cascade and then recreate. I have checked all the usual web sites and have not found one. Does anyone have one they want to share? My effort so far has produced the following (work in progress ) pseudo script:

first create any roles(and other users as needed) that do not exist and that the schema might grant security to
Create schema user with default tablespace, temp tablespace and possibly password if desired.

select 'create user TOSCHEMA identified by values '''||password||''' default tablespace TOSCHEMA temporary tablespace TEMP;' from sys.DBA_USERS
where username = 'FROMSCHEMA';

owner type export of schema to be copied
full import of above export to create the schema
Grant Roles the old schema had

select 'grant '||GRANTED_ROLE||' to TOSCHEMA;' from sys.DBA_ROLE_PRIVS
where grantee = 'FROMSCHEMA';

Grant System Privs the old schema had

select 'grant '||PRIVILEGE||' to TOSCHEMA;' from sys.DBA_SYS_PRIVS
where grantee = 'FROMSCHEMA';

copy all tablespace quotas given to the copied schema

select 'alter user TOSCHEMA quota
'||decode(MAX_BYTES,-1,'UNLIMITED',MAX_BYTES)

||' on '||TABLESPACE_NAME||';'
from sys.DBA_TS_QUOTAS
where username = 'FROMSCHEMA';

Table Privileges granted to the schema owner and
table privileges the schema owner granted to other users

select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to TOSCHEMA'

||decode(GRANTABLE,'YES',' with grant option ;',';') from sys.dba_tab_privs
where grantor='FROMSCHEMA' or grantee = 'FROMSCHEMA';

Grant Column Privs same as table privileges

select 'grant '||PRIVILEGE||' ('||COLUMN_NAME||')'||' on '||OWNER||'.'||TABLE_NAME||
' to TOSCHEMA'||decode(GRANTABLE,'YES',' with grant option ;',';') from sys.dba_col_privs
where grantor='FROMSCHEMA' or grantee = 'FROMSCHEMA';

if necessary create synonyms in other schemas for objects in the new schema
create constraints in other schemas that need to reference objects in the new schema
re-compile invalid objects

Thanks,

Ben

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 27 2004 - 13:29:59 CST

RE: moving a schema from prod to test (or vice versa) in 8i - no dbms_metadata