Home » RDBMS Server » Server Administration » Tablespace Size Checking Script
icon7.gif  Tablespace Size Checking Script [message #247703] Tue, 26 June 2007 22:44 Go to next message
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..........................

[Updated on: Tue, 26 June 2007 22:46]

Report message to a moderator

Re: Tablespace Size Checking Script [message #248033 is a reply to message #247703] Wed, 27 June 2007 17:36 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Embarassed
Previous Topic: oracle 10g migration
Next Topic: STATPACK REPORT
Goto Forum:
  


Current Time: Sun Dec 11 05:52:59 CST 2016

Total time taken to generate the page: 0.12576 seconds