move high water mark on datafiles

Posted on 2009-02-12
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

Question by:pavan27
    1 Comment
    LVL 28

    Accepted Solution

    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;

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now