Oracle Database - TableSpace

Card Puncher Data Processing

About

A tablespace is a logical data structure which is physically represented by one or more datafiles physically.

From a analysis point of view, a tablespace is a group of one or more:

Type

select distinct contents from dba_tablespaces;
CONTENTS
---------
PERMANENT
TEMPORARY

Permanent

A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.

Undo

An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.

Temporary

A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles. files that are stored in temporary tablespaces are called tempfiles and are subject to some restrictions.

System Tablespace

Created when you create a database.

Tablespace Type Description
SYSTEM Primary contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment
SYSAUX Auxiliary contains information about the schemas used by various Oracle products and features, so that those products do not require their own tablespaces.

Tablespace FILE type

Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database if the type of file is not set during the creation.

Oracle database - File (Common Specification for data file, control file, )

BIGFILE

A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (<math>2^{32}</math> ) blocks. The maximum size of the single datafile or tempfile is:

  • 128 terabytes (TB) for a tablespace with 32K blocks
  • and 32TB for a tablespace with 8K blocks.

The database by default creates a locally managed tablespace with automatic segment-space management.

Restrictions on Bigfile Tablespaces:

  • You can specify only one datafile in the DATAFILE clause or one tempfile in the TEMPFILE clause.
  • You cannot specify EXTENT MANAGEMENT DICTIONARY.

SMALLFILE

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (<math>2^{22}</math> ) blocks.

How to

Creation

You must have the apps/search/search.jsp system privilege. To create the SYSAUX tablespace, you must have the SYSDBA system privilege.

Before you can create a tablespace, you must create a database to contain it, and the database must be open.

When you create a tablespace, it is initially a read/write tablespace.

DDL

Example: temp tablespace:

select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE 
  '/home/oracle/app/oracle/oradata/cdb1/orcl/orcl_temp012014-07-30_04-39-23-PM.dbf' SIZE 206569472
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"

  • Users tablespace
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
CREATE BIGFILE TABLESPACE "USERS" 
  DATAFILE '/home/oracle/app/oracle/oradata/cdb1/orcl/SAMPLE_SCHEMA_users01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING 
  ONLINE 
  PERMANENT 
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
  NOCOMPRESS  
  SEGMENT SPACE MANAGEMENT AUTO

where:

  • the BIGFILE clause specifies that it's a bigfile
  • the DATAFILE clause specifies a data file of size 5242880 bytes (ie 5242880 = 1024*1024*5= 5MB). Default size: 100M file. See Oracle database - File (Common Specification for data file, control file, ). The AUTOEXTEND clause said that when more space is required, 1310720 byte (1,25MB) extents will be added up to a maximum size of 32767 megabytes (size of a segment)
  • ONLINE specifies the status of the tablespace
  • PERMANENT: the type of tablespace
  • the SEGMENT clause specifies an automatic segment-space management

blocksize

The existing tablespace's blocksize can't be changed without recreating it. See :

In order to specify a nonstandard block size for the tablespace.:

Restriction: You cannot specify nonstandard block sizes for a temporary tablespace (or if you intend to assign this tablespace as the temporary tablespace for any users).

Offline/Online

use the apps/search/search.jsp statement.

Online is the default value.

ALTER TABLESPACE tablespaceName ONLINE;
ALTER TABLESPACE tablespaceName OFFLINE;

select status from dba_tablespaces;

Maxsize

The maxsize of a tablespace is determined by:

  • the type of tabelspace
  • the file autoextend property

Type of tabelspace

Type File Max Blocks With a
Block Size
of
Allowed Max Size
smallfile 4 million (<math>2^{22}</math> ) blocks 32K 128 Gb
smallfile 4 million (<math>2^{22}</math> ) blocks 8K 32 Gb
bigfile 4 billion (<math>2^{32}</math> ) blocks 32K 128 terabytes (TB)
bigfile 4 billion (<math>2^{32}</math> ) blocks 8K 32 terabytes (TB)

File autoextend property

Maxsize Limitation for that (data|temp) file is determined by the file autoextend property in the create statement.

If the (data|temp) file is:

  • auto extensible: the total sum of MAXBYTES of DBA_…_FILES
  • not auto extensible: the total sum of BYTES of DBA_…_FILES
SELECT   TABLESPACE_NAME
  , AUTOEXTENSIBLE
  , COUNT ( 1 )                                                                    AS Number_Of_Files
  , SUM ( DECODE ( AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES ) ) / 1024 / 1024 / 1024 AS MAX_SIZE_GB
  , SUM ( MAXBYTES )                                          / 1024 / 1024 / 1024 AS MAXBYTES_GB
  , SUM ( BYTES )                                             / 1024 / 1024 / 1024 AS BYTES_GB
  FROM DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
  , AUTOEXTENSIBLE;
  • Temp:
select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from   DBA_TEMP_FILES
group  by TABLESPACE_NAME;

Read-Only

When you create a tablespace, it is initially a read/write tablespace. use the ALTER TABLESPACE statement to make it a read-only tablespace.

Default

ALTER DATABASE DEFAULT TABLESPACE "USERS";

Rename

alter tablespace temp3 rename to temp2; 

The following errors can occur when renaming a tablespace to a name that existed for version previous to 11.2: ORA-959 Tablespace '_deleted00' Does Not Exist Error Executing Some Code

Drop

Take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace.

apps/search/search.jsp statement.

  • Dropping a Tablespace and all referential integrity constraints that refer to primary and unique keys inside tbs_01:
DROP TABLESPACE tbs_01 
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS; 
  • Deleting Operating System Files: Example The following example drops the tbs_02 tablespace and deletes all associated operating system datafiles:
DROP TABLESPACE tbs_02
   INCLUDING CONTENTS AND DATAFILES;

Segment space management

See Oracle Segment - (Free) Space Management (ASSM) of a Tablespace

Data Dictionary

See:

in the oracle database reference manual

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Oracle Segment Extent Data Block
Oracle Database - (Fragmented) (Unused|Free) space (Reclaim|Shrink|resize)

Used and free space. A data file contains ordered extents (by block Id). One segments is made up of one of several extents (not ordered, contiguous). Over time, updates and deletes on objects within...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Extent (Unit of Storage)

An extent is a group of contiguous data blocks. An extent can contain data from only one data file. An extent is a logical unit of database storage space allocation made up of a number of (contiguous|ordered)...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Segment (Database Object Storage)

A segment is a logical storage structure that contains data structure (segment type) of database objects. For example: each table's data is stored in its own data segment, while each index's data...
Card Puncher Data Processing
Oracle Database - (Memory) Cache (Tables, )

Set the tablespace offline and online or flush the buffer cache or flush the shared pool Small tables are automatically cached according to this criteria : Table Caching Criteria Table Size Size...
Card Puncher Data Processing
Oracle Database - ADDM (Automated Database Diagnostic Monitor) - Automatic Performance Diagnostics Tool

ADDM (Automated Database Diagnostic Monitor) is available since Oracle 10g. are useful features for performance tuning and troubleshooting. ADDM reports the most significant performance problems between...
Card Puncher Data Processing
Oracle Database - DB_BLOCK_SIZE initialization parameter

Value : 16K of 32K ( 1024) This parameter in the init.ora is the most important. This can be done only during creation time. If you have already created the Database you cannot change this value. You...
Card Puncher Data Processing
Oracle Database - Data Files

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the...
Card Puncher Data Processing
Oracle Database - Database Writer Process (DBWn)

Database writer process (DBWn) is a background process that writes buffers in the database buffer cache to data files. Modified or new data is not necessarily written to a datafile immediately. To reduce...
Card Puncher Data Processing
Oracle Database - Export Utility (exp)

Client Export utility. See The Export utility supports four modes of operation: Full: Exports a full database. Only users with the EXP_FULL_DATABASE role can use this mode. The FULL parameter...



Share this page:
Follow us:
Task Runner