24 December 2007

A simple demonstration about how to comparing 2 Oracle Database Instance(or Schema) objects(such as tables)

I want to show, how to compare objects of 2 oracle instances or schemas. Below, i show indexes and referential constraints. You can write for all other objects. There are some tools or scripts to compare 2 schemas. But they compare by object names. For instance if an index is renamed, you can see that as if the index does not exist. I compare the objects by their structures:

--QUERY_001
--This query compares SOURCE_DBLINK and current database in terms of INDEXes.
--Comparison is done only by INDEX_NAMEs. Renamed-indexes can not be found!!!
--Returns INDEXes which exist in only SOURCE_DBLINK

SELECT index_name, table_name
  FROM all_indexes@SOURCE_DBLINK md
WHERE table_owner = 'DBUSER'
   AND NOT EXISTS (SELECT 1
          FROM all_indexes db
         WHERE md.table_owner = db.table_owner
           AND md.index_name  = db.index_name);

--QUERY_002
--This query compares SOURCE_DBLINK and current database in terms of INDEXes.
--Comparison is done by index structure. Renamed-indexes can be found.This is am enhancement of QUERY_001
--Returns INDEXes which exist in only SOURCE_DBLINK

SELECT *
  FROM all_ind_columns@SOURCE_DBLINK md
WHERE table_owner = 'DBUSER'
   AND index_owner = 'DBUSER'
   AND NOT EXISTS (SELECT 1
          FROM all_ind_columns db
         WHERE md.table_owner     = db.table_owner
           AND md.index_owner     = db.index_owner
           AND md.table_name      = db.table_name
           AND md.column_name     = db.column_name
           AND md.column_position = db.column_position);
--QUERY_003
--This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints.
--Comparison is done only by CONTRAINT_NAMEs. Renamed-ref-constraints can not be found!!!
--Returns REF-CONTRAINTs which exist in only SOURCE_DBLINK
        
SELECT constraint_name, table_name,
  FROM all_constraints@SOURCE_DBLINK md
WHERE owner = 'DBUSER'
   AND constraint_type = 'R'
   AND NOT EXISTS
(SELECT 1
          FROM all_constraints db
         WHERE md.owner = db.owner
           AND md.constraint_type = db.constraint_type
           AND md.constraint_name = db.constraint_name);
--QUERY_004
--This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints.
--Comparison is done by constraint structure. Renamed-ref-constraints can be found.This is am enhancement of QUERY_003
--Returns REF-CONTRAINTs which exist in only SOURCE_DBLINK
         
SELECT *
  FROM all_cons_columns@SOURCE_DBLINK md
WHERE owner = 'DBUSER'
   AND NOT EXISTS
(SELECT 1
          FROM all_cons_columns db
         WHERE md.owner = db.owner
           AND md.table_name = db.table_name
           AND md.column_name = db.column_name
           AND NVL(md.position, 0) = NVL(db.position, 0));

23 December 2007

Solution of ORA-02070 "database .... does not support ..... In this context"

Today, when i checking migration reliability i got an error, ORA-02070. When i analyzed that i realized that columns which are LONG data-type were caused of this problem:

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as DBUSER

SQL>
SQL> SELECT *
  2    FROM all_constraints@UAT md
  3   WHERE owner = 'DBUSER'
  4     AND constraint_type = 'R'
  5     AND NOT EXISTS
  6   (SELECT 1
  7            FROM all_constraints db
  8           WHERE md.owner = db.owner
  9             AND md.constraint_type = db.constraint_type
10             AND md.constraint_name = db.constraint_name);

SELECT *
  FROM all_constraints@UAT md
WHERE owner = 'DBUSER'
   AND constraint_type = 'R'
   AND NOT EXISTS
(SELECT 1
          FROM all_constraints db
         WHERE md.owner = db.owner
           AND md.constraint_type = db.constraint_type
           AND md.constraint_name = db.constraint_name)

ORA-02070: veritabanı UAT bu içerik içinde operator USERENV desteklemez
database UAT does not support  USERENV in this context

SQL> describe all_constraints@UAT;
Object all_constraints@UAT does not exist.

SQL> describe all_constraints;
Name              Type         Nullable Default Comments                                                                   
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER             VARCHAR2(30)                  Owner of the table                                                         
CONSTRAINT_NAME   VARCHAR2(30)                  Name associated with constraint definition                                 
CONSTRAINT_TYPE   VARCHAR2(1)  Y                Type of constraint definition                                              
TABLE_NAME        VARCHAR2(30)                  Name associated with table with constraint definition                      
---->>>>
SEARCH_CONDITION  LONG         Y                Text of search condition for table check                                   
----<<<<
R_OWNER           VARCHAR2(30) Y                Owner of table used in referential constraint                              
R_CONSTRAINT_NAME VARCHAR2(30) Y                Name of unique constraint definition for referenced table                  
DELETE_RULE       VARCHAR2(9)  Y                The delete rule for a referential constraint                               
STATUS            VARCHAR2(8)  Y                Enforcement status of constraint - ENABLED or DISABLED                     
DEFERRABLE        VARCHAR2(14) Y                Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE                
DEFERRED          VARCHAR2(9)  Y                Is the constraint deferred by default -  DEFERRED or IMMEDIATE             
VALIDATED         VARCHAR2(13) Y                Was this constraint system validated? -  VALIDATED or NOT VALIDATED        
GENERATED         VARCHAR2(14) Y                Was the constraint name system generated? -  GENERATED NAME or USER NAME   
BAD               VARCHAR2(3)  Y                Creating this constraint should give ORA-02436.  Rewrite it before 2000 AD.
RELY              VARCHAR2(4)  Y                If set, this flag will be used in optimizer                                
LAST_CHANGE       DATE         Y                The date when this column was last enabled or disabled                     
INDEX_OWNER       VARCHAR2(30) Y                The owner of the index used by this constraint                             
INDEX_NAME        VARCHAR2(30) Y                The index used by this constraint                                          
INVALID           VARCHAR2(7)  Y                                                                                           
VIEW_RELATED      VARCHAR2(14) Y                                                                                           

SQL>
SQL> SELECT constraint_name, table_name
  2    FROM all_constraints@UAT md
  3   WHERE owner = 'DBUSER'
  4     AND constraint_type = 'R'
  5     AND NOT EXISTS
  6   (SELECT 1
  7            FROM all_constraints db
  8           WHERE md.owner = db.owner
  9             AND md.constraint_type = db.constraint_type
10             AND md.constraint_name = db.constraint_name);

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
....                           .....
....                           .....
....                           .....

26 rows selected
SQL>

18 December 2007

Sequence In Oracle RAC System might not generate numbers In a sequence!

Today, after migration to RAC system, i realized that sequence numbers(usally for generating primary keys) are not generated in a sequence. Applications that read historical data would fail because of that.  Before RAC system ;
ID    INSERT_DATE
1     16.12.2007 16:21:00
2     16.12.2007 16:21:10
3     16.12.2007 16:23:49

Note that ID field is generated with an Oracle SEQUENCE.

After migration to RAC it became as;
ID    INSERT_DATE
81     16.12.2007 19:43:23
82     16.12.2007 16:39:00
83     16.12.2007 16:46:08

As you see, insert date of 82 is before than insert date of 81. There is a mismatch with insertion dates and sequences. Then i read Oracle documentation:

ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

So, in order to keep numbers in an order, i altered the sequence and the problem was solved:
SQL>ALTER SEQUENCE SEQ_ID ORDER;