OraFAQ Forum: Server Administration » File Usage (tablespace)( (merged)

Home » RDBMS Server » Server Administration » File Usage (tablespace)( (merged) (Enterprise Edition Release 11.1.0.7.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

File Usage (tablespace)( (merged) [message #498108]

Tue, 08 March 2011 16:31

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

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

Report message to a moderator

Re: File Usage (tablespace) [message #498109 is a reply to message #498108]

Tue, 08 March 2011 16:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT DISTINCT gfu.INST_ID,dbf.FILE_ID ,dbf.file_name, dbf.tablespace_name,

Report message to a moderator

File Usage (tablespace) [message #498111 is a reply to message #498108]

Tue, 08 March 2011 16:53

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

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 #498112 is a reply to message #498111]

Tue, 08 March 2011 16:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Issue is below query returns duplicate records.
Post proof using COPY & PASTE.

SQL> SELECT DISTINCT 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   2    3    4    5    6  ;

   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    1
/u01/app/oracle/oradata/v112/system01.dbf
SYSTEM						   0		  4864
      4194302


   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    1
/u01/app/oracle/oradata/v112/system01.dbf
SYSTEM						 160		 37760
      4194302


   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    1
/u01/app/oracle/oradata/v112/system01.dbf
SYSTEM					       89760		 90880
      4194302


   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    2
/u01/app/oracle/oradata/v112/sysaux01.dbf
SYSAUX					      142168		149760
      4194302


   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    3
/u01/app/oracle/oradata/v112/undotbs01.dbf
UNDOTBS1					2848		 37760
      4194302


   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    4
/u01/app/oracle/oradata/v112/users01.dbf
USERS					       15928		 16800
      4194302


   INST_ID    FILE_ID
---------- ----------
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME 	       Total Allocated Space Current File Size
------------------------------ --------------------- -----------------
Max File Size
-------------
	 1	    5
/u01/app/oracle/oradata/v112/example01.dbf
EXAMPLE 					9832		 12960
      4194302


7 rows selected.

Report message to a moderator

Re: File Usage (tablespace) [message #498146 is a reply to message #498112]

Tue, 08 March 2011 22:41

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

Everything looks fine in query result except first 4-rows; where file_id, file_name, tablespace_names values are identical.

File attached carrying snapshots.

Thanks

Attachment: proof_pic1.png
(Size: 79.95KB, Downloaded 1140 times)

Report message to a moderator

Re: File Usage (tablespace) [message #498147 is a reply to message #498146]

Tue, 08 March 2011 22:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Everything looks fine in query result except first 4-rows; where file_id, file_name, tablespace_names values are identical.
True, but remaining columns are different; so rows are NOT identical.

Which of the first 4 rows should be in the results set (& why?)?

Report message to a moderator

Re: File Usage (tablespace) [message #498149 is a reply to message #498108]

Tue, 08 March 2011 23:17

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you sure there are duplicate records which means ALL columns are equal?

Regards
Michel

Report message to a moderator

Re: File Usage (tablespace) [message #498154 is a reply to message #498111]

Tue, 08 March 2011 23:26

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do NOT multipost your questions.

Regards
Michel

Report message to a moderator

Re: File Usage (tablespace) [message #498248 is a reply to message #498146]

Wed, 09 March 2011 05:48

John Watson
Messages: 8960
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.

Report message to a moderator

Re: File Usage (tablespace) [message #498306 is a reply to message #498149]

Wed, 09 March 2011 09:42

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

I mean to say duplicate in terms of "file_id, file_name, tablespace_names" fileds value are identical
e.g.
file_id=1
file_name=+DATA/ssam03np/datafile/system.318.731618755
tablespace=SYSTEM

as shown in previous snapshot. any clue why?

Report message to a moderator

Re: File Usage (tablespace) [message #498308 is a reply to message #498306]

Wed, 09 March 2011 10:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what question is the SQL supposed to answer?

Report message to a moderator

Re: File Usage (tablespace) [message #498312 is a reply to message #498306]

Wed, 09 March 2011 10:20

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

pchandna1 wrote on Wed, 09 March 2011 16:42

I mean to say duplicate in terms of "file_id, file_name, tablespace_names" fileds value are identical
e.g.
file_id=1
file_name=+DATA/ssam03np/datafile/system.318.731618755
tablespace=SYSTEM

as shown in previous snapshot. any clue why?

So do not include inst_id and use DISTINCT.

Regards
Michel

Report message to a moderator

Re: File Usage (tablespace) [message #498316 is a reply to message #498312]

Wed, 09 March 2011 10:32

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

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

Attachment: inst1_duplicate.JPG
(Size: 103.74KB, Downloaded 955 times)

Report message to a moderator

Re: File Usage (tablespace) [message #498318 is a reply to message #498316]

Wed, 09 March 2011 10:43

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

reason for including inst_id was; we have RAC environment.

Yes but in this case, you have to live with what you call duplicates and are not.
Also because you get rows from gv$filespace_usage that are not data/temp files.
You have to also join with sys.ts$.

Regards
Michel

Report message to a moderator

Re: File Usage (tablespace) [message #498324 is a reply to message #498318]

Wed, 09 March 2011 11:14

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

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

Report message to a moderator

Previous Topic:	Jan PSU on 11gR2
Next Topic:	ORA-01092 - manually creating a database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 06:47:50 CST 2024