Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle tablespace script

Oracle Tips by Burleson Consulting

 

This next Oracle tablespace query can be very helpful when you are trying to understand what tablespaces you have in your database, and what datafiles are associated with those tablespaces. In this query we use DBA_DATA_FILES to extract each tablespace name, each datafile is listed with the tablespace, and the size of the datafile is also listed.

We have also joined in DBA_FREE_SPACE to determine how much free space we have left in our tablespace. This gives us some idea about future growth. Note that we have also included some SQL*Plus commands to make the report look much nicer.

SQL>BREAK ON tablespace_name SKIP 2
SQL>COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name
SQL>COLUMN allocated_bytes FORMAT 9,999,999,999
SQL>COLUMN free_bytes FORMAT 9,999,999,999
 
SQL>SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
2 b.free_bytes
3 FROM dba_data_files a,
4 (SELECT file_id, SUM(bytes) free_bytes
5 FROM dba_free_space b GROUP BY file_id) b
6 WHERE a.file_id=b.file_id
7 ORDER BY a.tablespace_name;
 
 
TABLESPACE_NAME      FILE_NAME                 ALLOCATED_BYTES     FREE_BYTES
-------------------- ------------------------- --------------- --------------
TBS_LOCALS           /u01/app/oradata/devdb/de      20,971,520     20,774,912
                     vdb/devdb_tbs_locals_01.d
                     bf
 
********************                           --------------- --------------
sum                                                 20,971,520     20,774,912
 
 
TBS_TOURISTS         /u01/app/oradata/devdb/de      20,971,520      8,257,536
                     vdb/devdb_tbs_tourists_01
                     .dbf
 
********************                           --------------- --------------
sum                                                199,229,440    176,291,840
 
 
USERS                /u01/app/oradata/devdb/de     112,721,920        655,360
                     vdb/users01.dbf
 
********************                           --------------- --------------
sum                                                112,721,920        655,360

 

Queries like this make great tablespace monitoring queries, though I?d probably further restrict the rows that are returned to those where the tablespace is has only five or ten percent free space available. The point that we want you to get here is that there are a number of different ways of looking at this tablespace data and querying the data dictionary, but you have to dig into the data dictionary and learn it before you will be able to find such queries.  Here is another example script to show space usage within tablespaces:

set lines 132
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)"      format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)"             format 9,999,990.00
column "FREE (MB)"             format 9,999,990.00
column "% USED"                format 990.00

select
   a.tablespace_name,
   a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
   a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
   nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
   (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
   (select
      tablespace_name,
      sum(bytes) physical_bytes,
      sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
    from
      dba_data_files
    group by
      tablespace_name ) a,
   (select
      tablespace_name,
      sum(bytes) tot_used
    from
      dba_segments
    group by
      tablespace_name ) b
where
   a.tablespace_name = b.tablespace_name (+)
and
   a.tablespace_name not in
   (select distinct
       tablespace_name
    from
       dba_temp_files)
and
   a.tablespace_name not like 'UNDO%'
order by 1
;

For a complete set of Oracle tablespace scripts, try the Oracle Script collection.  It's over 600 ready-to-run Oracle dictionary scripts.

 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.