No question at this time
DBA Top 10
1 M. Cadot 10400
2 A. Kavsek 9400
3 B. Vroman 4800
4 P. Wisse 4300
5 J. Schnackenberg 2900
6 J. PĂ©ran 2000
7 . Lauri 1000
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
9 T. Boles 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48526
Total active users1410
Act. users last 24h6
Act. users last hour0
Registered user hits last week104
Registered user hits last month341
Go up

High Water mark
Next thread: sun foundation
Prev thread: BLOB column Size for each record

Message Score Author Date
Hi Friends, I reduced the datafile size and try...... sathish kumar Jun 27, 2012, 20:01
SQL> select file_name, 2 ceil( (nvl(hwm,1)*&&b...... sathish kumar Jun 27, 2012, 20:02
Hi, Just curious, are you doing this exercise s...... BenBart BartBen Jun 28, 2012, 04:12

Follow up by mail Click here


Subject: High Water mark
Author: sathish kumar, India
Date: Jun 27, 2012, 20:01, 2967 days ago
Os info: Any OS
Oracle info: oracle Any Version
Message: Hi Friends,

I reduced the datafile size and try to release the free blocks.
I tried like below.

select a.tablespace_name, a.file_name, ceil( (nvl(hwm,1)*8192)/1024/1024 )
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id) b
where a.file_id = b.file_id(+) AND A.TABLESPACE_NAME='TBS'
order by tablespace_name, file_name;


TABLESPACE_NAME FILE_NAME Mo
------------------------------ --------------------- --------
TBS /oradata1/oradata/testdb/TBS02.dbf 24576
TBS /oradata3/oradata/testdb/TBS01.dbf 25600
TBS /oradata2/oradata/testdb/TBS04.dbf 14652


SQL> SELECT BYTES/1024/1024,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TBS';

BYTES/1024/1024 FILE_NAME
--------------- --------------
25600 /oradata3/oradata/testdb/TBS01.dbf
21600 /oradata2/oradata/testdb/TBS04.dbf
24576 /oradata1/oradata/testdb/TBS02.dbf

SQL> select file_name,
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
3 ceil( blocks*&&blksize/1024/1024) currsize,
4 ceil( blocks*&&blksize/1024/1024) -
5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
6 7 ( select file_id, max(block_id+blocks-1) hwm
8 from dba_extents
9 group by file_id ) b
10 where a.file_id = b.file_id(+) and
a.TABLESPACE_NAME='TBS'; 11
Enter value for blksize: 8192
old 2: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
new 2: ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
old 3: ceil( blocks*&&blksize/1024/1024) currsize,
new 3: ceil( blocks*8192/1024/1024) currsize,
old 4: ceil( blocks*&&blksize/1024/1024) -
new 4: ceil( blocks*8192/1024/1024) -
old 5: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
new 5: ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings


FILE_NAME SMALLEST CURRSIZE SAVINGS
--------------------------------------------- ---------- ---------- ----------
/oradata2/oradata/testdb/TBS04.dbf 14652 21600 6948
/oradata3/oradata/testdb/TBS01.dbf 25600 25600 0
/oradata1/oradata/testdb/TBS02.dbf 24576 24576 0

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS AS EMPTY,AVG_SPACE,AVG_ROW_LEN FROM ALL_tables where owner='HR' and table_name='TEST1';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
77866 2318 1266 995 209


SQL> SQL> ALTER TABLE HR.TEST1 DEALLOCATE UNUSED KEEP 1000K;

Table altered.

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS AS EMPTY,AVG_SPACE,AVG_ROW_LEN FROM ALL_tables where owner='HR' and table_name='TEST1';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
77866 2318 1266 995 209

SQL> analyze table HR.TEST1 compute statistics;

Table analyzed.

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS AS EMPTY,AVG_SPACE,AVG_ROW_LEN FROM ALL_tables where owner='HR' and table_name='TEST1';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
77900 2318 178 990 209
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: High Water mark
Author: sathish kumar, India
Date: Jun 27, 2012, 20:02, 2967 days ago
Message: SQL> select file_name,
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
3 ceil( blocks*&&blksize/1024/1024) currsize,
4 ceil( blocks*&&blksize/1024/1024) -
5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
6 7 ( select file_id, max(block_id+blocks-1) hwm
8 from dba_extents
9 group by file_id ) b
10 where a.file_id = b.file_id(+) and
a.TABLESPACE_NAME='TBS'; 11
old 2: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
new 2: ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
old 3: ceil( blocks*&&blksize/1024/1024) currsize,
new 3: ceil( blocks*8192/1024/1024) currsize,
old 4: ceil( blocks*&&blksize/1024/1024) -
new 4: ceil( blocks*8192/1024/1024) -
old 5: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
new 5: ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings

FILE_NAME SMALLEST CURRSIZE SAVINGS
--------------------------------------------- ---------- ---------- ----------
/oradata2/oradata/testdb/TBS04.dbf 14652 21600 6948
/oradata3/oradata/testdb/TBS01.dbf 25600 25600 0
/oradata1/oradata/testdb/TBS02.dbf 24576 24576 0


select a.tablespace_name, a.file_name, ceil( (nvl(hwm,1)*8192)/1024/1024 )
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id) b
where a.file_id = b.file_id(+) AND A.TABLESPACE_NAME='TBS'
order by tablespace_name, file_name;


TABLESPACE_NAME FILE_NAME Mo
------------------------------ --------------------- --------
TBS /oradata1/oradata/testdb/TBS02.dbf 24576
TBS /oradata3/oradata/testdb/TBS01.dbf 25600
TBS /oradata2/oradata/testdb/TBS04.dbf 14652


1)My doubt is even if reduce 1000k, Why none of the datafile freespace is increased?


2)Also please let me know the steps i followed above is correct or not?

3)Please give the query to find the High Water Mark of
1)Specific Table
2)Specific Datafile
3)Entire Database

Please help me.

Thanks in Advance.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: High Water mark
Author: BenBart BartBen, Philippines
Date: Jun 28, 2012, 04:12, 2966 days ago
Message: Hi,

Just curious, are you doing this exercise so you will have more freespace on the tablespace in question?

I don't see the point of playing around with resizing datafiles if eventually they will increase again due to usage.



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here