Objective: clone one schema from one Oracle 11g database to another 11g database server, schema name might change.
Environment: RHEL5, From Oracle 11g 64bit to Oracle 11g 32bit.
Steps:
1. create server side directory first before exporting schema
. oraenv
orcl
sqlplus / as sysdba
create or replace directory dmpdp as '/u01/dmpdp';
grant read,write on directory dmpdp to system;
note: you can use 'select * from dba_directories' to check after creation.
2. use expdp to dump schema
expdp system directory=dmpdp dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logdp schemas=jephe
note: if you are exporting schema for lower version of Oracle database, such as 10g, you might need to use exp instead of expdp:
exp system file=jephe.20091210.dmp log=jephe.20091210.log statistics=none owner=jephe
3. check the existing schema on destination database server
. oraenv
uatdb
sqlplus / as sysdba
select name from v$datafile;
select distinct s.owner,s.tablespace_name,d.file_name from dba_segments s,dba_data_files d where s.tablespace_name = d.tablespace_name;
note: above statement lists out tablespace names for relevant schema.
drop tablespace jephe including contents and datafiles
drop user jephe cascade;
note: if above drop user command got ORA-01940 error: cannot drop a user that is currently connected. Then you need to kill all the existing user connections first.
You can use the following scripts to do it:
# more killusersession.sql
set head off feedback off pagesize 0 echo off term off linesize 32767 trimsp on tab off define off;
spool /u01/scripts/killusersessionfinal.sql;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='JEPHE';
select 'exit;' from dual;
spool off;
exit;
cd /u01/scripts/
sqlplus / as sysdba @killusersession.sql
sqlplus / as sysdba @killusersessionfinal.sql
4. create tablespace on destination database server and import
sqlplus / as sysdba
select name from v$datafile;
create tablespace jephe datafile '/path/to/jephe.dbf' size 100m autoextend on next 10m flashback off;
exit
if you are going to use imp instead of impdp to import schema later, you will have to do this:
create tablespace jephe datafile ‘/u01/app/oracle/oradata/uatdb/jephe.dbf’ size 100m autoextend on next 10m flashback off
create user jephe identified by password default tablespace jephe temporary tablespace temp;
grant connect,resource,create view, select any table to jephe
revoke unlimited tablespace from jephe
alter user jephe quota unlimited on jephe;
5. transfer dumpfile to /home/oracle on destination server and define the /home/oracle as 'oracle'
sqlplus / as sysdba
create or replace directory oracle as '/home/oracle';
grant read,write on directory dmpdp to system;
exit;
# impdp system directory=oracle dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logimpdp schemas=jephe
if you use imp, do this:
imp system file=/home/oracle/jephe.dmp log=/home/oracle/jephe.implog fromuser=jephe touser=jephe;
note:
1. if you need to import to another schema, you can do this:
firstly create tablespace zhitan, then run
# impdp system directory=oracle dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logimpdp remap_schema=jephe:zhitan remap_tablespace=jephe:zhitan transform=oid:n
or
# imp system file=/home/oracle/jephe.dmp log=/home/oracle/jephe.implog fromuser=jephe touser=zhitan;
note: if using imp to import from one user to another use, you need to
alter user zhitan default tablespace zhitan;
Revoke the unlimited tablespace and change the new user’s quota to have space only on the new tablespace:
revoke unlimited tablespace from zhitan;
alter user zhitan quota unlimited on zhitan quota 0 on jephe;
6. checking after import
see if the destination tablespace got data after import
sqlplus / as sysdba
SQL> column owner format a20
SQL> select owner,count(*) from dba_segments where tablespace_name='TEST_DATA' group by owner;
see if user jephe is using his own tablespace:
select username,default_tablespace from dba_users where username='JEPHE';
7. compile the schema
sqlplus / as sysdba
sql> exec dbms_utility.compile_schema('JEPHE');
8. check invalid objects and the number of invalid objects for schema JEPHE
# more numberofinvalidobjects.sql
select count(*) JEPHE, object_type from dba_objects where owner='JEPHE' and status <> 'VALID' and object_name not like 'FB_%' group by object_type;
# more invalidobjects.sql
column object_type format A10;
column object_name format A30;
select object_type "Invalid JEPHE",object_name from dba_objects where owner='JEPHE' and status <> 'VALID' and object_name not like 'FB_%' order by object_type,object_name;
Posts
