Home » RDBMS Server » Server Administration » Space management in 10g + high water mark (10.2.0.4)
Space management in 10g + high water mark [message #586796] Mon, 10 June 2013 14:18 Go to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

Hello,

I have one tablespace called U01. This tablepspace contains 31 data files. Due to high water mark I was unable to most datafiles. Since my database running onair application they will not provide me downtime to move the tables.

Is there anyway to fix the high water mark without getting downtime window? almost 700+g space unused. I need to reuse them asap because running out of space with in asm diskgroup.


SQL> SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
  2  round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
  3  round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
  4    5    6  FROM (
  7  SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
  8  MAX(BYTES) LARGEST,COUNT(*) CHUNKS
  9  FROM SYS.DBA_FREE_SPACE A
 10  GROUP BY TABLESPACE_NAME
 11  UNION
 12  SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
 13   14  GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
 15  where A.TABLESPACE_NAME=upper('&ts')
 16  GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
 17  /
Enter value for ts:
old  15: where A.TABLESPACE_NAME=upper('&ts')
new  15: where A.TABLESPACE_NAME=upper('')

no rows selected

SQL> /
Enter value for ts: U01
old  15: where A.TABLESPACE_NAME=upper('&ts')
new  15: where A.TABLESPACE_NAME=upper('U01')

TABLESPACE_NAME                Tot size MB Tot Free MB      %FREE      %USED   MAX_FREE CHUNKS_FREE
------------------------------ ----------- ----------- ---------- ---------- ---------- -----------
U01                           793830      701252         88         12       3968        2096

SQL>



COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT   df.file# , df.NAME file_name, df.bytes / 1024 / 1024 allocated_mb,
         ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
               used_mb,
         NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
    FROM v$datafile df, dba_free_space dfs
   WHERE df.file# = dfs.file_id(+) and dfs.TABLESPACE_NAME='&tsname'
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY free_space_mb

 

    FILE# FILE_NAME                                                    ALLOCATED_MB    USED_MB FREE_SPACE_MB
---------- ------------------------------------------------------------ ------------ ---------- -------------
        76 +DATA/U01/+DATAfile/U01.353.812984681              2048.00     906.13       1141.88
        77 +DATA/U01/+DATAfile/U01.354.812984917              2048.00     906.13       1141.88
        70 +DATA/U01/+DATAfile/U01_28.dbf                     3968.13     276.76       3691.38
        68 +DATA/U01/+DATAfile/U01.345.800251797              5448.07     522.63       4925.44
        69 +DATA/U01/+DATAfile/U01_27.dbf                     6144.00     468.69       5675.31
[color=red]        35 +DATA/U01/+DATAfile/U01.304.722992259             28226.13    4328.70      23897.44
        40 +DATA/U01/+DATAfile/U01.300.722992265             29244.13    4464.45      24779.69
        57 +DATA/U01/+DATAfile/U01_16.dbf                    27776.13    2682.76      25093.38
        58 +DATA/U01/+DATAfile/U01_17.dbf                    27776.13    2543.63      25232.50
        14 +DATA/U01/+DATAfile/U01.312.722992253             30000.00    4748.63      25251.38
        42 +DATA/U01/+DATAfile/U01.303.722992273             30000.00    4701.50      25298.50
        41 +DATA/U01/+DATAfile/U01.309.722992265             30000.00    4574.75      25425.25
        32 +DATA/U01/+DATAfile/U01.310.722992259             30000.00    4543.06      25456.94
        38 +DATA/U01/+DATAfile/U01.328.722992267             30000.00    4454.88      25545.13
        43 +DATA/U01/+DATAfile/U01.295.722992271             30000.00    4444.19      25555.81
        60 +DATA/U01/+DATAfile/U01_19.dbf                    27776.13    2155.63      25620.50
        36 +DATA/U01/+DATAfile/U01.311.722992259             30000.00    4376.50      25623.50
        39 +DATA/U01/+DATAfile/U01.306.722992265             30000.00    4334.63      25665.38
        31 +DATA/U01/+DATAfile/U01.330.722992253             30000.00    4318.88      25681.13
        37 +DATA/U01/+DATAfile/U01.297.722992259             30000.00    4260.94      25739.06
        30 +DATA/U01/+DATAfile/U01.298.722992253             30000.00    4107.63      25892.38
        44 +DATA/U01/+DATAfile/U01.326.722992271             30000.00    4066.75      25933.25
        67 +DATA/U01/+DATAfile/U01_26.dbf                    27776.13    1696.76      26079.38
        46 +DATA/U01/+DATAfile/U01.317.722992295             30720.00    4406.94      26313.06
        59 +DATA/U01/+DATAfile/U01_18.dbf                    30559.13    2336.63      28222.50
        62 +DATA/U01/+DATAfile/U01_21.dbf                    30720.00    2470.44      28249.56
        61 +DATA/U01/+DATAfile/U01_20.dbf                    30720.00    2284.63      28435.38
        63 +DATA/U01/+DATAfile/U01_22.dbf                    30720.00    2087.81      28632.19
        64 +DATA/U01/+DATAfile/U01_23.dbf                    30720.00    1775.63      28944.38
        65 +DATA/U01/+DATAfile/U01_24.dbf                    30720.00    1697.81      29022.19
        66 +DATA/U01/+DATAfile/U01_25.dbf                    30720.00    1633.44      29086.56
[/color]
Re: Space management in 10g + high water mark [message #586797 is a reply to message #586796] Mon, 10 June 2013 14:24 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
It appears you have a (minor?) design issue.

Why so many datafile with so much FREE space?
Why is the free space not being used by Oracle?
Re: Space management in 10g + high water mark [message #586798 is a reply to message #586797] Mon, 10 June 2013 14:40 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

Ok.. Here you go..

There was one LOB table in my tablespace.. Recently this table was removed from tablespace. It was around 600+ gb. In tablespace I can see the free space but in each datafile there was HWM.

Question. How to release this HWM from tablespace or datafile ?
Re: Space management in 10g + high water mark [message #586799 is a reply to message #586798] Mon, 10 June 2013 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without application downtime, you can't.

Regards
Michel
Re: Space management in 10g + high water mark [message #586800 is a reply to message #586799] Mon, 10 June 2013 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
http://oracle-magician.blogspot.com/2008/04/function-of-high-water-mark-in-oracle.html
Re: Space management in 10g + high water mark [message #586811 is a reply to message #586800] Tue, 11 June 2013 00:04 Go to previous message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think OP was talking about file HWM not segment HWM.

Regards
Michel
Previous Topic: Value of sga_target too small
Next Topic: Problem patching from Oracle 11.2.0.2 to 11.2.0.3 + changePerm.sh script
Goto Forum:
  


Current Time: Thu Apr 17 22:42:56 CDT 2014

Total time taken to generate the page: 0.22357 seconds