Each tablespace consists of 1 or more files called datafiles. A datafile physically stores the info objects of the database like tables and indexes on disk.

In other words, Oracle logically stores data within the tablespaces and physically stores data in datafiles related to the corresponding tablespaces.

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

By using tablespaces, you’ll perform the subsequent operations:

Control the storage size allocated for the database data.
Grant specific space quotas to the database users.
Control the supply of knowledge 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 subsequent default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.

The SYSTEM and SYSAUX tablespaces store system-generated objects like data dictionary tables. And you ought to not store any object in these tablespaces.
The USERS tablespace is useful for ad-hoc users.
The UNDOTBS1 holds the undo data.
The TEMP is that the temporary tablespace which is employed for storing intermediate results of sorting, hashing, and enormous object processing operations.

Online and Offline Tablespaces

A tablespace are often online or offline. If a tablespace is offline, you can’t access data stored in it. On the opposite hand, if a tablespace is online, its data is out there for reading and writing.

Note that the SYSTEM tablespace should be online because it contains the info dictionary that has got to be available to Oracle.

Normally, a tablespace is online in order that its data is out there to users. However, you’ll take a tablespace offline to form data inaccessible to users once you update and maintain the applications.

In case of some errors like hardware failures, Oracle automatically takes a web tablespace offline. Any plan to access data in offline tablespace will end in a mistake .

Read-Only Tablespaces

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

Oracle allows you to get rid of objects like tables and indexes from a read-only tablespace. However, it doesn’t allow you to make or alter objects during a read-only tablespace.

When you create a replacement tablespace, it’s within the read-write mode. to vary a tablespace to read-only tablespace, you employ the ALTER TABLESPACE command with the READ ONLY option.

Oracle CREATE TABLESPACE

Introduction to the CREATE TABLESPACE statement

The CREATE TABLESPACE statement allows you to make a replacement tablespace. the subsequent illustrates the way to create a replacement tablespace named tbs1 with size 1MB:

CREATE TABLESPACE tbs1

DATAFILE ‘tbs1_data.dbf’

SIZE 1m;

In this statement:

First, specify the name of the tablespace after the CREATE TABLESPACE keywords. during this example, the tablespace name is tbs1.
Second, specify the trail to the info file of the tablespace within the DATAFILE clause. during this case, it’s tbs1.dbf. Note that you simply can use the datafile full path.
Third, specify the dimensions of the tablespace within the SIZE clause. during this example, 1m stands for 1MB, which is sort of small.

Once the tablespace is made , you’ll find its information by querying data from the dba_data_files view:

SELECT

tablespace_name,

file_name,

bytes / 1024/ 1024 MB

FROM

dba_data_files;

Here are all the tablespaces within the current database:

The CREATE TABLESPACE is sort of complex with many options, you’ll find more information from the Oracle CREATE TABLESPACE page.

Tablespaces and make TABLE statement

When you create a replacement table, Oracle automatically places the table within the default tablespace of the user which you employ to make the table. However, you’ll explicitly specify the tablespace to which the table belongs as shown within the following query:

CREATE TABLE table_name(

…

)

TABLESPACE tablespace_name;

Note that you simply simply must have privilege on the tablespace that you specify within the CREATE TABLE statement.

Consider the subsequent example.

First, create a replacement table called t1 whose tablespace is tbs1:

CREATE TABLE t1(

id INT GENERATED ALWAYS AS IDENTITY,

c1 VARCHAR2(32)

) TABLESPACE tbs1;

Second, insert 10,000 rows into the t1 table:

BEGIN

FOR counter IN 1..10000 loop

INSERT INTO t1(c1)

VALUES(sys_guid());

END loop;

END;

Third, check the free space of the tbs1 tablespace by querying from the dba_free_space view:

SELECT

tablespace_name,

bytes / 1024 / 1024 MB

FROM

dba_free_space

WHERE

tablespace_name = ‘TBS1’;

Fourth, insert 10,000 rows into the t1 table, Oracle will issue a mistake thanks to insufficient storage within the tablespace:

BEGIN

FOR counter IN 1..10000 loop

INSERT INTO t1(c1)

VALUES(sys_guid());

END loop;

END;

Here is that the error message:

ORA-01653: unable to increase table OT.T1 by 8 in tablespace TBS1

To fix this, you’ll resize the tablespace using the subsequent ALTER DATABASE statement:

ALTER DATABASE

DATAFILE ‘tbs1.dbf’

RESIZE 10m;

If you insert 10,00 rows into the t1 table again, it should work.

The second thanks to avoid this issue, when creating a replacement tablespace, you’ll use the AUTOEXTEND ON clause as follows:

CREATE TABLESPACE tbs1

DATAFILE ‘tbs1.dbf’

SIZE 1m

AUTOEXTEND 20m;

Oracle DROP TABLESPACE

Introduction to Oracle DROP TABLESPACE statement

The DROP TABLESPACE allows you to get rid of a tablespace from the database. Here is that the basic syntax of the DROP TABLESPACE statement:

DROP TABLESPACE tablespace_name

[INCLUDING CONTENTS [AND | KEEP] DATAFILES]

[CASCADE CONSTRAINTS];

In this syntax:

First, specify the name of the tablespace that you simply want to drop after the DROP TABLESPACE keywords.
Second, use the INCLUDE CONTENTS to delete all contents of the tablespace. If the tablespace has any objects, you want to use this feature to get rid of the tablespace. Any plan to remove a tablespace that has objects without specifying the INCLUDING CONTENTS option will end in a mistake .
Third, use AND DATAFILES choice to instruct Oracle to delete the datafiles of the tablespace and KEEP DATAFILES choice to leave the datafiles untouched.
Fourth, if the tablespace that has objects like tables whose primary keys are referenced by referential integrity constraints from tables outside the tablespace, you want to use the CASCADE CONSTRAINTS choice to drop these constraints. If you omit the CASCACDE CONSTRAINTS clause in such situations, Oracle returns a mistake and doesn’t remove the tablespace.

You can use the DROP TABLESPACE to get rid of a tablespace no matter whether it’s online and offline. However, it’s good practice to require the tablespace offline before removing it to make sure that no sessions are currently accessing any objects within the tablespace.

Note that you simply cannot drop the SYSTEM tablespace and only can drop the SYSAUX tablespace once you started the database within the MIGRATE mode.

You need to possess the DROP TABLESPACE system privilege to execute the DROP TABLESPACE statement. To drop the SYSAUX tablespace, you would like to possess the SYSDBA system privilege.

Oracle DROP TABLESPACE statement examples

Let’s take some samples of using the DROP TABLESPACE statement.

1) Using Oracle DROP TABLESPACE to get rid of an empty tablespace example

First, create a replacement tablespace named tbs1:

CREATE TABLESPACE tbs1

DATAFILE ‘tbs1_data.dbf’

SIZE 10m;

Second, use the DROP TABLESPACE to get rid of the tbs1 tablespace:

DROP TABLESPACE tbs1;

2) Using Oracle DROP TABLESPACE to get rid of a non-empty tablespace example

First, create a replacement tablespace named tbs2:

CREATE TABLESPACE tbs2

DATAFILE ‘tbs2_data.dbf’

SIZE 5m;

Second, create a replacement table t2 within the tablespace tbs2:

CREATE TABLE t2 (

c1 INT

) TABLESPACE tbs2;

Third, use the DROP TABLESPACE statement to drop the tbs2 tablespace:

DROP TABLESPACE tbs2;

Oracle issued the subsequent error:

ORA-01549: tablespace not empty, use `INCLUDING CONTENTS` option

To drop the tbs2 tablespace, we’d like to use the INCLUDING CONTENTS option:

DROP TABLESPACE tbs2

INCLUDING CONTENTS;

Oracle issued the subsequent message indicating that the tablespace has been dropped:

Tablespace dropped.

3) Using Oracle DROP TABLESPACE to get rid of a tablespace whose tables are referenced by referential constraints

First, create two tablespaces named tbs3 and tbs4:

CREATE TABLESPACE tbs3

DATAFILE ‘tbs3_data.dbf’

SIZE 5m;

CREATE TABLESPACE tbs4

DATAFILE ‘tbs4_data.dbf’

SIZE 5m;

Next, create a replacement table within the tbs3 tablespace:

CREATE TABLE t3(

c1 INT PRIMARY KEY

) TABLESPACE tbs3;

Then, create a replacement table within the tbs4 tablespace:

CREATE TABLE t4(

c1 INT PRIMARY KEY,

c2 INT NOT NULL,

FOREIGN KEY(c2) REFERENCES t3(c1)

) TABLESPACE tbs4;

After that, drop the tablespace tbs3:

DROP TABLESPACE tbs3

INCLUDING CONTENTS;

Oracle issued the subsequent error:

ORA-02449: unique/primary keys in table referenced by foreign keys

Finally, use the DROP TABLESPACE that has the CASCADE CONSTRAINTS choice to drop the tablespace:

DROP TABLESPACE tbs3

INCLUDING CONTENTS AND DATAFILES

CASCADE CONSTRAINTS;

It worked needless to say.

So, this brings us to the end of blog. This Tecklearn ‘How to Manage Tablespaces in Oracle’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

598