Extend Tablespace in Oracle

Last updated on Sep 10 2022
Sankalp Joshi

Table of Contents

Extend Tablespace in Oracle

Extending a tablespace by adding a replacement datafile

The first thanks to extend a tablespace is to feature a replacement datafile by using the ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name

 ADD DATAFILE 'path_to_datafile'

 SIZE size;

If you employ the AUTOEXTEND ON clause, Oracle will automatically extend the dimensions of the datafile when needed:

ALTER TABLESPACE tablespace_name

 ADD DATAFILE 'path_to_datafile'

 SIZE size

 AUTOEXTEND ON;

Let’s see the subsequent example.

First, create a replacement tablespace called tbs10 with the dimensions 1MB:

 CREATE TABLESPACE tbs10

 DATAFILE 'tbs10.dbf' SIZE 1m;

Next, create a replacement table t1 whose tablespace is tbs10:

CREATE TABLE t1(id INT PRIMARY KEY)

TABLESPACE tbs10;

Then, insert 1,000,000 rows into the t1 table:

BEGIN

 FOR counter IN 1..1000000 loop

 INSERT INTO t1(id)

 VALUES(counter);

 END loop;

END;

/

Oracle issued the subsequent error:

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

So the tablespace tbs10 doesn’t have enough space for the 1 million rows.

After that, use the ATLER TABLESPACE statement to feature another datafile whose size is 10MB with the AUTOEXTEND ON option:

ALTER TABLESPACE tbs10

 ADD DATAFILE 'tbs10_2.dbf'

 SIZE 10m

 AUTOEXTEND ON;

Finally, insert 1 million rows into the t1 table. It should work now. this question returns the amount of rows from the t1 table:

SELECT count(*) FROM t1;

Here is that the output:




COUNT(*)

----------

1000000

Extending a tablespace by resizing the datafile

Another way to increase a tablespace is to resize the info file by using the the ALTER DATABASE RESIZE DATAFILE statement:

ALTER DATABASE

 DATAFILE 'path_to_datafile'

 RESIZE size;

Consider the subsequent example.

First, create a replacement tablespace called tbs11:

CREATE TABLESPACE tbs11

 DATAFILE 'tbs11.dbf'

 SIZE 1m;

Next, create a replacement table called t2 that uses tbs11 because the tablespace:

CREATE TABLE t2(

 c INT PRIMARY KEY

) TABLESPACE tbs11;

Then, query the dimensions of the tablespace tbs11:

SELECT

 tablespace_name,

 bytes / 1024 / 1024 MB

FROM

 dba_free_space

WHERE

 tablespace_name = 'TBS11';

The following illustrates the output:

 

TABLESPACE_NAME MB

--------------- ----------

TBS11 .9375

After that, use the ALTER DATABASE to increase the dimensions of the datafile of the tablespace to 15MB:

ALTER DATABASE

 DATAFILE 'tbs11.dbf'

 RESIZE 15m;

Finally, query the dimensions of the tbs11 tablespace:

 

SELECT

 tablespace_name,

 bytes / 1024 / 1024 MB

FROM

 dba_free_space

WHERE

 tablespace_name = 'TBS11';

Here is that the output:

TABLESPACE_NAME MB

---------------- ----------

TBS11 14.9375

As you’ll see, the dimensions of the tablespace tbs11 has been extended to 15MB.

Note that Oracle doesn’t allow you to feature a datafile to a bigfile tablespace, therefore, you simply can use ALTER DATABASE DATAFILE RESIZE command.

Oracle Temporary Tablespace

Introduction to Oracle temporary tablespaces

A temporary tablespace, as its name implied, stores the temporary data that only exists during the database session.

Oracle uses temporary tablespaces to enhance the concurrency of multiple sort operations which don’t slot in memory. On top of this, Oracle stores temporary tables, temporary indexes, temporary B-trees, and temporary LOBs in temporary tablespaces.

By defeault, Oracle creates one temporary tablespace named TEMP for every new Oracle Database installation. This TEMP tablespace are often shared by multiple users.

Besides the TEMP default temporary tablespace, you’ll create addition temporary tablespaces and assign them to a user using the CREATE USER or ALTER USER statement.

Oracle default temporary tablespace

When you create a user without specifying a short lived tablespace, Oracle assigns the default temporary tablespace TEMP to user. If you would like to vary the default temporary tablespace, you’ll use the subsequent command:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

To find the present default temporary tablespace, you execute the subsequent statement:

 SELECT

 property_name,

 property_value

FROM

 database_properties

WHERE

 property_name='DEFAULT_TEMP_TABLESPACE';

Here is that the output:
oracle 2

Viewing space allocation during a temporary tablespace

This statement returns the space allocated and free space during a temporary tablespace:

SELECT * FROM dba_temp_free_space;

Creating a short lived tablespace

To create a replacement temporary tablespace, you employ the CREATE TEMPORARY TABLESPACE statement:

CREATE TEMPORARY TABLESPACE tablespace_name

 TEMPFILE 'path_to_file'

 SIZE size;

Oracle default tablespace examples

First, create a replacement temporary tablespace named temp2 with the dimensions of 100MB:

CREATE TEMPORARY TABLESPACE temp2

TEMPFILE ‘temp2.dbf’

SIZE 100m;

Next, find all temporary tablespaces within the current Oracle Database:

SELECT

 tablespace_name,

 file_name,

 bytes/1024/1024 MB,

 status

FROM

 dba_temp_files;
oracle 3

 

Then, check which tablespace is that the default temporary tablespace:

SELECT

 property_name,

 property_value

FROM

 database_properties

WHERE

 property_name='DEFAULT_TEMP_TABLESPACE';
oracle 4

 

After that, change the default temporary tablespace name to temp2:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Finally, drop the temp2 tablespace:

 

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Oracle issued the subsequent error:

SQL Error: ORA-12906: cannot drop default temporary tablespace

You cannot drop the default temporary tablespace. To delete the temp2 tablespace because the default temporary tablespace, you want to first change the default tablespace back to the TEMP tablespace:

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

And then drop the temp2 temporary tablespace:

 

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 

So, this brings us to the end of blog. This Tecklearn ‘Extend Tablespace 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

 

0 responses on "Extend Tablespace in Oracle"

Leave a Message

Your email address will not be published. Required fields are marked *