Re: problems with dba_free_space and stuff

In article <1993Sep30.072836.1_at_ashley.cofc.edu> johnsona_at_ashley.cofc.edu writes:
>I have recently added a second raw tablespace to hold indexes. At the same
>time I expanded a temporary tablespace with a 100MB file system file. No I
>have two problems: (that I can't explain :->)
>
> A select from dba_free_space returns an internal oracle error (sorry,
> can't remember it now. I can get it tho)
> My other temporary tablespace is invisible to the RDBMS. (i.e. an attempt
> to alter user temporary tablespace TMP_SPACE_2 says TMP_SPACE_2 DNE, however,
> is certainly see it with select * from dba_tablespaces)
>
>Are these harbingers of really bad things happening at a very low level? If
>so, what to do? (Please don't say full export and rebuild!!!!)

I seem to recall that a DBA I worked with had similar problems under 6.0.30. After much conversation it came out that he had tried to delete directly from the v$ tables to get rid of all those invalid tablespaces that had been dropped. (They offended his sence of neatness)

The bottom line is that your lowlevel v$ tables are out of wack and aren't being joined together properly.

Frankly I would be a little supprised if a full export worked at all. Since the internal tables are messed up I would expect it crash when it tries to figure out how to make the create statment for TMP_SPACE_2.

I would try the full export AND do an export of each user you care about. After that you may want to look at the v$ tables associated with the tablespaces. You may be able to pin point the inconsistancy and correct it manually by doing updates.

Not supported of course but at this point what do you have to loose?

-- 
Regards, 

Lee E. Parsons                  		Baker Hughes Inteq, Inc
Oracle Database Administrator 			lparsons_at_exlog.com