We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


move high water mark on datafiles

pavan27 asked
Medium Priority
Last Modified: 2013-12-18

i have dropped the some partitions on table. and i got lot of space at tablespace. when i am trying to resize the the existing datafile i am getting this problem.

alter database datafile '/opt/mis2/oracle/data/BMVZWSTG/db4/data/BMVZWSTG/BMVZWSTG_largedata1k.dbf' resize 20000M;
alter database datafile '/opt/mis2/oracle/data/BMVZWSTG/db4/data/BMVZWSTG/BMVZWSTG_largedata1k.dbf' resize 20000M
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

i though it is the problem with high water mark.  how to down the high water mark.

how to shrink the table partitions. please provide help me to get space back to file system level

TS                ID FILE                                                                                  (M)           (M)     PCT
--------------- ---- --------------------------------------------------------------------------- ------------- ------------- -------
LARGEDATA          6 /opt/mis2/oracle/data/BMVZWSTG/db2/data/BMVZWSTG/BMVZWSTG_largedata1a.dbf       30,000.00     11,024.00   36.75
                   8 /opt/mis2/oracle/data/BMVZWSTG/db8/data/BMVZWSTG/BMVZWSTG_largedata1b.dbf       30,000.00     11,104.00   37.01
                  11 /opt/mis2/oracle/data/BMVZWSTG/db8/data/BMVZWSTG/BMVZWSTG_largedata1c.dbf       30,000.00     11,648.00   38.83
                  12 /opt/mis2/oracle/data/BMVZWSTG/db8/data/BMVZWSTG/BMVZWSTG_largedata1d.dbf       30,000.00     12,544.00   41.81
                  16 /opt/mis2/oracle/data/BMVZWSTG/db8/data/BMVZWSTG/BMVZWSTG_largedata1e.dbf       29,000.00     12,256.00   42.26
                  20 /opt/mis2/oracle/data/BMVZWSTG/db7/data/BMVZWSTG/BMVZWSTG_largedata1f.dbf       30,000.00     12,368.00   41.23
                  23 /opt/mis2/oracle/data/BMVZWSTG/db7/data/BMVZWSTG/BMVZWSTG_largedata1g.dbf       30,000.00     12,512.00   41.71
                  26 /opt/mis2/oracle/data/BMVZWSTG/db7/data/BMVZWSTG/BMVZWSTG_largedata1h.dbf       30,000.00     12,624.00   42.08
                  28 /opt/mis2/oracle/data/BMVZWSTG/db7/data/BMVZWSTG/BMVZWSTG_largedata1i.dbf       30,000.00     12,864.00   42.88
                  30 /opt/mis2/oracle/data/BMVZWSTG/db7/data/BMVZWSTG/BMVZWSTG_largedata1j.dbf       29,000.00     12,512.00   43.14
                  31 /opt/mis2/oracle/data/BMVZWSTG/db4/data/BMVZWSTG/BMVZWSTG_largedata1k.dbf       30,000.00     13,424.00   44.75
                  32 /opt/mis2/oracle/data/BMVZWSTG/db4/data/BMVZWSTG/BMVZWSTG_largedata1l.dbf       30,000.00     13,392.00   44.6

Open in new window

Watch Question

Production Manager / Application Support Manager
Use the below query to resize your data files...

column file_id and file_name can be understood.
column total_size_m is the total space allocated through that file.
column free_size_m gives the free space in that file.
column used_size_m gives the used space in the file.
column resize_to gives the space to which you can resize the data file. here i have given 50MB as buffer to be on the safer side to resize which can take care of immediate space growth. you can modify it accordingly to 200MB or 500MB according to your database space requirements.

SELECT dfs.file_id, ddf.file_name,
(ddf.bytes)/1024/1024 total_size_M,
(ddf.bytes/1024/1024 ) - free_size_M used_size_M,  
((ddf.bytes)/1024/1024 - free_size_M + 50 ) resize_to
FROM dba_data_files ddf,
(SELECT file_id, SUM(bytes)/1024/1024 free_size_M
FROM dba_free_space
GROUP BY file_id) dfs
WHERE ddf.file_id = dfs.file_id;

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.