How to solve ORA-14323: cannot add partition when DEFAULT partition exists

You have a partitioned table with an overflow partition, and you try to add another partition to it:

ALTER TABLE RECEIVED_DOCUMENTS 
ADD PARTITION EES_COUNTRIES VALUES('NORWAY','ICELAND')

Result:

ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

Solution: split the overflow partition:

ALTER TABLE RECEIVED_DOCUMENTS 
SPLIT PARTITION "OTHERS" 
VALUES ('NORWAY','ICELAND')
INTO ( PARTITION EES_COUNTRIES,
       PARTITION OTHERS
       )
UPDATE INDEXES;

How to split an overflow partition using ALTER TABLE .... SPLIT PARTITION

For a range partitioned table:

ALTER TABLE SCOTT.EMPLOYEES
SPLIT PARTITION START_DATE_MAX AT (TO_DATE('2014-03-01', 'YYYY-MM-DD')) 
INTO (
      PARTITION START_DATE_FEB_2014 TABLESPACE ACTIVE_DATA,
      PARTITION START_DATE_MAX TABLESPACE TOOLS
     );

For a list-partitioned table:

ALTER TABLE received_documents
SPLIT PARTITION OTHERS
VALUES ('EAST','WEST','NORTH','SOUTH')
INTO (PARTITION NON_EU_CITIZEN,
      PARTITION OTHERS
) UPDATE INDEXES PARALLEL 8;

See this post for an example of splitting a default subpartition.

How to create a global temporary table

An example of a Global Temporary Table (GTT) would be:

CREATE GLOBAL TEMPORARY TABLE INCOMING_DATA (
  ROW_ID                      NUMBER(10) NOT NULL,
  SSN                         NUMBER(11),
  ENTRY_DATE                  DATE NOT NULL,
  HANDLED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  MESSAGE                     CLOB
 )
ON COMMIT DELETE ROWS
;

ON COMMIT DELETE ROWS indicates that Oracle will purge the rows after an ended transaction (after a COMMIT, ROLLBACK or an implicit COMMIT).
Instead of ON COMMIT DELETE ROWS you could also use ON DELETE PRESERVE ROWS, which will remove the rows at the end of the user's session.

You can add indexes, triggers and views on a GTT. You can even truncate a temporary table, and it will only affect the curren session's rows, leaving other users' rows intact.


With Oracle 12c came the ability for temporary tables to store its UNDO segments in temporary tablespaces, rather than conventional tablespaces, thus removing the need to generate REDO.

To enable this for your session, use:

ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

before creating the GTT.

Sources:

Oracle-Base: article about GTT and another one explaining the 12c new feature "temporary undo"

Oracle Documentation: Overview of GTT
Check my note on private temporary tables, available from Oracle 18c.

Create a globally partitioned hash index

Partitioned table layout:

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a global hash-partitioned index. Notice the GLOBAL keyword:

CREATE INDEX ARCHIVED_NOTES_IDX2 ON ARCHIVED_NOTES (REGISTRED_DATE)
  GLOBAL PARTITION BY HASH (REGISTRED_DATE)
  PARTITIONS 3;

Verify its existence:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_IDX2	SYS_P441
ARCHIVED_NOTES_IDX2	SYS_P442
ARCHIVED_NOTES_IDX2	SYS_P443

Let's take a deeper look at the index properties:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	ARCHIVED_NOTES_IDX2	HASH	NONE	GLOBAL	PREFIXED	REGISTRED_DATE	1

For global partitioned indexes, the "alignment" column will always show the index as "prefixed". This is the only supported index type for global partitioned indexes.

Create a globally partitioned range index

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a global range partitioned index. Notice the GLOBAL keyword:

CREATE INDEX SCOTT.ARCHIVED_NOTES_IDX1 
ON SCOTT.ARCHIVED_NOTES(NOTE_ID)
GLOBAL PARTITION BY RANGE(NOTE_ID)(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),  
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000),
  PARTITION ARCHIVED_OTHERS VALUES LESS THAN (MAXVALUE)
);

Also note that Oracle required the MAXVALUES clause as the last partition in the index, to ensure that all rows in the table will be represented in the index. Without MAXVALUES, Oracle will throw the error:

ORA-14021: MAXVALUE must be specified for all columns

Verify its existence:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_IDX1	ARCHIVED20
ARCHIVED_NOTES_IDX1	ARCHIVED21
ARCHIVED_NOTES_IDX1	ARCHIVED22
ARCHIVED_NOTES_IDX1	ARCHIVED_OTHERS

You could also make the index UNIQUE:

CREATE UNIQUE INDEX SCOTT.ARCHIVED_NOTES_IDX1 ... 

A unique index like this could support a primary key constraint on the table, if desirable:

ALTER TABLE ARCHIVED_NOTES ADD CONSTRAINT ARCHIVED_NOTES_PK
PRIMARY KEY (NOTE_ID)
USING INDEX ARCHIVED_NOTES_IDX1;

Let's take a deeper look at the index Properties:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	ARCHIVED_NOTES_IDX1	RANGE	NONE	GLOBAL	PREFIXED	NOTE_ID	1

For globally partitioned indexes, the "alignment" column will always show the index as "prefixed". This is the only supported index type for global partitioned indexes.

An important point regarding globally partitioned indexes is pointed out in the Oracle Documentation:

"Normally, a global index is not equipartitioned with the underlying table."

and

"There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. So an index that is equipartitioned with the underlying table should be created as LOCAL."

In other words, while the example above would work, it may not be a good idea. It would make better sense for the global partitioned index to be created on another column:

CREATE INDEX SCOTT.ARCHIVED_NOTES_IDX1 
ON SCOTT.ARCHIVED_NOTES(REGISTRED_DATE)
GLOBAL PARTITION BY RANGE(REGISTRED_DATE)(  
  PARTITION NOTES_2012 VALUES LESS THAN (to_date('01.01.2013', 'dd.mm.yyyy')),  
  PARTITION NOTES_2013 VALUES LESS THAN (to_date('01.01.2014', 'dd.mm.yyyy')),  
  PARTITION NOTES_2014 VALUES LESS THAN (to_date('01.01.2015', 'dd.mm.yyyy'))
  ,PARTITION ARCHIVED_OTHERS VALUES LESS THAN (MAXVALUE)
);

Create a local non-prefixed partitioned index

The table below is partitioned by range, using the column NOTE_ID as the partition key:

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a global partitioned index:

CREATE INDEX SCOTT.REGISTRED_DATE_SK1 ON SCOTT.ARCHIVED_NOTES
(REGISTRED_DATE)
LOCAL (  
  PARTITION ARCHIVED20,  
  PARTITION ARCHIVED21,  
  PARTITION ARCHIVED22
)

Confirm its existence:

SELECT INDEX_NAME,PARTITIONED
FROM USER_INDEXES
WHERE TABLE_NAME='ARCHIVED_NOTES';

INDEX_NAME	PARTITIONED
REGISTRED_DATE_SK1	YES

Confirm that the expected index partitions have been created:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
REGISTRED_DATE_SK1	ARCHIVED20
REGISTRED_DATE_SK1	ARCHIVED21
REGISTRED_DATE_SK1	ARCHIVED22

Add a new partition:

ALTER TABLE SCOTT.ARCHIVED_NOTES
 ADD 
  PARTITION ARCHIVED23 VALUES LESS THAN (24000000);

If you confirm the index partitions again, you'll see that a new index partition has been added:

INDEX_NAME	PARTITION_NAME
REGISTRED_DATE_SK1	ARCHIVED23

Let's take a deeper look at the properties of this index:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	REGISTRED_DATE_SK1	RANGE	NONE	LOCAL	NON_PREFIXED	REGISTRED_DATE	1

The "alignment" column of the above output reveals that the index is of type "non_prefixed", which means that the partitioning key is not the first column in the index.

Create a local prefixed partitioned index

The table below is partitioned by range, using the column NOTE_ID as the partition key:

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a local index:

CREATE UNIQUE INDEX SCOTT.ARCHIVED_NOTES_PK ON SCOTT.ARCHIVED_NOTES
(NOTE_ID)
LOCAL (  
  PARTITION ARCHIVED20,  
  PARTITION ARCHIVED21,  
  PARTITION ARCHIVED22
);

Confirm its existence:

INDEX_NAME	PARTITIONED
ARCHIVED_NOTES_PK	YES

The index we just created can be used to support a unique or a primary key constraint:

ALTER TABLE ARCHIVED_NOTES ADD CONSTRAINT ARCHIVED_NOTES_PK
PRIMARY KEY (NOTE_ID)
USING INDEX ARCHIVED_NOTES_PK;

Confirm that the expected index partitions have been created:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_PK	ARCHIVED20
ARCHIVED_NOTES_PK	ARCHIVED21
ARCHIVED_NOTES_PK	ARCHIVED22

Add a new partition:

ALTER TABLE SCOTT.ARCHIVED_NOTES
 ADD 
  PARTITION ARCHIVED23 VALUES LESS THAN (23000000);

If you confirm the index partitions again, you'll see that a new index partition has been added:

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_PK	ARCHIVED23

Let's take a deeper look at the properties of this index:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	ARCHIVED_NOTES_PK	RANGE	NONE	LOCAL	PREFIXED	NOTE_ID	1

The "alignment" column of the above output reveals that the index is of type "prefixed", which means that the partitioning key is the first column in the index.