Re: dba_free_space missing a tablespace???

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Thu, 25 Jan 2001 15:31:48 GMT
Message-ID: <3A7046E4.AD8FC736@edcmail.cr.usgs.gov>

Is the missing tablespace LOCALLY MANAGED? If so, then it won't show up in DBA_FREE_SPACE.

I have Oracle 8.1.6 running on Solaris 7. If I query DBA_TABLESPACES, I get the following:

SQL> select TABLESPACE_NAME,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN

-------------------- ----------
SYSTEM               DICTIONARY
RBS                  DICTIONARY
TEMP                 LOCAL
SDE                  DICTIONARY
ENDUSERS             DICTIONARY
GEOVECTOR            DICTIONARY
GEOSHADE             DICTIONARY
GEO_IDX              DICTIONARY
ALBERS_RASTER        DICTIONARY
ALBERS_VECTOR        DICTIONARY
ALBERS_IDX           DICTIONARY
IP_IDX               DICTIONARY
IP_VECTOR            DICTIONARY
IP_RASTER            DICTIONARY

14 rows selected.

If I query DBA_FREE_SPACE, I get the following:

SQL> select distinct tablespace_name from dba_free_space;

TABLESPACE_NAME

---

ALBERS_IDX
ALBERS_RASTER
ALBERS_VECTOR

ENDUSERS
GEOSHADE
GEOVECTOR
GEO_IDX

IP_IDX
IP_RASTER
IP_VECTOR

RBS
SDE
SYSTEM 13 rows selected.

One less tablespace. Note that it is my TEMP tablespace which is LOCALLY MANAGED which does not appear in DBA_FREE_SPACE.

HTH,
Brian

Ed Stevens wrote:
>
> Here's an odd one. I SELECT DISTINCT TABLSPACE_NAME from 3 different
> views: dba_tablespaces, dba_data_files, and dba_free_space. I should
> get the same results, but look at the following, and you'll see that
> dba_free_space returned one less tablespace name . . . .
>
> SQL> select tablespace_name
> 2 from dba_tablespaces
> 3 order by tablespace_name;
>
> TABLESPACE_NAME
> ------------------------------
> APHDTATS
> APHIDXTS
> APHRB1TS
> APHRB2TS
> APHTMPTS
> SYSTEM
>
> 6 rows selected.
>
> SQL> select distinct tablespace_name
> 2 from dba_data_files
> 3 order by tablespace_name;
>
> TABLESPACE_NAME
> ------------------------------
> APHDTATS
> APHIDXTS
> APHRB1TS
> APHRB2TS
> APHTMPTS
> SYSTEM
>
> 6 rows selected.
>
> SQL> select distinct tablespace_name
> 2 from dba_free_space
> 3 order by tablespace_name;
>
> TABLESPACE_NAME
> ------------------------------
> APHDTATS
> APHIDXTS
> APHRB2TS
> APHTMPTS
> SYSTEM
>
> 5 rows selected.
>
> SQL> spool off
>
> --
> - Ed Stevens
>
> Sent via Deja.com
> http://www.deja.com/
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================

Received on Thu Jan 25 2001 - 09:31:48 CST