ORA-01450: maximum key length (3215) exceeded

Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.

SQL> create index tab1_I on tab1(a,b);
Index created.

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

Let's now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.

SQL> create index tab3_I on tab3(a,b);
Index created.

Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.

Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.

SQL> alter table tab3 modify b varchar2(200);
Table altered.

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

Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.

ORA-00997: illegal use of LONG datatype

Error Description
Whenever you try to do some illegal operations of LONG datatype it fails with error ORA-00997: illegal use of LONG datatype.
SQL> alter table a move ;
alter table a move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Cause of the Problem
The usage of LONG datatype has several restrictions. One of the restriction is, if a table contain long datatype column then it can't be used to move by using ALTER TABLE. In order to see list of restrictions just have a look at LONG Datatype in Oracle

Solution of the Problem
Don't use any LONG datatype in any table. If you have LONG datatype you must convert it to either BLOB/CLOB datatype using TO_LOB function. The conversion process is discussed on How to Convert Long data type to Lob

Root login fails with Permission denied on Unix

Problem Description
Using ssh whenever you try to login to another machine as a root user it failed with message Permission denied (gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive) like below.
Arju@debian:~$ ssh root@saturn
Password:
Password:
Password:
Permission denied (gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive).
Though I have provided the correct password but it fails. On server I can connect as other OS user rather than root and su as root user with password works. But from network it does not.

Cause of The Problem
In the machine to which you try to login within the script /etc/ssh/sshd_config parameter PermitRootLogin defines whether root user can ssh to the system or not.

Solution of The Problem
In order to see the contents of /etc/ssh/sshd_config you can issue,
$cat /etc/ssh/sshd_config

To see the settings of PermitRootLogin issue,
$ cat /etc/ssh/sshd_config |grep PermitRootLogin
PermitRootLogin no

As it is set to no so using ssh root connection is not accepted. In order to permit root login through network you have have to change this to yes from no after logging to the machine as a root user.

1)$su
password
#vi /etc/ssh/sshd_config
PermitRootLogin yes

2)Then restart the ssh daemon. On linux you can do it by,
#service sshd restart
On Solaris machine do it by,
# svcadm restart ssh

3)Then from another machine try to connect to the machine using root user.
Arju@debian:~$ ssh root@saturn
Password:
Last login: Fri Apr 25 11:15:34 2008
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
You have new mail.
#

And that's all.

Crash Recovery Fails With ORA-27067

Problem Symptoms
After performing shutdown abort whenever I try to start my database it fails to perform crash recovery. Below is from alert log.

Wed May 21 07:11:43 2008
Errors in file /ora/udump/arju_ora_12424.trc:
ORA-01115: IO error reading block from file 2 (block # 11546)
ORA-01110: data file 2: '/ora/data/data01/arju_undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1245184

Cause of the Problem
This is oracle bug. It fired whenever someone gave very large insert operation and during that time issued shutdown abort.

Solution of the Problem
Just perform normal recovery. To do so,
1)$sqlplus / as sysdba
2)startup mount;
3)recover database;
4)alter database open;

Install flashplayer fails with Please enter the installation path

After downloading flashplayer from site http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash whenever I try to install flashplayer on my system it always ask me "Please enter the installation path of the Mozilla, Netscape,or Opera browser". It gave me a lots of pain.
1)Logon as a root user.
$su
Password:

2)Untar the file that you download from the site above.
#tar -xvf tar -xvf install_flash_player_9_linux.tar.gz

3)Run the installer.
debian:# ./flashplayer-installer
Copyright(C) 2002-2006 Adobe Macromedia Software LLC. All rights reserved.
Adobe Flash Player 9 for Linux
Adobe Flash Player 9 will be installed on this machine.
You are running the Adobe Flash Player installer as the "root" user.
Adobe Flash Player 9 will be installed system-wide.
Support is available at http://www.adobe.com/support/flashplayer/
To install Adobe Flash Player 9 now, press ENTER.
To cancel the installation at any time, press Control-C.
NOTE: Please exit any browsers you may have running.
Press ENTER to continue...
Please enter the installation path of the Mozilla, Netscape,
or Opera browser (i.e., /usr/lib/mozilla):
WARNING: Please enter a valid installation path.

4)At this stage press CTRL+C and have a look at your mozilla installation directory. It by default take directory as /usr/lib/mozilla. Based on system you use it may vary. Like /usr/lib/firefox-2.0.0.3 or others. In my system to see I used,
debian:# ls /usr/lib/moz*
/usr/lib/mozilla:
libflashplayer.so plugins

/usr/lib/mozilla-firefox:
components plugins

/usr/lib/mozilla-snapshot:
plugins

So my installation directory is /usr/lib/mozilla-firefox. I have to provide this path whenever it prompts.

5)Again start the installer and provide this path.
debian:# ./flashplayer-installer
Please enter the installation path of the Mozilla, Netscape,
or Opera browser (i.e., /usr/lib/mozilla): /usr/lib/mozilla-firefox

WARNING: The Adobe Flash Player binary is a symbolic link.
The installer will replace this symbolic link with the actual binary.

----------- Install Action Summary -----------

Adobe Flash Player 9 will be installed in the following directory:

Browser installation directory = /usr/lib/mozilla-firefox

Proceed with the installation? (y/n/q): y

Installation complete.

At this stage your flashplayer installation is completed and in order to affect restart your browser.

Database startup fails with ORA-27302: failure occurred at: sskgpsemsper

Error Description
Whenever I try to start my database it fails with ORA-27302: failure occurred at: sskgpsemsper as following.
RMAN> STARTUP FORCE NOMOUNT PFILE='/backup03/webkey/testinitdb.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 09/23/2008 00:45:51
RMAN-04014: startup failed: ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

Cause of the problem
The error may mislead you. Though it indicates No space left on device but whenever I issue df -h on my OS there is enough space. The problem happened because of short of semaphores setting in the OS.

Solution of the problem
Solution 1)
Increase number of semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
The details is in How to configure(modify,see) Kernel Parameters in Linux

Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';

If you use spfile to startup your database you can use a trick to start your database which is discussed on,
http://arjudba.blogspot.com/2008/09/how-to-avoid-of-recreating-pfile-or.html.

Related Documents
Startup fails with ORA-27102: out of memory Solaris-AMD64 Error

ORA-14120: incompletely specified partition bound for a DATE column

In order to create partitioning table of FORM_AT whenever I try to create a temporary partitioned table name FORM_AT_PART it fails with ORA-14120: incompletely specified partition bound for a DATE column error as below.

SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-06','DD-MON-YY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-06','DD-MON-YY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-07','DD-MON-YY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);

partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column

Cause of The Problem

There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.

Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.

Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like below,
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);

Table created.

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example.

In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second.

My query was,
SELECT DISTINCT fs.type, fs.id
, fs.pid, fs.cid
, fs.cr_id, fs.created_date
FROM summary fs where fs.id in
(select fa.id from forms fa where fa.sar in
(select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') )
)and mode=0;

After seeing above query I decide to make range partition on column created_date both in summary table and forms table.

Below is the list of procedures of making partition of forms table

1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"

2)Get the creation script of the associated Indexes of the table.

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------

CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"


CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"

3)Get the creation script of the associated Constraints.

SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';

DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------

ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)

ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)

4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.

CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);

5)Load data into the temporary partitioned table.

In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.

insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.

6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.

SQL> rename forms to forms_bak;
Table renamed.

SQL> rename forms_part to forms;
Table renamed.

7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;

8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);

Now have a test your original query and compare performance with the one not partitioned.

Set Date format inside RMAN environment

Suppose inside rman environment I got the following output.
RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 558.80M DISK 00:01:14 08-SEP-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835
Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/ARJU/backupset/
2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/system01.dbf
2 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/undotbs01.dbf
3 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/sysaux01.dbf
4 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/users01.dbf

.
.
.

Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
1 Full 558.80M DISK 00:01:14 08-SEP-08 03:59:49
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835
Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/ARJU/backupset/
2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/users01.dbf

On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS