Quick Reference on Database Characterset.

Recently i was upgrading the Oracle apps 11i to R12 and the customer want Arabic NLS. So i need to convert my database characterset form US7ASCII to a characterset that supports Arabic. I have confused which one should i use, UTF8 or AL32UTF8.  I did some reserch on oracle support and decided to use AL32UTF8.  Below given points helped me to make that decision.

The default UTF-8 (Unicode) characterset for 9i/10G is AL32UTF8, however this characterset is NOT recognized by any pre-9i clients/server systems.

Recommend that you use UTF8 instead of AL32UTF8 as database characterset if you have 8i (or older) servers and clients connecting to the 9i/10g system
until you can upgrade the older versions to 9i or higher.
UTF8 is Unicode revision 3.0 in 8.1.7 and up. AL32UTF8 is updated with newer Unicode versions in each major release

Besides the difference in Unicode version the "big difference" is that AL32UTF8 has build in support for "Surrogate Pairs", also known as "Surrogate characters"
or "Supplementary characters". Practically this means that in 99% of the cases you can use UTF8 instead of AL32UTF8 without any problem.
There is no performance difference between UTF8 and AL32UTF8. But seen UTF8 is NOT updated with newer Unicode versions we however suggest to use AL32UTF8 when possible.

Summary:
If you use 8.1 or 8.0 clients or servers or connect to 8.1 or 8.0 databases then use UTF8 as NLS_CHARACTERSET for the 9i (or up) databases, otherwise
(unless the application vendor explicit mentions UTF8 of course) use AL32UTF8.

For an Unicode database Oracle does not need "Unicode support" from the OS where the database is running on because the Oracle AL32UTF8 implementation is not depending on OS features
There is also no need to "install Unicode" or so for the Oracle database/client software, all character sets known in a database version , and this includes
Unicode character sets, are always installed. You simply cannot choose to not install them
If your current Oracle version is 8.1.7 or lower then it's best to upgrade first to a higher release, mainly because
a) you then can use AL32UTF8 (not possible in 8i)
b) Csscan has a few issues in 817 who might provoke confusion.
If your current Oracle version is 9i or up then both (before or after) are a good choice, it simply depends on your preference or needed application changes
Storage.
AL32UTF8 is a varying width characterset, which means that the code for 1 character can be 1 , 2 , 3 or 4 bytes long. This is a big difference with
character sets like WE8ISO8559P1 or WE8MSWIN1252 where 1 character is always 1 byte.

Salvage Data from corrupted Oracle Table

Recently, when i query a table with full table scan it gives error and i failing.
It given error ORA-8103 object no longer exists

Then i tried to do analyze the table using validate structure option, that also given same error.
SO it look like there is some kond of corruption. I restored some old backup and checked the table again, that also reported the same error.
Now i will not be able to restore the table from the backup as error persist for long time.

I have two indexs attached to this table, so I tried analyzing the Indexes and that also reported same error.
Then i tried to repair the table using DBMS_REPAIR package and it crashed with ORA-600 error.

Now i have only one option left, somehow extract the data that can be retrived from the table.

Oracle is providing a method to retrive recoverable rows by getting rowid of the row using index. Recovered rows will be insert into new table.

table data can be saved by skipping the blocks that are causing the ORA-8103 error:

Following are the step i performed to recover data.

Disclaimer: This is just for information and accademic purpose only.

Pre-requisite: Table must have an indexed column.

Step 1: Create a new table structure similar to original table

Create table <new table name> as select * from <original table name>
where 1<>1

Step 2: Create a table to store bad row's rowid and error numnber.

create table bad_rows (row_id rowid
                      ,oracle_error_code number);

Step 3: Recover Data by executing below pl/sql block (Replace the table names, columns as per your need)

set serveroutput on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

  CURSOR c1 IS select /*+ index(tab1) */ rowid
  from <original table name> tab1
  where <indexed column> is NOT NULL;

  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into <new table name>
     select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
     from <original table name> A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

Step 4: Note down the indexes on original table for creating on new table

Step 5: Rename the Original Table

Step 6: Drop the indexes

Step 7: Rename the new table to original table name

Step 8: Recreate the indexes

Step 9: Recreate the synoyms & grants

Step 10: Compile invalid objects any depends on this table.

========================