Online Redefinition fails with ORA-23540: Redefinition not defined or initiated

Problem Description
While doing online redefinition on a table dbms_redefinition.copy_table_dependents fails with error ORA-23540 as below.

SQL> set serverout on
SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('MUCI', 'HISTORY', 'HISTORY_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /
declare
*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1662
ORA-06512: at line 4

Cause of the problem
There may be different causes against it. If the schema specified in the dbms_redefinition.copy_table_dependents procedure does not exist already in the database then above error can appears. Suppose it may be the case that you specified schema name as lower case but by default username is created in uppercase.

There may be other reason is that the redefinition process is not yet started or initiated by dbms_redefinition.start_redef_table over the table. In fact dbms_redefinition.start_redef_table function create a materialized view on the table. If the materialized view is not yet created then ORA-23540 come.

Solution of the Problem
Let's see whether materialized view created on the underlying table.
SQL> select master,log_table from dba_mview_logs where log_owner='MUCI' and master='HISTORY';
no rows selected

Check for underlying table under the user.
SQL> desc muci.history;
ERROR:
ORA-04043: object muci.history does not exist

In fact user MUCI is not existed on the system and hence error comes.

The same error also appears if redefinition is not defined on the table. Suppose here schema MUCI_FINAL exist and also table HISTORY_DETAIL as well as interim table HISTORY_ but redefinition is not defined.

SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('MUCI_FINAL', 'HISTORY_DETAIL', 'HISTORY_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /
declare
*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1662
ORA-06512: at line 4

Check for tables by,

SQL> select table_name from dba_tables where owner='MUCI_FINAL' and table_name like 'HISTORY%';

TABLE_NAME
------------------------------
HISTORY_DETAIL_
HISTORY_DETAIL
HISTORY_
HISTORY

See materialized view log exists on HISTORY (ran dbms_redefinition.start_redef_table earlier) and hence dbms_redefinition.copy_table_dependents procedure can be run over HISTORY table.

SQL> select master,log_table from dba_mview_logs where log_owner='MUCI_FINAL' and master='HISTORY';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
HISTORY                        MLOG$_HISTORY
SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('MUCI_FINAL', 'HISTORY', 'HISTORY_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /
errors := 0

PL/SQL procedure successfully completed.

Online Redefinition fails with ORA-23540: Redefinition not defined or initiated

Problem Description
While doing online redefinition on a table dbms_redefinition.copy_table_dependents fails with error ORA-23540 as below.

SQL> set serverout on
SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('MUCI', 'HISTORY', 'HISTORY_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /
declare
*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1662
ORA-06512: at line 4

Cause of the problem
There may be different causes against it. If the schema specified in the dbms_redefinition.copy_table_dependents procedure does not exist already in the database then above error can appears. Suppose it may be the case that you specified schema name as lower case but by default username is created in uppercase.

There may be other reason is that the redefinition process is not yet started or initiated by dbms_redefinition.start_redef_table over the table. In fact dbms_redefinition.start_redef_table function create a materialized view on the table. If the materialized view is not yet created then ORA-23540 come.

Solution of the Problem
Let's see whether materialized view created on the underlying table.
SQL> select master,log_table from dba_mview_logs where log_owner='MUCI' and master='HISTORY';
no rows selected

Check for underlying table under the user.
SQL> desc muci.history;
ERROR:
ORA-04043: object muci.history does not exist

In fact user MUCI is not existed on the system and hence error comes.

The same error also appears if redefinition is not defined on the table. Suppose here schema MUCI_FINAL exist and also table HISTORY_DETAIL as well as interim table HISTORY_ but redefinition is not defined.

SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('MUCI_FINAL', 'HISTORY_DETAIL', 'HISTORY_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /
declare
*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1662
ORA-06512: at line 4

Check for tables by,

SQL> select table_name from dba_tables where owner='MUCI_FINAL' and table_name like 'HISTORY%';

TABLE_NAME
------------------------------
HISTORY_DETAIL_
HISTORY_DETAIL
HISTORY_
HISTORY

See materialized view log exists on HISTORY (ran dbms_redefinition.start_redef_table earlier) and hence dbms_redefinition.copy_table_dependents procedure can be run over HISTORY table.

SQL> select master,log_table from dba_mview_logs where log_owner='MUCI_FINAL' and master='HISTORY';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
HISTORY                        MLOG$_HISTORY
SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('MUCI_FINAL', 'HISTORY', 'HISTORY_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /
errors := 0

PL/SQL procedure successfully completed.

Block or Accept Oracle access by IP Address

You sometimes may wish to access to logon to your database filtered by IP address. Suppose you will allow to connect to database having a list of IP address. Or you like to ban a list of IP addresses in order to deny logon as a database user.

With oracle this scenario can be achieved, however this seems to me a bit of fun.

The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora.

In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line,

tcp.validnode_checking = yes

This in fact, turns on the hostname/IP checking for your listeners. After this, with
tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such:


tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (192.168.100.101,192.168.100.160)

Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.

Some rules for entering invited/excluded nodes:

1. You cannot use wild cards in your specifications.
2. You must put all invited nodes in one line; likewise for excluded nodes.
3. You should always enter localhost as an invited node.

Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.

To do so,
$lsnrctl stop
$lsnrctl start

A simple example: Suppose in your database server you simply allow the host containing IP Address 192.168.100.2 and 192.168.100.3. Then your sqlnet.ora file look like,

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,192.168.100.2,192.168.100.3)

Block or Accept Oracle access by IP Address

You sometimes may wish to access to logon to your database filtered by IP address. Suppose you will allow to connect to database having a list of IP address. Or you like to ban a list of IP addresses in order to deny logon as a database user.

With oracle this scenario can be achieved, however this seems to me a bit of fun.

The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora.

In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line,

tcp.validnode_checking = yes

This in fact, turns on the hostname/IP checking for your listeners. After this, with
tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such:


tcp.invited_nodes = (hostname1, hostname2)
tcp.excluded_nodes = (192.168.100.101,192.168.100.160)

Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.

Some rules for entering invited/excluded nodes:

1. You cannot use wild cards in your specifications.
2. You must put all invited nodes in one line; likewise for excluded nodes.
3. You should always enter localhost as an invited node.

Once you have set up your rules and enabled valid node checking, you must restart your listeners to reap the benefits.

To do so,
$lsnrctl stop
$lsnrctl start

A simple example: Suppose in your database server you simply allow the host containing IP Address 192.168.100.2 and 192.168.100.3. Then your sqlnet.ora file look like,

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,192.168.100.2,192.168.100.3)

ORA-01033: ORACLE initialization or shutdown in progress

Error Description
While connecting to database user get the error,
ORA-01033: ORACLE initialization or shutdown in progress

Cause of The Problem
SYSDBA used issued STARTUP command to the database and while starting up database, connecting to database as normal user will get the error ORA-01033.

There may be the scenario that SHUTDOWN command waits a long time.
Solution of The Problem
Scenario 01:
Based on the situation STARTUP may take fewer minutes. As a normal user what more you do is to wait few minutes and try again. You will succeed if database is open state.

Here is the waiting result.
C:\Documents and Settings\Queen>sqlplus shaik/a

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 7 17:34:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Enter user-name: shaik
Enter password:
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Enter user-name: shaik
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

At third attempts I became succeed.

Scenario 02:
If you have SYSDBA privilege then connect to database as SYSDBA and see the status of the database.
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

As we can see that database gradually became usable state. Someone issued STARTUP and it take some times to be in READ WRITE state.

Scenario 03:
If shutdown takes more times suppose SHUTDOWN NORMAL then issue,
SHUTDOWN ABORT;
and later normal startup,
STARTUP;

How to convert non-partitioned table to partition table using re-definition

We will do partition of table OUT_CDR which reside on CR_2 schema. We will do partition on column CDATE using RANGE partitioning technique. We will not change any table structure other than partition. All indexes, constraints, triggers, privileges defined on the table will be remain same.

Step 01: Let's have a look at the table on which we will do partitioning.

SQL> set pagesize 200
SQL> set long 999999
SQL> set linesize 150
SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual;

DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2')
--------------------------------------------------------------------------------

CREATE TABLE "CR_2"."OUT_CDR"
(    "ID" NUMBER(32,0) NOT NULL ENABLE,
     "CDATE" DATE NOT NULL ENABLE,
     "DDATE" DATE NOT NULL ENABLE,
     "ACCTSESSIONID" VARCHAR2(100),
     "CALLINGNO" VARCHAR2(100),
     "CALLEDNO" VARCHAR2(100) NOT NULL ENABLE,
     "AREACODE" VARCHAR2(100),
     "PREFIX" VARCHAR2(100),
     "SESSIONTIME" NUMBER(32,0),
     "BILLABLETIME" NUMBER(32,0),
     "RATE" NUMBER(32,4),
     "CALL_COST" NUMBER(32,4),
     "CURRENTBILL" NUMBER(32,4),
     "DISCONNECTCAUSE" VARCHAR2(50),
     "SOURCEIP" VARCHAR2(100),
     "DESTIP" VARCHAR2(100),
     "BILLABLE" NUMBER(32,0) NOT NULL ENABLE,
     "LESS" NUMBER(32,0) NOT NULL ENABLE,
     "ACCID" NUMBER(32,0),
     "IN_DDATE" DATE,
     "IN_PREFIX" VARCHAR2(100),
     "IN_SESSIONTIME" NUMBER(32,0),
     "IN_BILLABLETIME" NUMBER(32,0),
     "IN_RATE" NUMBER(32,4),
     "IN_CALL_COST" NUMBER(32,4),
     "IN_MONEYLEFT" NUMBER(32,4),
     "IN_DISCONNECTCAUSE" VARCHAR2(50),
     "IN_BILLABLE" NUMBER(32,0),
     "IN_LESS" NUMBER(32,0),
     "SWITCH_ID" NUMBER(32,0) NOT NULL ENABLE,
     "USER_ID" NUMBER(32,0) NOT NULL ENABLE,
     "IN_USER_ID" NUMBER(32,0),
     "PROCESSED" NUMBER(1,0),
      CONSTRAINT "OUT_CDR_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ" UNIQUE ("CDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ_2" UNIQUE ("DDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "OUT_CDR_NEW_SPC"

Step 02: Let's determine if the table OUT_CDR can be redefined online.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.

Step 03: Create a interim table which holds the same structure as the original table except constraints, indexes, triggers but add the partitioning attribute.
I named the interim table as OUT_CDR_. Later we may drop it.

SQL> CREATE TABLE "CR_2"."OUT_CDR_"
2    (    "ID" NUMBER(32,0),
3         "CDATE" DATE ,
4         "DDATE" DATE ,
5         "ACCTSESSIONID" VARCHAR2(100),
6         "CALLINGNO" VARCHAR2(100),
7         "CALLEDNO" VARCHAR2(100) ,
8         "AREACODE" VARCHAR2(100),
9         "PREFIX" VARCHAR2(100),
10         "SESSIONTIME" NUMBER(32,0),
11         "BILLABLETIME" NUMBER(32,0),
12         "RATE" NUMBER(32,4),
13         "CALL_COST" NUMBER(32,4),
14         "CURRENTBILL" NUMBER(32,4),
15         "DISCONNECTCAUSE" VARCHAR2(50),
16         "SOURCEIP" VARCHAR2(100),
17         "DESTIP" VARCHAR2(100),
18         "BILLABLE" NUMBER(32,0) ,
19         "LESS" NUMBER(32,0) ,
20         "ACCID" NUMBER(32,0),
21         "IN_DDATE" DATE,
22         "IN_PREFIX" VARCHAR2(100),
23         "IN_SESSIONTIME" NUMBER(32,0),
24         "IN_BILLABLETIME" NUMBER(32,0),
25         "IN_RATE" NUMBER(32,4),
26         "IN_CALL_COST" NUMBER(32,4),
27         "IN_MONEYLEFT" NUMBER(32,4),
28         "IN_DISCONNECTCAUSE" VARCHAR2(50),
29         "IN_BILLABLE" NUMBER(32,0),
30         "IN_LESS" NUMBER(32,0),
31         "SWITCH_ID" NUMBER(32,0) ,
32         "USER_ID" NUMBER(32,0) ,
33         "IN_USER_ID" NUMBER(32,0),
34         "PROCESSED" NUMBER(1,0)
35          ) TABLESPACE "OUT_CDR_NEW_SPC"
36  Partition by range(cdate)
37  (
38  partition P08152008 values less than (to_date('15-AUG-2008','DD-MON-YYYY')),
39  partition P09012008 values less than (to_date('01-SEP-2008','DD-MON-YYYY')),
40  partition P09152008 values less than (to_date('15-SEP-2008','DD-MON-YYYY')),
41  partition P10012008 values less than (to_date('01-OCT-2008','DD-MON-YYYY')),
42  partition P10152008 values less than (to_date('15-OCT-2008','DD-MON-YYYY')),
43  partition P11012008 values less than (to_date('01-NOV-2008','DD-MON-YYYY')),
44  partition P11152008 values less than (to_date('15-NOV-2008','DD-MON-YYYY')),
45  partition P12012008 values less than (to_date('01-DEC-2008','DD-MON-YYYY')),
46  partition P12152008 values less than (to_date('15-DEC-2008','DD-MON-YYYY')),
47  partition P01012009 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),
48  partition P01152009 values less than (to_date('15-JAN-2009','DD-MON-YYYY')),
49  partition P02012009 values less than (to_date('01-FEB-2009','DD-MON-YYYY')),
50  partition PMAX values less than (maxvalue));

Table created.

Step 04: Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.

SQL> exec dbms_redefinition.start_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 05: Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. But this procedure does not clone the already registered dependent objects.

In fact COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which in facr represents the post-redefinition table.

SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('CR_2', 'OUT_CDR', 'OUT_CDR_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /

PL/SQL procedure successfully completed.

Step 06: Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 07: Check the partitioning validation by,

SQL> Select partition_name, high_value from user_tab_partitions where table_name='OUT_CDR';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------------------------------
P01012009                      TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P01152009                      TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P02012009                      TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P08152008                      TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09012008                      TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09152008                      TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10012008                      TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10152008                      TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11012008                      TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11152008                      TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12012008                      TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12152008                      TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PMAX                           MAXVALUE

13 rows selected.

Check index status by,

SQL> select index_name , status from user_indexes where table_name='OUT_CDR';

INDEX_NAME                     STATUS
------------------------------ --------
OUT_CDR_PK                     VALID
OUT_CDR_UQ                     VALID
OUT_CDR_UQ_2                   VALID

Step 08: Drop the interim table OUT_CDR_.
SQL> DROP TABLE OUT_CDR_;
Table dropped.

ORA-01033: ORACLE initialization or shutdown in progress

Error Description
While connecting to database user get the error,
ORA-01033: ORACLE initialization or shutdown in progress

Cause of The Problem
SYSDBA used issued STARTUP command to the database and while starting up database, connecting to database as normal user will get the error ORA-01033.

There may be the scenario that SHUTDOWN command waits a long time.
Solution of The Problem
Scenario 01:
Based on the situation STARTUP may take fewer minutes. As a normal user what more you do is to wait few minutes and try again. You will succeed if database is open state.

Here is the waiting result.
C:\Documents and Settings\Queen>sqlplus shaik/a

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 7 17:34:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Enter user-name: shaik
Enter password:
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Enter user-name: shaik
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

At third attempts I became succeed.

Scenario 02:
If you have SYSDBA privilege then connect to database as SYSDBA and see the status of the database.
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

As we can see that database gradually became usable state. Someone issued STARTUP and it take some times to be in READ WRITE state.

Scenario 03:
If shutdown takes more times suppose SHUTDOWN NORMAL then issue,
SHUTDOWN ABORT;
and later normal startup,
STARTUP;

How to convert non-partitioned table to partition table using re-definition

We will do partition of table OUT_CDR which reside on CR_2 schema. We will do partition on column CDATE using RANGE partitioning technique. We will not change any table structure other than partition. All indexes, constraints, triggers, privileges defined on the table will be remain same.

Step 01: Let's have a look at the table on which we will do partitioning.

SQL> set pagesize 200
SQL> set long 999999
SQL> set linesize 150
SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual;

DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2')
--------------------------------------------------------------------------------

CREATE TABLE "CR_2"."OUT_CDR"
(    "ID" NUMBER(32,0) NOT NULL ENABLE,
     "CDATE" DATE NOT NULL ENABLE,
     "DDATE" DATE NOT NULL ENABLE,
     "ACCTSESSIONID" VARCHAR2(100),
     "CALLINGNO" VARCHAR2(100),
     "CALLEDNO" VARCHAR2(100) NOT NULL ENABLE,
     "AREACODE" VARCHAR2(100),
     "PREFIX" VARCHAR2(100),
     "SESSIONTIME" NUMBER(32,0),
     "BILLABLETIME" NUMBER(32,0),
     "RATE" NUMBER(32,4),
     "CALL_COST" NUMBER(32,4),
     "CURRENTBILL" NUMBER(32,4),
     "DISCONNECTCAUSE" VARCHAR2(50),
     "SOURCEIP" VARCHAR2(100),
     "DESTIP" VARCHAR2(100),
     "BILLABLE" NUMBER(32,0) NOT NULL ENABLE,
     "LESS" NUMBER(32,0) NOT NULL ENABLE,
     "ACCID" NUMBER(32,0),
     "IN_DDATE" DATE,
     "IN_PREFIX" VARCHAR2(100),
     "IN_SESSIONTIME" NUMBER(32,0),
     "IN_BILLABLETIME" NUMBER(32,0),
     "IN_RATE" NUMBER(32,4),
     "IN_CALL_COST" NUMBER(32,4),
     "IN_MONEYLEFT" NUMBER(32,4),
     "IN_DISCONNECTCAUSE" VARCHAR2(50),
     "IN_BILLABLE" NUMBER(32,0),
     "IN_LESS" NUMBER(32,0),
     "SWITCH_ID" NUMBER(32,0) NOT NULL ENABLE,
     "USER_ID" NUMBER(32,0) NOT NULL ENABLE,
     "IN_USER_ID" NUMBER(32,0),
     "PROCESSED" NUMBER(1,0),
      CONSTRAINT "OUT_CDR_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ" UNIQUE ("CDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ_2" UNIQUE ("DDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "OUT_CDR_NEW_SPC"

Step 02: Let's determine if the table OUT_CDR can be redefined online.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.

Step 03: Create a interim table which holds the same structure as the original table except constraints, indexes, triggers but add the partitioning attribute.
I named the interim table as OUT_CDR_. Later we may drop it.

SQL> CREATE TABLE "CR_2"."OUT_CDR_"
2    (    "ID" NUMBER(32,0),
3         "CDATE" DATE ,
4         "DDATE" DATE ,
5         "ACCTSESSIONID" VARCHAR2(100),
6         "CALLINGNO" VARCHAR2(100),
7         "CALLEDNO" VARCHAR2(100) ,
8         "AREACODE" VARCHAR2(100),
9         "PREFIX" VARCHAR2(100),
10         "SESSIONTIME" NUMBER(32,0),
11         "BILLABLETIME" NUMBER(32,0),
12         "RATE" NUMBER(32,4),
13         "CALL_COST" NUMBER(32,4),
14         "CURRENTBILL" NUMBER(32,4),
15         "DISCONNECTCAUSE" VARCHAR2(50),
16         "SOURCEIP" VARCHAR2(100),
17         "DESTIP" VARCHAR2(100),
18         "BILLABLE" NUMBER(32,0) ,
19         "LESS" NUMBER(32,0) ,
20         "ACCID" NUMBER(32,0),
21         "IN_DDATE" DATE,
22         "IN_PREFIX" VARCHAR2(100),
23         "IN_SESSIONTIME" NUMBER(32,0),
24         "IN_BILLABLETIME" NUMBER(32,0),
25         "IN_RATE" NUMBER(32,4),
26         "IN_CALL_COST" NUMBER(32,4),
27         "IN_MONEYLEFT" NUMBER(32,4),
28         "IN_DISCONNECTCAUSE" VARCHAR2(50),
29         "IN_BILLABLE" NUMBER(32,0),
30         "IN_LESS" NUMBER(32,0),
31         "SWITCH_ID" NUMBER(32,0) ,
32         "USER_ID" NUMBER(32,0) ,
33         "IN_USER_ID" NUMBER(32,0),
34         "PROCESSED" NUMBER(1,0)
35          ) TABLESPACE "OUT_CDR_NEW_SPC"
36  Partition by range(cdate)
37  (
38  partition P08152008 values less than (to_date('15-AUG-2008','DD-MON-YYYY')),
39  partition P09012008 values less than (to_date('01-SEP-2008','DD-MON-YYYY')),
40  partition P09152008 values less than (to_date('15-SEP-2008','DD-MON-YYYY')),
41  partition P10012008 values less than (to_date('01-OCT-2008','DD-MON-YYYY')),
42  partition P10152008 values less than (to_date('15-OCT-2008','DD-MON-YYYY')),
43  partition P11012008 values less than (to_date('01-NOV-2008','DD-MON-YYYY')),
44  partition P11152008 values less than (to_date('15-NOV-2008','DD-MON-YYYY')),
45  partition P12012008 values less than (to_date('01-DEC-2008','DD-MON-YYYY')),
46  partition P12152008 values less than (to_date('15-DEC-2008','DD-MON-YYYY')),
47  partition P01012009 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),
48  partition P01152009 values less than (to_date('15-JAN-2009','DD-MON-YYYY')),
49  partition P02012009 values less than (to_date('01-FEB-2009','DD-MON-YYYY')),
50  partition PMAX values less than (maxvalue));

Table created.

Step 04: Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.

SQL> exec dbms_redefinition.start_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 05: Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. But this procedure does not clone the already registered dependent objects.

In fact COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which in facr represents the post-redefinition table.

SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('CR_2', 'OUT_CDR', 'OUT_CDR_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /

PL/SQL procedure successfully completed.

Step 06: Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 07: Check the partitioning validation by,

SQL> Select partition_name, high_value from user_tab_partitions where table_name='OUT_CDR';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------------------------------
P01012009                      TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P01152009                      TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P02012009                      TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P08152008                      TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09012008                      TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09152008                      TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10012008                      TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10152008                      TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11012008                      TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11152008                      TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12012008                      TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12152008                      TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PMAX                           MAXVALUE

13 rows selected.

Check index status by,

SQL> select index_name , status from user_indexes where table_name='OUT_CDR';

INDEX_NAME                     STATUS
------------------------------ --------
OUT_CDR_PK                     VALID
OUT_CDR_UQ                     VALID
OUT_CDR_UQ_2                   VALID

Step 08: Drop the interim table OUT_CDR_.
SQL> DROP TABLE OUT_CDR_;
Table dropped.

ORA-12091: cannot online redefine table with materialized views

Problem Description
While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below.

SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
BEGIN dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); END;

*
ERROR at line 1:
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1

Cause of the Problem
If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the table.

Solution of the Problem
Solution 01:
Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by,
SQL> select log_table from user_snapshot_logs;

LOG_TABLE
------------------------------
MLOG$_IN_CDR
MLOG$_OUT_CDR

SQL> select master,log_table from user_mview_logs;

MASTER                         LOG_TABLE
------------------------------ ------------------------------
IN_CDR                         MLOG$_IN_CDR
OUT_CDR                        MLOG$_OUT_CDR

Remove it by,
SQL> DROP MATERIALIZED VIEW LOG ON IN_CDR;
Materialized view log dropped.

SQL> DROP MATERIALIZED VIEW LOG ON OUT_CDR;
Materialized view log dropped.

You can use the keyword SNAPSHOT in place of MATERIALIZED VIEW. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

Now execute your statement like,
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.
And it works.

Solution 02:
Run the dbms_redefinition.abort_redef_table procedure which will automatically do the clean up task. This procedure will remove the temporary objects that are created by the redefinition process such as materialized view logs.
SQL> exec dbms_redefinition.abort_redef_table('CR_2', 'IN_CDR', 'IN_CDR_');
PL/SQL procedure successfully completed.

ORA-12091: cannot online redefine table with materialized views

Problem Description
While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below.

SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
BEGIN dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); END;

*
ERROR at line 1:
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1

Cause of the Problem
If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the table.

Solution of the Problem
Solution 01:
Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by,
SQL> select log_table from user_snapshot_logs;

LOG_TABLE
------------------------------
MLOG$_IN_CDR
MLOG$_OUT_CDR

SQL> select master,log_table from user_mview_logs;

MASTER                         LOG_TABLE
------------------------------ ------------------------------
IN_CDR                         MLOG$_IN_CDR
OUT_CDR                        MLOG$_OUT_CDR

Remove it by,
SQL> DROP MATERIALIZED VIEW LOG ON IN_CDR;
Materialized view log dropped.

SQL> DROP MATERIALIZED VIEW LOG ON OUT_CDR;
Materialized view log dropped.

You can use the keyword SNAPSHOT in place of MATERIALIZED VIEW. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

Now execute your statement like,
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.
And it works.

Solution 02:
Run the dbms_redefinition.abort_redef_table procedure which will automatically do the clean up task. This procedure will remove the temporary objects that are created by the redefinition process such as materialized view logs.
SQL> exec dbms_redefinition.abort_redef_table('CR_2', 'IN_CDR', 'IN_CDR_');
PL/SQL procedure successfully completed.

IMPDP fails with ORA-39002, ORA-29283: invalid file operation

Problem Description
My impdp operation fails with error ORA-39002, ORA-39070, ORA-29283, ORA-29283: invalid file operation as below.
SQL> host impdp cr_3/cr_3 remap_schema=cr_2:cr_3 dumpfile=skydbcr_2-04-10-2008.dmp directory=e

Import: Release 10.2.0.1.0 - Production on Wednesday, 12 November, 2008 16:42:27

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Cause and Solution of the Problem
1. Check whether the user who run the expdp operation has READ,WRITE permission on the database directory. If not then as a dba user run grant permission on cr_3,
GRANT READ,WRITE ON DIRECTORY E TO CR_3;

2. Check whether the path used in database directory physically exists on the OS.
Whenever you create a directory oracle does not check existence physical directory on OS. The checking is done on runtime.

3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed. On unix issue,
ls -l name_of_the_file and see permission. If not then using chmod grant read, write access to the file to oracle user.
On windows if NTFS file system then you can change it by right clicking on the file or folder and then select sharing and security option.

4. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle. In such case using chown change the ownership of the file on unix. On windows if NTFS file system then you can change it by right clicking on the file or folder and then select sharing and security option.

IMPDP fails with ORA-39002, ORA-29283: invalid file operation

Problem Description
My impdp operation fails with error ORA-39002, ORA-39070, ORA-29283, ORA-29283: invalid file operation as below.
SQL> host impdp cr_3/cr_3 remap_schema=cr_2:cr_3 dumpfile=skydbcr_2-04-10-2008.dmp directory=e

Import: Release 10.2.0.1.0 - Production on Wednesday, 12 November, 2008 16:42:27

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Cause and Solution of the Problem
1. Check whether the user who run the expdp operation has READ,WRITE permission on the database directory. If not then as a dba user run grant permission on cr_3,
GRANT READ,WRITE ON DIRECTORY E TO CR_3;

2. Check whether the path used in database directory physically exists on the OS.
Whenever you create a directory oracle does not check existence physical directory on OS. The checking is done on runtime.

3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed. On unix issue,
ls -l name_of_the_file and see permission. If not then using chmod grant read, write access to the file to oracle user.
On windows if NTFS file system then you can change it by right clicking on the file or folder and then select sharing and security option.

4. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle. In such case using chown change the ownership of the file on unix. On windows if NTFS file system then you can change it by right clicking on the file or folder and then select sharing and security option.

ORA-00600 internal error code

Error Description
In our production server while doing data pump import operation at the end of import it fails with following errors, then the instance crashed and database went down.
Errors in file /var/opt/dumpfile/bdump/shiak_mman_23373.trc:
ORA-00600: internal error code, arguments: [kmgs_pre_process_request_6], [4], [2
], [0], [3], [0xC992C04D0], [], []
Sun Oct 19 18:16:20 2008
MMAN: terminating instance due to error 822
Instance terminated by MMAN, pid = 23373

In the trace file we got the call stack as,
ksedst, ksedmp, ksfdmp, kgerinv, kgeasnmierr, kmgs_pre_process_request, kmgsdrv, ksbabs, ksbrdp, opirip, opidrv, sou2o, opimai_real, main.

Cause of the problem
As we know in Oracle ORA-600 error series are oracle bug. This is oracle Bug 4433838. From alert log we see our MMAN (Memory Manager) process has been terminated. So something wrong happen with oracle MMAN.

This bug actually fires when the initialization parameter SGA_TARGET is set to an exact multiple of 4Gb. So, while setting big SGA be conscious.

A little bit more about this bug is if the SGA_TARGET value in HEXADECIMAL has 00000000 as the last bytes then you can hit this bug.

Detail Explanation of this bug
In out production server we set our SGA_TARGET=48G and this bug fires. We see 48G is multiple of 12 times of 4G. So this bug can error. A little bit elaborate of this issue,
Of 48G we get decimal value as,
SQL> col dec for 9999999999999
SQL> select 48*1024*1024*1024 dec from dual;
DEC
--------------
51539607552

Now let's get hexadecimal value of this number,
SQL> select to_char(51539607552,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
C00000000

As we see in the hexadecimal representation we get last 8 letters are all 0s; 00000000. The bug happens whenever in hexadecimal representation the 4 bytes have 0. Here in setting of 48G we get last 4 bytes all 0. As for last 0 in binary it represent 0000. So 2 letters of 0 in hexadecimal just form 1 byte(8 bits). So last 8 letters in hexadecimal means we got last 4 bytes 0. And forming this type causes to fire hug.

Solution of the problem

Solution 1)
As bug fires whenever in HEXADECIMAL representation we have 00000000 as the last 4 bytes(8 letters in hex value). So our target is to set SGA_TARGET so that it does not form like this way(4 bytes 0 at last). We can do this if we set our SGA_TARGET not exact multiple of 4G.
Doing set our SGA_TARGET to 47G would happen not the last 4 bytes as all 0s of binary representation.

ALTER SYSTEM SET SGA_TARGET=47G SCOPE=BOTH;
SQL> select to_char(47*1024*1024*1024,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
BC0000000
We see last 7 letters as 0, and hence the bug will not fire.

Solution 2)
Out database version was 10.2.0.1; base version. And this bug fires. This bug remains in oracle patchset 10.2.0.2 and 10.2.0.3. And is fixed in 10.2.0.4 patchset. So applying patchset 10.2.0.4 would fix the problem.

Solution 3)
Upgrade your database to 11.1.0.6 also will fix the problem.

Solution 4)
Apply the one-off Patch 4433838.

ORA-00600 internal error code

Error Description
In our production server while doing data pump import operation at the end of import it fails with following errors, then the instance crashed and database went down.
Errors in file /var/opt/dumpfile/bdump/shiak_mman_23373.trc:
ORA-00600: internal error code, arguments: [kmgs_pre_process_request_6], [4], [2
], [0], [3], [0xC992C04D0], [], []
Sun Oct 19 18:16:20 2008
MMAN: terminating instance due to error 822
Instance terminated by MMAN, pid = 23373

In the trace file we got the call stack as,
ksedst, ksedmp, ksfdmp, kgerinv, kgeasnmierr, kmgs_pre_process_request, kmgsdrv, ksbabs, ksbrdp, opirip, opidrv, sou2o, opimai_real, main.

Cause of the problem
As we know in Oracle ORA-600 error series are oracle bug. This is oracle Bug 4433838. From alert log we see our MMAN (Memory Manager) process has been terminated. So something wrong happen with oracle MMAN.

This bug actually fires when the initialization parameter SGA_TARGET is set to an exact multiple of 4Gb. So, while setting big SGA be conscious.

A little bit more about this bug is if the SGA_TARGET value in HEXADECIMAL has 00000000 as the last bytes then you can hit this bug.

Detail Explanation of this bug
In out production server we set our SGA_TARGET=48G and this bug fires. We see 48G is multiple of 12 times of 4G. So this bug can error. A little bit elaborate of this issue,
Of 48G we get decimal value as,
SQL> col dec for 9999999999999
SQL> select 48*1024*1024*1024 dec from dual;
DEC
--------------
51539607552

Now let's get hexadecimal value of this number,
SQL> select to_char(51539607552,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
C00000000

As we see in the hexadecimal representation we get last 8 letters are all 0s; 00000000. The bug happens whenever in hexadecimal representation the 4 bytes have 0. Here in setting of 48G we get last 4 bytes all 0. As for last 0 in binary it represent 0000. So 2 letters of 0 in hexadecimal just form 1 byte(8 bits). So last 8 letters in hexadecimal means we got last 4 bytes 0. And forming this type causes to fire hug.

Solution of the problem

Solution 1)
As bug fires whenever in HEXADECIMAL representation we have 00000000 as the last 4 bytes(8 letters in hex value). So our target is to set SGA_TARGET so that it does not form like this way(4 bytes 0 at last). We can do this if we set our SGA_TARGET not exact multiple of 4G.
Doing set our SGA_TARGET to 47G would happen not the last 4 bytes as all 0s of binary representation.

ALTER SYSTEM SET SGA_TARGET=47G SCOPE=BOTH;
SQL> select to_char(47*1024*1024*1024,'XXXXXXXXXXXXX') hex from dual;
HEX
---------------
BC0000000
We see last 7 letters as 0, and hence the bug will not fire.

Solution 2)
Out database version was 10.2.0.1; base version. And this bug fires. This bug remains in oracle patchset 10.2.0.2 and 10.2.0.3. And is fixed in 10.2.0.4 patchset. So applying patchset 10.2.0.4 would fix the problem.

Solution 3)
Upgrade your database to 11.1.0.6 also will fix the problem.

Solution 4)
Apply the one-off Patch 4433838.

ORA-00923: FROM keyword not found where expected

Problem Symptom
While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below.
SQL> select 48*1024*1024*1024 decimal from dual;
select 48*1024*1024*1024 decimal from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Cause of the problem
The ORA-00923 can be caused by two different reasons.
1)Oracle itself expect FROM keyword in the position but it could not find any. Like below,
SQL> select 48*1024 om dual;
select 48*1024 om dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

As no from keyword found so error arises. Oracle expected from in place of keyword dual.

2)The secong cause can sometimes mislead you. Like in case of
SQL> select 48*1024*1024*1024 decimal from dual;
select 48*1024*1024*1024 decimal from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Here we see from keyword is in appropriate position but still error occurs. This is because of the reserved keywords in oracle. Oracle database has several reserved keywords and these reserved keywords can't be used as column alias or in column name. As decimal is a reserved keyword so use of it as a column alias raises error ORA-00923.

Solution of the Problem
Use FROM clause in correct position and then either change the name of the column alias so that it can't be a reserved keyword in oracle. Like change decimal to decima as column alias.

SQL> select 48*1024*1024*1024 decima from dual;
DECIMA
----------
5.1540E+10

Or, if you use reserved keywords then use it within double quotes. Like,
SQL> select 48*1024*1024*1024 "decimal" from dual;
decimal
----------
5.1540E+10

Convert Decimal to hexadecimal on Oracle

Way 01

SQL> create or replace package number_utils as
 2          function d_to_hex(decimal_num in integer) return varchar2;
 3          pragma restrict_references (d_to_hex, wnds, wnps, rnps);
 4  end;
 5  /
Package created.

SQL> create or replace package body number_utils as
 2  function d_to_hex (decimal_num in integer)
 3  return varchar2 is
 4          v_result       varchar2(12);
 5          v_hex_digit    varchar2(1);
 6          v_quotient     pls_integer;
 7          v_remainder    pls_integer;
 8  begin
 9          if (decimal_num < 10) then
10                 v_result := to_char(decimal_num);
11          elsif (decimal_num < 16) then
12                 v_result := chr(65+(decimal_num-10));
13          else
14                 v_remainder := mod(decimal_num,16);
15                 v_quotient  := round((decimal_num - v_remainder) /16);
16                 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
17          end if;
18          return v_result;
19  end  d_to_hex;
20  end number_utils;
21  /
Package body created.

SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05

Way 02:

SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
 2    hex_num varchar2(64);
 3    digit  pls_integer;
 4    decimal_num pls_integer:=decimal_val;
 5    hexdigit  char;
 6  BEGIN
 7    while ( decimal_num > 0 ) loop
 8       digit := mod(decimal_num, 16);
 9       if digit > 9 then
10          hexdigit := chr(ascii('A') + digit - 10);
11       else
12          hexdigit := to_char(digit);
13       end if;
14       hex_num := hexdigit || hex_num;
15       decimal_num := trunc( decimal_num / 16 );
16    end loop;
17    return hex_num;
18  END dec_to_hex;
19  /
Function created.

SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 03:

SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
 2   v_decin NUMBER;
 3   v_next_digit NUMBER;
 4   v_result varchar(2000);
 5  BEGIN
 6    v_decin := dec_num;
 7    WHILE v_decin > 0 LOOP
 8    v_next_digit := mod(v_decin,16);
 9  IF v_next_digit > 9 THEN
10   IF v_next_digit = 10 THEN v_result := 'A' || v_result;
11    ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
12    ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
13    ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
14    ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
15    ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
16    ELSE raise_application_error(-20600,'Untrapped exception');
17   END IF;
18  ELSE
19   v_result := to_char(v_next_digit) || v_result;
20  END IF;
21  v_decin := floor(v_decin / 16);
22  END LOOP;
23  RETURN v_result;
24  END dec_to_hex;
25  /

Function created.

SQL> select dec_to_hex(17) from dual;

DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11

Way 04:

SQL> create or replace function dec_to_hex(dec_num in number )
 2  return varchar2
 3  is
 4     l_str   varchar2(255) default NULL;
 5     l_num   number  default dec_num;
 6     l_hex   varchar2(16) default '0123456789ABCDEF';
 7          p_base   number:=16;
 8  begin
 9     if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
10             raise PROGRAM_ERROR;
11     end if;
12     loop
13             l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
14             l_num := trunc( l_num/p_base );
15             exit when ( l_num = 0 );
16     end loop;
17     return l_str;
18  end dec_to_hex;
19  /

Function created.

SQL> select dec_to_hex(120) from dual;

DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.

Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78

SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;

ORA-00923: FROM keyword not found where expected

Problem Symptom
While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below.
SQL> select 48*1024*1024*1024 decimal from dual;
select 48*1024*1024*1024 decimal from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Cause of the problem
The ORA-00923 can be caused by two different reasons.
1)Oracle itself expect FROM keyword in the position but it could not find any. Like below,
SQL> select 48*1024 om dual;
select 48*1024 om dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

As no from keyword found so error arises. Oracle expected from in place of keyword dual.

2)The secong cause can sometimes mislead you. Like in case of
SQL> select 48*1024*1024*1024 decimal from dual;
select 48*1024*1024*1024 decimal from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Here we see from keyword is in appropriate position but still error occurs. This is because of the reserved keywords in oracle. Oracle database has several reserved keywords and these reserved keywords can't be used as column alias or in column name. As decimal is a reserved keyword so use of it as a column alias raises error ORA-00923.

Solution of the Problem
Use FROM clause in correct position and then either change the name of the column alias so that it can't be a reserved keyword in oracle. Like change decimal to decima as column alias.

SQL> select 48*1024*1024*1024 decima from dual;
DECIMA
----------
5.1540E+10

Or, if you use reserved keywords then use it within double quotes. Like,
SQL> select 48*1024*1024*1024 "decimal" from dual;
decimal
----------
5.1540E+10

Convert Decimal to hexadecimal on Oracle

Way 01

SQL> create or replace package number_utils as
 2          function d_to_hex(decimal_num in integer) return varchar2;
 3          pragma restrict_references (d_to_hex, wnds, wnps, rnps);
 4  end;
 5  /
Package created.

SQL> create or replace package body number_utils as
 2  function d_to_hex (decimal_num in integer)
 3  return varchar2 is
 4          v_result       varchar2(12);
 5          v_hex_digit    varchar2(1);
 6          v_quotient     pls_integer;
 7          v_remainder    pls_integer;
 8  begin
 9          if (decimal_num < 10) then
10                 v_result := to_char(decimal_num);
11          elsif (decimal_num < 16) then
12                 v_result := chr(65+(decimal_num-10));
13          else
14                 v_remainder := mod(decimal_num,16);
15                 v_quotient  := round((decimal_num - v_remainder) /16);
16                 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder);
17          end if;
18          return v_result;
19  end  d_to_hex;
20  end number_utils;
21  /
Package body created.

SQL> select number_utils.d_to_hex(7685) from dual;
NUMBER_UTILS.D_TO_HEX(7685)
--------------------------------------------------------------------------------
1E05

Way 02:

SQL> CREATE OR REPLACE FUNCTION dec_to_hex (decimal_val in number) RETURN varchar2 IS
 2    hex_num varchar2(64);
 3    digit  pls_integer;
 4    decimal_num pls_integer:=decimal_val;
 5    hexdigit  char;
 6  BEGIN
 7    while ( decimal_num > 0 ) loop
 8       digit := mod(decimal_num, 16);
 9       if digit > 9 then
10          hexdigit := chr(ascii('A') + digit - 10);
11       else
12          hexdigit := to_char(digit);
13       end if;
14       hex_num := hexdigit || hex_num;
15       decimal_num := trunc( decimal_num / 16 );
16    end loop;
17    return hex_num;
18  END dec_to_hex;
19  /
Function created.

SQL> select dec_to_hex(120) from dual;
DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 03:

SQL> CREATE OR REPLACE FUNCTION dec_to_hex (dec_num IN NUMBER) RETURN VARCHAR2 IS
 2   v_decin NUMBER;
 3   v_next_digit NUMBER;
 4   v_result varchar(2000);
 5  BEGIN
 6    v_decin := dec_num;
 7    WHILE v_decin > 0 LOOP
 8    v_next_digit := mod(v_decin,16);
 9  IF v_next_digit > 9 THEN
10   IF v_next_digit = 10 THEN v_result := 'A' || v_result;
11    ELSIF v_next_digit = 11 THEN v_result := 'B' || v_result;
12    ELSIF v_next_digit = 12 THEN v_result := 'C' || v_result;
13    ELSIF v_next_digit = 13 THEN v_result := 'D' || v_result;
14    ELSIF v_next_digit = 14 THEN v_result := 'E' || v_result;
15    ELSIF v_next_digit = 15 THEN v_result := 'F' || v_result;
16    ELSE raise_application_error(-20600,'Untrapped exception');
17   END IF;
18  ELSE
19   v_result := to_char(v_next_digit) || v_result;
20  END IF;
21  v_decin := floor(v_decin / 16);
22  END LOOP;
23  RETURN v_result;
24  END dec_to_hex;
25  /

Function created.

SQL> select dec_to_hex(17) from dual;

DEC_TO_HEX(17)
--------------------------------------------------------------------------------
11

Way 04:

SQL> create or replace function dec_to_hex(dec_num in number )
 2  return varchar2
 3  is
 4     l_str   varchar2(255) default NULL;
 5     l_num   number  default dec_num;
 6     l_hex   varchar2(16) default '0123456789ABCDEF';
 7          p_base   number:=16;
 8  begin
 9     if ( trunc(dec_num) <> dec_num OR dec_num < 0 ) then
10             raise PROGRAM_ERROR;
11     end if;
12     loop
13             l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
14             l_num := trunc( l_num/p_base );
15             exit when ( l_num = 0 );
16     end loop;
17     return l_str;
18  end dec_to_hex;
19  /

Function created.

SQL> select dec_to_hex(120) from dual;

DEC_TO_HEX(120)
--------------------------------------------------------------------------------
78

Way 05:
Just using TO_CHAR function. Here the first argument is the decimal number. And the second argument is the format. In order to convert to decimal the format is 'XXXX'. Note that the format length must be greater enough so that the returned length in hexadecimal is not less than the format.

Example:
SQL> select to_char(120,'XXXX') from dual;
TO_CH
-----
78

SQL> select to_char(120000,'XXXXXXX') from dual;
TO_CHAR(
--------
1D4C0
select to_char(120,'XXXX') from dual;

Which Options are installed on your oracle database

There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.

2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.

Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.

3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;

COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID

17 rows selected.

Which Options are installed on your oracle database

There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.

2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.

Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.

3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;

COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID

17 rows selected.

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Error Description:
The scenario is I have created a materialized view through database link over a table reside on the remote database.

I used the ON COMMIT option and it fails with ORA-12054 as below.

SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on commit
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';
from PHONES@lnxdb where upper(IS_SOLD)='Y'
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Cause of the Problem
The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,

•ON COMMIT clause is not supported for materialized views containing object types.

•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.

In our case we satisfy the second restriction and hence error comes.

Solution of the Problem
To implement the solution you have to provide ON DEMAND clause. You have to create as below.
SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on demand
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';

How to avoid rebuild of indexes if DDL performed on partitioned table

On the partitioned table, if we don't do anything with the index then the index against the partitioned table remain global Non partitioned indexes and thus whenever we perform any DDL operation against the table partition like drop a partition or truncate a partition or add a partition or merge a partition or split a partition then the associated global indexes become invalid. Thus after doing DDL we need to rebuild the indexes. This may be very cumbersome and it may need several hours if the table is big enough.

With an example I am demonstrating how global indexes against a table become invalid if I do any DDL against the table.

1)Create a partitioned table.

CREATE TABLE test_partition
(
id number,
created_date  date,
col3 varchar2(20)
)
PARTITION BY RANGE (created_date)
   (
    PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) ,
    PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) ,
    PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) ,
    PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) ,
    PARTITION junk VALUES LESS THAN (MAXVALUE)
  )
/
Table created.

2)Insert data into the partitioned table.

insert into test_partition
   select rownum, to_date('10-oct-2008','dd-mon-yyyy')+mod(rownum,7),
          rownum
     from dba_users;

41 rows created.

3)Create index on the partitioned table.
SQL> create index tpid_i on test_partition(id);
Index created.

4)Be sure partitioned is done successful.
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
6

Let's see some sample data.

SQL> select * from test_partition partition (part2);
       ID CREATED_D COL3
---------- --------- --------------------
        1 11-OCT-08 1
        8 11-OCT-08 8
       15 11-OCT-08 15
       22 11-OCT-08 22
       29 11-OCT-08 29
       36 11-OCT-08 36

6 rows selected.

5)Let us see the status of the index against the partitioned table.
Note that since we have made global non-partitioned indexes so we will not get any entry about valid/unusable index in view user_ind_partitions. To get index status we have to query from user_indexes view.

SQL> select partition_name, status from user_ind_partitions where
index_name = 'TPID_I';
no rows selected

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

6)Do some DDL operation on the partitioned table.
SQL> alter table test_partition truncate partition part2;
Table truncated.

Check by,
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
0

7)Check the index status now.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         UNUSABLE

Since we have global index so any DDL under the table marks the index unusable. If you want to make the index valid then you have to rebuild the index.

SQL> alter index tpid_i rebuild;
Index altered.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

This rebuild process really time consuming. And for production server it may not be tolerable.

8)The solution is to make the index as local partitioned index.
SQL> drop index tpid_i;
Index dropped.


SQL> create index tpid_i on test_partition(id) local;
Index created.

Check status by,

SQL>  select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

Since it is local partitioned index we have to query from user_ind_partitions.
SQL> select partition_name, status from user_ind_partitions where
2 index_name = 'TPID_I';

PARTITION_NAME STATUS
------------------------------ --------
JUNK USABLE
PART1 USABLE
PART2 USABLE
PART3 USABLE
PART4 USABLE

SQL> select count(*) from test_partition partition (part3);
COUNT(*)
----------
6

9)Let's do some DDL operation and test.
SQL> alter table test_partition truncate partition part3;
Table truncated.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

SQL>  select partition_name, status from user_ind_partitions where
 2      index_name = 'TPID_I';

PARTITION_NAME                 STATUS
------------------------------ --------
JUNK                           USABLE
PART1                          USABLE
PART2                          USABLE
PART3                          USABLE
PART4                          USABLE

And it still remains in USABLE status. So you might able to save a lots of time as well as non-interrupt service and effort against it.

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Error Description:
The scenario is I have created a materialized view through database link over a table reside on the remote database.

I used the ON COMMIT option and it fails with ORA-12054 as below.

SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on commit
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';
from PHONES@lnxdb where upper(IS_SOLD)='Y'
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Cause of the Problem
The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,

•ON COMMIT clause is not supported for materialized views containing object types.

•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.

In our case we satisfy the second restriction and hence error comes.

Solution of the Problem
To implement the solution you have to provide ON DEMAND clause. You have to create as below.
SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on demand
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';

How to avoid rebuild of indexes if DDL performed on partitioned table

On the partitioned table, if we don't do anything with the index then the index against the partitioned table remain global Non partitioned indexes and thus whenever we perform any DDL operation against the table partition like drop a partition or truncate a partition or add a partition or merge a partition or split a partition then the associated global indexes become invalid. Thus after doing DDL we need to rebuild the indexes. This may be very cumbersome and it may need several hours if the table is big enough.

With an example I am demonstrating how global indexes against a table become invalid if I do any DDL against the table.

1)Create a partitioned table.

CREATE TABLE test_partition
(
id number,
created_date  date,
col3 varchar2(20)
)
PARTITION BY RANGE (created_date)
   (
    PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) ,
    PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) ,
    PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) ,
    PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) ,
    PARTITION junk VALUES LESS THAN (MAXVALUE)
  )
/
Table created.

2)Insert data into the partitioned table.

insert into test_partition
   select rownum, to_date('10-oct-2008','dd-mon-yyyy')+mod(rownum,7),
          rownum
     from dba_users;

41 rows created.

3)Create index on the partitioned table.
SQL> create index tpid_i on test_partition(id);
Index created.

4)Be sure partitioned is done successful.
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
6

Let's see some sample data.

SQL> select * from test_partition partition (part2);
       ID CREATED_D COL3
---------- --------- --------------------
        1 11-OCT-08 1
        8 11-OCT-08 8
       15 11-OCT-08 15
       22 11-OCT-08 22
       29 11-OCT-08 29
       36 11-OCT-08 36

6 rows selected.

5)Let us see the status of the index against the partitioned table.
Note that since we have made global non-partitioned indexes so we will not get any entry about valid/unusable index in view user_ind_partitions. To get index status we have to query from user_indexes view.

SQL> select partition_name, status from user_ind_partitions where
index_name = 'TPID_I';
no rows selected

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

6)Do some DDL operation on the partitioned table.
SQL> alter table test_partition truncate partition part2;
Table truncated.

Check by,
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
0

7)Check the index status now.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         UNUSABLE

Since we have global index so any DDL under the table marks the index unusable. If you want to make the index valid then you have to rebuild the index.

SQL> alter index tpid_i rebuild;
Index altered.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

This rebuild process really time consuming. And for production server it may not be tolerable.

8)The solution is to make the index as local partitioned index.
SQL> drop index tpid_i;
Index dropped.


SQL> create index tpid_i on test_partition(id) local;
Index created.

Check status by,

SQL>  select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

Since it is local partitioned index we have to query from user_ind_partitions.
SQL> select partition_name, status from user_ind_partitions where
2 index_name = 'TPID_I';

PARTITION_NAME STATUS
------------------------------ --------
JUNK USABLE
PART1 USABLE
PART2 USABLE
PART3 USABLE
PART4 USABLE

SQL> select count(*) from test_partition partition (part3);
COUNT(*)
----------
6

9)Let's do some DDL operation and test.
SQL> alter table test_partition truncate partition part3;
Table truncated.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

SQL>  select partition_name, status from user_ind_partitions where
 2      index_name = 'TPID_I';

PARTITION_NAME                 STATUS
------------------------------ --------
JUNK                           USABLE
PART1                          USABLE
PART2                          USABLE
PART3                          USABLE
PART4                          USABLE

And it still remains in USABLE status. So you might able to save a lots of time as well as non-interrupt service and effort against it.