dbms_stats.gather_table_stats(....degree=>6....,cascade=>true)

Hi all,

Today I was given with a question, why a dbms_stats with degree=>6 created 12 parallel process instead of 6 process.

I was wondering, how come Oracle create 12 process where as degree is only 6 process.  I think many of you might know already.

Oracle creates 6 parallel process as requested, but because of "cascade=>true", it spawns another 6 process to collect statistics from Index.

Oracle uses the following hint to run the stats collect process.

/*+ PARALLEL(t, 6) PARALLEL_INDEX(t,6) */

ORA-00001: unique constraint(schema.table_name) violated

I faced an error from a production ERP system, which the trace file shows "Unique constraint violated".  Actually this process inserts rows from temporary table to target table.  The temporary table has got 1000 of rows.  Easy way to check the error,

1) check which columns are part of the unique constraints

    select constraint_name,constraint_type,search_condition from dba_constraints where table_name=<TABLE_NAME>;

In my case the target table has got check constraints, so can't find it in dba_constraints.

2) check unique index for the target table

    select index_name,uniqueness from dba_indexes where table_name=<TABLE_NAME>;
    select column_name,column_position from dba_ind_columns where index_name=<PREVIOUS STEP VALUE>;

3) check target table rows compare with temporary table rows for the specific unique index columns.

4) Correct the data.

Modify table with few additional columns with not null constraint and default value

There was a new requirement to add few columns to a big table (few gigs in size) with not null constraints and default value.

The developer came up with a script and asked me to review,

alter table T1 add F1 varchar2(10);
alter table T1 add F2 varchar2(10);
alter table T1 add F3 varchar2(10);

update T1 set F1=' ',F2=' ',F3=' ';


alter table T1 modify F1 not null;
alter table T1 modify F2 not null;
alter table T1 modify F3 not null;



Oracle has fantastic feature to do all the above steps in one go ....:()   Here you go....

alter table T1 add (F1 varchar2(10) default ' ' not null, F2 varchar2(10) default ' ' not null, F3 varchar2(10) default ' ' not null);

The above DDL ran faster and easy to manage it.

How to move a index from one tablespace to another

How to move index from tablespace to another tablespace online.

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ---------------

SYS                                  TEST_SC                               OLD_INDEX

I want to move TEST_SC which is wrongly created in OLD_INDEX tablespace.  User want to move to NEW_INDEX tablespace,

SQL>alter index <INDEX_NAME> rebuild tablespace <NEW TABLESPACE> PARALLEL NOLOGGING;

SQL>alter index TEST_SC rebuild tablespace NEW_INDEX PARALLEL;

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ---------------

SYS                                  TEST_SC                               NEW_INDEX

User carefully with NOLOGGING option if you have DR. 

If you are moving large index, check in V$SESSION_LONGOPS to see how much it progressed and how long it will take (approx).