Oracle 9i no file_id in dba_free_space

[Kalin Evtimov]

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!

[spex]

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

[Jonathan Fife · Accepted solution]

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.

[Kalin Evtimov]

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.

[Jonathan Fife]

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.