Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

script to monitoring tablespace usage

SOLVED
Go to solution
steven chang_1
Regular Advisor

script to monitoring tablespace usage

hello:

I write a sql statement to report tablespace usage, but oracle report a error message
ORA-00904: invalid column name
Instead I create temporary table, the same situation occurs. I don't know why it is.
Would somebody gives me any instruction.
Thank you very much

steven chang
steven
5 REPLIES
Sanjay Kumar Suri
Honored Contributor
Solution

Re: script to monitoring tablespace usage

Dear Steven

Can you give the details of the script?

Is the column name being listed part of table/view definition?

Check the description with SQL

desc table_name;

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Thierry Poels_1
Honored Contributor

Re: script to monitoring tablespace usage

hi,

(the script is already attached ;)

just add quotes and it will work:

select a.tablespace_name,a."Total",b."Used",b."Used"/a."Total" "%"

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Christian Gebhardt
Honored Contributor

Re: script to monitoring tablespace usage

Hi

or remove the quotes:

select a.tablespace_name,a.total,b.used,b.used/a.total "%"
from (select tablespace_name, sum(bytes) total
from dba_data_files
group by tablespace_name) a ,
(select tablespace_name, sum(bytes) used
from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
/

If you are using Quotes, the name of the columns are case sensitive.

Chris
steven chang_1
Regular Advisor

Re: script to monitoring tablespace usage

thank you all:

I would consider to create temporary table to store the data from dba_data_files,dba_free_space. And now there is no need to to such things.Thank you !!!

steven chang
steven
Yogeeraj_1
Honored Contributor

Re: script to monitoring tablespace usage

hi,

We use the attached SQL script to list freespace by tablespace.

Contains the following columns:
Tablespace Name
KBytes
Used
%Free
%Used
Largest
Max Poss Kbytes
...
We can order accoding the the above mentioned columns by passing the coulmn number as argument:

e.g. @free 1 ====> sorts by tablespace name first

hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)