Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

Using in-line view to get tablespace usage

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query. The best example of the in-line view is the common Oracle DBA script that is used to show the amount of free space and used space within all Oracle tablespaces. Let’s take a close look at this SQL to see how it works. Carefully note that the FROM clause in this SQL query specifies two sub-queries that perform summations and grouping from two views, dba_data_files, and dba_free_space.

In ANSI standard SQL, it is quite difficult to compare two result sets that are summed together in a single query, and this is a common problem with Oracle SQL where specific values must be compared to a summary.  Without the use of an in-line view, several separate SQL queries would need to be written, one to compute the sums from each view and another to compare the intermediate result sets.

This is a great report for display the actual amount of free space within an Oracle tablespace.

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
colimn "Total MB"   format 99,999,999

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
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;

This SQL quickly compares the sum of the total space within each tablespace to the sum of the free space within each tablespace. Here is a sample of the output:

SQL> @tsfree

Tablespace        Used MB     Free MB    Total MB  Pct. Free
------------- ----------- ----------- ----------- ----------
RANNOD                  6          44          50         88
RANNOX                  5          45          50         90
RBOOKX                  5           0           5          0
SGROUPD                 2           8          10         80
SGROUPX                 2           8          10         80
BRBS                   68          32         100         32
RDRUSERD                2          18          20         90

Basically, this query needs to compare the sum of total space within each tablespace with the sum of the free space within each tablespace.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.