Reasons and Workarounds to Fix ORA-00997: illegal use of LONG datatype

Error Description:

CREATE TABLE AS SELECT (CTAS) and Insert into command fails with ORA-00997: illegal use of LONG datatype

SQL>   insert into test  (select note from siebel.EIM_SR_DTL where rownum<10);

  insert into test  (select note from siebel.EIM_SR_DTL where rownum<10)

                            *

How to Convert the AIA xref_data Cross Reference table LAST_MODIFIED, LAST_ACCESSED to Date?

Datatypes for the LAST_MODIFIED and LAST_ACCESSED columns are VARCHAR2. The data stored in the columns are in milliseconds. First of all you have to convert the millisecond values to Days.

So here is the calculation:

1Day is equivalent to ~ 24Hrs * 60 Mins * 60 Secs * 1000(milli)= 86400000

Reasons for ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Error Description:

SQL*Plus Connection fails with following error.

SQL> connect sys/welcome

ERROR: connection as SYS should be as SYSDBA or SYSOPER

Solution Description:

Data Pump vs EXP/IMP: Difference or Comparison Between Data Pump Expdp/impdp and Conventional EXP/IMP

• Datapump introduced in Oracle 10g whereas conventional exp/imp was used for logical backups in prior versions of oracle 10g. Exp/imp works even in all versions of Oracle.
• Conventional exp/imp can utilize the client machine resource for taking the backups but, the datapump works only in server.

Execute Immediate Command Fails with DBA role: ORA-01031: insufficient privileges

Error Description:

The procedure with execute immediate command is failing with ORA-01031: insufficient privileges. The procedure is trying to create a table and drop the table. The owner of the procedure has DBA role. Still the user cannot create the table or drop the table through execute immediate command. The user is able to create and drop the table using SQL*Plus prompt.

Fix ORA-10636: ROW MOVEMENT is not enabled Table Shrink Command Failed

Error Description:

Table shrink command failed with following error.

SQL> alter table siebel.EIM_ACCNT_UT shrink space compact;

alter table siebel.EIM_ACCNT_UT shrink space compact

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

ORA-10662: Segment has long columns, Table Shrink Command Failed: Workaround

Error Description

‘Alter table tablename shrink space compact’ command failed with following error.

SQL> alter table siebel.EIM_SR_DTL shrink space compact

*

ERROR at line 1:

ORA-10662: Segment has long columns

Problem Description: