ORA-14080: partition cannot be split along the specified high bound

Problem Scenarios

SQL>    CREATE TABLE USER_ACTIVITY
  2     (
  3     COL1 NUMBER NOT NULL,
  4     COL2 VARCHAR2(10) NULL,
  5     COL3 NUMBER NOT NULL
  6     )
  7     TABLESPACE USERS
  8     PARTITION BY RANGE (COL3)
  9     (
 10      PARTITION P10 VALUES LESS THAN (100),
 11      PARTITION P20 VALUES LESS THAN (200),
 12      PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
 13      );

Table created.

SQL> ALTER TABLE USER_ACTIVITY SPLIT PARTITION
  2      P_MAX AT (200)
  3      INTO
  4      (
  5      PARTITION P30,
  6      PARTITION MAXPART
  7      );
    P_MAX AT (200)
    *
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound

Cause of the Problem
There is already a partition with a specified high bound of 200. You need to split the partition on a value higher than the next range down but lower that the next range up.

Solution of the Problem
The solution is to split the partition into appropriate bound. The following statement will split the partition at value 250. From the two, the first partition (P30) holding values 200
to 249 and the second partition (MAXPART) holding values 250 and above.

SQL> ALTER TABLE USER_ACTIVITY SPLIT PARTITION
  2      P_MAX AT (250)
  3      INTO
  4      (
  5      PARTITION P30,
  6      PARTITION MAXPART
  7      );

Table altered.

Export fails with ORA-01406: fetched column value was truncated

Problem Description
Exporting oracle 11g database using 10g exp utility fails with ORA-01406 errors.

E:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log

Export: Release 10.2.0.1.0 - Production on Thu Jul 22 23:01:49 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
EXP-00008: ORACLE error 1406 encountered
ORA-01406: fetched column value was truncated
EXP-00000: Export terminated unsuccessfully

By further investigation it is noticed that the statement that fails with ORA-01406 is:
SELECT GRANTEE, PRIV, WGO FROM SYS.EXU8SPV ORDER BY SEQUENCE

And the database character set is WE8MSWIN1252.

SQL> select property_value from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
WE8MSWIN1252

The same problem arises whenever you connect to 11g database using oracle 9i or oracle 10gR1 exp client utility.

Cause of the Problem
The cause of this problem has been identified as Oracle Bug 6804150 and fixed in 11g and 10.2.0.5.

It is caused by the new privilege name "ADMINISTER SQL MANAGEMENT OBJECT" added in 11g.
Since the value exceeds 30 characters, then truncation happens and the error ora-01406 is encountered.

Solution of the Problem
Solution 01:
Change the Database character set to AL32UTF8 will solve this problem.

Solution 02:
Apply Patch for BUG 6804150 if available for your platform.

Solution 03:
Install the Oracle 10.2.0.5 patchset.

Solution 04:
Use 11g exp client utility.

exp fails with EXP-00023 and expdp fails with ORA-31631, ORA-39161

Problem Description
It is needed to export/import full database. To export full database, "EXPORT FULL DATABASE" privilege is granted and to import full database "IMPORT FULL DATABASE" privilege is granted. Now while doing full database export/import, exp fails with EXP-00023 and expdp fails with
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges

In the following example the error case is demonstrated.

A user named exp_user is created and it is granted EXPORT FULL DATABASE privilege.

E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 22 19:35:15 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user exp_user identified by exp_user;

User created.

SQL> grant create session, resource, export full database to exp_user;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee='EXP_USER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_USER                       EXPORT FULL DATABASE                     NO
EXP_USER                       CREATE SESSION                           NO
EXP_USER                       UNLIMITED TABLESPACE                     NO

SQL> select * from dba_role_privs where grantee='EXP_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXP_USER                       RESOURCE                       NO  YES

E:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log

Export: Release 10.2.0.1.0 - Production on Thu Jul 22 19:43:52 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00023: must be a DBA to do Full Database or Tablespace export
(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user EXP_USER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EXP_USER
About to export EXP_USER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export EXP_USER's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

E:\>expdp exp_user/exp_user full=y dumpfile=exp_dump.dmp logfile=exp_dump.log

Export: Release 10.2.0.1.0 - Production on Thursday, 22 July, 2010 19:51:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges

From the above example, we see both exp and expdp operations have failed.

Cause of the Problem
The system privileges "EXPORT FULL DATABASE" and "IMPORT FULL DATABASE" was introduced in oracle database 10gR1. But these two privileges are not currently in use by oracle. May be they will be implemented in future releases. In oracle 10g and 11g these two system privileges are not operational and hence assigning these privileges will do nothing.

The right privileges used by export/import are the roles EXP_FULL_DATABASE/ IMP_FULL_DATABASE.

Solution of the Problem
Assign correct privileges to the user. He who will do full database export operation assign him EXP_FULL_DATABASE role and he who will perform full database import operation assign him IMP_FULL_DATABASE role.

E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 22 19:53:32 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> revoke export full database from exp_user;

Revoke succeeded.

SQL> grant exp_full_database to exp_user;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee = 'EXP_USER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_USER                       CREATE SESSION                           NO
EXP_USER                       UNLIMITED TABLESPACE                     NO

SQL> select * from dba_role_privs where grantee = 'EXP_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXP_USER                       EXP_FULL_DATABASE              NO  YES
EXP_USER                       RESOURCE                       NO  YES

Now invoking exp and expdp with full=y went fine.

D:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log

Export: Release 11.1.0.6.0 - Production on Thu Jul 22 21:19:03 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
.
.
.

How to uninstall Oracle 11g

Uninstall Oracle software 11.2g
In 11.2g there is deinstall command through which you can uninstall standalone Oracle Database installations, Oracle Clusterware and Automatic Storage Management (ASM) from your server, as well as Oracle Real Application Clusters (Oracle RAC) and Oracle Database client installations. You can find out deinstall tool under $ORACLE_HOME/deinstall folder.

If you don't have deinstall tool you can download it from url http://www.oracle.com/technology/software/products/database/index.html

- Under Oracle Database 11g Release 2, click See All for the respective platform for which you want to download the Deinstallation Tool.

- The Deinstallation Tool is available for download at the end of the page.

So, in 11.2g just use,
$$ORACLE_HOME/deinstall/deinstall

Uninstall Oracle software in 11.1g
If you are running Oracle 11.1g then you have to open the Oracle Universal Installer on windows and runInstaller on unix. In windows you can find oui under $ORACLE_HOME/oui/bin and file name is setup.exe.

ORA-29913, ORA-29400, KUP-00554 while querying external table

Problem Description
While querying an external table it fails with error ORA-29913, ORA-29400, KUP-00554, KUP-01005 like below.

SQL> create directory ext_dir as 'c:\';

Directory created.

SQL> create table external_table(
  2  col1 varchar2(1),
  3  col2 varchar2(20),
  4  col3 varchar2(10)
  5  )
  6  organization external
  7  (type oracle_loader
  8  default directory ext_dir
  9  access parameters
 10  (
 11  records delimited by newline
 12  fields
 13  missing field value are null
 14  (col1 position(1,1),
 15  col2 position(2,20),
 16  col3 position(21,30)
 17  )
 18  )
 19  location ('c:\temp\TEST.TXT')
 20  )
 21  ;

Table created.

SQL> select * from external_table;
select * from external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "values"
KUP-01008: the bad identifier was: value
KUP-01007: at line 3 column 15

Cause of the Problem
While creating external table the access parameters are not parsed. The access parameters are parsed when the external table is queried. The above errors are returned due to syntax error in the external table access parameters.

Solution of the Problem
The solution is correct the syntax in the external table creation access parameters. Let's try to solve syntax error one by one.

- Drop the table as in the database two tables as same name under one schema can't exist.

SQL> drop table external_table;

Table dropped.

- Write the previous external table creation script.

SQL> create table external_table(
  2  col1 varchar2(1),
  3  col2 varchar2(20),
  4  col3 varchar2(10)
  5  )
  6  organization external
  7  (type oracle_loader
  8  default directory ext_dir
  9  access parameters
 10  (
 11  records delimited by newline
 12  fields
 13  missing field value are null
 14  (col1 position(1,1),
 15  col2 position(2,20),
 16  col3 position(21,30)
 17  )
 18  )
 19  location ('c:\temp\TEST.TXT')
 20  )
 21

- At line 13 the keyword will be values.

SQL> 13
 13* missing field value are null
SQL> c/value/values
 13* missing field values are null

- At line 14, 15 and 16 position value comma (,) will be replaced by colon (:).

SQL> 14
 14* (col1 position(1,1),
SQL> c/1,1/1:1
 14* (col1 position(1:1),
SQL> 15
 15* col2 position(2,20),
SQL> c/2,20/2:20
 15* col2 position(2:20),
SQL> 16
 16* col3 position(21,30)
SQL> c/21,30/21:30
 16* col3 position(21:30)
SQL> /

Table created.

- Now selecting the table avoid any syntax type errors but it lead to another error related to path. In the external table we can't specify absolute path in this way. So in the following example we are correcting that.

SQL> select * from external_table;
select * from external_table
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04076: file name cannot contain a path specification: c:\temp\TEST.TXT

SQL> drop table external_table;

Table dropped.

- Creating external table after correcting path.

SQL> create table external_table(
  2  col1 varchar2(1),
  3  col2 varchar2(20),
  4  col3 varchar2(10)
  5  )
  6  organization external
  7  (type oracle_loader
  8  default directory ext_dir
  9  access parameters
 10  (
 11  records delimited by newline
 12  fields
 13  missing field values are null
 14  (col1 position(1:1),
 15  col2 position(2:20),
 16  col3 position(21:30)
 17  )
 18  )
 19  location ('test.txt')
 20  );

Table created.

SQL> select * from external_table;

C COL2                 COL3
- -------------------- ----------
1  222222222222222222  2 11111111
1  222255555555555222  2 11111333

Can one use Oracle 10g XE in commercial enviroment

As of July 21, 2010 Oracle has not yet released Express Edition (Oracle Database XE) for 11g. But there is available Oracle database 10g XE and many ones use it for their personal use as Enterprise Edition of Oracle is really expensive. Now many ones think whether one can use Oracle database 10g XE for their commercial environment.

It is true that Oracle 10g XE comes with many limitations and also has less features compared to Oracle database Enterprise Edition (EE). But there is no restriction about where you can use it or not. So in the commercial environment you can use Oracle database 10g XE but have following limitation while using it.

1. It can have a single instance on any server.

2. You can install it on a multiple CPU server, but it can only be executed on one processor in any server.

3. XE only supports up to 4GB of user data (not including XE system datafile data.)

4. XE can only use up to 1 GB RAM of available memory.

So if your settings/environmental satisfies/follows above 4 limitations, you can use XE at your commercial environment without any problems.

Along with above limitations in XE there is many others limitations in terms of features/option. Such as the following features is not included in XE.

Major Features not included in XE
1) Oracle Real Application Clusters

2) Oracle Spatial

3) Advanced Security Option

4) Oracle Label Security

5) Oracle Partitioning

6) Oracle OLAP

7) Oracle Data Mining

8) Oracle Enterprise Manager

9) Oracle Change Management Pack

10) Oracle Configuration Management Pack

11) Oracle Diagnostic Pack

12) Oracle Tuning Pack

13) Oracle Clusterware

14) Oracle Connection Manager

15) Oracle Names

16) Oracle Streams (Apply process only)

17) Oracle Workflow

18) Messaging Gateway

19) Oracle Data Guard

20) Oracle Fail Safe

21) Ultra Search

22) interMedia

23) Oracle Workspace Manager

Application Development, Language, and Database Features not included in 10g XE

1) iSQL*Plus
2) Database Web services
3) Java support in the database
4) Java Server Pages
5) Java native compilation
6) SQLJ
7) XQuery, JNDI, or Servlet support
8) Data Compression
9) Bitmapped index, bitmapped join index
10) Summary Management
11) Materialized View Query Rewrite
12) Parallel query/DML
13) Parallel statistics gathering
14) Parallel index build/scans
15) Parallel export/import

Backup and Recovery Features not included in XE
1) Online schema reorganization/redefinition
2) Flashback Table
3) Flashback Database
4) Flashback Transaction Query
5) Server-managed backup and recovery
6) Backup Unused Block Compression
7) Backup Encryption
8) In case of Incremental backup and recovery does not support change tracking file or optimized incremental backup capability.
9) Duplexed backup sets
10) Fast-Start Selectable Recovery Time
11) Block-level media recovery
12) Parallel backup and recovery
13) Point-in-time tablespace recovery
14) Trial recovery
15) Rolling Upgrade Support – Patch Set, Database and O/S

Security & Manageability Features not included in 10g XE
1) Virtual Private Database
2) Fine grained auditing

3) Automatic Storage Management
4) Database Resource Manager

Data Movement/Integration Features not included in XE
1) Synchronous Change Data Capture
2) Asynchronous Change Data Capture
3) Transportable tablespaces, including cross-platform
4) Advanced Replication
5) Transparent Gateways

Networking Features not included in Oracle 10g XE
1) Directory connectivity.

PRKP-1001, CRS-0215 while starting instance using srvctl

Problem Description
The database/instances are not starting up using srvctl command, while starting up it shows PRKP-1001, CRS-0215 errors.

oracle@DBRAC2:~> srvctl status instance -i dbrac2 -d dbrac
Instance dbrac2 is not running on node dbrac2

oracle@DBRAC2:~> srvctl start instance -i dbrac2 -d dbrac
PRKP-1001 : Error starting instance dbrac2 on node dbrac2
CRS-0215: Could not start resource ora.dbrac.dbrac2.inst.

But using sqlplus the instances are able to come up. For example,
SQL> startup
works fine.

Cause of the Problem
"srvctl" command picks the parameter file from the location specified while registering the database with OCR.

In this case srvctl will pick "+DATA1/dbrac/spfiledbrac.ora" to start the instance,

# srvctl config database -d dbrac -a 
adc17 dbrac1 /u01/app/oracle/product/10.2.0/db_1 
adc18 dbrac2 /u01/app/oracle/product/10.2.0/db_1 
DB_UNIQUE_NAME: dbrac 
DB_NAME: dbrac 
ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1 
SPFILE: +DATA1/dbrac/spfiledbrac.ora                          
DOMAIN: null 
DB_ROLE: PRIMARY 
START_OPTIONS: open 
POLICY: AUTOMATIC 
ENABLE FLAG: DB ENABLED 

While we start the instance using sqlplus, it picks the parameter file from $ORACLE_HOME/dbs directory by default

SQL> startup
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0/db_1/dbs/spfiledbrac.ora

As the parameter file used to start the instance using srvctl and sqlplus are not the same srvctl fails to start the oracle instance.

Solution of the problem
Make sure that both sqlplus (i.e $ORACLE_HOME/dbs) and srvctl command (i.e +DATA1/dbrac/spfiledbrac.ora ) are using same parameter file to start up the instances.