Create and manage bigfile tablespaces

Bigfile Tablespaces
- a tablespace with a single, but very large (up to 4G blocks) datafile.
-A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile.A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile.

- The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
- An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.
- Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.
- Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.
- Bigfile tablespaces are intended to be used with Automatic Storage Management (Oracle ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
- Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.
- You can also identify a bigfile tablespace by the relative file number of its single datafile (DBA_DATA_FILES). That number is 1024 on most platforms, but 4096 on OS/390.
Creating a Bigfile Tablespace
SQL> set timing on
SQL> CREATE BIGFILE TABLESPACE bigtbs
  2  DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\bigtbs01.dbf' SIZE 10G
  3  /
Tablespace created.
Elapsed: 00:01:09.66
SQL>

Compressed Tablespaces
CREATE BIGFILE TABLESPACE bigtbs DEFAULT COMPRESS FOR OLTP
DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\bigtbs01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 10G   -- the statement fails on 11gR1
You can override the default tablespace compression specification when you create a table in that tablespace.
Encrypted Tablespaces
- To encrypt a tablespace, you must open the database with the COMPATIBLE initialization parameter set to 11.1.0 or higher.

SQL> CREATE TABLESPACE securespace
  2  DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\secure01.dbf' SIZE 100M
  3  ENCRYPTION
  4  DEFAULT STORAGE(ENCRYPT);
CREATE TABLESPACE securespace
*
ERROR at line 1:
ORA-28365: wallet is not open
Elapsed: 00:00:00.20
SQL>

How open wallet see the topic Transparent Data Encryption (TDE) & LogMiner

After opening wallet run the statement again and it will be with success

SQL> alter system set wallet open identified by "welcome1";
System altered.
SQL> CREATE TABLESPACE securespace
  2  DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\secure01.dbf' SIZE 100M
  3  ENCRYPTION
  4  DEFAULT STORAGE(ENCRYPT)
  5  /
Tablespace created.
SQL>


Related Topic:
Create and manage temporary, permanent, and undo tablespaces