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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #335605 is a reply to message #335407] |
Tue, 22 July 2008 23:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #335830 is a reply to message #335606] |
Wed, 23 July 2008 19:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #335853 is a reply to message #335407] |
Thu, 24 July 2008 00:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #336133 is a reply to message #335407] |
Fri, 25 July 2008 00:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 09:58:15 CDT 2024
|