topic Oracle 9i no file_id in dba_free_space in Operating System - HP-UX

Oracle 9i no file_id in dba_free_space

Kalin Evtimov — Mon, 31 Jul 2006 07:24:46 GMT

Hi!

I am confised with this:

select file_id from dba_free_space where tablesapce_name like 'MMM';

But there is no row selected from the dba_free_space, although the index exists in dba_data_files...What do? Any ideas??

Thank you!

Re: Oracle 9i no file_id in dba_free_space

spex — Mon, 31 Jul 2006 07:44:25 GMT

Hi Katlin,

1) "tablespace_name" is misspelled in the query you gave.

2) Unless 'MMM' is the complete tablespace name, you need to use wildcards with the 'like' clause:

SELECT file_id
FROM dba_free_space
WHERE tablespace_name like '%MMM%';

3) Make sure you are logged in as a user with sufficient privileges to view 'dba_free_space'.

PCS

Re: Oracle 9i no file_id in dba_free_space

Jonathan Fife — Mon, 31 Jul 2006 07:54:06 GMT

In addition to what Spex said, dba_free_space doesn't show tablespaces with 0 free extents, so you could just be out of free space.

Re: Oracle 9i no file_id in dba_free_space

Kalin Evtimov — Mon, 31 Jul 2006 07:57:53 GMT

This was only an example. My syntax is OK.
Maybe I didn't explain the problem.

To each file name I should have a corresponding file id. This information is stored in dba_data_files.

Now, I want to see some other information stored in dba_free_space and I know (for example), that my file1 has an ID 1. Then I run this: "select bytes from dba_free_space where file_id=1" and get as result nothing, because there is no such file_id in the table...This is confusing me, because there should be such file_id.

Re: Oracle 9i no file_id in dba_free_space

Jonathan Fife — Mon, 31 Jul 2006 08:06:15 GMT

Check out the following article from the CoOp Oracle FAQ -- http://www.jlcomp.demon.co.uk/faq/freespace.html

It explains what you are seeing and gives the table joins you should be using.