Jephe wu - http://linuxtechres.blogspot.com
Objective: to compare the 2 schemas to make sure the database structures are same. The data inside the tables might not be the same, but the table columns and data type must be the same. Also, all the programs such as packages/procedures/functions etc must be the same.
Environment: Oracle 11g 64bit on RHEL 5
Steps:
1. exporting the packages/functions/procedures from jephe1 schema
You can use sql developer database export function (tick terminator, pretty print, and include drop statement) to export them from schema jephe1 then use 'run script(F5)' to run in another schema(jephe2).
But I have encountered some issues such as the sql developer doesn't seem to understand the following multiple lines comments:
/***************
....
...
****************/
and if I use sqlplus, I encountered error like this:
SP2-0027: Input is too long (> 2499 characters) - line ignored
=> solution to this
=>
Open the file in a text editor and then resave the file as a different file
type. In Microsoft Word, you click on File, Save As, then select file type
'text only with line breaks' and save the file. Once this is done, you can
successfully run the script from SQL*Plus or Server Manager.In this article, I am using expdp/impdp tool to sync them.
$ more jephe1.par
directory=oracle
schemas=jephe1
dumpfile=jephe1.packages.dumpdp
logfile=jephe1.packages.logdp
include=PACKAGE
include=PROCEDURE
include=FUNCTION
$ sqlplus / as sysdba
create [ or replace ] directory oracle as '/home/oracle’
grant read,write on directory oracle to system
select * from dba_directories
$ expdp system parfile=jephe1.par
2. drop all packages/procedures/functions in jephe2 schema
We need to drop all of them before using impdp to import. You can use SQL developer 'database export' function to generate the drop statements, or use the following sql statements to generate it through sqlplus command line.
$ more generate_drop_obj.sql
set pagesize 0 feedback off
spool drop_obj.sql
select 'drop '||object_type||' '||object_name||';' from user_objects where object_type in ('PACKAGE','PROCEDURE','FUNCTION','VIEW') and object_name not like 'FB_%' order by object_type,object_name;
exit;
spool off;
$ sqlplus jephe1
@generate_drop_obj.sql
@drop_obj.sql
After that, you can use sql developer to check jephe2 schema to make sure all procedures/packages/functions are dropped.
3. importing the expdp dump file to jephe2 schema
$ impdp system directory=oracle dumpfile=jephe1.packages.dumpdp logfile=jephe2.packages.logdp remap_schema=jephe1:jephe2 remap_tablespace=jephe1:jephe2 transform=oid:n
4. compare table structures between jephe1 and jephe2.
$ more comparetables.sql
Set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
Spool /tmp/jephe1;
Select table_name||','||column_name||','||data_type||','||char_length||','||data_precision||','||data_scale from user_tab_columns order by table_name||','||column_name;
Spool off;
exit;
$ sqlplus jephe1
@comparetables.sql
change spool /tmp/jephe1 line to spool /tmp/jephe2, then run it again, after that, use diff to compare:
$ diff -c /tmp/jephe1 /tmp/jephe2
5. compile the whole schema for jephe2
$ sqlplus / as sysdba
exec dbms_utility.compile_schema('JEPHE2');
Posts
