Mennan On Writing...

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));

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>

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;

I demonstrate a simple example in order to show how to read-write PGM files.

using System;

namespace PGM
{
    class Program
    {
        static void Main(string[] args)
        {
            string InputFileName = "lenna.pgm"; //args[0]

            //read PGM 
            PGM Picture = new PGM(InputFileName);
            //Print info
            Picture.PrintPGMInfo();
            //Set image data 
            //Picture.Data
            //Save PGM
            Picture.Save("new_"+InputFileName);

            Console.ReadLine();
            
        }
    }
}

using System;
using System.IO;

namespace PGM
{
    public class PGM
    {
        private int mWidth;
        private int mLength;
        private int mColor;
        private string mType;
        private byte[] mData;
        private string mComments;

        public string Comment
        {
            get { return this.mComments; }
        }

        public int Width
        {
            get { return this.mWidth; }
        }

        public int Length
        {
            get { return this.mLength; }
        }

        public int ColorSize
        {
            get { return this.mColor; }
        }
        public string Type
        {
            get
            {
                return this.mType;
            }
        }

        public string Header
        {
            get
            {
                return this.Type + Convert.ToChar(10) +
                       '#' + this.Comment + Convert.ToChar(10) +
                        this.mWidth.ToString() + " " + this.mLength.ToString() + Convert.ToChar(10) +
                        this.mColor.ToString() + Convert.ToChar(10);
            }
        }

        public byte[] Data
        {
            get { return this.mData; }
            set { this.mData = value; }
        }


        public PGM(string _filePath)
        {
            ReadPGM(_filePath);
        }

        public void Save(string _filePath)
        {
            WritePGM(_filePath);
        }

        private void ReadPGM(string _filePath)
        {
            FileStream InputStream = File.OpenRead(_filePath);
            BinaryReader PGMReader = new BinaryReader(InputStream);
            char[] Seperators = { ' ', '\n' };

            byte NewLineAsciiCode = 10;
            byte DiezAsciiCode = 35;
            byte SpaceAsciiCode = 32;
            byte[] TempArray = new byte[1000];
            int i = 0;

            string TempS;
            byte TempByte;


            /* Sample PGM :
             * 
             * 
             * P2
             * # Created by ...
             * 512 512
             * 255
             * [data]
             */


            //read PGM Type P2, P5
            TempArray[0] = PGMReader.ReadByte();
            TempArray[1] = PGMReader.ReadByte();
            this.mType = System.Text.ASCIIEncoding.Default.GetString(TempArray, 0, 2);

            //read until new line
            while (PGMReader.ReadByte() != NewLineAsciiCode) { ;}

            //read comments if exists. Only one comment line supported!!
            i = 0;
            TempArray[i] = PGMReader.ReadByte();

            if (TempArray[i] == DiezAsciiCode)
            {
                TempByte = PGMReader.ReadByte();
                while (TempByte != NewLineAsciiCode)
                {
                    TempArray[i++] = TempByte;
                    TempByte = PGMReader.ReadByte();
                }
            }
            this.mComments = System.Text.ASCIIEncoding.Default.GetString(TempArray, 0, i);

            //read width
            i = 0;
            TempByte = PGMReader.ReadByte();
            while (TempByte != SpaceAsciiCode)
            {
                TempArray[i++] = TempByte;
                TempByte = PGMReader.ReadByte();
            }

            TempS = System.Text.ASCIIEncoding.Default.GetString(TempArray, 0, i);
            this.mWidth = Convert.ToInt32(TempS);

            //read length
            i = 0;
            TempByte = PGMReader.ReadByte();
            while (TempByte != NewLineAsciiCode)
            {
                TempArray[i++] = TempByte;
                TempByte = PGMReader.ReadByte();
            }

            TempS = System.Text.ASCIIEncoding.Default.GetString(TempArray, 0, i);
            this.mLength = Convert.ToInt32(TempS);

            //read color
            i = 0;
            TempByte = PGMReader.ReadByte();
            while (TempByte != NewLineAsciiCode)
            {
                TempArray[i++] = TempByte;
                TempByte = PGMReader.ReadByte();
            }

            TempS = System.Text.ASCIIEncoding.Default.GetString(TempArray, 0, i);
            this.mColor = Convert.ToInt32(TempS);

            //read image data
            byte[] PGMDataBuffer = new byte[this.mWidth * this.mLength];
            int k = 0;
            if (this.mType == "P5")
            {
                //If file is binary, read every byte
                byte[] ReadedByte = PGMReader.ReadBytes(PGMDataBuffer.Length);
                Array.Copy(ReadedByte, PGMDataBuffer, ReadedByte.Length);
            }
            else if (this.mType == "P2")
            {
                //If file is text based every pixel is distinguished by "space" and it has up to 3 chars(255)
                try
                {
                    TempByte = PGMReader.ReadByte();
                    while (TempByte != -1)
                    {
                        i = 0;
                        while (TempByte != NewLineAsciiCode && TempByte != SpaceAsciiCode)
                        {
                            TempArray[i++] = TempByte;
                            TempByte = PGMReader.ReadByte();
                        }

                        TempS = System.Text.ASCIIEncoding.Default.GetString(TempArray, 0, i);
                        i = 0;
                        //TempS contains, string representation of every pixel
                        PGMDataBuffer[k++] = Convert.ToByte(TempS);

                        TempByte = PGMReader.ReadByte();
                        if (TempByte == NewLineAsciiCode || TempByte == SpaceAsciiCode)
                        {
                            TempByte = PGMReader.ReadByte();
                        }
                    }
                }
                catch (Exception e)
                {
                    //Console.WriteLine(e.InnerException);
                    ;
                }
            }
            this.mData = PGMDataBuffer;

            PGMReader.Close();
            InputStream.Close();
        }
        private void WritePGM(string _filePath)
        {
            FileStream OutputStream = File.Create(_filePath);
            BinaryWriter PGMWriter = new BinaryWriter(OutputStream);

            string PGMInfo = this.Header;
            byte[] PGMInfoBuffer = System.Text.ASCIIEncoding.Default.GetBytes(PGMInfo);
            PGMWriter.Write(PGMInfoBuffer);
            if (this.mType == "P5")
            {
                //File is binary, write complete data
                PGMWriter.Write(this.mData);
            }
            else if (this.mType == "P2")
            {
                byte NewLineAsciiCode = 10;
                byte SpaceAsciiCode = 32;
                int Temp;

                for (