PROMPT Posted by Anuj Kumar Pandey
PROMPT Schema Comparison
PROMPT =================
PROMPT
PROMPT Run this script while connected to one Oracle schema. Enter the Oracle
PROMPT username, password, and SQL*Net / Net8 service name of a second schema.
PROMPT This script will compare the two schemas and generate a report of
PROMPT differences.
PROMPT
PROMPT A temporary database link and table will be created and dropped by
PROMPT this script.
PROMPT

ACCEPT schema CHAR PROMPT “Enter username for remote schema: ”
ACCEPT passwd CHAR PROMPT “Enter password for remote schema: ” HIDE
ACCEPT tnssvc CHAR PROMPT “Enter SQL*Net / Net8 service for remote schema: ”
ACCEPT dblinkname CHAR PROMPT “Enter the dblink name to be created: ”
ACCEPT rem_schema CHAR PROMPT “Enter the dblink name: ”

PROMPT

ACCEPT report CHAR PROMPT “Enter filename for report output: ”

SET FEEDBACK OFF
SET VERIFY OFF

CREATE DATABASE LINK &dblinkname CONNECT TO &schema IDENTIFIED BY &passwd USING ‘&tnssvc’;

SET TRIMSPOOL ON

SPOOL &report

SELECT SUBSTR (RPAD (TO_CHAR (SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’), 25), 1, 25)
“REPORT DATE AND TIME”
FROM SYS.dual;

COL local_schema FORMAT a35 TRUNC HEADING “LOCAL SCHEMA”
COL remote_schema FORMAT a35 TRUNC HEADING “REMOTE SCHEMA”

SELECT USER || ‘@’ || C.global_name local_schema,
A.username || ‘@’ || B.global_name remote_schema
FROM user_users@&rem_schema A, global_name@&rem_schema B, global_name C
WHERE ROWNUM = 1;

SET PAGESIZE 9999
SET LINESIZE 250
SET FEEDBACK 1

SET TERMOUT OFF

PROMPT

REM Object differences
REM ==================

COL object_name FORMAT a30

PROMPT SUMMARY OF OBJECTS MISSING FROM LOCAL SCHEMA

SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects@&rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects
)
GROUP BY object_type
ORDER BY object_type;

PROMPT SUMMARY OF EXTRANEOUS OBJECTS IN LOCAL SCHEMA

SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects
WHERE object_type != ‘DATABASE LINK’
OR object_name NOT LIKE ‘&rem_schema.%’
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects@&rem_schema
)
GROUP BY object_type
ORDER BY object_type;

PROMPT OBJECTS MISSING FROM LOCAL SCHEMA

SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects@&rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects
ORDER BY object_type, object_name;

PROMPT EXTRANEOUS OBJECTS IN LOCAL SCHEMA

SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects
WHERE object_type != ‘DATABASE LINK’
OR object_name NOT LIKE ‘&rem_schema.%’
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects@&rem_schema
ORDER BY object_type, object_name;

PROMPT OBJECTS IN LOCAL SCHEMA THAT ARE NOT VALID

SELECT object_name, object_type, status
FROM user_objects
WHERE status != ‘VALID’
ORDER BY object_name, object_type;

REM Table differences
REM =================

PROMPT TABLE COLUMNS MISSING FROM ONE SCHEMA
PROMPT (NOTE THAT THIS REPORT DOES NOT LIST DISCREPENCIES IN COLUMN ORDER)

(
SELECT table_name, column_name, ‘Local’ “MISSING IN SCHEMA”
FROM user_tab_columns@&rem_schema
WHERE table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT table_name, column_name, ‘Local’ “MISSING IN SCHEMA”
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, ‘Remote’ “MISSING IN SCHEMA”
FROM user_tab_columns
WHERE table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
MINUS
SELECT table_name, column_name, ‘Remote’ “MISSING IN SCHEMA”
FROM user_tab_columns@&rem_schema
)
ORDER BY 1, 2;

COL schema FORMAT a15
COL nullable FORMAT a8
COL data_type FORMAT a9
COL data_length FORMAT 9999 HEADING LENGTH
COL data_precision FORMAT 9999 HEADING PRECISION
COL data_scale FORMAT 9999 HEADING SCALE
COL default_length FORMAT 9999 HEADING LENGTH_OF_DEFAULT_VALUE

PROMPT DATATYPE DISCREPENCIES FOR TABLE COLUMNS THAT EXIST IN BOTH SCHEMAS

(
SELECT table_name, column_name, ‘Remote’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@&rem_schema
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns
)
MINUS
SELECT table_name, column_name, ‘Remote’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, ‘Local’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns@&rem_schema
)
MINUS
SELECT table_name, column_name, ‘Local’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@&rem_schema
)
ORDER BY 1, 2, 3;

REM Index differences
REM =================

COL column_position FORMAT 999 HEADING ORDER

PROMPT INDEX DISCREPENCIES FOR INDEXES THAT EXIST IN BOTH SCHEMAS

(
SELECT A.index_name, ‘Remote’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@&rem_schema A, user_ind_columns@&rem_schema B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, ‘Remote’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
UNION ALL
(
SELECT A.index_name, ‘Local’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes@&rem_schema
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, ‘Local’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@&rem_schema A, user_ind_columns@&rem_schema B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
ORDER BY 1, 2, 6;

REM Constraint differences
REM ======================

PROMPT CONSTRAINT DISCREPENCIES FOR TABLES THAT EXIST IN BOTH SCHEMAS

SET FEEDBACK OFF

CREATE TABLE temp_schema_compare
(
database NUMBER(1),
object_name VARCHAR2(30),
object_text VARCHAR2(2000),
hash_value NUMBER
);

DECLARE
CURSOR c1 IS
SELECT constraint_name, search_condition
FROM user_constraints
WHERE search_condition IS NOT NULL;
CURSOR c2 IS
SELECT constraint_name, search_condition
FROM user_constraints@&rem_schema
WHERE search_condition IS NOT NULL;
v_constraint_name VARCHAR2(30);
v_search_condition VARCHAR2(32767);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_constraint_name, v_search_condition;
EXIT WHEN c1%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
1, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_constraint_name, v_search_condition;
EXIT WHEN c2%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
2, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c2;
COMMIT;
END;
/

SET FEEDBACK 1

(
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Remote’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@&rem_schema A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables
)
AND B.database (+) = 2
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Remote’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE B.database (+) = 1
AND B.object_name (+) = A.constraint_name
)
UNION ALL
(
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Local’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
AND B.database (+) = 1
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Local’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@&rem_schema A, temp_schema_compare B
WHERE B.database (+) = 2
AND B.object_name (+) = A.constraint_name
)
ORDER BY 1, 4, 2;

REM Database link differences
REM =========================

PROMPT DATABASE LINK DISCREPENCIES

COL db_link FORMAT a40

(
SELECT db_link, ‘Remote’ schema, username, host
FROM user_db_links@&rem_schema
MINUS
SELECT db_link, ‘Remote’ schema, username, host
FROM user_db_links
)
UNION ALL
(
SELECT db_link, ‘Local’ schema, username, host
FROM user_db_links
WHERE db_link NOT LIKE ‘&rem_schema.%’
MINUS
SELECT db_link, ‘Local’ schema, username, host
FROM user_db_links@&rem_schema
)
ORDER BY 1, 2;

REM Sequence differences
REM ====================

PROMPT SEQUENCE DISCREPENCIES

(
SELECT sequence_name, ‘Remote’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@&rem_schema
MINUS
SELECT sequence_name, ‘Remote’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
)
UNION ALL
(
SELECT sequence_name, ‘Local’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
MINUS
SELECT sequence_name, ‘Local’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@&rem_schema
)
ORDER BY 1, 2;

REM Private synonym differences
REM ===========================

PROMPT PRIVATE SYNONYM DISCREPENCIES

(
SELECT synonym_name, ‘Remote’ schema, table_owner, table_name, db_link
FROM user_synonyms@&rem_schema
MINUS
SELECT synonym_name, ‘Remote’ schema, table_owner, table_name, db_link
FROM user_synonyms
)
UNION ALL
(
SELECT synonym_name, ‘Local’ schema, table_owner, table_name, db_link
FROM user_synonyms
MINUS
SELECT synonym_name, ‘Local’ schema, table_owner, table_name, db_link
FROM user_synonyms@&rem_schema
)
ORDER BY 1, 2;

REM PL/SQL differences
REM ==================

PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS

SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, text
FROM user_source@&rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, text
FROM user_source
)
UNION ALL
(
SELECT name, type, line, text
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@&rem_schema
)
MINUS
SELECT name, type, line, text
FROM user_source@&rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;

PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS (CASE INSENSITIVE COMPARISON)

SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, UPPER (text)
FROM user_source@&rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source
)
UNION ALL
(
SELECT name, type, line, UPPER (text)
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@&rem_schema
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source@&rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;

REM Trigger differences
REM ===================

PROMPT TRIGGER DISCREPENCIES

SET FEEDBACK OFF

TRUNCATE TABLE temp_schema_compare;

DECLARE
CURSOR c1 IS
SELECT trigger_name, trigger_body
FROM user_triggers;
CURSOR c2 IS
SELECT trigger_name, trigger_body
FROM user_triggers@&rem_schema;
v_trigger_name VARCHAR2(30);
v_trigger_body VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c1%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ‘ ‘, NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c2%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ‘ ‘, NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/

SET FEEDBACK 1

(
SELECT A.trigger_name, ‘Local’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
AND A.table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
MINUS
SELECT A.trigger_name, ‘Local’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.trigger_name, ‘Remote’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
AND A.table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT A.trigger_name, ‘Remote’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
)
ORDER BY 1, 2, 5, 3;

REM View differences
REM ================

PROMPT VIEW DISCREPENCIES

SET FEEDBACK OFF

TRUNCATE TABLE temp_schema_compare;

DECLARE
CURSOR c1 IS
SELECT view_name, text
FROM user_views;
CURSOR c2 IS
SELECT view_name, text
FROM user_views@&rem_schema;
v_view_name VARCHAR2(30);
v_text VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_view_name, v_text;
EXIT WHEN c1%NOTFOUND;
v_text := REPLACE (v_text, ‘ ‘, NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_view_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_view_name, v_text;
EXIT WHEN c2%NOTFOUND;
v_text := REPLACE (v_text, ‘ ‘, NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_view_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/

SET FEEDBACK 1

(
SELECT A.view_name, ‘Local’ schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
AND A.view_name IN
(
SELECT view_name
FROM user_views@&rem_schema
)
MINUS
SELECT A.view_name, ‘Local’ schema, B.hash_value
FROM user_views@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.view_name, ‘Remote’ schema, B.hash_value
FROM user_views@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
AND A.view_name IN
(
SELECT view_name
FROM user_views
)
MINUS
SELECT A.view_name, ‘Remote’ schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
)
ORDER BY 1, 2;

REM Job queue differences
REM =====================

COL what FORMAT a30
COL interval FORMAT a30

PROMPT JOB QUEUE DISCREPENCIES

(
SELECT what, interval, ‘Remote’ schema
FROM user_jobs@&rem_schema
MINUS
SELECT what, interval, ‘Remote’ schema
FROM user_jobs
)
UNION ALL
(
SELECT what, interval, ‘Local’ schema
FROM user_jobs
MINUS
SELECT what, interval, ‘Local’ schema
FROM user_jobs@&rem_schema
)
ORDER BY 1, 2, 3;

REM Privilege differences
REM =====================

PROMPT OBJECT-LEVEL GRANT DISCREPENCIES

(
SELECT owner, table_name, ‘Remote’ schema, grantee, privilege, grantable
FROM user_tab_privs@&rem_schema
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects
)
MINUS
SELECT owner, table_name, ‘Remote’ schema, grantee, privilege, grantable
FROM user_tab_privs
)
UNION ALL
(
SELECT owner, table_name, ‘Local’ schema, grantee, privilege, grantable
FROM user_tab_privs
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects@&rem_schema
)
MINUS
SELECT owner, table_name, ‘Local’ schema, grantee, privilege, grantable
FROM user_tab_privs@&rem_schema
)
ORDER BY 1, 2, 3;

PROMPT SYSTEM PRIVILEGE DISCREPENCIES

(
SELECT privilege, ‘Remote’ schema, admin_option
FROM user_sys_privs@&rem_schema
MINUS
SELECT privilege, ‘Remote’ schema, admin_option
FROM user_sys_privs
)
UNION ALL
(
SELECT privilege, ‘Local’ schema, admin_option
FROM user_sys_privs
MINUS
SELECT privilege, ‘Local’ schema, admin_option
FROM user_sys_privs@&rem_schema
)
ORDER BY 1, 2;

PROMPT ROLE PRIVILEGE DISCREPENCIES

(
SELECT granted_role, ‘Remote’ schema, admin_option, default_role, os_granted
FROM user_role_privs@&rem_schema
MINUS
SELECT granted_role, ‘Remote’ schema, admin_option, default_role, os_granted
FROM user_role_privs
)
UNION ALL
(
SELECT granted_role, ‘Local’ schema, admin_option, default_role, os_granted
FROM user_role_privs
MINUS
SELECT granted_role, ‘Local’ schema, admin_option, default_role, os_granted
FROM user_role_privs@&rem_schema
)
ORDER BY 1, 2;

SPOOL OFF

SET TERMOUT ON

PROMPT
PROMPT Report output written to &report

SET FEEDBACK OFF

DROP TABLE temp_schema_compare;
DROP DATABASE LINK &rem_schema;

SET FEEDBACK 6
SET PAGESIZE 20
SET LINESIZE 80

Select substr(b.tablespace_name,1,20) TableSpace, Total_Max_MB, NVL(Total_Used_MB,0) Total_Used_MB,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0)),0) “Free_MB”,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0))/1024,2) “Free_GB”,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0))/Total_Max_MB * 100,2) “Free%”
from (select tablespace_name, round(sum(bytes/1024/1024),2) Total_Used_MB
from dba_segments
group by tablespace_name) a,
(select tablespace_name,
round(sum(decode(maxbytes,0,bytes/1024/1024,maxbytes/1024/1024)),0) Total_Max_MB,
round(sum(user_bytes/1024/1024),0) Total_User_MB
from dba_data_files
group by tablespace_name) b
where b.tablespace_name = a.tablespace_name (+)
and ROUND((Total_Max_MB – Total_Used_MB)/Total_Max_MB * 100,2) <=100
order by “Free%” desc;

TTITLE “Total buffers used by individual objects.”
COL OWNER FOR A8
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A10
select obj.owner “OWNER”, obj.object_name “OBJECT_NAME”,
obj.object_type “OBJECT_TYPE”, COUNT(distinct bh.block#) “# Buffers”
from dba_objects obj, v$bh bh
where obj.object_id = bh.objd and
obj.owner != ‘SYS’
group by obj.owner, obj.object_name, obj.object_type
having COUNT(distinct bh.block#) >= 2
order by 4;

1)

set linesize 200
SELECT SUBSTR (df.NAME, 1, 65) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

2)

select d.tablespace_name, d.file_id, d.file_name, d.bytes allocated, nvl(f.bytes,0) free
from (select tablespace_name, file_id, file_name, sum(bytes) bytes from dba_data_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes from dba_free_space
group by tablespace_name, file_id) f
where d.tablespace_name in (‘SYSTEM’,'SYSAUX’)
and d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
order by tablespace_name, file_id;

#!/bin/ksh
# set -x
PATH=/oraeng/app/oracle/product/10.2.0/bin:/sbin:/usr/bin:/usr/sbin:/usr/sbin:/usr/ccs/bin:/usr/local/bin:/usr/ucb/bin: export PATH
ORACLE_HOME=/oraeng/app/oracle/product/10.2.0 ; export ORACLE_HOME
ORACLE_SID=orcl ; export ORACLE_SID
TNS_ADMIN=/oraeng/app/oracle/product/10.2.0/network/admin ; export TNS_ADMIN
TABLE_NAME=emp; export TABLE_NAME
DATE=`date “+%m/%d/%y:%H:%M:%S”` ; export DATE
PIPE_FILE=/tmp/exp_${ORACLE_SID}${TABLE_NAME}_pipe
start_time=`date “+ %m/%d/%Y %H:%M:%S”`

find /export -name ${ORACLE_SID}_exp_${TABLE_NAME}_\*.dmp.Z -exec rm {} \;
find /export -name ${ORACLE_SID}_exp_${TABLE_NAME}_\*.log -exec rm {} \;
export dmpfile=${ORACLE_SID}_exp_${TABLE_NAME}_`date +%y%m%d%H%M%S`_%U.dmp
export logfile=${ORACLE_SID}_exp_${TABLE_NAME}_`date +%y%m%d%H%M%S`.log

nohup expdp / directory=datapump schemas=SYSPROD dumpfile=$dmpfile PARALLEL=2 filesize=1g > $logfile 2>&1 &

while [ 1 ]
do
## checking Expdp process
#
echo “======================== ”
echo “checking Expdp process …”

ps -ef |grep -v grep |grep -i expdp > /dev/null
if [ ${?} -ne 0 ]; then
find /export -name ${ORACLE_SID}_exp_${TABLE_NAME}_\*.dmp -exec compress {} \;
exit 1
else
find /export/ -size 1073741824c -exec compress {} \;
fi
done

success=”SUCCESS”
errors=”"
if test -f ${logfile}
then
if test `grep “with warnings” ${logfile} | wc -l` -ne 0
then
status=”WARNING”
errors=`cat ${logfile} |egrep ‘ORA-|EXP-’`
subject=”WARNING: Export of ${TABLE_NAME} finished with warnings at ${DATE}”
mailx -s “${subject}” “xyzemail.com,anuj.wst@gmail.com > /dev/null /dev/null < $logfile
else
status=”SUCCESS”
errors=”"
subject=”SUCCESS: Export of ${TABLE_NAME} finished successfully at ${DATE}”
fi
fi
fi

exit

1) Create a shared folder on window box.

2) Login to UNIX server as a root user and create an directory as below.

mkdir -p /mnt/test

3) Then start the smb services.

orarac2:/mnt/test # service smb status
Checking for Samba SMB daemon running
orarac2:/mnt/test #

4) Check the window server accessiable from Linux box.

nmblookup -A 192.168.223.34
Looking up status of 192.168.223.34
SV-L-ANUJ – B
WORKGROUP – B
SV-L-ANUJ – B
WORKGROUP – B

MAC Address = 00-21-70-BA-12-99

5) Then put the entry for hostname name in /etc/hosts , which is recognized on network. From above “SV-L-ANUJ” hostname recognized on network.

cat >> /etc/hosts
192.168.223.34 SV-L-ANUJ
Ctrl + c

6) Checking the accessiablity of server name and username of window box from Linux Box.

smbclient -L SV-L-ANUJ -U Anuj

Password: / give the password of Window Box user account

Domain=[SV-L-ANUJ] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

Sharename Type Comment
——— —- ——-
E$ Disk Default share
IPC$ IPC Remote IPC
F$ Disk Default share
anuj Disk
ADMIN$ Disk Remote Admin
C$ Disk Default share
DATA (E) Disk
Domain=[SV-L-ANUJ] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

Server Comment
——— ——-

Workgroup Master
——— ——-

7) Then mount the window drive from Linux:

orarac2:/mnt/test # mount -t smbfs -o username=Anuj,password=password //SV-L-ANUJ/anuj /mnt/test

orarac2:/mnt/test # df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda5 9.9G 3.6G 5.8G 39% /
udev 506M 188K 506M 1% /dev
/dev/hda1 388M 30M 338M 9% /boot
/dev/hda7 4.0G 3.1G 723M 82% /home
/dev/hda8 9.9G 7.2G 2.2G 77% /opt
/dev/hda9 4.0G 2.1G 1.7G 55% /tmp
/dev/hda11 18G 17G 63M 100% /u01
/dev/hda12 18G 18G 14M 100% /u02
/dev/hda6 9.9G 8.0G 1.5G 85% /usr
//192.168.223.34/anuj
32G 30G 2.8G 92% /mnt/test

Here is the source code with steps how to configure:

1) First create the table as below:

CREATE TABLE USER1.DBGROWTH (DATABASE_NAME VARCHAR2(10),
TOTAL_BYTES NUMBER, COLLECTION_DATE DATE);

ALTER TABLE USER1.DBGROWTH MODIFY (COLLECTION_DATE DEFAULT to_char(sysdate,'DD-MON-YY'));

CREATE UNIQUE INDEX USER1.DB_GROWTH_P ON DBGROWTH (DATABASE_NAME,COLLECTION_DATE);

ALTER TABLE USER1.DBGROWTH ADD CONSTRAINT USER1.DB_GROWTH_P PRIMARY KEY (DATABASE_NAME, COLLECTION_DATE) USING INDEX

2) Collect the daily growth of the database using below script.

ORACLE_SID=TEST
ORACLE_HOME=/opt/oracle
export ORACLE_HOME
export ORACLE_SID
echo "Run date:" `date`
SID="PROD1,PROD2" ||->> It will treat as alias name also so for you need to configure the tnsnames to access the production databases.
for i in `echo $SID|tr -s ',' ' '`
do
$ORACLE_HOME/bin/sqlplus -s USER1/USER1< ${ReportFile}
> ${LogFile}
> ${htmlFile}

${ORACLE_HOME}/bin/sqlplus -s "USER1/USER1" < /dev/null
set heading off verify off echo off feedback off linesize 120 pagesize 1000
spool ${LogFile}
select substr(a1.database_name,1,15) || '|' ||
to_char(a1.collection_date,'mm/dd/yyyy') || '|' || round(a1.DB_Size_MB,0) || '|' ||
to_char(a2.collection_date,'mm/dd/yyyy') || '|' || round(a2.DB_Size_MB,0) || '|' ||
(a2.collection_date - a1.collection_date + 1) || '|' ||
round((a2.DB_Size_MB - a1.DB_Size_MB),0) || '|' ||
greatest(round(100*(a2.DB_Size_MB - a1.DB_Size_MB)/a1.DB_Size_MB,2),0) || '|' ||
round(greatest(30 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0) || '|' ||
round(greatest(90 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0) || '|' ||
round(greatest(180 * (a2.DB_Size_MB - a1.DB_Size_MB) / (a2.collection_date - a1.collection_date),0),0)
from
(select database_name, total_bytes/1024/1024 DB_Size_MB, collection_date
from USER1.dbgrowth a
where to_char(collection_date,'mm/dd/yyyy') = (select to_char(min(collection_date),'mm/dd/yyyy')
from USER1.dbgrowth b
where b.database_name = a.database_name
and b.collection_date between (sysdate - 10) and sysdate)) a1,
(select database_name, total_bytes/1024/1024 DB_Size_MB, collection_date
from USER1.dbgrowth a
where to_char(collection_date,'mm/dd/yyyy') = (select to_char(max(collection_date),'mm/dd/yyyy')
from USER1.dbgrowth b
where b.database_name = a.database_name
and b.collection_date <= sysdate)) a2
where a1.database_name = a2.database_name;
spool off
EndSQL
echo "" > ${ReportFile}
echo "" >> ${ReportFile}
echo "" >> ${ReportFile}
echo '' >> ${ReportFile}
echo '' >> ${ReportFile}
echo "Report run date: `date`" >> ${ReportFile}
echo "" >> ${ReportFile}
echo '
' >> ${ReportFile}
echo '
' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

c3tot=0
c5tot=0
c7tot=0
c9tot=0
c10tot=0
c11tot=0

for rows in `cat ${LogFile}`
do
c1=`echo ${rows} | cut -f1 -d"|"`
c2=`echo ${rows} | cut -f2 -d"|"`
c3=`echo ${rows} | cut -f3 -d"|"`
c4=`echo ${rows} | cut -f4 -d"|"`
c5=`echo ${rows} | cut -f5 -d"|"`
c6=`echo ${rows} | cut -f6 -d"|"`
c7=`echo ${rows} | cut -f7 -d"|"`
c8=`echo ${rows} | cut -f8 -d"|"`
c9=`echo ${rows} | cut -f9 -d"|"`
c10=`echo ${rows} | cut -f10 -d"|"`
c11=`echo ${rows} | cut -f11 -d"|"`

c3tot=$(( c3tot + c3 ))
c5tot=$(( c5tot + c5 ))
c7tot=$(( c7tot + c7 ))
c9tot=$(( c9tot + c9 ))
c10tot=$(( c10tot + c10 ))
c11tot=$(( c11tot + c11 ))

echo "
" >> ${ReportFile}echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}
done

echo "
" >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}

c3tot=$(( c3tot / 1024 ))
c5tot=$(( c5tot / 1024 ))
c7tot=$(( c7tot / 1024 ))
c9tot=$(( c9tot / 1024 ))
c10tot=$(( c10tot / 1024 ))
c11tot=$(( c11tot / 1024 ))

echo "
" >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}
echo '


' >> ${ReportFile}

echo "

" >> ${ReportFile}

echo "

Database
First Date
DB Size (MB)on First Date
Second Date
DB Size (MB)on Second Date
TotalDays
DB Growth(MB)
PercentGrowth
Projected Growth inNext 30 days (MB)
Projected Growth inNext 90 days (MB)
Projected Growth inNext 180 days (MB)
' >> ${ReportFile} echo " ${c1}" >> ${ReportFile} echo '
' >> ${ReportFile} echo ${c2} >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c3}  &nbsp" >> ${ReportFile} echo '
' >> ${ReportFile} echo ${c4} >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c5}  &nbsp" >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c6}  &nbsp" >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c7}  &nbsp" >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c8} %  " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c9}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c10}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c11}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "  TOTAL (MB)" >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c3tot}   " >> ${ReportFile} echo '
' >> ${ReportFile} echo " " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c5tot}   " >> ${ReportFile} echo '
' >> ${ReportFile} echo " " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c7tot}   " >> ${ReportFile} echo '
' >> ${ReportFile} echo " " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c9tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c10tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c11tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "  TOTAL (GB)" >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c3tot}   " >> ${ReportFile} echo '
' >> ${ReportFile} echo " " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c5tot}   " >> ${ReportFile} echo '
' >> ${ReportFile} echo " " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c7tot}   " >> ${ReportFile} echo '
' >> ${ReportFile} echo " " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c9tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c10tot}       " >> ${ReportFile} echo '
' >> ${ReportFile} echo "${c11tot}       " >> ${ReportFile} echo '


" >> ${ReportFile}
echo "

" >> ${ReportFile}
echo "" >> ${ReportFile}
echo "" >> ${ReportFile}

#cat ${ReportFile}
uuencode ${ReportFile} ${htmlFile} | mailx -s "Database Growth Projection Report" ${MailList}

rm -rf ${TempDir}
rm -rf ${htmlFile}

exit 0

4) Schedule it in crontab as per requirement to collect the data and generate the report.

#DV db growth report script - Anuj
30 23 * * * /home/oracle/scripts/check_ts_growth.ksh >> /home/oracle/scripts/check_ts_growth.log 2>&1
00 02 * * * /home/oracle/scripts/dbgrowth.ksh > /home/oracle/scripts/dbgrowth.log 2>&1

I will be glad to see your blogs on this as well if any problem during configuring just let me know the error and problem by blog.