Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: TABLESPACE: List tablespaces, files, allocated, and free space Jump to:  
Category: >> Oracle PL/SQL >> TABLESPACE: List tablespaces, files, allocated, and free space Bookmark and Share

<< lastnext >>

Snippet Name: TABLESPACE: List tablespaces, files, allocated, and free space

Description: Listing tablespace data, such as associated files, the tablespace's allocated space, free space, and the next free extent.

Also see:
» TABLESPACE: Show contiguous space
» TABLESPACE: SYSAUX tablespace
» TABLESPACE: Tablespace management
» TABLESPACE: Dropping Tablespaces
» TABLESPACE: Alter Permanent Tablespace
» TABLESPACE: Transportable tablespaces
» TABLESPACE: Tempfile operations
» TABLESPACE: Create temp tablespace
» TABLESPACE: Change UNDO tablespace
» TABLESPACE: Undo Tablespace
» TABLESPACE: SYSAUX Tablespace
» TABLESPACE: Set default tablespace type
» TABLESPACE: Oracle Managed Auto-extend...
» TABLESPACE: Permanent Tablespace Using...
» TABLESPACE: Using Raw Devices
» TABLESPACE: permanent tablespace

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 12th, 2009

-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
 
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
 
 
 
-- list datafiles, tablespace names, and size in MB:
 
col file_name format a50
col tablespace_name format a10
 
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
 
 
 
-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
 
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
 
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 41 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?