Home » RDBMS Server » Server Administration » user/dba_segments refresh issue (oracle 9.2)
user/dba_segments refresh issue [message #379212] Mon, 05 January 2009 07:14 Go to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
I am using the "truncate table" statement to free the space in a table and then query the dba_segments but the bytes, blocks and extents should have been reset to reflect the initial extent values but they still show the original values.
On the other hand, the dba_extents has been updated to only show the initial extent.
Any idea why the user/dba_segments has not been updated?
Re: user/dba_segments refresh issue [message #379216 is a reply to message #379212] Mon, 05 January 2009 08:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm going to want some evidence for this one, I think. Here's a script you can tun in SQL*Plus, and post the results back here:
create table test_056 (col_1 varchar2(100));

insert into test_056 select 'Row '||level||rpad('A',50,'A') from dual connect by level <= 100000;

select segment_name,bytes,blocks,extents from dba_segments where segment_name = 'TEST_056';

truncate table test_056;

select segment_name,bytes,blocks,extents from dba_segments where segment_name = 'TEST_056';


When I run it, I get just what I'd expect:
create table succeeded.
100000 rows inserted
SEGMENT_NAME                                                   BYTES                  BLOCKS                 EXTENTS                
------------------------------------------------------------- ---------------------- ---------------------- ---------------------- 
TEST_056                                                      8388608                1024                   23                     

1 rows selected

truncate table test_056 succeeded.
SEGMENT_NAME                                                  BYTES                  BLOCKS                 EXTENTS                
------------------------------------------------------------- ---------------------- ---------------------- ---------------------- 
TEST_056                                                      65536                  8                      1                      

1 rows selected
Re: user/dba_segments refresh issue [message #379218 is a reply to message #379212] Mon, 05 January 2009 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Metalink notes:
463101.1 "HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS"
271030.1 "Difference Between Bytes In Dba_segments And Sum(Bytes) In Dba_extents"

Regards
Michel
Re: user/dba_segments refresh issue [message #379227 is a reply to message #379212] Mon, 05 January 2009 09:38 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Same thing...

Quote:

SQL> create table test_056 (col_1 varchar2(100));

Table created.

SQL> insert into test_056 select 'Row '||level||rpad('A',50,'A') from dual connect by level <= 100000;

100000 rows created.

SQL> select segment_name,bytes,blocks,extents from user_segments where segment_name = 'TEST_056';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES BLOCKS EXTENTS
---------- ---------- ----------
TEST_056
8388608 1024 2


SQL> truncate table test_056;

Table truncated.

SQL> select segment_name,bytes,blocks,extents from user_segments where segment_name = 'TEST_056';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES BLOCKS EXTENTS
---------- ---------- ----------
TEST_056
8388608 1024 2

Re: user/dba_segments refresh issue [message #379228 is a reply to message #379227] Mon, 05 January 2009 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer may be in the notes I posted.

In addition, use CODE tags and not QUOTE one.

Regards
Michel
Re: user/dba_segments refresh issue [message #379229 is a reply to message #379212] Mon, 05 January 2009 09:58 Go to previous message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Thanks Michel Cadot. It looks like it should sort the issue.
Previous Topic: Oracle Jobs failed
Next Topic: impact of deleting rows on table
Goto Forum:
  


Current Time: Thu Dec 08 08:09:36 CST 2016

Total time taken to generate the page: 0.14487 seconds