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:
select distinct contents from dba_tablespaces;
CONTENTS
---------
PERMANENT
TEMPORARY
A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.
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.
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.
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. |
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, )
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:
The database by default creates a locally managed tablespace with automatic segment-space management.
Restrictions on Bigfile Tablespaces:
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.
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.
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"
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 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).
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;
The maxsize of a tablespace is determined by:
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) |
Maxsize Limitation for that (data|temp) file is determined by the file autoextend property in the create statement.
If the (data|temp) file is:
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;
select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from DBA_TEMP_FILES
group by TABLESPACE_NAME;
When you create a tablespace, it is initially a read/write tablespace. use the ALTER TABLESPACE statement to make it a read-only tablespace.
ALTER DATABASE DEFAULT TABLESPACE "USERS";
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
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.
DROP TABLESPACE tbs_01
INCLUDING CONTENTS
CASCADE CONSTRAINTS;
DROP TABLESPACE tbs_02
INCLUDING CONTENTS AND DATAFILES;
See Oracle Segment - (Free) Space Management (ASSM) of a Tablespace
See:
in the oracle database reference manual