Mastering Oracle query tablespace: A Comprehensive Guide for DBAs

Introduction

In this guide, you’ll learn how to efficiently query Oracle tablespaces using SQL commands. This guide covers essential techniques for monitoring tablespace size, available space, and differentiating between various types of tablespaces. This practical approach aims to enhance database management skills for Oracle administrators.

1. What is Oracle SQL Tablespace?
   An overview of tablespaces in Oracle databases, explaining their purpose and types.
2. How to Query Oracle Tablespace?
   Basic SQL queries to retrieve information about tablespaces, including size and contents.
3. Check Tablespace Usage Percentage in Oracle
   Methods to calculate the usage percentage of tablespaces to monitor efficiency and plan for scaling.
4. Oracle Query Tablespace Usage
   Advanced querying techniques to analyze tablespaces’ performance and optimize storage management.

Step-by-Step: Guide to Oracle Query Tablespace

How does a query check tablespace size, free space, and Big file vs small file SYSTEM tablespace? The following statement is below

SELECT tablespace_name,
  SUM(bytes)/1024/1024 AS mb
FROM
  ( SELECT tablespace_name, bytes FROM dba_data_files
  UNION ALL
  SELECT tablespace_name,bytes FROM dba_temp_files
  )
WHERE tablespace_name='SYSTEM'
GROUP BY tablespace_name;

Sample Output

TABLESPACE_NAME                    MB
-------------------- ----------------
SYSTEM                          2,048

To query tablespace “SIZE, AUTO EXTEND, BIG_SMALL_FILE, BLOCK_SIZE” in Oracle.

SELECT dt.tablespace_name tablespace_name,
  SUBSTR(ddf.file_name,0, instr(ddf.file_name, '/', -1, 1) - 1) AS placement_directory,
  SUBSTR(ddf.file_name, instr(ddf.file_name, '/',   -1, 1) + 1) AS file_name,
  ddf.bytes                                         /1024/1024  AS mb,
  ddf.autoextensible,
  DECODE (dt.bigfile,'NO','SMALL','YES','BIG') AS BIG_SMALL_FILE,
  dt.block_size block_size
FROM
  (SELECT tablespace_name, file_name, bytes,autoextensible FROM dba_data_files
  UNION
  SELECT tablespace_name, file_name, bytes,autoextensible FROM dba_temp_files
  ) ddf,
  (SELECT tablespace_name, block_size,bigfile FROM dba_tablespaces
  ) dt
WHERE dt.tablespace_name = ddf.tablespace_name
AND dt.tablespace_name   = 'SYSTEM';

Sample Output

TABLESPACE_NAME
--------------------
PLACEMENT_DIRECTORY
------------------------------------------------------------------------------------------------------------------------------------
FILE_NAME                                                                            MB AUTOEXTEN BIG_SMALL_FILE  BLOCK_SIZE
---------------------------------------------------------------------- ---------------- --------- --------------- ----------
SYSTEM
/mnt_nfs/dbdata/mydata
system02.dbf                                                                      2,048 NO        SMALL                 9192

Conclusion

This article provides a comprehensive guide to Oracle query tablespaces for size, auto-extension, file types, and block sizes. By mastering these queries, you can effectively monitor and optimize your Oracle database storage. Continuous monitoring and analysis will ensure that your database runs efficiently and remains scalable. I hope will this your helpful. Thank you for reading the DevopsRoles page!