move high water mark on datafiles

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

Question by:pavan27
1 Comment
LVL 28

Accepted Solution

Naveen Kumar earned 2000 total points
ID: 23630436
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by 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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

809 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