No question at this time
The DBA-Village forum
as RSS feed
Site StatisticsEver registered users | 48684 | Total active users | 1328 | Act. users last 24h | 1 | Act. users last hour | 0 | Registered user hits last week | 67 | Registered user hits last month | 224 |
|
Go up
High Water mark
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 |
Subject: |
High Water mark |
Author: |
sathish kumar, India |
Date: |
Jun 27, 2012, 20:01, 3700 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, 3700 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, 3700 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
|