| truncate not resetting high water mark [message #287755] |
Thu, 13 December 2007 06:35  |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Hi, I am issuing a truncate on the table. But it is not re-setting the high water mark. I used select sum(bytes) from dba_segments
where segment_name = <tablename>
But this is same after the issuing the truncate. Do you know any possible reason behind this.
Thanks,
Arun
|
|
|
|
|
|
| Re: truncate not resetting high water mark [message #287759 is a reply to message #287755] |
Thu, 13 December 2007 06:49  |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Hi,
I am issuing a truncate on the table.
But it is not re-setting the high water mark.
I did following
SELECT SUM(BYTES) FROM dba_segments
WHERE SEGMENT_NAME = <tablename>
TRUNCATE TABLE <tablename>
SELECT SUM(BYTES) FROM dba_segments
WHERE SEGMENT_NAME = <tablename>
But sum bytes is same after the issuing the truncate.
Do you know any possible reason behind this. How can I achieve the resetting high water mark using TRUNCATE.
(hope this is inline with posting standards)
Thanks,
Arun
|
|
|
|
| Re: truncate not resetting high water mark [message #287760 is a reply to message #287759] |
Thu, 13 December 2007 06:53 |
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
|
Senior Member |
|
|
Did you analyze your tables after truncate.
and what is your block size. and please post exact sql output instead of sql commands.
SQL> create table big as select * from all_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats('SYSTEM','BIG');
PL/SQL procedure successfully completed.
SQL> select sum(bytes) from user_segments where segment_name='BIG';
SUM(BYTES)
----------
5242880
SQL> truncate table big;
Table truncated.
SQL> select sum(bytes) from user_segments where segment_name='BIG';
SUM(BYTES)
----------
65536
Oracle Version:10gr1.
[Updated on: Thu, 13 December 2007 06:58] Report message to a moderator |
|
|
|
|
|
| Re: truncate not resetting high water mark [message #287764 is a reply to message #287759] |
Thu, 13 December 2007 07:00 |
 |
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: |
But sum bytes is same after the issuing the truncate.
|
- You use KEEP STORAGE clause
- You have min extents set to the current size
- You have a uniform size tablespace and the quantum is the current table size
- You have a system manage tablespace and your current tablesize is the minimal one
- Your table is partitioned and each partition is at minimal size
I think I can find a couple of more reasons why your table size does not decrease.
Regards
Michel
[Updated on: Thu, 13 December 2007 07:01] Report message to a moderator |
|
|
|
| Re: truncate not resetting high water mark [message #287781 is a reply to message #287764] |
Thu, 13 December 2007 07:54 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Hi Michel
1. I have tried with and without REUSE STORAGE. Still the same
2. Min extents is set to 1 and initial extent set to 5242880
3. I DON'T KNOW what this means. But this tablespace has got min extent 1, max extents to 2147483645, initial extent set to 5242880, and next extent set to 5242880.
4. Extent management set to 'LOCAL' and segment_space_management to 'MANUAL on this tablespace.
5. This is not an partitioned table.
May be these are not the reasons.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: truncate not resetting high water mark [message #288071 is a reply to message #287755] |
Fri, 14 December 2007 12:16 |
arunprasad_bh
Messages: 32
Registered: June 2007
|
Member |
|
|
Hi Found the problem and a workable solution.
This is a known issue for oracle not resetting HWM when you truncate for versions before 10g. The workaround is issuing following command
ALTER TABLE MOVE TABLESPACE <same tablespace name>
It works for me as these tables are regularly(hourly) truncated and of small in size.
Thanks
Aruna
|
|
|
|
|
|
| Re: truncate not resetting high water mark [message #288145 is a reply to message #288080] |
Sat, 15 December 2007 01:35 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
| Quote: |
This is a known issue for oracle not resetting HWM when you truncate for versions before 10g.
|
A table must full scan upto its HWM
Following this example
SQL> create table example
2 pctfree 90
3 pctused 10
4 as
5 select * from all_objects;
Table created.
SQL> delete from example
2 where object_id!=50;
49501 rows deleted.
SQL>commit;
Commit complete.
SQL> set autotrace on
SQL> select object_name from example;
OBJECT_NAME
------------------------------
I_CDEF1
Execution Plan
----------------------------------------------------------
Plan hash value: 328913759
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1284 (1)| 00:00:16 |
| 1 | TABLE ACCESS FULL| EXAMPLE | 1 | 17 | 1284 (1)| 00:00:16 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
7195 consistent gets
0 physical reads
35684 redo size
419 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL>
You can see the 7195 consistent gets which explain that the table example is big.
You would either drop the table and repopulate or truncate it and repopulate to lower the HWM. However, if an index on it's type would "fix" the problem as well.
SQL> create table example1 as select * from example;
Table created.
SQL> truncate table example;
Table truncated.
SQL> insert into example select * from example1;
1 row created.
SQL> drop table example1;
Table dropped.
SQL> set autotrace on
SQL> select object_name from example;
OBJECT_NAME
------------------------------
I_CDEF1
Execution Plan
----------------------------------------------------------
Plan hash value: 328913759
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EXAMPLE | 1 | 17 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Whenever you use the statement alter table X remove..., consider about rebuilding index afterwards
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
