HWM in table [message #234373] |
Tue, 01 May 2007 04:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
hi,
I had a table which holds 1 million records, recently i deleted most of the records. Now it is havibng only 10k records.
But still when i query this table i am getting same delay
Thanks.
|
|
|
|
|
|
Re: HWM in table [message #234388 is a reply to message #234380] |
Tue, 01 May 2007 06: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) |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
Hi Michel,
This tablespace is in automatic segment space management only,but still the same time.
My oracle vesion is 9.2.0.6.0
Thanks.
|
|
|
|
|
|
|
|
|
Re: HWM in table [message #235065 is a reply to message #234860] |
Thu, 03 May 2007 23:46 ![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) |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
Thanks all for the suggestion.
Hi Mahesh,
Thanks for your advice. We could able to search the forum but the exact answer we are not getting that is why we are posting.
Thanks.
|
|
|
|
|
Re: HWM in table [message #241502 is a reply to message #234373] |
Tue, 29 May 2007 16:09 ![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) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Check your application - it may keep rowids of the table as data in some other table(s) ( ERP does it a lot, but it's not a "usual" application behavior).
In that case - you can NOT reorg it, because rowids will change.
HTH.
Michael
|
|
|
Re: HWM in table [message #241662 is a reply to message #234373] |
Wed, 30 May 2007 06:12 ![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) |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
Thanks Michel,
I just ran the following script
DECLARE
total_blocks number;
total_bytes number;
unused_blocks number;
unused_bytes number;
last_used_extent_file_id number;
last_used_extent_block_id number;
last_used_block number;
begin
DBMS_SPACE.UNUSED_SPACE('SHAHNAZ', 'XYZ', 'TABLE', total_blocks => total_blocks ,
total_bytes => total_bytes,
unused_blocks =>unused_blocks,
unused_bytes => unused_bytes,
last_used_extent_file_id => last_used_extent_file_id,
last_used_extent_block_id =>last_used_extent_block_id ,
last_used_block => last_used_block );
DBMS_OUTPUT.PUT_LINE ( 'total_blocks '|| total_blocks);
DBMS_OUTPUT.PUT_LINE ( 'total_bytes '|| total_bytes);
DBMS_OUTPUT.PUT_LINE ( 'unused_block '|| unused_blocks);
DBMS_OUTPUT.PUT_LINE ( 'unused_bytes '|| unused_bytes);
end;
BUT it is giving like
total_blocks 2578
total_bytes 45298483
unused_block 0
unused_bytes 0
I know this table is taking too much time to query eventhough it is having minimum records!!!
Thanks.
|
|
|
Re: HWM in table [message #241685 is a reply to message #241662] |
Wed, 30 May 2007 07:11 ![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) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
This shows you the blocks that never been used (none here).
You may have blocks that have been used but are empty now.
Use dbms_space.space_usage.
Regards
Michel
[Updated on: Wed, 30 May 2007 07:11] Report message to a moderator
|
|
|
Re: HWM in table [message #241918 is a reply to message #241685] |
Thu, 31 May 2007 01:00 ![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) |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
Thanks
when i see the value of full_blocks it shows less blocks compared to dba_segments block column. Is the procedure dbms_space.space_usage is checking till last analyzed?
Thanks.
|
|
|
Re: HWM in table [message #241921 is a reply to message #241918] |
Thu, 31 May 2007 01:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You also have partially used blocks and empty blocks.
dbms_space.space_usage checks what is current.
Regards
Michel
|
|
|