Useful Oracle SQL Queries You Should Know

Hello! In this article, I’ll explain four Oracle SQL queries that can be very useful tools for Oracle database administrators who want to monitor and optimize the performance of their databases, manage disk space usage, and perform other administrative tasks.

Get schema indexes

Indexes are a crucial feature of relational databases that allow faster data retrieval by accessing specific rows in a table.

This query can be helpful for database administrators who want to analyze and optimize the performance of their database by identifying which tables have indexes and what columns are being indexed.

SELECT t1.index_name, 
       t1.index_type, 
       t1.uniqueness, 
       t1.table_name, 
       t2.column_name, 
       t3.column_expression
FROM user_indexes t1
     INNER JOIN user_ind_columns t2 ON (t2.index_name = t1.index_name)
     LEFT OUTER JOIN user_ind_expressions t3 ON (t3.index_name = t1.index_name)
WHERE t1.table_name LIKE 'YOUR_TABLE' --Replace this with the name of your table
ORDER BY t1.index_name ASC;

To run this query, you must have user permissions in that schema.

Get table partitions

Partitioning is another feature of Oracle databases that can help improve performance by allowing large tables to be divided into smaller, more manageable pieces. This query can be helpful for database administrators who want to understand how a particular table is partitioned and how data is distributed across the different partitions.

SELECT *
FROM USER_TAB_PARTITIONS t1
WHERE t1.TABLE_NAME = 'YOUR_TABLE' --Replace this with the name of your table
;

Get tablespace size

In Oracle databases, tablespaces are used to group related data files together, which can simplify administration and improve performance. This query can be helpful for database administrators who want to monitor the space usage of their tablespaces and identify any that are running low on free space.

SELECT t3.TABLESPACE_NAME AS "Tablespace",
       TOTAL_USED_SPACE AS "Used Space (GB)",
       (t3.TOTAL_SPACE - t4.TOTAL_USED_SPACE) AS "Free Space (GB)",
       t3.TOTAL_SPACE AS "Total GB"
FROM (SELECT t1.TABLESPACE_NAME AS TABLESPACE_NAME,
             ROUND(SUM(t1.BYTES) / 1073741824, 2) AS TOTAL_SPACE
      FROM DBA_DATA_FILES t1
      GROUP BY t1.TABLESPACE_NAME) t3,
     (SELECT t2.TABLESPACE_NAME AS TABLESPACE_NAME,
             ROUND(SUM(t2.BYTES) / 1073741824, 2) AS TOTAL_USED_SPACE
      FROM DBA_SEGMENTS t2
      GROUP BY t2.TABLESPACE_NAME) t4
WHERE t3.TABLESPACE_NAME = t4.TABLESPACE_NAME
      AND t3.TABLESPACE_NAME IN ('YOUR_TBS_DATA', 'YOUR_TBS_INDEX');--Change it with the name of your data and index tablespace, or remove it to get the information of all the tablespaces.

Empty recycle bin

The Oracle recycle bin is a feature that allows users to recover dropped tables, indexes, and other objects. However, over time, the recycle bin can accumulate a large amount of data, which can take up valuable disk space. This query can be helpful for database administrators who want to free up disk space by permanently deleting objects from the recycle bin. However, it’s important to note that this action is irreversible, so it should be used with caution.

PURGE RECYCLEBIN;