A tablespace group typically consists of multiple temporary tablespaces. A tablespace group allows a user to consume temporary space from multiple temporary tablespaces, rather than one temporary tablespace. By employing a tablespace group, you’ll avoid the matter caused when one temporary tablespace doesn’t have enough space to carry the results of a kind.

The following describes the property of a tablespace group:

A tablespace group must contain a minimum of one temporary tablespace.
The name of a tablespace group can’t be an equivalent as any tablespace.
A tablespace group are often assigned as a default temporary tablespace for the database or a short lived tablespace for a user.

Creating a tablespace group

Oracle doesn’t provide a press release to make a tablespace group explicitly. You create a tablespace group once you assign the primary temporary tablespace to the group using the CREATE TEMPORARY TABLESPACE statement:

CREATE TEMPORARY TABLESPACE tablespace_name

TEMPFILE ‘path_to_file’

SIZE 50M

TABLESPACE GROUP group_name;

Or ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name

TABLESPACE GROUP group_name;

Removing a tablespace group

Oracle automatically drops a tablespace group once you remove the last temporary tablespace from the tablespace group.

Viewing tablespace groups

The view DBA_TABLESPACE_GROUPS lists all tablespace groups and their member temporary tablespace.

SELECT * FROM DBA_TABLESPACE_GROUPS;

Moving a tablespace to a different tablespace group

To move a short lived tablespace to a different tablespace group you employ the ALTER TABLESPACE statement. This statement moves the temporary tablespace tablespace_name to the tablespace group destination:

ALTER TABLESPACE tablespace_name

TABLESPACE GROUP destination;

Note that the destination tablespace group must exist.

Assigning a tablespace group because the default temporary tablespace

To assign a tablespace group because the default temporary tablespace for a database, you employ the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_group;

All users who haven’t been assigned a short lived tablespace will use the temporary tablespaces contained within the tablespace_group.

Note that you simply cannot drop any temporary tablespace that belongs to a tablespace group which is specified as a default temporary tablespace. during this case, you initially got to de-assign the temporary tablespace from the tablespace group then remove it.

Benefits of employing a tablespace group

A tablespace group brings the subsequent benefits:

Enable multiple default temporary tablespaces to be used at the database level.
Allow the user to use multiple temporary tablespaces in several sessions at an equivalent time.
Reduce the contention just in case you’ve got multiple temporary tablespaces.

Oracle tablespace group examples

First, create a replacement temporary tablespace and assign it to the tablespace group tbs1:

CREATE TEMPORARY TABLESPACE temp2

TEMPFILE ‘temp2.dbf’

SIZE 100M

TABLESPACE GROUP tbsg1;

Because the tablespace group tbsg1 has not existed, the statement also created the tablespace group tbsg1.

Second, assign the temp temporary tablespace temp to the tbsg1 tablespace group:

ALTER TABLESPACE temp TABLESPACE GROUP tbsg1;

Third, assign the tablespace group gbsg1 because the default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbsg1;

Finally, verify the present default temporary tablespace:

SELECT

property_name,

property_value

FROM

database_properties

WHERE

property_name=’DEFAULT_TEMP_TABLESPACE’;

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

422