moving a schema from prod to test (or vice versa) in 8i - no dbms_metadata
- From: "Ben" <poelsb@xxxxxxxxxxxxxxx>
- To: "Oracle-L@Freelists. Org" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Feb 2004 14:23:46 -0500
Hi
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
