No question at this time
DBA Top 10
1 A. Kavsek 12200
2 M. Cadot 7700
3 B. Vroman 6000
3 P. Wisse 6000
5 J. PĂ©ran 2000
6 . Lauri 1300
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48476
Total active users1471
Act. users last 24h2
Act. users last hour0
Registered user hits last week107
Registered user hits last month462
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, 2768 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, 2768 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, 2768 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