ORA-00600: internal error code, arguments: [13013], [5001], .....

I was experiencing an ORA-00600 error on one of our development setup. While looking into error code arguments we thought that it is related to some sort of data corruption. So we applied all the techniques listed on oracle-base and tips provided by this blog.

Using those techniques we were able to identify table which is involved in the DML responsible for ORA-600. But that's it. Table does not have any data corruption and even after rebuilding whole table and its index we were facing same error.

Then we started looking into the query, which is a simple MERGE statement. Below is the query using similar table:

SQL> MERGE INTO abc X

  2     USING

  3     (

  4             SELECT 2 AS ID FROM dual

  5     ) U

  6     ON

  7     (X.b = U.id)

  8  WHEN MATCHED THEN UPDATE SET X.c = 0;

Interesting thing here is that if we modify the query and convert it into an UPDATE statement then query updates one record and does not throw any error.

Now we started thinking that it might not be related to database or data corruption but due to some sort of bug in processing of MERGE statement or structure of table or its indexes. But we were unable to find any thing relevant over that internet.

We started with changing structure of table like its keys, constraints, column datatype but ORA-00600 error keep reappearing on MERGE statement. Then we starteded altering/ dropping indexes. Here we got successes. This table has one composite index. This index was on two columns, first column has on ordering clause but second one has DESC order clause. Like this:

CREATE INDEX abc_ind ON abc (b, c DESC);

We we dropped this index, merge statement executed successfully. Now we knew that there is something wrong this combination of this index and merge statement. Because when we removed that DESC clause and recreated that index merge again executed successfully.

MERGE statement fails if base table has an composite index on columns involved in  query and DESC order is used during index creation. But there is one more requirement of failure. That is leading column of index should be used in ON clause of MERGE. For example if there are three columns a,b and c in a table and merge condition is on column b and update clause is on column c then if composite index is on columns (b, c DESC) then MERGE will fail. But if index is on columns (a, c DESC) then MERGE won’t fail.

I got away by modifying that index and removing DESC clause, because there was really no requirement for that. We don't know how it get introduced in the first place. But it may be possible some where else we might actually need that type of index and again face similar issue.

Following are the reproduction steps:

SQL> DROP TABLE abc;

DROP TABLE abc

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> CREATE TABLE abc(

  2         a INT,

  3        b INT,

  4        c INT

  5  );

Table created.

SQL> INSERT INTO abc VALUES (2,1,1);

1 row created.

SQL> INSERT INTO abc VALUES (1,4,2);

1 row created.

SQL> INSERT INTO abc VALUES (6,6,3);

1 row created.

SQL> INSERT INTO abc VALUES (1,1,4);

1 row created.

SQL> INSERT INTO abc VALUES (3,7,5);

1 row created.

SQL> INSERT INTO abc VALUES (1,2,5);

1 row created.

SQL> COMMIT;

Commit complete.

/*MERGE without any index. RESULT:: SUCCESS */

SQL> MERGE INTO abc X

  2     USING

  3     (

  4             SELECT 2 AS ID FROM dual

  5     ) U

  6     ON

  7     (X.b = U.id)

  8  WHEN MATCHED THEN UPDATE SET X.c = 0;

1 row merged.

SQL> ROLLBACK;

Rollback complete.

SQL> CREATE INDEX abc_ind ON abc (b, c DESC);

Index created.

/*MERGE with composite index on columns involved in ON clause and UPDATE clause of merge with ordering. RESULT:: FAILURE*/

SQL> MERGE INTO abc X

  2     USING

  3     (

  4             SELECT 2 AS ID FROM dual

  5     ) U

  6     ON

  7     (X.b = U.id)

  8  WHEN MATCHED THEN UPDATE SET X.c = 0;

MERGE INTO abc X

           *

ERROR at line 1:

ORA-00600: internal error code, arguments: [13013], [5001], [101263],

[17027069], [5], [17027069], [17], [], [], [], [], []

SQL> DROP INDEX abc_ind;

Index dropped.

SQL> CREATE INDEX abc_ind ON abc (b, c);

Index created.

/*MERGE with composite index on columns involved in ON clause and UPDATE clause of merge but without ordering. RESULT:: SUCCESS */

SQL> MERGE INTO abc X

  2     USING

  3     (

  4             SELECT 2 AS ID FROM dual

  5     ) U

  6     ON

  7     (X.b = U.id)

  8  WHEN MATCHED THEN UPDATE SET X.c = 0;

1 row merged.

SQL> ROLLBACK;

Rollback complete.

SQL> DROP INDEX abc_ind;

Index dropped.

SQL> CREATE INDEX abc_ind ON abc (a, c DESC);

Index created.

/*MERGE with composite index. But leading column of index is not involved in ON clause. RESULT:: SUCCESS */

SQL> MERGE INTO abc X

  2     USING

  3     (

  4             SELECT 2 AS ID FROM dual

  5     ) U

  6     ON

  7     (X.b = U.id)

  8  WHEN MATCHED THEN UPDATE SET X.c = 0;

1 row merged.

SQL> ROLLBACK;

Rollback complete.

SQL>

SQL Server: Partition view usage and limitations.

Partition views are just like any other view. They are build on two or more member tables and display records from those table with some differences and limitations. Partition can be used instead of partitioning. Enterprise edition of SQL Sever provides us facility of physical data partition of a big table. But partitioning is only available in enterprise edition. If you want to implement similar data partitioning in standard edition you can use partition views. But there are some limitations of partition views that we will see.

Partition view allows data of a larger table to be split into smaller member or base tables. We need a check constraint on column of each base table and this check constraint will determine the partition range for each member table. Range of this check constraint must be mutually exclusive in each member table so that database engine can decide on which table new incoming row should go in case of a DML.

Now take a look at one example:

create table member1 (
id int not null,
name varchar(100),
age int check (age < 18),
constraint member1_pk primary key (id, age)
);
GO

create table member2 (
id int not null,
name varchar(100),
age int check (age >= 18 AND age < 60),
constraint member2_pk primary key (id, age)
);
GO

create table member3 (
id int not null,
name varchar(100),
age int check (age >= 60),
constraint member3_pk primary key (id, age)
);
GO

create view members as
select * from member1
union all
select * from member2
union all
select * from member3;
GO

Here "members" is a partition view and "age" column of base tables will work as a partition column. As you can see check constraint on age column has exclusive conditions. And any specified value of age can satisfy, at most, one of the constraints and condition of each constraint is forming a set of disjointed or non-overlapping intervals.
One more condition for partition view is that primary key of all the member tables mush have partition column. Thats way we have to create a composite primary key having id and age columns.

Now we are going to insert some records into "members view" and see what happens to base tables:


insert into members(id, name, age)
values (1,'x', 35);

insert into members(id, name, age)
values (2,'y', 15);

insert into members(id, name, age)
values (3,'z', 5);

insert into members(id, name, age)
values (4,'p', 75);

insert into members(id, name, age)
values (5,'q', 18);

insert into members(id, name, age)
values (6,'r', 27);
GO

select 'members' as tab, COUNT(*) as count from members
union all
select 'member1', COUNT(*) from member1
union all
select 'member2', COUNT(*) from member2
union all
select 'member3', COUNT(*) from member3;
GO

tab           count
members 6
member1 2
member2 3
member3 1

So insert and select are woking file. Lets see what happens to update.

update members
set age = 79
where id = 6;
GO

select 'members' as tab, COUNT(*) as count from members
union all
select 'member1', COUNT(*) from member1
union all
select 'member2', COUNT(*) from member2
union all
select 'member3', COUNT(*) from member3;
GO

tab         count
members       6
member1       2
member2       2
member3       2

Above update statement moved row having id = 6 from member2 to member3 table.

Now talk about some limitations of partition views. INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.
For example consider this update:

update m
set m.name = 'a'
from members m inner join member1 m1 on m.id = m1.id and m1.id = 2;
GO

Msg 4439, Level 16, State 6, Line 2
Partitioned view 'eGPartitionTest.dbo.members' is not updatable because the source query contains references to partition table '[m1]'.

Also target of a MERGE statement cannot be a partitioned view.

For more details on partition view usage and limitations refer following links:
-Using partitioned views
-Create view

ORACLE: SQL Server table variable equivalent in oracle.

In SQL Server we can define a table variable and use it like a regular table. We can insert, update, delete records from that table variable. There table variable becomes very useful if we do not want to use collections or temp tables. Also scope of table variable remains only within a T-SQL block on which it was declared. Take a look at the below T-SQL code written for SQL Server:

--Declare a table variable. Syntax is like any other database table.
DECLARE @v_tab TABLE (a INT, b VARCHAR(100));
DECLARE @v_cnt INT;
begin
--Insert records into a table variable.
INSERT  INTO @v_tab(a,b)
SELECT object_id, name FROM sys.tables;

SELECT @v_cnt = COUNT(*) 
FROM @v_tab;

PRINT @v_cnt;

--delete few records.
delete FROM @v_tab 
WHERE b like '%mail%';

SELECT @v_cnt = COUNT(*) 
FROM @v_tab;

PRINT @v_cnt;

end;
GO

Unfortunately Oracle does not provide any such capabilities in PL/SQL. If we want to achieve similar functionality we have to use collections, records and table() function. But it becomes little complicated then SQL Server. Take a look at below script.

create or replace type a_record as object   ( object_name varchar2(30), created_date date, object_type number );
/
create or replace type a_tab_variable as table of a_record; 
/

DECLARE 
       a_tab a_tab_variable;
       v_cnt INT;
BEGIN
       select  a_record ( object_name, created, object_id )
       bulk collect into a_tab
       from user_objects;  
  
   SELECT count(*)
INTO v_cnt  
FROM TABLE(a_tab);   

DBMS_OUTPUT.PUT_LINE( v_cnt );
         
         FOR a_rec IN ( 
                   SELECT b.object_name
                        FROM TABLE(a_tab) b    
                         WHERE b.created_date < (SYSDATE - 2)
                               AND ROWNUM < 101) 
         LOOP
                   DBMS_OUTPUT.PUT_LINE( a_rec.object_name );
         END LOOP;                  
END;
/

Oracle: JDBC connectivity

First of all make sure you have proper JDK installation and java home path is set. In order to confirm that JDK configured correctly go to command prompt and type "javac". It should prompt you various javac usage options.
Now make sure that you have correct jdbc drivers. For this go to oracle website and download correct driver if not done already. Now append the location of jdbc drivers into your classpath. For example if I execute "echo %classpath%" into my system it will show me something like this:

D:\ankur\KB\java\JDBC\program>echo %classpath%
.;D:\ankur\KB\java\JDBC;D:\app\achourasia\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar;

You can set classpath into following location on windows machine:

My Compute --> Properties --> Advanced System Settings --> Environment Variables --> User variables --> edit classpath.

In order to test that everything is fine run following small test.

Execute following sql into SCOTT schema:

CREATE TABLE rectangles(
       seq INT,
  length INT, 
  width  INT,
  area NUMBER(19,5)
);

INSERT INTO rectangles (seq, length, width) VALUES (1, 10, 34);
                                                                            
INSERT INTO rectangles (seq, length, width) VALUES (2, 100, 89);

INSERT INTO rectangles (seq, length, width) VALUES (3, 20, 76);

INSERT INTO rectangles (seq, length, width) VALUES (4, 35, 14);

INSERT INTO rectangles (seq, length, width) VALUES (5, 1, 9);

Now execute following java from file system:

import java.sql.*;
public class JdbcRectangle {

public int calculateArea(int length, int width){
return (length * width);
}

public static void main(String args[]) throws SQLException {

try {
String username = "SCOTT";
String password = "SCOTT";
String database = "pune705";
JdbcRectangle j = new JdbcRectangle();

//Create a connection.
Connection conn1 = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:" + database, username, password);;
//stmt for select query
Statement stmt = conn1.createStatement();
//stmt for update query.
Statement stmt1 = conn1.createStatement();

ResultSet rs = stmt.executeQuery("SELECT seq, length, width FROM rectangles where area is null");

boolean b = false;
int seq  = 0;
int len  = 0;
int wdt  = 0;
int area = 0;
int i    = 0;

while(rs.next()){
seq = rs.getInt("seq");
len = rs.getInt("length");
wdt = rs.getInt("width");
area = j.calculateArea(len,wdt);

b = stmt1.execute("UPDATE rectangles SET area = " + area + " WHERE seq = " + seq);

System.out.println("SEQ: " + (++i) + " Length: " + len + " Width " + wdt + " Area: " + area + " is_updated: " + String.valueOf(b));
}

rs.close();
stmt.close();
stmt1.close();
conn1.close();

}

catch(SQLException e) {
e.printStackTrace();
}
}
} 


If everything allright it will show following message into command prompt and reflect changes into database:

D:\ankur\KB\java\JDBC\program>java JdbcRectangle
SEQ: 1 Length: 10 Width 34 Area: 340 is_updated: false
SEQ: 2 Length: 100 Width 89 Area: 8900 is_updated: false
SEQ: 3 Length: 20 Width 76 Area: 1520 is_updated: false
SEQ: 4 Length: 35 Width 14 Area: 490 is_updated: false
SEQ: 5 Length: 1 Width 9 Area: 9 is_updated: false

ORACLE: Meaning of string length in varchar2 datatype.

When we declare a variable of column as varchar2 datatype, we specify it size in brackets. For example consider following create table statement. Here what does 10 means?

CREATE TABLE test(
col1 VARCHAR2(10)
)

We all know that the VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column.

The length semantics of character datatypes can be measured in bytes or characters. For example:

CREATE TABLE test(
col1 VARCHAR2(10),
col2 VARCHAR2(10 BYTE),
col3 VARCHAR2(10 CHAR)
)

Here column col1 and col2 are of 10 bytes length and col3 is of 10 character length.

Byte semantics treat strings as a sequence of bytes. This is the default for character datatypes. we can change this default behavior by modifying NLS_LENGTH_SEMANTICS parameter.
Character semantics treat strings as a sequence of characters.

For a single byte character set, column defined in character semantics are same as those defined in byte semantics. For multi-byte characters things becomes interesting. For multi-byte characters character semantics is useful. Character semantics reduces the complexity when defining the actual length requirements for data storage. For example, if we have to define a VARCHAR2 column that can store up to five Chinese characters together with five English characters.  Assuming Unicode database (UTF8) in byte semantics, this would require (5*3 bytes) + (1*5 bytes) = 20 bytes; in character semantics, the column would require 10 characters.

Following sequence of examples will clear things and also teach us how to change the default behaviuor of byte semantics by altering NLS_LENGTH_SEMANTICS parameter.

SELECT VALUE 
FROM v$parameter 
WHERE  upper(NAME) LIKE '%NLS_LENGTH_SEMANTICS%';   --either byte of char.

SQL> alter SESSION set nls_length_semantics='CHAR';

Session altered
SQL> drop table test1;

Table dropped
SQL> CREATE TABLE test1(
  2  a VARCHAR2(5),
  3  b VARCHAR2(5 CHAR),
  4  c VARCHAR2(5 BYTE)
  5  );

Table created
SQL> SELECT column_name, data_length, char_length
  2  FROM user_tab_columns
  3  WHERE table_name = 'TEST1';

COLUMN_NAME         DATA_LENGTH      CHAR_LENGTH
------------------------------ -------------------------- -----------
A                                       20                           5
B                                       20                           5
C                                        5                           5
SQL> INSERT INTO test1(a,b,c)
  2  VALUES ('india','india','india');

1 row inserted
SQL> INSERT INTO test1(a,b,c)
  2  VALUES ('šovic','šovic','šovic');

INSERT INTO test1(a,b,c)
VALUES ('šovic','šovic','šovic')

ORA-12899: value too large for column "SYSTEM"."TEST1"."C" (actual: 6, maximum: 5)
SQL> INSERT INTO test1(a,b,c)
  2  VALUES ('šovic','šovic','šovi');

1 row inserted
SQL> SELECT DUMP('šovic'),LENGTHB('šovic') ,LENGTH('šovic')
  2  FROM dual;

DUMP('ŠOVIC')                                            LENGTHB('ŠOVIC')       LENGTH('ŠOVIC')
-------------------------------------------------------- ------------------------------- ---------------
Typ=96 Len=6: 197,161,111,118,105,99      6                                      5



SQL> alter SESSION set nls_length_semantics='BYTE';

Session altered
SQL> CREATE TABLE test2(
  2  a VARCHAR2(5),
  3  b VARCHAR2(5 CHAR),
  4  c VARCHAR2(5 BYTE)
  5  );

Table created
SQL> SELECT column_name, data_length, char_length
  2  FROM user_tab_columns
  3  WHERE table_name = 'TEST2';

COLUMN_NAME         DATA_LENGTH          CHAR_LENGTH
------------------------------ ----------------------------- ------------------------
A                                        5                                    5
B                                       20                                   5
C                                        5                                   5
SQL> INSERT INTO test2(a,b,c)
  2  VALUES ('india','india','india');

1 row inserted
SQL> INSERT INTO test2(a,b,c)
  2  VALUES ('šovic','šovic','šovic');

INSERT INTO test2(a,b,c)
VALUES ('šovic','šovic','šovic')

ORA-12899: value too large for column "SYSTEM"."TEST2"."A" (actual: 6, maximum: 5)
SQL> INSERT INTO test2(a,b,c)
  2  VALUES ('šovi','šovic','šovic');

INSERT INTO test2(a,b,c)
VALUES ('šovi','šovic','šovic')

ORA-12899: value too large for column "SYSTEM"."TEST2"."C" (actual: 6, maximum: 5)
SQL> INSERT INTO test2(a,b,c)
  2  VALUES ('šovi','šovic','šovi');

1 row inserted

References:

• Oracle® Database Concepts
  11g Release 1

SQL Server: Oracle's ROWNUM equivalent in SQL server.

SQL server does not have rownum pseudo column. So if we would like to fetch row number along with select statement we have to use a ranking function ROW_NUMBER.

In oracle we can achieve same thing very easily using rownum. for example:

CREATE TABLE test1
AS 
SELECT table_name, column_name 
FROM user_tab_columns;

SELECT ROWNUM, table_name, column_name
FROM test1;

But SQL Server does not have rownum like pseudo column. So instead we have to do something like this:

SELECT OBJECT_NAME(object_id) as table_name, name as column_name
INTO test1
FROM sys.columns;

SELECT ROW_NUMBER() OVER (ORDER BY table_name, column_name) as rownum,
table_name, column_name
FROM test1; 

Now row_number() is an anaytical function and it also has partition by clause just like its oracle counter-part. So if you would like to partition/divide the result set and show rownum based on each partition you can do that. In the above example lets say you would like to show row number by each table name. Then following query will do just like that:

SELECT ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY table_name, column_name) as rownum,
table_name, column_name
FROM test1;

SQL Server: CPU utilization troubleshooting using DMVs

Run the following query to get the TOP 50 cached plans that consumed the most cumulative CPU. All times are in microseconds. This is not necessarily an exhaustive list. If a query is recompiled the details for that plan are removed; if there are further executions later, its cumulative stats in sys.dm_exec_query_stats start off at zero. If the procedure cache is flushed or SQL Server is restarted, all plans will be similarly affected.

SELECT TOP 50 qs.creation_time,
qs.execution_count,
qs.total_worker_time as total_cpu_time,
qs.max_worker_time as max_cpu_time,
qs.total_elapsed_time,
qs.max_elapsed_time,
qs.total_logical_reads,
qs.max_logical_reads,
qs.total_physical_reads,
qs.max_physical_reads,
t.[text],
qp.query_plan,
t.dbid,
t.objectid,
t.encrypted,
qs.plan_handle,
qs.plan_generation_num
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time/qs.execution_count DESC;

For more information refer to this blog post by Jared Poché