Home » RDBMS Server » Server Administration » 10gR2 on AIX - Table size (10.2.0.1)
10gR2 on AIX - Table size [message #335407] Tue, 22 July 2008 01:57 Go to next message
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
Re: 10gR2 on AIX - Table size [message #335427 is a reply to message #335407] Tue, 22 July 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: 10gR2 on AIX - Table size [message #335433 is a reply to message #335407] Tue, 22 July 2008 03:11 Go to previous messageGo to next message
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

Re: 10gR2 on AIX - Table size [message #335440 is a reply to message #335433] Tue, 22 July 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: 10gR2 on AIX - Table size [message #335605 is a reply to message #335407] Tue, 22 July 2008 23:15 Go to previous messageGo to next message
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
Re: 10gR2 on AIX - Table size [message #335606 is a reply to message #335407] Tue, 22 July 2008 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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.
Re: 10gR2 on AIX - Table size [message #335830 is a reply to message #335606] Wed, 23 July 2008 19:40 Go to previous messageGo to next message
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

Re: 10gR2 on AIX - Table size [message #335837 is a reply to message #335407] Wed, 23 July 2008 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Which value is correct?
Re: 10gR2 on AIX - Table size [message #335841 is a reply to message #335407] Wed, 23 July 2008 23:58 Go to previous messageGo to next message
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
Re: 10gR2 on AIX - Table size [message #335842 is a reply to message #335841] Thu, 24 July 2008 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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.
Re: 10gR2 on AIX - Table size [message #335853 is a reply to message #335407] Thu, 24 July 2008 00:37 Go to previous messageGo to next message
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
Re: 10gR2 on AIX - Table size [message #335854 is a reply to message #335407] Thu, 24 July 2008 00:39 Go to previous messageGo to next message
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
Member
Oops i missed the file..

Regards
Lalit
Re: 10gR2 on AIX - Table size [message #335855 is a reply to message #335853] Thu, 24 July 2008 00:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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"?
Re: 10gR2 on AIX - Table size [message #335857 is a reply to message #335407] Thu, 24 July 2008 00:45 Go to previous messageGo to next message
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
Re: 10gR2 on AIX - Table size [message #335859 is a reply to message #335407] Thu, 24 July 2008 00:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please report back the results & response.
Something seems amiss & an explanation is needed.
Re: 10gR2 on AIX - Table size [message #336133 is a reply to message #335407] Fri, 25 July 2008 00:01 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

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


Current Time: Mon Dec 05 02:31:13 CST 2016

Total time taken to generate the page: 0.09723 seconds