ORA-01450: maximum key length (3215) exceeded

I was getting the error while rebuilding the index online


alter index INDEX$id rebuild online
                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded


The issue was fixed with rebuilding the index without online.

More than 10,000 views of my blog

I am very pleased to see that my blog is viewed more than ten thousand time. I would encourage you all that if you have a question or a feedback then feel free to comment on the post. We learn every day, may be I can learn something new from you.

If there is any issue you are facing then you can also comment on this post with an example and I will pick it and create a new blog and try to help you. 

ORA-01658: unable to create INITIAL extent for segment in tablespace

I was getting the following error while trying to create the index.

create index harvey_1647 on harvey_table( id ) tablespace INDEXES;

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace INDEXES

The message clearly says that oracle is unable to create the INITIAL extent for the segment in the tablespace INDEXES.

This means that the datafile(s) of INDEXES tablespace are unable to extend because the disk(s) are full.

To fix this move the datafile(s) to the disk which have space or add another datafile which is on the disk having space and it will fix the issue straight way.

Data consistency

This morning I had a confusion regarding the update and insert statement. If we have a table with one million rows (The rows can be any). Table structure

SQL> desc myt
 Name      Null?    Type
 --------- -------- ------------------------
 NO                 NUMBER(8)
 NAME               VARCHAR2(10CHAR)

this table is populated with 

begin
 for i in 1..1000000 loop
   insert into myt values(1,'harvey');
   end loop;
  commit;
end;
/

Means each row have no = 1 and name = harvey.

14:49:07 SQL> select count(*) from myt;
        COUNT(*)
----------------
       1,000,000
Elapsed: 00:00:00.01

Session 1:
if I run update

14:49:56 SQL> update myt set no=2 where no=1;

Session 2:
before this update in session 1 finishes run insert from second session 

14:48:53 SQL> insert into myt values(1,'harvey');
1 row created.
Elapsed: 00:00:00.01
14:50:06 SQL> commit;
Commit complete.
Elapsed: 00:00:00.14
14:50:07 SQL> 

Question: what will be the number of rows updated by session 1.

1,000,000
or 
1,000,001

The correct answer is 1,000,000

14:49:56 SQL> update myt set no=2 where no=1;
1000000 rows updated.
Elapsed: 00:00:17.7114:50:22 SQL> 

and total number of rows in myt will be 1,000,001 because I have commited one row from session 2.


15:09:37 SQL> select count(*) from myt;

        COUNT(*)
----------------
       1,000,001

Elapsed: 00:00:01.33
15:09:45 SQL>