Can any body guide me in script

oracle 8.1.7 and windows 2000 advance server

I have three INSTANCE set up
production
demo
test
Now the thing is that each of the instance has to be in sync with each other.

example - all the NUMBER OF tables, rows, triggers , procedures should be all equal in these 3 instances.
Note the oracle username is same in all 3 instances.

Currently they are not equal in numbers.

So i am thinking of writing a script , when i run it should tell me the
1 COMPARE and list the total number of tables in the user in
each instance
2 If the Total number of objects donot match then it should
list out what is the difference in objects in each instance.
3 difference in tables in each instance , where column
names is extra or less.

what happens is now that in production instance in a particular user we add new columns in x number of tables , but we forget to do in test and demo instance.
so in short all the objects are not in sync.

Is a script possible in this situation. Suggestion please

Basically you need to have a dblink established from one db to the rest of the dbs.

Execute few basic sql's say like select table_name,count(column_name) from dba_tab_columns
union
select ..... from dba_tab_columns@dblinkname.

Badrinath

Sorry, but I'd have to charge you some money to do this one. There is a flaw in your logic/comparison technique - total number of objects may be the same among instances, but WHAT those objects are could be entirely different.

You could spool out some queries, then use diff in UNIX to compare the files (you'd have to clean up the headers, titles, stuff like that first).

Rightly said!!!

If you are using windows, what you can even do is get the required stat into a temp table and query that table to get a report which looks something like this.


Num of rows:

Object: Prod: Test: dev:

emp 20 18 200
dept 5 2 9

Num of col

object Prod Test Dev:

emp 6 4 6
dept 3 3 5



Any way this requires selecting different types of info (row,columns) etc and insert them into a temp table to print out a report in the above format.

Badrinath

Quote:

---

Originally posted by stecal
Sorry, but I'd have to charge you some money to do this one. There is a flaw in your logic/comparison technique - total number of objects may be the same among instances, but WHAT those objects are could be entirely different.

You could spool out some queries, then use diff in UNIX to compare the files (you'd have to clean up the headers, titles, stuff like that first).

---

It your your boss who pay your salary and your are spend his money here then .... ;)

Cheers

Angel