OraFAQ Forum: Server Administration » Space management in 10g + high water mark

Home » RDBMS Server » Server Administration » Space management in 10g + high water mark (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Space management in 10g + high water mark [message #586796]

Mon, 10 June 2013 14:18

babuknb
Messages: 1736
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]

Report message to a moderator

Re: Space management in 10g + high water mark [message #586797 is a reply to message #586796]

Mon, 10 June 2013 14:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: Space management in 10g + high water mark [message #586798 is a reply to message #586797]

Mon, 10 June 2013 14:40

babuknb
Messages: 1736
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 ?

Report message to a moderator