File Usage (tablespace)( (merged) [message #498108] |
Tue, 08 March 2011 16:31 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/38eabd95413497e08eb3dd229cf6e1cc?s=64&d=mm&r=g) |
pchandna1
Messages: 24 Registered: March 2011 Location: USA
|
Junior Member |
![parikshit_chandna%40yahoo.co.uk](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi Members,
requirment is to track file usage for disk planning. Issue is below query returns duplicate records.
SELECT gfu.INST_ID,dbf.FILE_ID ,dbf.file_name, dbf.tablespace_name,
gfu.ALLOCATED_SPACE "Total Allocated Space",
gfu.FILE_SIZE "Current File Size",gfu.FILE_MAXSIZE "Max File Size"
FROM gv$filespace_usage gfu, dba_data_files dbf
WHERE gfu.RFNO=dbf.FILE_ID
ORDER BY 1,2
Thanks
Parikshit
|
|
|
|
File Usage (tablespace) [message #498111 is a reply to message #498108] |
Tue, 08 March 2011 16:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/38eabd95413497e08eb3dd229cf6e1cc?s=64&d=mm&r=g) |
pchandna1
Messages: 24 Registered: March 2011 Location: USA
|
Junior Member |
![parikshit_chandna%40yahoo.co.uk](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi Members,
requirment is to track file usage for disk planning. Issue is below query returns duplicate records.
SELECT gfu.INST_ID,dbf.FILE_ID ,dbf.file_name, dbf.tablespace_name,
gfu.ALLOCATED_SPACE "Total Allocated Space",
gfu.FILE_SIZE "Current File Size",gfu.FILE_MAXSIZE "Max File Size"
FROM gv$filespace_usage gfu, dba_data_files dbf
WHERE gfu.RFNO=dbf.FILE_ID
ORDER BY 1,2
Thanks
Parikshit
[Updated on: Tue, 08 March 2011 16:57] Report message to a moderator
|
|
|
|
|
|
|
|
Re: File Usage (tablespace) [message #498248 is a reply to message #498146] |
Wed, 09 March 2011 05:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You can't use v$filespace_usage in this way. Firstly, because it includes tempfiles. Secondly, because rfno is not a unique identifier.
I think your query is probably joining your tempfiles to your system tablespace datafiles.
|
|
|
|
|
|
Re: File Usage (tablespace) [message #498316 is a reply to message #498312] |
Wed, 09 March 2011 10:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/38eabd95413497e08eb3dd229cf6e1cc?s=64&d=mm&r=g) |
pchandna1
Messages: 24 Registered: March 2011 Location: USA
|
Junior Member |
![parikshit_chandna%40yahoo.co.uk](/forum/theme/orafaq/images/yahoo.png)
|
|
reason for including inst_id was; we have RAC environment. But that not an issue, I can run on each individual node.
The below changed as suggsted but still show duplicate values for (file_id, file_name, tablspace_name). snapshot attached
SELECT DISTINCT dbf.FILE_ID ,dbf.file_name, dbf.tablespace_name,
gfu.ALLOCATED_SPACE "Total Allocated Space",
gfu.FILE_SIZE "Current File Size",gfu.FILE_MAXSIZE "Max File Size"
FROM v$filespace_usage gfu, dba_data_files dbf, dba_tablespaces dt
WHERE gfu.RFNO=dbf.FILE_ID
AND dbf.tablespace_name=dt.tablespace_name
ORDER BY 1,2
|
|
|
|
Re: File Usage (tablespace) [message #498324 is a reply to message #498318] |
Wed, 09 March 2011 11:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/38eabd95413497e08eb3dd229cf6e1cc?s=64&d=mm&r=g) |
pchandna1
Messages: 24 Registered: March 2011 Location: USA
|
Junior Member |
![parikshit_chandna%40yahoo.co.uk](/forum/theme/orafaq/images/yahoo.png)
|
|
Thanks for your efforts. Finally I got the what I was looking for.
joining GV$DATAFILE helped to filter out duplicate i.e.(tempfiles which are not listed in this view)
SELECT DISTINCT gvd.inst_id,gvd.FILE#,gvd.name,dbf.tablespace_name,
gfu.ALLOCATED_SPACE "Total Allocated Space",
gfu.FILE_SIZE "Current File Size",gfu.FILE_MAXSIZE "Max File Size"
FROM gv$datafile gvd, dba_data_files dbf, gv$filespace_usage gfu
WHERE gvd.FILE#=dbf.file_id
AND gfu.RFNO=dbf.file_id
AND gfu.TABLESPACE_ID= gvd.TS#
ORDER BY 1,2
|
|
|