Possible solution to ORA-01450: maximum key length (3800) exceeded when rebuilding an index

As a part of disabling TDE in a test database, I was moving indexes out of the TDE encrypted tablespace to another, similarly created tablespace, but one without encryption.

When trying to execute

ALTER INDEX SCOTT.MYTABLE_U01 REBUILD ONLINE TABLESPACE DATA32K_NOTDE

one of the tables returned an error during the online rebuild:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3800) exceeded

This error is normally worked around by one of the following actions:

1. Rebuild the database with a larger blocksize
2. Add a new tablespace with a larger blocksize (my preferred solution)
3. Make the index smaller, meaning drop and recreate the index and omit one or more of the previously indexed columns


This is documented in Doc ID 747107.1 "ORA-01450 Error on Create Index" at Oracle Support.

For me though, it worked by simply changing

REBUILD ONLINE 

to

REBUILD

and the index rebuild executed without problems.
The Oracle Documentation for 12cR1 has a few restrictions regarding the use of online rebuilds, but none of them seems relevant to the error I observed.

For more information about the ALTER INDEX statement, see the official documentation from Oracle

How to check if Database Vault is enabled or disabled

To check whether or not the Database Vault option is enabled in your database, run the following query as a privileged user:

SELECT parameter, value
FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

The true/false value of the parameter will indicate whether or not it is enabled.

Source: Oracle Documentation>

Create a tablespace using input variables

Here is a script to help set up tablespaces based on the path used for the system tablespace:

SET LINES 200
-- NEW_VALUE in sqlplus specifies a variable to hold a column value
COL tabspace_path FORMAT A50 NEW_VALUE path

SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME, '/', -1) -1) tabspace_path
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSTEM';

SELECT '&path' variable_value
FROM DUAL;

CREATE BIGFILE TABLESPACE test_tbspc DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

exit

Run the script:

oracle@myserver:[testdb01]# sqlplus / as sysdba @test.sql

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


TABSPACE_PATH
--------------------------------------------------
/u02/oradata/testdb01
old   1: SELECT '&path' variable_value
new   1: SELECT '/u02/oradata/testdb01' variable_value

VARIABLE_VALUE
---------------------
/u02/oradata/testdb01

Enter value for tablespace_name: mytablespace
old   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
new   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '/u02/oradata/testdb01/mytablespace.dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED

Tablespace created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Question: Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Answer:
In versions prior to 12.2, you cannot.
From Version 12.2, you can.

In Oracle Versions prior to 12.2, this is what Oracle writes about decrypting a tablespace?

"You cannot decrypt a tablespace that has been created encrypted. You must create an unencrypted tablespace and re-create the database objects in the unencrypted tablespace."

In version 12.2, Oracle has added support to decrypt both online and offline tablespaces.

The syntax for an offline tablespace decryption would be

administer key management set keystore open identified by software_keystore_password; 
alter tablespace TEST_TABSPC offline;
alter tablespace TEST_TABSPC encryption offline decrypt;
alter tablespace TEST_TABSPC online;

An online decryption would executed like this:

alter tablespace TEST_TABSPC encryption online decrypt file_name_convert = ('test_tabspc.dbf', 'test_tabspc_decrypted.dbf');

For the online method to work, compatible should be set to 12.2, and there must be enough storage available for the database server that you can hold a copy of the entire tablespace to be decrypted.

How to check the current kernel settings

sysctl -a 

or, to limit the search to kernel parameters that starts with "sem":

sysctl -a |grep shm

Example output:

[root@myserver ~]# sysctl -a | grep sem
kernel.sem = 250        32000   100     128

What is the Software keystore used by Oracle Transparent Data Encryption?

The Software keystore is the container that stores the TDE master key.
There is one keystore per database.

From the Oracle Documentation:

"A software keystore is a container that stores the Transparent Data Encryption master encryption key."

By "container", Oracle means either

* a software keystore
* a hardware module security (HSM) keystore
* Oracle Key Vault keystore (separately licenced)

Personally I have only used software keystores, which is the equivalent of keeping the keystore as a file on a file system, or a file on an ASM disk group.


How is the keystore located by by the database?

"the database locates this keystore by checking the keystore location that you define in the sqlnet.ora file."

Example of sqlnet.ora from my own environment:

# WALLET_OVERRIDE is only applicable when using auto login, set to FALSE
SQLNET.WALLET_OVERRIDE = FALSE
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/oracle/admin/proddb01/wallet)
    )
  )
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_DES_CBC_SHA)

Now that the keystore is defined, you can log into the database to create and open the keystore.
Finally, you set the TDE master encryption key.

After that, you can encrypt the data.

See my post Quick guide to set up Transparent Data Encryption for a tablespace for examples on how to set it up.

What is Transparent Data Encryption and when should it be used?

Use TDE to protect data on disk, or "data at rest".
In other words, TDE adds another layer of security to your database setup, but is by no means sufficent to protect your data by itself.

Inside the database, Oracle uses different methods to protect the data against unauthorized access.

For example, you can set up auditing to trace Access to specific tables in the database.
Another example would be redaction, introduced in Oracle 12cR1, which can hide sensitive data from the users.

However, none of these methods would protect data on disk, or "data at rest" as Oracle calls it.

From the documentation:

"TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen."

and

"To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files."

How does Oracle prevent unauthorized decryption?

"To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore."

Source: Oracle Documentation