| Comparision script [message #23280] |
Tue, 26 November 2002 16:52  |
Sachin
Messages: 52
Registered: May 2001
|
Member |
|
|
Hi,
I would like to compare the objects from 2 different user. Do you have a script for this. My aim is actually to compare objects like table, indexes, views, procedures, funtions etc. This is actually a comparision between development and release env.
Any help appriciated.This is urgent
Sachin
|
|
|
|
| Re: Comparision script [message #23281 is a reply to message #23280] |
Tue, 26 November 2002 19:15  |
jiltin
Messages: 44
Registered: September 2002
|
Member |
|
|
I have given a query for simple table comparision. If you want for other objects, query similar one using "dba_objects" in place of "dba_tables".
1. You need to join similar object types.
2. You need privileges to query dba_objects.
3. There is also view called "all_objects" as given below:
SQL> desc all_objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc dba_objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select u1.table_name,u2.table_name
2 from ( select table_name from dba_tables where
3 owner='SU') U1,
4 ( select table_name from dba_tables where
5 owner='STUDENT' ) U2
6 where u1.table_name = u2.table_name (+);
TABLE_NAME TABLE_NAME
------------------------------ ------------------------------
BONUS
CUSTOMER
DATERANGE
DEPT DEPT
DETAIL
DUMMY
EMP EMP
EMPINFO
EMPLOYEE
EXCEPTION_TABLE
ITEM
TABLE_NAME TABLE_NAME
------------------------------ ------------------------------
MASTER
ORD
PRICE
PRODUCT
SALGRADE
SAMPLE
X_TABLES
18 rows selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
