Re: Understanding sqlplus syntax

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 31 Dec 2003 12:45:24 -0800
Message-ID: <1072903436.404221@yasure>

Hans Forbrich wrote:

> Jon wrote:
> 

>>Hi,
>>
>>I'm attempting to come up with a SQL script that calculates disk space
>>usage with columns for Total, Free, Used, %Free & %Used. I think I've
>>found several scripts that I can piece together into one script that
>>will do what I'm looking for. However, being quite new to SQL
>>scripts, I need to understand several areas of these scripts. Here is
>>a sample of one of the scripts:
>>
>>select df.tablespace_name tablespace,
>> df.bytes/(1024*1024) tot_ts_size,
>> sum(fs.bytes)/(1024*1024) free_ts_size,
>> df.bytes/(1024*1024)-(fs.bytes)/(1024*1024) used_ts_size,
>> round(sum(fs.bytes)*100/df.bytes) ts_pct,
>> round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
>>from dba_free_space fs, (select tablespace_name, sum(bytes) bytes
>> from dba_data_files
>> group by tablespace_name ) df
>>where fs.tablespace_name = df.tablespace_name
>>group by df.tablespace_name, df.bytes
>>
>>I know that "bytes" and "tablespace_name" are columns in the
>>"dba_free_space" and "dba_data_files" System tables, but I don't know
>>what the "df." and "fs." in the "df.tablespace_name", "df.bytes" &
>>"fs.bytes" columns mean, along with the "df" and "fs" in the FROM
>>clause. Any ideas?
>>
>>Thanks,

> 
> 
> Look up the term Table ALIAS in the docco/text.  (If you look at the SQL
> Reference manual at http://docs.oracle.com, specifically search for
> t_alias.) Then notice the section where you have 
> 
>      from dba_free_space fs, (se...
> 
> HTH
> /Hans
> 
> By the way, "dba_free_space" and "dba_data_files" are views, not tables.

In addition: When looking for column definitions look in $ORACLE_HOME/rdbms/admin at the following:

sql.bsq
catalog.sql

and you will a lot of source material.

Just make sure you don't change anything.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Dec 31 2003 - 14:45:24 CST