Table of Contents
1. Overview
This document will be helpful to compares Prod and Dev schema and generates scripts for missing objects from dev schema that to be deployed in Prod instance.
2. Technologies and Tools Used
- Oracle Plsql code.
3. Use Case
To migrate Scripts from development instance to prod instance we used below method to get complete script.
4. Architecture
This query compares Prod and Dev schema and generates missing objects as script from Dev that to be deployed in Prod instance.
Step1 : Login as sysdba.
Step2: Run below code.
Code:
DECLARE
/*
Compare Prod and Dev Schema, generate script from Dev database to deploy in Prod database.
*/
PROCEDURE metadata_p (
p_schema_1 IN VARCHAR2, — Prod Schema
p_schema_2 IN VARCHAR2, — Dev Schema
p_object_ty IN VARCHAR2 — Selected Object Type like — TABLE, SEQUENCE etc.
) IS
lv_name clob;
CURSOR meta_c IS
SELECT DISTINCT
obj_nm_2,
obj_ty_2,
s2_owner
FROM
(
SELECT
s1.object_name obj_nm_1,
s1.object_type obj_ty_1,
s1.owner s1_owner,
s2.object_name obj_nm_2,
s2.object_type obj_ty_2,
s2.owner s2_owner
FROM
(
SELECT
object_name,
object_type,
owner
FROM
all_objects
WHERE
owner = p_schema_1
AND object_type = decode(p_object_ty, ‘ALL’, object_type, p_object_ty)
) s1
FULL JOIN (
SELECT
object_name,
object_type,
owner
FROM
sys.all_objects
WHERE
owner = p_schema_2
AND object_type = decode(p_object_ty, ‘ALL’, object_type, p_object_ty)
) s2 ON s1.object_name = s2.object_name
AND s1.object_type = s2.object_type
)
WHERE
2 = 2
AND obj_nm_2 IS NOT NULL
AND obj_nm_1 IS NULL
AND obj_ty_2 IN ( ‘TABLE’, ‘SEQUENCE’, ‘TRIGGER’, ‘FUNCTION’, ‘PROCEDURE’,
‘PACKAGE’ )
ORDER BY
decode(obj_ty_2, ‘TABLE’, ‘1’, ‘SEQUENCE’, ‘2’,
‘FUNCTION’, ‘3’, ‘PROCEDURE’, ‘4’, ‘TRIGGER’,
‘5’,
‘PACKAGE’,
‘6’,
‘7’) ASC;
BEGIN
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘EMIT_SCHEMA’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SQLTERMINATOR’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘PRETTY’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SIZE_BYTE_KEYWORD’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, false);
FOR idx IN meta_c LOOP
IF instr(idx.obj_nm_2, ‘$’, 1, 1) > 0 THEN
NULL;
ELSE
begin
dbms_output.put_line(dbms_metadata.get_ddl(object_type => idx.obj_ty_2,
name => idx.obj_nm_2,
Schema=>s2_owner
));
exception
when others then
lv_name:= lv_name||Chr(10) ||idx.obj_ty_2||’ – ‘||idx.obj_nm_2;
end;
END IF;
END LOOP;
dbms_output.put_line(‘/* Error Objects’);
dbms_output.put_line(‘Objecttype – object name ‘);
dbms_output.put_line(lv_name);
dbms_output.put_line(‘*/’);
dbms_output.put_line(‘exit’);
END metadata_p ;
BEGIN
metadata_p
(
p_schema_1 => :p_schema_1 —–> Prod Schema
, p_schema_2 => :p_schema_2, ——-> Dev Schema.
p_object_ty => ‘PACKAGE’
/*To generate DLL for all this (‘TABLE’,’SEQUENCE’, ‘TRIGGER’, ‘FUNCTION’,
‘PROCEDURE’, ‘PACKAGE’ ) object type then give p_object_ty => ‘ALL’ */
);
END;
5. Screen Shot
Output:
