OraFAQ Forum: Server Administration » Tablespace Size Checking Script

Home » RDBMS Server » Server Administration » Tablespace Size Checking Script

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace Size Checking Script [message #247703]

Tue, 26 June 2007 22:44

abcindiaxyz
Messages: 13
Registered: March 2005
Location: Indonesia

Junior Member

Dear All,
Please find the script to find out the tablespace sizes in the database.
*******************************************************************
SET PAGESIZE 66
COLUMN pct_used FORMAT 999.99 HEADING "% Used"
COLUMN name FORMAT A16 HEADING "Tablespace Name"
COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes"
COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes"
COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes"

BREAK ON REPORT
COMPUTE SUM OF mbytes ON REPORT
COMPUTE SUM OF free ON REPORT
COMPUTE SUM OF used ON REPORT

SELECT
fs.tablespace_name name,
df.totalspace mbytes,
(df.totalspace - fs.freespace) used,
fs.freespace free,
100 * ((df.totalspace - fs.freespace)/ df.totalspace) pct_used
FROM
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM
dba_data_files
GROUP BY
tablespace_name
) df,
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
df.tablespace_name = fs.tablespace_name(+);
*****************************************************************
(Same script attached as attachment)

Suggestions always welcome..........................