dba_free_space missing a tablespace???
Ed Stevens
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/
David Fitzjarrell
Since DBA_FREE_SPACE lists the free space in all tablespaces could it
be that there is no free space in APHRB1TS?
--
David Fitzjarrell
Oracle Certified DBA
Walter T Rejuney
> be that there is no free space in APHRB1TS?
Is it possible that APHRBlTS is a read-only tablespace? If so, it would
never have any free space.
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
Brian Peasland
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
--
========================================
Brian Peasland
Raytheons Systems at
USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my
company!
========================================
Niall Litchfield
news:3A7046E4...@edcmail.cr.usgs.gov...
> Is the missing tablespace LOCALLY MANAGED? If so, then it won't show up
> in DBA_FREE_SPACE.
>
I don't get this behaviour. I get the following
1 select t.tablespace_name,extent_management,sum(blocks)
2 from dba_tablespaces t,dba_free_space f
3 where t.tablespace_name = f.tablespace_name (+)
4* group by t.tablespace_name,extent_management
SQL> /
TABLESPACE_NAME EXTENT_MAN SUM(BLOCKS)
------------------------------ ---------- -----------
AGRHISTR LOCAL 16128
AGRHISTR4 LOCAL 26624
AGRINDEX LOCAL 160
AGRINDEX4 LOCAL 512
AGRSTATIC LOCAL 61584
AGRSTATIC4 LOCAL 1536
AGRTEMP LOCAL 528640
AGRTRANS LOCAL 17008
AGRTRANS4 LOCAL 39936
RBS LOCAL 19200
SYSTEM DICTIONARY 11796
TEMP LOCAL
TOOLS DICTIONARY 1919
In other words locally managed tablespaces do appear in dba_free_space. The
one that is missing is our temp tablespace. I note that yours is also a temp
tablespaces. We have defined our temp tablespace with the TEMPFILE option
and my hunch is that this is the root of the behaviour shown.
Regards
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Niall Litchfield
either null or zero in the appropriate columns. I haven't actually tried
this though tablespaces with no free space being something to avoid....
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"David Fitzjarrell" <ora...@aol.com> wrote in message
news:94n5fl$gej$1...@nnrp1.deja.com...
Gerrit Scholten
DBA_FREE_SPACE tablespace SYSTEM was missing :(.
The database had just been created, no user objects had been created
yet.
I managed to let SYSTEM show up in DBA_FREE_SPACE by creating a table in
SYSTEM, and dropping the table thereafter. DBA_FREE_SPACE only showed
the space that had been occupied by that table, NOT the other 60 MB that
I added to SYSTEM by enlarging the datafile. Looks like a genuine bug to
me.
Gerrit
yon...@yahoo.com
It's not true that read only tablespaces or locally managed tablespaces
will not show in dba_free_space.
Yong Huang
yon...@yahoo.com
In article <94n56a$g95$1...@nnrp1.deja.com>,
haile...@my-deja.com
temporary tablespace don't show up in
v$datafile
dba_data_files
dba_free_space
but do show up in
dba_tablespaces
v$tempfile
dba_temp_files
I haven't played with the temporary files that much. Finding free space
in them doesn't seem totally straight forward. Currently I'm using the
v$sort_segment, but would be currious as to what other people use to
monitor the space in the temporary files.
Best
Kyle
In article <94pr6e$rlv$1...@nnrp1.deja.com>,
Howard J. Rogers
What, pray, do you expect? DBA_FREE_SPACE shows only FREE space within a
tablespace. If you haven't got any free space, there's nothing to report
(presumably, you wouldn't expect the EMP table to report on employees that
you haven't hired yet??). If you DO have free space, it reports on the free
space. Used space belong somewhere else (try DBA_EXTENTS).
When you first create a database, SYSTEM is highly likely to be
chock-a-block full. Your creating a new table would almost certainly have
required the system datafile to autoextend, and the new table extents would
have been placed in that extra space When you dropped the table, the extra
file space is retained, but is now marked free. Hence, SYSTEM suddenly
becomes listed in free extents.
It's certainly not a bug (well, almost certainly -with Oracle you can never
be quite sure). It's what's supposed to happen, *if* you think about it.
Regards
HJR
Gerrit Scholten <ggj.sc...@worldonline.nl> wrote in message
news:3A706D67...@worldonline.nl...
Howard J. Rogers
in ordinary use, you can expect the entire thing to be full of temporary
segments, and thus not have any free space (and hence not show). But when a
database is freshly started, and no users have started doing sorts,
temporary tablespace WILL be listed.
The following is off my test system:
SVRMGR> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELAT
IVE_F
------------------------------ ---------- ---------- ---------- ---------- -
----
-----
SYSTEM 1 30073 589824 72
1
SYSTEM 1 30049 65536 8
1
RBS 2 8770 1564672 191
2
RBS 2 3650 524288 64
2
RBS 2 2754 524288 64
2
USERS 3 2 20963328 2559
3
TEMP 4 250 18931712 2311
4
TOOLS 5 2 10477568 1279
5
INDX 6 2 20963328 2559
6
DRSYS 7 490 16965632 2071
7
TEMP2 8 9 10420224 1272
8
And from DBA_TABLESPACES:
SVRMGR> select * from dba_tablespaces;
TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT
PCT_I
NCREA MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO PLU
------------------------------ ---------- ---------- ---------- ---------- -
----
----- ---------- --------- --------- --------- ---------- --------- ---
SYSTEM 65536 65536 1 2147483645
50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
RBS 524288 524288 8 4096
50 524288 ONLINE PERMANENT LOGGING DICTIONARY USER NO
USERS 131072 131072 1 4096
0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
TEMP 65536 65536 1
0 65536 ONLINE TEMPORARY LOGGING DICTIONARY USER NO
TOOLS 32768 32768 1 4096
0 32768 ONLINE PERMANENT LOGGING DICTIONARY USER NO
INDX 131072 131072 1 4096
0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
DRSYS 65536 65536 1 2147483645
50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
WHACKO 1048576 1048576 1
0 1048576 ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM NO
TEMP2 65536 1 2147483645
65536 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO
Notice that TEMP is proper TEMPORARY tablespace, dictionary managed, using
datafiles. TEMP2 is PERMANENT tablespace, locally managed, using datafiles
(ie, it's really just normal tablespace). Both are listed in FREE_SPACE.
WHACKO is proper TEMPORARY tablespace, locally managed, using tempfiles.
That ISN'T in FREE_SPACE (but since there are no sorts going on, I might
have expected it to be).
So it appears to be true is that temporary tablespace created with the
"tempfile" keyword instead of the old "datafile" one is not listed, but good
old-fashioned temporary tablespace most definitely is.
Incidentally, and something I wasn't aware of, it's fairly easy to issue the
command 'alter tablespace blah TEMPORARY' to turn it into proper temporary
tablespace -but the command won't work on locally managed tablespaces that
were created using the 'datafile' keyword. As ever, the error message is a
master of obfuscation: 'invalid option for create/alter tablespace'. I
can't think why it should be so hard to turn datafile-locally managed
tablespace into temporary tablespace.
Regards
HJR
<haile...@my-deja.com> wrote in message
news:94ptdc$tv4$1...@nnrp1.deja.com...
Gerrit Scholten
As I stated, I extended the SYSTEM tablespace by 60 MB, so it grew from
60 MB to 120 MB. Even Oracle8i 8.1.6 doesn't get _that_ much
chock-a-block full. So DBA_FREE_SPACE didn't report free extents in
SYSTEM, and it didn't report free extents after I double the datafile
size to 120 MB. At least 60 MB fresh free space in SYSTEM, and
DBA_FREE_SPACE doesn't see it. Strange.
No, the datafile didn't autoextend when I created the table (I checked
that :) ). Only after I created tables in SYSTEM, and dropped them, did
I see free space in SYSTEM (using DBA_FREE_SPACE).
Gerrit Scholten
but I added some translations):
<screencopy>
SQL*Plus: Release 8.0.5.0.0 - Production on Ma Jan 8 10:19:45 2001
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Verbonden met:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> connect sys/*********@ofptd2k
Verbonden. // connected
SQL> select * from dba_free_space where tablespace_name = 'SYSTEM';
Geen rijen geselecteerd. // no rows selected
SQL> create table test ( tekst varchar2(2000) ) tablespace SYSTEM;
Tabel is aangemaakt. // table created
SQL> select * from dba_free_space where tablespace_name = 'SYSTEM';
Geen rijen geselecteerd. // no rows selected
SQL> drop table test;
Tabel is verwijderd. // table dropped
SQL> select * from dba_free_space where tablespace_name = 'SYSTEM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES
BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ----------
---------- ------------
SYSTEM 1 31257 65536
8 1
</screencopy>
I'm sorry Howard, but I've never anything seen this before.
Howard J. Rogers
amorphous blobs of space that hasn't been laid out yet. In any event, the
view gets utterly confused by re-sizes, regardless of tablespace. Consider
this little test:
SVRMGR> create tablespace freespace datafile 'c:\free.dbf' size 1m;
Statement processed.
SVRMGR> select * from dba_free_space where tablespace_name='FREESPACE';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELAT
IVE_F
------------------------------ ---------- ---------- ---------- ---------- -
----
-----
FREESPACE 11 2 1040384 127
11
1 row selected.
SVRMGR> alter database datafile 11 resize 2m;
Statement processed.
SVRMGR> select * from dba_free_space where tablespace_name='FREESPACE';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELAT
IVE_F
------------------------------ ---------- ---------- ---------- ---------- -
----
-----
FREESPACE 11 2 1040384 127
11
1 row selected.
So, I create a brand new tablespace, 1m big, and get 1m listed in
free_space. I resize the file to 2m, and still have only the original 1m of
free space. And a shutdown-startup doesn't make the free_space pick up on
the extra 1m, either.
On the other hand, when I add a second datafile into tablespace FREESPACE,
DBA_FREE_SPACE immediately reports two lines for that tablespace. Clearly,
additions of brand new files are picked up immediately, resizes aren't.
Which is a bit quirky, but at least it's consistent.
Except for this next test (where I got a bit confused with my megabytes and
kilobytes, and ended up with a 600M SYSTEM tablespace!! Guess I'm ready to
upgrade to 9i, huh??!):
SVRMGR> select * from dba_free_space where tablespace_name='SYSTEM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELAT
IVE_F
------------------------------ ---------- ---------- ---------- ---------- -
----
-----
SYSTEM 1 30073 589824 72
1
1 row selected.
SVRMGR> alter database datafile 'C:\ORACLE\ORADATA\HJR\SYSTEM01.DBF' resize
6000
00k;
Statement processed.
SVRMGR> select * from dba_free_space where tablespace_name='SYSTEM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELAT
IVE_F
------------------------------ ---------- ---------- ---------- ---------- -
----
-----
SYSTEM 1 30073 368050176 44928
1
1 row selected.
Here, I resize a file, and the increase in space is immediately evident.
On the other hand, when I resized SYSTEM back down to a more reasonable
275M, my BYTES in free_space actually went UP!!!
So God alone knows. Resizes clearly confuse the bugger out of it, and
additions of files don't. Clearly, we need to know more about the
mechanisms underlying free_space.
Jonathan...??? Anyone....??
Regards
HJR
Gerrit Scholten <ggj.sc...@worldonline.nl> wrote in message
news:3A70B26E...@worldonline.nl...
haile...@my-deja.com
you started off your email with such a misleading comment as:
>>Temporary tablespace most certainly DOES show up in dba_free_space
Temporary tablespaces do NOT show up in dba_free_space. A temporary
tablespace is the following:
create temporary tablespace ...
Now you can create a tablespace whose contents are temporary as in:
create tablespace foo ...
alter tablespace temporary ...
this is a normal tablespace that contents are considered temporary. Not
the same thing. Creating a temporary tablespace creates the tablespace
as nologging, locally managed and temporary. Altering a tablespace
temporary only changes the content type. It doesn't set the nologging
or locally managed (of course making a data dictionary tbs into a
locally managed would be a pain). Try to create a locally managed
tablespace and alter temporary and you get ora-03218, as you have found.
Best
Kyle
In article <3a70...@news.iprimus.com.au>,
Howard J. Rogers
about what proper temporary tablespace is.
In 8i, it is perfectly legitimate to create 'old fashioned' temporary
tablespace with the 'create tablespace blah ... temporary' commands that
were alive and kicking in versions 7 and 8. And such tablespace is as
temporary as the kind you create using the 'create temporary tablespace
tempfile 'x'' command that was introduced in 8i.
Or are you claiming that Oracles 7 and 8 didn't have proper temporary
tablespaces?
The correct distinction to be drawn is between tempfiles and datafiles. And
tempfiles don't show up in free_space. Datafiles do. And to say that
"temporary tablespace" doesn't appear is therefore simply incorrect, since
temporary tablespace can use datafiles.
Regards
HJR
<haile...@my-deja.com> wrote in message
news:94r649$1n4$1...@nnrp1.deja.com...
Ed Stevens
--
Further investigation shows that to be the case. Thanks.
Brian Peasland
I agree, it must be Locally Managed tablespaces with TEMPFILES that
don't show up in DBA_FREE_SPACE. Currently, I'm only using LMT's for
TEMP tablespaces. I've yet to move other tablespaces to LMT's. So I must
have "jumped the gun" a little bit.
Thanks for the info,
Brian
--
yon...@yahoo.com
You have a mistake in your ALTER DATABASE DATAFILE statement.
Unfortunately Oracle silently ignores it. You need to use the filename
instead of file# per SQL Reference documentation:
SQL> create tablespace test
datafile '/lurch/disk_4/oradata/TEST/test.dbf' size 1m;
Tablespace created.
SQL> select * from dba_free_space where tablespace_name = 'TEST';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELATIVE_FNO
------------------------------ --------- --------- --------- --------- -
-----------
TEST 4 2 1040384
127 4
SQL> alter database datafile 4 resize 2m;
Database altered.
SQL> select * from dba_free_space where tablespace_name = 'TEST';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELATIVE_FNO
------------------------------ --------- --------- --------- --------- -
-----------
TEST 4 2 1040384
127 4
SQL> alter database datafile '/lurch/disk_4/oradata/TEST/test.dbf'
resize 2m;
Database altered.
SQL> select * from dba_free_space where tablespace_name = 'TEST';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
RELATIVE_FNO
------------------------------ --------- --------- --------- --------- -
-----------
TEST 4 2 2088960
255 4
SQL> alter database datafile 123456 resize 2m;
Database altered.
Note that I do not have a datafile numbered 123456. In fact, I can use
any number less than or equal to 2000000 (2 million). Above that I get
ORA-2827. I'm not sure what parameter governs that. My Oracle version
is 8.1.6.0.0, 32-bit, on Solaris 2.6 Sparc. DB_FILES is set to 200. I
think MAXDATAFILES is 254 when the database is created (as seen in
output of alter database backup controlfile to trace).
Yong Huang
yon...@yahoo.com
In article <3a70...@news.iprimus.com.au>,
"Howard J. Rogers" <howa...@www.com> wrote:
Howard J. Rogers
Teach me to be lazy, I guess (though one could have wished for a meaningful
error message).
So does this resolve the mystery of why Gerrit's additional 60M didn't show
up? His script didn't show how he resized his file, so perhaps it too
didn't *really* resize it, and when he created his table, autoextend kicked
in ('cos autoextend is on for SYSTEM by default, IIRC).
Cheers Yong!
<yon...@yahoo.com> wrote in message news:94s9o3$vgm$1...@nnrp1.deja.com...
haile...@my-deja.com
Its neat to see your enthusiasm but your mails come across to me as
curriously charged or maybe its just your local lingo that I'm not used
to. What I said is "temporary tablespaces" ie
create temporary tablespace ...
don't show up in dba_free_space
which is true and is a case that could fit the original posts question.
Our discussion seems to be six of one half a dozen of the other as in
my saying "create temporary tablepsace" verses you "its only tempfile"
which is cool (I'd enjoy more if it didn't seem charged) because it
clarifies the details. Cutting hairs in this case is great and helps
point out the details temporary tablespaces which have meant different
things over different versions, v6 and 7 for normal tbs used for temp,
later v7 for normal tbs of type temporary mainly for OPS contention,
and then v8 with full fledged temporary tablespaces - no logging, no
data dictionary updates, no need to backup. I'm currently refering to
only to 8, which is most definitly worth pointing out; I over eagerly
take it for granted.
Again I think your contributions are cool and look for mutually
supportive exchanges.
As I mentioned in my first post,
>>I haven't played with the temporary files that much. Finding free
>>space in them doesn't seem totally straight forward. Currently I'm
>>using the v$sort_segment, but would be currious as to what other
>>people use to monitor the space in the temporary files.
If you have info on montoring free space in temporary files that would
be cool. Have you looked into this?
Best
Kyle
In article <3a714a38$1...@news.iprimus.com.au>,
"Howard J. Rogers" <howa...@www.com> wrote:
Jonathan Lewis
See Practical Oracle 8i - Chapter 9 Temporary Space
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
haile...@my-deja.com wrote in message <94snbe$cov$1...@nnrp1.deja.com>...