Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Database Administration / Oracle Tablespace

Oracle Tablespace

Summary: in this tutorial, you will learn about the Oracle tablespace and how Oracle uses tablespaces to logically store the data in the database.

What is an Oracle Tablespace

Oracle divides a database into one or more logical storage units called tablespaces.

Each tablespace consists of one or more files called datafiles. A datafile physically stores the data objects of the database such as tables and indexes on disk.

In other words, Oracle logically stores data in the tablespaces and physically stores data in datafiles associated with the corresponding tablespaces.

The following picture illustrates the relationship between a database, tablespaces, and datafiles:

Oracle tablespace

By using tablespaces, you can perform the following operations:

  • Control the storage size allocated for the database data.
  • Grant specific space quotas to the database users.
  • Control the availability of data by taking tablespaces online or offline (more on this later).
  • Improve the performance of the database by allocating data storage across devices.
  • Perform partial database backup or recovery.

Default tablespaces in Oracle

Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.

  • The SYSTEM and SYSAUX tablespaces store system-generated objects such as data dictionary tables. And you should not store any object in these tablespaces.
  • The USERS tablespace is helpful for ad-hoc users.
  • The UNDOTBS1 holds the undo data.
  • The TEMP is the temporary tablespace which is used for storing intermediate results of sorting, hashing, and large object processing operations.

Online and Offline Tablespaces

A tablespace can be online or offline. If a tablespace is offline, you cannot access data stored in it. On the other hand, if a tablespace is online, its data is available for reading and writing.

Note that the SYSTEM tablespace must always be online because it contains the data dictionary that must be available to Oracle.

Normally, a tablespace is online so that its data is available to users. However, you can take a tablespace offline to make data inaccessible to users when you update and maintain the applications.

In case of some errors such as hardware failures, Oracle automatically takes an online tablespace offline. Any attempt to access data in offline tablespace will result in an error.

Read-Only Tablespaces

The read-only tablespaces allow Oracle to avoid performing backup and recovery of large, static parts of a database. Because Oracle doesn’t update the files of a read-only tablespace, you can store the files on the read-only media.

Oracle allows you to remove objects such as tables and indexes from a read-only tablespace. However, it does not allow you to create or alter objects in a read-only tablespace.

When you create a new tablespace, it is in the read-write mode. To change a tablespace to read-only tablespace, you use the ALTER TABLESPACE command with the READ ONLY option.

More on Oracle Tablespaces

  • Create tablespace – show you how to create a new tablespace in the database.
  • Drop tablespace – describe the steps of removing a tablespace from the database.
  • Extend tablespace – how to extend the size of a tablespace.
  • Temporary tablespace – manipulate temporary tablespace effectively.
  • Tablespace group – how to use the tablespace group more effectively to optimize internal Oracle operations
  • Was this tutorial helpful?
  • YesNo
Previous Oracle SHUTDOWN
Next Oracle CREATE TABLESPACE

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.