Home » RDBMS Server » Server Administration » truncate not resetting high water mark
truncate not resetting high water mark [message #287755] Thu, 13 December 2007 06:35 Go to next message
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 #287756 is a reply to message #287755] Thu, 13 December 2007 06:36 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Post here whatever said & did.
Please don't forget to use code tags and format your query.

[Updated on: Thu, 13 December 2007 06:37]

Report message to a moderator

Re: truncate not resetting high water mark [message #287759 is a reply to message #287755] Thu, 13 December 2007 06:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #287763 is a reply to message #287755] Thu, 13 December 2007 06:59 Go to previous messageGo to next message
arunprasad_bh
Messages: 32
Registered: June 2007
Member
Tried the same. But sum(bytes) is same (rather high 1095761920) and the table is having 0 records. Total row size can be 3250 bytes.
Re: truncate not resetting high water mark [message #287764 is a reply to message #287759] Thu, 13 December 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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 Go to previous messageGo to next message
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 #287792 is a reply to message #287781] Thu, 13 December 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

May be these are not the reasons.

I don't know as I don't know your table size, you never post it.

What is your allocation type (see dba_tablespaces)?
By the way, instead of saying what you see, copy and paste it as well as your queries.

Regards
Michel
Re: truncate not resetting high water mark [message #287799 is a reply to message #287755] Thu, 13 December 2007 09:12 Go to previous messageGo to next message
arunprasad_bh
Messages: 32
Registered: June 2007
Member
Hi Michel,

Select * from dba_tablespaces
where tablespace_name = 'GRW_DATA'

Results are attached in the file(2.xls).
Not all the tables are behaving the same way. I have a table that releases the blocks. I could not see any differences in terms of storage parameters (from dba_tablespaces).

Thanks,
Aruna
  • Attachment: 2.xls
    (Size: 5.50KB, Downloaded 124 times)
Re: truncate not resetting high water mark [message #287807 is a reply to message #287799] Thu, 13 December 2007 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't download xls file, post a txt one.
There may be not difference tablespace storage parameters but in table ones like size.
And you still don't post table size.

Regards
Michel
Re: truncate not resetting high water mark [message #287978 is a reply to message #287755] Fri, 14 December 2007 03:32 Go to previous messageGo to next message
arunprasad_bh
Messages: 32
Registered: June 2007
Member
text file attached. The table size is 1.4gb

Thanks
  • Attachment: 2.txt
    (Size: 0.34KB, Downloaded 153 times)
Re: truncate not resetting high water mark [message #287983 is a reply to message #287978] Fri, 14 December 2007 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Align the columns!
Post the information I requested 3 or 4 times.
Do you expect I still go on to help you if you don't post what I requested on and on?

Regards
Michel
Re: truncate not resetting high water mark [message #288071 is a reply to message #287755] Fri, 14 December 2007 12:16 Go to previous messageGo to next message
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 #288080 is a reply to message #288071] Fri, 14 December 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

This is a known issue for oracle not resetting HWM when you truncate for versions before 10g.

So well known that I don't know it.

Regards
Michel
Re: truncate not resetting high water mark [message #288145 is a reply to message #288080] Sat, 15 December 2007 01:35 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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
Re: truncate not resetting high water mark [message #288149 is a reply to message #288145] Sat, 15 December 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This has nothing to do with the question. Read again at least the title. You quoted a sentence and posted something that has no relation.

2/ "alter table X remove" this command does not exist.

Regards
Michel
Re: truncate not resetting high water mark [message #288288 is a reply to message #287755] Sun, 16 December 2007 22:45 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

It is good to know that.
Previous Topic: Will Drop User cascade deallocate the space.
Next Topic: to change .dbf file to another location
Goto Forum:
  


Current Time: Sat Dec 10 16:39:41 CST 2016

Total time taken to generate the page: 0.08459 seconds