OraFAQ Forum: Server Administration » user/dba

Home » RDBMS Server » Server Administration » user/dba_segments refresh issue (oracle 9.2)

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

user/dba_segments refresh issue [message #379212]

Mon, 05 January 2009 07:14

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?

Report message to a moderator

Re: user/dba_segments refresh issue [message #379216 is a reply to message #379212]

Mon, 05 January 2009 08:23

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

Report message to a moderator

Re: user/dba_segments refresh issue [message #379218 is a reply to message #379212]

Mon, 05 January 2009 08:28

Michel Cadot
Messages: 68666
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

Report message to a moderator

Re: user/dba_segments refresh issue [message #379227 is a reply to message #379212]

Mon, 05 January 2009 09:38

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

Report message to a moderator