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:

Fix Data Pump EXPDP Error ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Error Description:

Datapump EXPDP export is failed with following error.

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Job "SYS"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 03:08:04

Database Recovery from Corrupted, Missing Redo, Archive Log Files Using _ALLOW_RESETLOGS_CORRUPTION, _CORRUPTED_ROLLBACK_SEGMENTS, _minimum_giga_scn and _allow_error_simulation

Recovery Scenario:

This recovery method will help you to recover the database in following situations.

1. If you lose all redo log files for the database. It can be deleted by mistake or disk corruption.
2. If you database recovery needed by redo log group corruption

ORA-01548: active rollback segment, Find out Oracle 10g 11g Active Undo Segments from system datafile

Error Description:

Drop UNDO tablespace command fails with following error.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU1_2986795754$' found, terminate

dropping tablespace

How to Share / Add / Embed Youtube Video to Blogger Posts?

YouTube Videos are another way of making money through Adsense. If you have your own channels on the YouTube, you can embed those videos to the blog. Even you can embed/share the useful videos from other channels.

1. Find the video from YouTube to be shared and click on the link to play.

Google Animated Music Doodle on Valentine’s Day 2012

Google published a very cute music doodle on Valentine ’s Day 2012. It is with a beautiful animation movie. Doodle start with a play button. When you click on the play button, the animation starts. Boy goggling different items from Google which cannot win girls heart. Only true love can win the love. Watch the full video in following link.

Purpose of ADR_BASE_Listener & DIAG_ADR_ENABLED_listener : Oracle 11gR1 Listener ADR Parameter

Automatic Diagnostic Repository (ADR) is introduced in Oracle 11g. ADR is a common repository location for all logs and trace files etc. 

ADR More details

1. ADR_BASE_ListenerName: This parameter is to specify the ADR location for the database where listener generates the log and trace files to the directory. ADR location is specified in “diagnostic_dest” parameter of the database.  

Unique Index Creation Failed: ORA-12801: error signaled in parallel query server P018, instance

Error Description:

Oracle Unique Index creation failed with ORA-12801: error signaled in parallel query server P018.

CREATE UNIQUE INDEX

*

ERROR at line 1:

ORA-12801: error signaled in parallel query server P018,

Oracle 11g LMon RAC Background Process Functions

1. LMon (lock Monitor) is one of the mandatory background process in Oracle RAC instance.
2. It is also called as Global Enqueue Service Monitor
3. It manages the Global enqueues and resources.
4. LMON manages the instance and process failures. Which includes the

Terminate/Kill/Stop Data Pump EXPDP IMPDP Executing Jobs

Terminate Data Pump (EXPDP IMPDP) Executing Jobs exited without KILL_JOB command?

Here is the Scenario.

I have initiated an expdp job and exited without killing the job. In this scenario I cannot reinitiate the expdp with the same name again as it is in the executing status. You can use “drop table <jobname>;” to terminate such kind of jobs. See the below example.

$ expdp dumpfile=exp_table.dmp logfile=exp_table.log

Oracle SQL Script to Compile All Package, Body in a Schema

This script will compile all the Package and package body in the schema. If you wanted to compile all the packages in the all schema, change the user_objects to dba_objects and you have to connect as a DBA privileged user.

1. Create a sql file and copy below script.
2. Connect to sqlplus with the username where you wanted to compile all packages.