Tablespace Monitoring

 
Query to check the Tablespace details and available free space

The below query give output as group by Tablespace name:

select
                a.tablespace_name,
                round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
                round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
                (SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
                round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))),2) FREE_GB,
                round(100*(SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
                dba_data_files a,
                sys.filext$ b,
                (SELECT
                               d.tablespace_name ,sum(nvl(c.bytes,0)) Free
                FROM
                               dba_tablespaces d,
                               DBA_FREE_SPACE c
                WHERE
                               d.tablespace_name = c.tablespace_name(+)
                               group by d.tablespace_name) c
WHERE
                a.file_id = b.file#(+)
                and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY used_pct desc; 

the above data shows the group by TABLESPACE_NAME.

This is a complex SQL query that retrieves information about the tablespaces in an Oracle database. A tablespace is a logical unit of storage that consists of one or more data files. The query calculates the current, maximum, used, and free space in gigabytes (GB) and the percentage of used space for each tablespace. It also sorts the results by the used percentage in descending order. Here is a brief explanation of each part of the query:

• The select clause specifies the columns to be displayed in the result table. Each column is derived from an expression that involves the round, sum, and decode functions. The round function rounds a number to a specified number of decimal places. The sum function calculates the total of a set of values. The decode function compares an expression to a list of values and returns a corresponding result. For example, decode(b.maxextend, null, A.BYTES/(1024*1024*1024), b.maxextend*8192/(1024*1024*1024)) means if b.maxextend is null, then return A.BYTES/(1024*1024*1024), otherwise return b.maxextend*8192/(1024*1024*1024).
• The from clause specifies the tables to be queried. The query joins three tables: dba_data_files, sys.filext$, and a subquery. The dba_data_files table contains information about the data files that belong to each tablespace. The sys.filext$ table contains information about the extent allocation of each data file. An extent is a contiguous set of data blocks allocated to a segment within a tablespace. The subquery calculates the free space in each tablespace by summing the bytes in the dba_free_space table, which contains information about the free extents in each tablespace. The dba_tablespaces table contains information about the tablespaces in the database.
• The where clause specifies the conditions for filtering the rows from the tables. The query uses the = operator to join the tables on the matching columns. For example, a.file_id = b.file#(+) means join the dba_data_files table (aliased as a) with the sys.filext$ table (aliased as b) on the file_id and file# columns. The (+) indicates an outer join, which means include the rows from a even if there is no matching row in b.
• The group by clause specifies the columns to be used for grouping the rows. The query groups the rows by the tablespace_name and c.Free/1024 columns. The c.Free/1024 column is derived from the subquery and represents the free space in megabytes (MB) for each tablespace.
• The order by clause specifies the column to be used for sorting the result table. The query sorts the table by the used_pct column in descending order. The used_pct column is derived from an expression that calculates the percentage of used space for each tablespace.

In case we need to view filename-wise, i/e separately as each tablespace would have multiple files, that information can be viewed with the below query:

The below query give output as separate file wise detail:

col file_name for a70;

col tablespace_name for a50;

set linesize 1000

set pagesize 1000

SELECT

    df.file_name,

    df.tablespace_name,

    df.status,

    df.bytes / 1024 / 1024 AS size_mb,

    (df.bytes - fs.bytes) / 1024 / 1024 AS used_mb,

    ROUND((df.bytes - fs.bytes) / df.bytes * 100, 2) AS used_pct,

    df.autoextensible

FROM

    dba_data_files df,

    (SELECT

        file_id,

        SUM(bytes) AS bytes

    FROM

        dba_free_space

    GROUP BY

        file_id) fs

WHERE

    df.file_id = fs.file_id(+)

ORDER BY

    df.tablespace_name;

To Check the parameter value used while creating the exiting table space file:

SELECT

    df.file_name AS Name,

    df.tablespace_name AS Tablespace,

    df.status AS Status,

    df.bytes / 1024 / 1024 AS "File Size (MB)",

    df.autoextensible AS "Auto Extend",

    df.increment_by / 1024 / 1024 AS "Increment (MB)",

    df.maxbytes / 1024 / 1024 AS "Maximum File Size (MB)"

FROM

    dba_data_files df

WHERE

    df.file_name LIKE '%filename%'

ORDER BY

    df.tablespace_name;

UI value and Alter query 

To check the tablespace through SQL Developer

SQL Developer -> View -> DBA -> Connection->Select database -> Storage-> expand and double click datafile header

There will be multiple files in one tablespace, arrange it in one order and look for the used space /proportion

based on the requirement add tablespace to it, through sql developer or ALTER query in sql prompt

Example:

We can view the existing file configuration as well through sql developer, to view what parameter were added in existing file, in a particular tablespace and accordingly create the alter tablespace query.

To view the existing tablespace file 

• Click on the file name on the datafile after going through 
• SQL Developer -> View -> DBA -> Connection->Select database -> Storage-> expand and double click datafile header

• There would be two tab General and SQL
• General will give overall info of the file and its size and we can create it accordingly or 
• we can make use of the SQL tab where the query pre exist and copy and make the changes as per the need like change the filename etc.
• Run the Query in sql prompt to create new tablespace file in that particular tablespace

 

alter database datafile '/u01/efsp01data/APPS_TS_TOOLS01.dbf' autoextend on maxsize unlimited; 

or

ALTER TABLESPACE DW_SYS_DATA ADD DATAFILE '/oracledata/DMPG/dw_sys_data_003.dbf' SIZE 33523200K AUTOEXTEND ON NEXT 8K MAXSIZE 33554416K;