How to compare table/columns/index/constraints structure across two different environments in oracle without using db link and not having central repository where data files can be stored.
We cannot create db links. We do not have central repository where files containing structural data from different environments can be stored in. We can send data file in an separate email for each environments.
WARNING: this is not a complete, don't-need-to-learn-or-think solution. Sorry.
Anway, you can Use this DBMS_METADATA in each environment to extract the structure of each object you want. Like below. You'll have to read about DBMS_METADATA.SET_TRANSFORM_PARAM to configure it to include constraints and storage and whatnot. I don't think it will include INDEXES in any case, so you'd need to run though this whole process again, separately, for each index.
Anyway, continuing with just how to compare a table, here's the DBMS_METADATA call you'd want in each environment:
SELECT DBMS_METADATA.GET_SXML (
object_type => 'TABLE',
name => 'MTL_SYSTEM_ITEMS_B',
schema => 'INV',
version => 'COMPATIBLE',
model => 'ORACLE',
transform => 'SXML' )
FROM DUAL;
Then, email the output from each environment to yourself and then load them into a test environment. Then, use DBMS_METADATA_DIFF to generate a report of structural differences. E.g.,
-- Pseudocode
DECLARE
l_handle NUMBER;
l_sxml_environment1 SYS.XMLType;
l_sxml_environment2 SYS.XMLType;
l_difference_report CLOB;
BEGIN
-- LEFT FOR READER. ADD CODE HERE TO LOAD THE SXML YOU EMAILED
-- FROM EACH ENVIRONMENT INTO l_sxml_environment1 and ..2 VARIABLES.
l_handle := DBMS_METADATA_DIFF.OPENC(OBJECT_TYPE => 'TABLE');
DBMS_METADATA_DIFF.ADD_DOCUMENT(handle=>l_handle, document => l_sxml_environment1);
DBMS_METADATA_DIFF.ADD_DOCUMENT(handle=>l_handle, document => l_sxml_environment2);
l_difference_report := DBMS_METADATA_DIFF.FETCH_CLOB(handle=>l_handle);
-- LEFT FOR READER: Put l_difference_report (a CLOB value) where you want. This is the output you are looking for.
DBMS_METADATA_DIFF.CLOSE(handle=>l_handle);
END;
Or, as a MUCH MUCH EASIER alternative, use a tool that does this.
SQL*Developer has the Tools -> Database Diff... function that will do all this for you easy-peasy. You don't need database links, only a SQL*Developer connection defined for each environment.
DBA_orALL_dictionary views and write to a file in some programmatically parseable format. Run against two databases, then write a program to compare the two. There are quite a few views to pull from, though. You could usedbms_metadatabut the output wouldn't be something you can easily parse. This kind of task is common to tools like ERWin and other data modeling utilities that are geared to running diffs on schemas.