Operating System - HP-UX
1748204 Members
3240 Online
108759 Solutions
New Discussion юеВ

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 5
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)