OraFAQ Forum: Server Administration » 10gR2 on AIX - Table size

Home » RDBMS Server » Server Administration » 10gR2 on AIX - Table size (10.2.0.1)

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

10gR2 on AIX - Table size [message #335407]

Tue, 22 July 2008 01:57

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Experts,
We are stuck on one of the issue with size of the table. Our team is laoding records in the table using sqlldr. The size of the data is more than 26G. But when we see the size of the table in the dba_segments it remain same during the data loading. But the size in dba_extents is increasing during the data loading . And it is almost double the size of table from dba_segments.

Below is some information about the table:

SQL> select blocks, empty_blocks ,avg_space ,num_freelist_blocks from DBA_tables where TABLE_NAME LIKE 'XXX' AND OWNER ='RMS';

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
5715729 0 0 0

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_EXTENTS WHERE SEGMENT_NAME= 'XXX' AND OWNER='RMS' GROUP BY SEGMENT_NAME;

SUM(BYTES/1024/1024/1024)
-------------------------
46.3085938

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME= 'XXX' AND OWNER='RMS' GROUP BY SEGMENT_NAME;

SUM(BYTES/1024/1024/1024)
-------------------------
25.3583374

SQL> select chain_cnt,last_analyzed from dba_tables where table_name= 'XXX' AND OWNER='RMS';
CHAIN_CNT LAST_ANAL
--------- ---------
0 17-JUL-08

Your help is really appreciated.

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335427 is a reply to message #335407]

Tue, 22 July 2008 03:05

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In LMT it can happens during a loading process (or during dropping a user, or any other bulk operations) as dba_segments and dba_extents don't get their information from the same point and they can be temporarly not synchronized.

Regards
Michel

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335433 is a reply to message #335407]

Tue, 22 July 2008 03:11

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Thanks Michel for your instant reply.
But how much time does it take to synchronize these two. I am having this from last 4-5 days. The data was loaded last week.

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335440 is a reply to message #335433]

Tue, 22 July 2008 03:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In this case, you may have a bitmap corruption. This can happen on bulk operation.
First use DBMS_SPACE_ADMIN.SEGMENT_VERIFY (header file and block can be found in dba_segments).
Then, if it is not fixed and you are using ASSM, you can use DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY or DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY procedure.

Regards
Michel

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335605 is a reply to message #335407]

Tue, 22 July 2008 23:15

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Michel,
Once again for your reply.
I followed the step suggested by you. But still the same result.
SQL> select 'exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('''||'RMS_DATA'||''',22,'||header_block||');' from dba_segments where segment_name= 'XXX' AND OWNER='RMS';

exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1549353);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1549337);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1549345);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1549361);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381033);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381041);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381049);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381057);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381065);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381073);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381081);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381089);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1381129);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398545);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398553);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398577);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398585);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398593);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398601);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398609);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398617);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398625);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398633);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398641);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398649);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398657);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1398665);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1536785);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1536793);
exec dbms_SPACE_ADMIN.SEGMENT_VERIFY ('RMS_DATA',22,1536801);

30 rows selected.

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME= 'XXX' AND OWNER='RMS' GROUP BY SEGMENT_NAME;
25.3583374

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_EXTENTS WHERE SEGMENT_NAME= 'XXX' AND OWNER='RMS' GROUP BY SEGMENT_NAME;
46.3085938

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335606 is a reply to message #335407]

Tue, 22 July 2008 23:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>30 rows selected
You generated 30 "lines" & you executed exactly ZERO of them.

Generating text string may be interesting to an the invoker but accomplishes nothing useful.

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335830 is a reply to message #335606]

Wed, 23 July 2008 19:40

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Anacedent,
I have executed these lines but didn't paste the output. All these lines executed successfully. But when i check the size after the execution, the issue is still there.

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335837 is a reply to message #335407]

Wed, 23 July 2008 23:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Which value is correct?

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335841 is a reply to message #335407]

Wed, 23 July 2008 23:58

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Anacedent,
The data in the table is more than 25GB, so the value from dba_extents is correct.

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335842 is a reply to message #335841]

Thu, 24 July 2008 00:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

lalitm_2003 wrote on Wed, 23 July 2008 21:58

Hi Anacedent,
The data in the table is more than 25GB, so the value from dba_extents is correct.

Regards
Lalit

If what you state is correct, are you claiming value reported by DBA_SEGMENTS is incorrect?
If so, how/why has Oracle allowed this error to persist.

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335853 is a reply to message #335407]

Thu, 24 July 2008 00:37

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Anacedent,
Pls find the snapshot of the queries that states that there might be something wrong with the table size. And that problem still there even after the segment verify and stats generation.
It might be the error in oracle.

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335854 is a reply to message #335407]

Thu, 24 July 2008 00:39

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Oops i missed the file..

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335855 is a reply to message #335853]

Thu, 24 July 2008 00:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

lalitm_2003 wrote on Wed, 23 July 2008 22:37

Hi Anacedent,
Pls find the snapshot of the queries that states that there might be something wrong with the table size. And that problem still there even after the segment verify and stats generation.
It might be the error in oracle.

Regards
Lalit

Then file a bug.
What resolution do you expect from this forum?

BTW, what "snapshot"?

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335857 is a reply to message #335407]

Thu, 24 July 2008 00:45

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Ancedent,
Let me file a bug and see what they reply for the same.

Regards
Lalit

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #335859 is a reply to message #335407]

Thu, 24 July 2008 00:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please report back the results & response.
Something seems amiss & an explanation is needed.

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #336133 is a reply to message #335407]

Fri, 25 July 2008 00:01

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi Anacedent,
The research on SR says that its a bug. Below is the details about the bug from oracle on SR.

Bug 4771672 Abstract: DIFFERENCES IN DBA_SEGMENTS AND DB_EXTENTS AFTER PARALLEL CREATE INDEX
Bug 3270428 - Difference between bytes in DBA_SEGMENTs and DBA_EXTENTs

Note 352330.1
Abstract Differences for Bytes and Blocks Columns Between DBA_SEGMENTS and DBA_EXTENTS After Crea
ting Index in Parallel

In the mean while we have to drop the table for some scheduled activity to bring new data into the table. Now after drop the tables is showing the correct bytes in dba_segments and dba_extents.
Below is the result of the queries:
SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='INTER_BHARTI' and owner='RMS';

SUM(BYTES/1024/1024/1024)
-------------------------
7.88378906
SQL> select sum(bytes/1024/1024/1024) from dba_EXTENTS where segment_name='INTER_BHARTI' and owner='RMS';

SUM(BYTES/1024/1024/1024)
-------------------------
7.88378906

The issue is resolved now.
Thanks everybody for your precious time on this issue.

Regard
Lalit

[Updated on: Fri, 25 July 2008 00:46] by Moderator

Report message to a moderator

Re: 10gR2 on AIX - Table size [message #336148 is a reply to message #336133]

Fri, 25 July 2008 00:46

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

Previous Topic:	unable to drop user get ORA-00600
Next Topic:	finding fragmented tables.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 22:20:44 CST 2024