Go up

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, 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