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

From: Ben <poelsb_at_post.queensu.ca>
Date: Fri, 27 Feb 2004 14:23:46 -0500
Message-ID: <IAEFKEBFDELIGHKOHDGJIEDIELAA.poelsb@post.queensu.ca>

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

Received on Fri Feb 27 2004 - 13:19:54 CST

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