Managing tablespaces is a critical aspect of database administration, particularly in Oracle 19c, where creating, altering, and dropping tablespaces are essential tasks for optimizing database performance and storage. This guide will focus on the various techniques and best practices for managing tablespaces, ensuring your database operates smoothly and efficiently.
This guide will show you how to manage tablespaces. Be aware that if your database is using OMF (db_create_file_dest) to manage file naming, you don’t need to specify the file name manually in the commands.
What is a Tablespace?
A tablespace is a logical storage unit within an Oracle database, which allows you to group related logical structures together, such as tables and indexes. These logical structures are stored in physical data files, and the management of these files is crucial to maintaining the integrity and performance of the database.
Importance of Managing Tablespace
Proper management of tablespaces is vital for several reasons, including enhancing database performance by reducing I/O contention, organizing data logically for easier management, and providing better control over database operations, such as taking tablespaces offline, enabling read-only mode, or compressing tablespace data.
Creating Tablespaces in Oracle 19c
Creating a tablespace in Oracle 19c involves several steps, each crucial to ensuring the tablespace is set up correctly and functions as intended.
Basic Command for Creating a Tablespace
To create a basic tablespace, use the following command:
CREATE TABLESPACE example_tablespace DATAFILE '/path/to/datafile.dbf' SIZE 100M;
In this command, example_tablespace is the name of the tablespace, and the data file is specified with its path and size.
Advanced Options for Creating Tablespaces
Oracle 19c offers several advanced options when creating tablespaces. Locally Managed Tablespaces use bitmaps to manage extents, which enhances performance and simplifies management. To create a locally managed tablespace, use the following command:
CREATE TABLESPACE lmtbsb DATAFILE '/path/to/lmtbsb01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Bigfile Tablespaces contain a single, large data file, simplifying management and increasing storage capacity. The command for creating a bigfile tablespace is:
CREATE BIGFILE TABLESPACE bigtbs DATAFILE '/path/to/bigtbs01.dbf' SIZE 50G;
For security, you can create encrypted tablespaces to protect sensitive data with the following command:
CREATE TABLESPACE secure_tablespace DATAFILE '/path/to/secure01.dbf' SIZE 100M ENCRYPTION USING 'AES256' ENCRYPT;
Managing Tablespace – Altering TBS in Oracle 19c
After creating a tablespace, you might need to alter it to adjust to changing database requirements. Oracle 19c provides several options for altering tablespaces.
Modifying Tablespace Size
You can increase the size of a tablespace by resizing its data file or adding new data files. For example:
ALTER TABLESPACE example_tablespace ADD DATAFILE '/path/to/new_datafile.dbf' SIZE 50M;
Managing Tablespace – Enabling Read-Only Mode
If you need to prevent any updates to a tablespace, you can set it to read-only mode:
ALTER TABLESPACE example_tablespace READ ONLY;
This is useful for archival purposes or when you want to ensure the integrity of historical data.
Managing Tablespace – Renaming a Tablespace
If necessary, you can rename a tablespace using the following command:
ALTER TABLESPACE example_tablespace RENAME TO new_name;
This command updates all references to the tablespace name in the data dictionary, control file, and data file headers.
Dropping Tablespaces in Oracle 19c
When a tablespace is no longer needed, you can drop it from the database. However, dropping a tablespace is irreversible, so it should be done with caution.
Dropping a Tablespace
The basic command to drop a tablespace, including its contents, is:
DROP TABLESPACE example_tablespace INCLUDING CONTENTS AND DATAFILES;
This command removes the tablespace and its associated data files from the database, freeing up space.
Managing Storage with Tablespace Administration
In addition to basic tablespace operations, Oracle 19c provides several tools and features for advanced tablespace management.
Monitoring Tablespaces
Oracle provides several views, such as DBA_TABLESPACES and DBA_DATA_FILES, to monitor tablespace usage, file status, and storage parameters. These views help ensure that tablespaces are used efficiently and are not running out of space.
Utilizing the SYSAUX Tablespace
The SYSAUX tablespace serves as an auxiliary to the SYSTEM tablespace, housing various Oracle database components. Proper management of SYSAUX is essential to ensure database components function correctly.
Temporary Tablespaces
Temporary tablespaces are used for operations that require transient storage, such as sorting and temporary table operations. Managing these tablespaces effectively can improve performance during large-scale data operations.
Best Practices for Managing Tablespace
To ensure optimal database performance and storage efficiency, follow these best practices:
Regularly monitor tablespace usage and perform routine maintenance to avoid storage issues. Distribute data across multiple tablespaces to reduce I/O contention and improve manageability. Use tablespace compression to save space and improve I/O performance, especially for read-intensive operations. Enable auto-extension for tablespaces likely to grow, ensuring they do not run out of space unexpectedly.
Viewing Tablespace in Oracle 11g and Above
set line 200
set pages 300
column tablespace format A26
column KPERC format 99
Column Tipo format A4
SELECT * FROM (
select t.tablespace_name "Tablespace",
substr(t.contents, 1, 1) "Tipo",
trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) "Used(MB)",
trunc(d.tbs_size/1024/1024) "ActualSize(MB)",
trunc(nvl(s.free_space, 0)/1024/1024) "FreeSpace(MB)",
trunc(d.tbs_maxsize/1024/1024) "MaxSize(MB)",
decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) "PERC(%)"
from
(select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
tablespace_name tablespace
from (select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files)
group by tablespace_name
union
select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
tablespace_name tablespace
from (select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_temp_files)
group by tablespace_name) d,
(SELECT free_space, tablespace_name tablespace
FROM dba_temp_free_space
UNION
SELECT SUM (bytes) free_space, tablespace_name tablespace
FROM dba_free_space
GROUP BY tablespace_name) s,
dba_tablespaces t
where t.tablespace_name = d.tablespace(+)
and t.tablespace_name = s.tablespace(+)
order by "PERC(%)"
);
Viewing Tablespace in Oracle 10g
set line 2000
column tablespace format A17
column KPERC format 99
set page 50
SELECT * FROM (
select t.tablespace_name "Tablespace",
substr(t.contents, 1, 1) "Tipo",
trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) "Used(MB)",
trunc(d.tbs_size/1024/1024) "ActualSize(MB)",
trunc(nvl(s.free_space, 0)/1024/1024) "FreeSpace(MB)",
trunc(d.tbs_maxsize/1024/1024) "MaxSize(MB)",
decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) "PERC(%)"
from
(select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
tablespace_name tablespace
from (select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files)
group by tablespace_name
union
select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
tablespace_name tablespace
from (select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_temp_files)
group by tablespace_name) d,
(SELECT SUM(bytes) free_space, tablespace_name tablespace
FROM dba_free_space
GROUP BY tablespace_name) s,
dba_tablespaces t
where t.tablespace_name = d.tablespace(+)
and t.tablespace_name = s.tablespace(+)
order by "PERC(%)"
);
Monitoring ASM Diskgroup Space
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT name group_name,
sector_size sector_size,
block_size block_size,
allocation_unit_size allocation_unit_size,
state state,
type type,
total_mb total_mb,
(total_mb - free_mb) used_mb,
free_mb free_mb,
ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Conclusion
Effective management of tablespaces in Oracle 19c is a cornerstone of database administration. By understanding how to create, alter, and drop tablespaces, and by following best practices, you can ensure your database remains robust, efficient, and scalable.
See more on Oracle’s website!
Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!
