OraFAQ Forum: Server Administration » Tablespace not listed in dba_free

Home » RDBMS Server » Server Administration » Tablespace not listed in dba_free_space

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace not listed in dba_free_space [message #57571]

Mon, 23 June 2003 14:01

Bubba
Messages: 3
Registered: January 2002

Junior Member

I have a dictionary managed tablespace in my database. When I perform a query against the dba_free_space view, my tablespace is not listed. I am in the process of running a deletion script against the only table that lives in this tablespace. The table has over 90 million records and I am issuing commits after every 5000 or so deletions. Any ideas on why this tablespace is not listed in the dba_free_space view?

Report message to a moderator

Re: Tablespace not listed in dba_free_space [message #57572 is a reply to message #57571]

Mon, 23 June 2003 16:22

Bubba
Messages: 3
Registered: January 2002

Junior Member

OK, so the reason I didn't see the tablespace is because it is "FULL". So my question has changed...now that I am deleting rows...and committing those deletions...why am I not able to see the modification to the dba_free_space. Obviously, I have some free space...I did the math:

select sum(a.bytes) - sum(b.bytes)
from dba_segments a, dba_data_files b
where a.tablespace_name=b.tablespace_name
and a.tablespace_name='&TBS';

Report message to a moderator

Re: Tablespace not listed in dba_free_space [message #57573 is a reply to message #57572]

Tue, 24 June 2003 05:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

deleting the rows from the table will not reset the highwater mark.
You have to truncate the table.
Since truncation is not possible in all cases there are workarounds.
1. Move the table to the same tablespace as it is.
   this will reset the exetents.
   alter table table_name move tablespace < same_as_before >;
or
2. T1 is the source table.
   delete from T1 where .. conditons ..
   creat table t2 as select * from t1;
   truncate table t1;
   insert into t1 ( select * from t2);
 if everything is OK, then   
   drop table t2;

Report message to a moderator

Previous Topic:	My Sessions are frequently terminated
Next Topic:	interesting, for UNIX GUYS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 04:41:16 CDT 2024