Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 9i no file_id in dba_free_space

SOLVED
Go to solution
Kalin Evtimov
Regular Advisor

Oracle 9i no file_id in dba_free_space

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!
4 REPLIES
spex
Honored Contributor

Re: Oracle 9i no file_id in dba_free_space

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
Honored Contributor
Solution

Re: Oracle 9i no file_id in dba_free_space

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.
Decay is inherent in all compounded things. Strive on with diligence
Kalin Evtimov
Regular Advisor

Re: Oracle 9i no file_id in dba_free_space

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
Honored Contributor

Re: Oracle 9i no file_id in dba_free_space

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.
Decay is inherent in all compounded things. Strive on with diligence