Home » RDBMS Server » Server Administration » Can't resize datafile: ORA-03297
Can't resize datafile: ORA-03297 [message #636098] Thu, 16 April 2015 15:21 Go to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
Hello everyone! This is my first post here.

I am working with an Oracle 11G database and we use one single tablespace for data. This tablespace has 3 datafiles and actually they have 32G, 32G and 10G size respectivelly.
Last week I dropped a huge table and the datafile_2 now has only 5GB occupation. I would like to "move" extents from datafile_3 to this one and also resize both of then. (maybe drop datafile_3)

I took a look at dba_extents to see the HWM at datafile_2 and it was still near the 32GB mark. Then I found the last extents on the datafile and did the move/shrink/"index rebuild" procedure. Now I can see that the HWM is about 5G.

Then I tried:

Alter database datafile '+data/MyDatabase/datafiles/datafile_2' resize 6G;

And received "ORA-03297: file contains used data beyond requested RESIZE value".

Tried again with bigger values: 10G, 15G, 20G, 30G without success. Also did a "purge recyclebin" and nothing.

Moving objects on datafile_3 seems to get it worst. They are moved entirely to datafile_3 instead of going to the free space on datafile_2.

So... After all that I tried another solution: create a new tablespace and move all objects within Datafile_3 to it. Then delete Datafile_3 and move all objects back.

I can see now that Datafile_3 is empty according to DBA_EXTENTS.

Then tried:

alter tablespace MyTablespace drop datafile '+DATA/mydtabase/datafile/datafile_3';

Then "ORA-03262: the file is non-empty"

Well, maybe I can just resize it to a small file...

alter database datafile '+DATA/mydtabase/datafile/datafile_3' resize 1G;

Again "ORA-03297" (even if I use bigger values).


Finally I decided to sum all the free extents from Datafile_3 using DBA_FREE_SPACE and compare it to the value at DBA_DATA_FILES. There is a difference of 1024KB somewhere! I am very confused... Confused

Some tip plz?

Thanks in advance!
Re: Can't resize datafile: ORA-03297 [message #636100 is a reply to message #636098] Thu, 16 April 2015 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

The Oracle database is essentially oblivious to the actual OS datafiles that comprise any tablespace.
Oracle provide you no ability to control into which datafile new data is placed.
From my perspective at a minimum a new tablespace will be required; which can temporary or permanent.
One option would be to just move all existing objects into the new tablespace.
After that you can drop the old tablespace & the job is finished.
The other option is to identify those object that reside in FILE2 & only move them to the new tablespace.
After a file has been added to any tablespace, it can not be removed. But it can be SHRUNK to a very small size.
Then you need to decide if you should move the object that are now in the new tablespace back to the old tablespace.

GOOG LUCK!
Re: Can't resize datafile: ORA-03297 [message #636102 is a reply to message #636100] Thu, 16 April 2015 18:30 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
Thank you BlackSwan for your answer and links.

I am really considering to create a new tablespace and move everything there. I am just a little bit afraid because there are a lot of objects and I fear something going wrong. Smile

I still can not understand why I am not able to resize an empty datafile. I just discovered that as I was using SYS user I should use
purge dba_recyclebin

But without success.

I will try to do that next saturday so I can bring the database down.
Re: Can't resize datafile: ORA-03297 [message #636103 is a reply to message #636102] Thu, 16 April 2015 18:55 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>I still can not understand why I am not able to resize an empty datafile
do NOT say what you do. "Not able to resize" is NOT an Oracle error code & message.

using COPY & PASTE you need to SHOW exactly what you do & how Oracle actually responds.

>But without success.
100% devoid of any actionable detail
Re: Can't resize datafile: ORA-03297 [message #636104 is a reply to message #636103] Thu, 16 April 2015 18:58 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
03297, 00000, "file contains used data beyond requested RESIZE value"
// *Cause:  Some portion of the file in the region to be trimmed is 
//          currently in use by a database object
// *Action: Drop or move segments containing extents in this region prior to
//          resizing the file, or choose a resize value such that only free
//          space is in the trimmed.
Re: Can't resize datafile: ORA-03297 [message #636108 is a reply to message #636104] Thu, 16 April 2015 20:26 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
I understood. I am really very sorry! Confused

Well this is what I did.

First checked datafile3 size;
SELECT file_name, 
       tablespace_name, 
       bytes / ( 1024 * 1024 ) MB_Size 
FROM   dba_data_files 
WHERE  file_id = 9;


The output:
FILE_NAME		                	TABLESPACE_NAME     MB_SIZE
-----------------------------		        ---------------    --------
+DATA/mytablespace/datafile/datafile_3		   MYTABLESPACE        8192 


Then checked for extents on datafile_3:
SELECT owner, 
       segment_name, 
       block_id, 
       blocks 
FROM   dba_extents 
WHERE  file_id = 9 
ORDER  BY block_id DESC; 

no rows selected


Double checking:
SELECT Max(block_id) 
FROM   dba_extents 
WHERE  file_id = 9; 

MAX(BLOCK_ID)
-------------
         NULL


Let me purge recyclebin:
purge dba_recyclebin;

DBA recyclebin purged


Ok, no segment on datafile_3 and no recyclebin. Now I will try to reclaim just about 500MB:
ALTER DATABASE datafile '+DATA/mytablespace/datafile/datafile_3' resize 7500m; 

Finally:
SQL ERROR: ORA-03297: file contains used data beyond requested RESIZE value


Checking free size:
SELECT Sum(bytes) / ( 1024 * 1024 ) AS MB_Free 
FROM   dba_free_space 
WHERE  file_id = 9; 

   MB_FREE
----------
      8191


The free space is 1MB less then the datafile total size.
I don't know if it is just some kind of header or if it there is still data on the datafile.

As I said, I can't resize even it seeming to be empty. Maybe I forgot something?
Re: Can't resize datafile: ORA-03297 [message #636109 is a reply to message #636108] Thu, 16 April 2015 20:43 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member

  1  select file_name, file_id, tablespace_name
  2* from dba_data_files
SQL> /

FILE_NAME                                                           FILE_ID TABLESPACE_NAME
---------------------------------------------------------------- ---------- ------------------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf                          4 USERS
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf                        3 UNDOTBS1
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf                         2 SYSAUX
/home/oracle/app/oracle/oradata/orcl/system01.dbf                         1 SYSTEM
/home/oracle/app/oracle/oradata/orcl/example01.dbf                        5 EXAMPLE
/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf            6 APEX_1930613455248703

6 rows selected.

SQL> select file_id, max(block_id) from dba_extents group by file_id order by 1;

   FILE_ID MAX(BLOCK_ID)
---------- -------------
         1        106240
         2        147832
         3          3712
         4         28672
         5        164904
         6           800

6 rows selected.

SQL> 



Consider doing similar to above

Oracle is too dumb to lie about errors.
Re: Can't resize datafile: ORA-03297 [message #636110 is a reply to message #636109] Thu, 16 April 2015 21:12 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
SELECT file_name, 
       file_id, 
       tablespace_name 
FROM   dba_data_files 
ORDER  BY file_id;


FILE_NAME                                                   FILE_ID             TABLESPACE_NAME              
-------------------------------------------------           -------             ---------------
+DATA/mytablespace/datafile/system.271.810056497                1               SYSTEM                         
+DATA/mytablespace/datafile/sysaux.263.810056497                2               SYSAUX                         
+DATA/mytablespace/datafile/undotbs1.264.810056497              3               UNDOTBS1                       
+DATA/mytablespace/datafile/users.261.810056497                 4               USERS                          
+DATA/mytablespace/datafile/datafile.262.810057775              5               MYTABLESPACE                        
+DATA/mytablespace/datafile/datafile_idx.267.810057825          6               MYTABLESPACE_IDX                    
+DATA/mytablespace/datafile/perfstat.467.810768761              7               PERFSTAT                    
+DATA/mytablespace/datafile/datafile_2                          8               MYTABLESPACE                        
+DATA/mytablespace/datafile/datafile_3                          9               MYTABLESPACE                        
+DATA/mytablespace/datafile/datafile_aux.2568.877209477         10              MYTABLESPACE_AUX



SELECT file_id, 
       Max(block_id) 
FROM   dba_extents 
GROUP  BY file_id 
ORDER  BY 1; 


  FILE_ID MAX(BLOCK_ID)
---------- -------------
         1        235264 
         2        101120 
         3        208128 
         4           792 
         5       4188928 
         6       1730432 
         7        114560 
         8        285440 
        10       1633664                       

Re: Can't resize datafile: ORA-03297 [message #636111 is a reply to message #636110] Thu, 16 April 2015 21:36 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
SQL> WITH top_end 
     AS (SELECT owner, 
                segment_name, 
                segment_type, 
                Max(block_id) blockid 
         FROM   dba_extents 
         WHERE  file_id = 4 
         GROUP  BY owner, 
                   segment_name, 
                   segment_type 
         ORDER  BY 4 DESC) 
SELECT owner, 
       segment_name, 
       segment_type, 
       block_id 
FROM   dba_extents 
WHERE  file_id = 4 
       AND block_id IN (SELECT blockid 
                        FROM   top_end) 
       AND ROWNUM < 10 

/   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21  SQL> 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE         BLOCK_ID
------------------------------ ------------------------------ ------------------ ----------
XDBEXT                         SYS_NTr8Sify7b5pLgQBmQXa5h2g== NESTED TABLE             9544
XDBEXT                         SYS_NTr8Sify7c5pLgQBmQXa5h2g== NESTED TABLE             9560
SCOTT                          DATA_STAGING_OTN               TABLE                   28672
XDBEXT                         DICOM_METADATA_TABLE           TABLE                   26608
SCOTT                          DATA_STAGING_EVO               TABLE                   14800
SCOTT                          DATA_STAGING_PTN               TABLE                   12928
SCOTT                          DATA_STAGING_XQY               TABLE                   15488
SCOTT                          DATA_STAGING_REPOS             TABLE                   15536
XDBEXT                         SYS_NTr8Sify7u5pLgQBmQXa5h2g== NESTED TABLE            10168

9 rows selected.



use FILE_ID=9 when you run SQL above
Re: Can't resize datafile: ORA-03297 [message #636130 is a reply to message #636111] Fri, 17 April 2015 06:44 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
Hi. Good morning.

Here is it:
WITH top_end 
     AS (SELECT owner, 
                segment_name, 
                segment_type, 
                Max(block_id) blockid 
         FROM   dba_extents 
         WHERE  file_id = 9 
         GROUP  BY owner, 
                   segment_name, 
                   segment_type 
         ORDER  BY 4 DESC) 
SELECT owner, 
       segment_name, 
       segment_type, 
       block_id 
FROM   dba_extents 
WHERE  file_id = 9 
       AND block_id IN (SELECT blockid 
                        FROM   top_end) 
       AND ROWNUM < 10


no rows selected
Re: Can't resize datafile: ORA-03297 [message #636131 is a reply to message #636130] Fri, 17 April 2015 07:22 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member

  1  select segment_type, count(*) from dba_extents
  2  where file_id = 4
  3  group by segment_type
  4* order by 1
SQL> /

SEGMENT_TYPE         COUNT(*)
------------------ ----------
INDEX                     332
LOBINDEX                  415
LOBSEGMENT                471
NESTED TABLE               34
TABLE                     299


you replace FILE_ID=4 with 9
Re: Can't resize datafile: ORA-03297 [message #636133 is a reply to message #636131] Fri, 17 April 2015 07:36 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
SELECT segment_type,
  COUNT(*)
FROM dba_extents
WHERE file_id = 9
GROUP BY segment_type
ORDER BY 1;

no rows selected   

Re: Can't resize datafile: ORA-03297 [message #636134 is a reply to message #636133] Fri, 17 April 2015 07:50 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
Re: Can't resize datafile: ORA-03297 [message #636135 is a reply to message #636134] Fri, 17 April 2015 08:27 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
Wow! Nice report!

VALUE
--------------------------------------------------------------------------------
8192


VALUE
--------------------------------------------------------------------------------
8192


                                                          Smallest
                                                              Size   Current    Poss.
FILE_NAME                                                     Poss.     Size  Savings
--------------------------------------------------         -------- -------- --------
+DATA/mytablespace/datafile/datafile2                         2,231   32,767   30,536
+DATA/mytablespace/datafile/datafile3                             1    8,192    8,191
+DATA/mytablespace/datafile/undotbs1.264.810056497            1,690    5,484    3,794
+DATA/mytablespace/datafile/datafile_aux.2568.877209477      12,774   13,490      716
+DATA/mytablespace/datafile/datafile_blob                     5,891    6,548      657
+DATA/mytablespace/datafile/perfstat_ts.467.810768761           896    1,000      104
+DATA/mytablespace/datafile/sysaux.263.810056497                791      830       39
+DATA/mytablespace/datafile/users.261.810056497                   7       40       33
+DATA/mytablespace/datafile/system.271.810056497              1,839    1,850       11

                                                            Smallest
                                                                Size  Current    Poss.
FILE_NAME                                                      Poss.     Size  Savings
--------------------------------------------------          -------- -------- --------
+DATA/mytablespace/datafile/datafile_idx.267.810057825        13,520   13,520        0
+DATA/mytablespace/datafile/datafile.262.810057775            32,767   32,767        0
                                                                              --------
sum                                                                             44,081

11 rows selected.
Re: Can't resize datafile: ORA-03297 [message #636136 is a reply to message #636135] Fri, 17 April 2015 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
ALTER DATABASE DATAFILE '+DATA/mytablespace/datafile/datafile2' RESIZE 2200M;
Re: Can't resize datafile: ORA-03297 [message #636140 is a reply to message #636136] Fri, 17 April 2015 10:28 Go to previous messageGo to next message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
ALTER DATABASE DATAFILE '+DATA/mytablespace/datafile/datafile2' RESIZE 2200M;

Error Report -
SQL error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.
Re: Can't resize datafile: ORA-03297 [message #636141 is a reply to message #636140] Fri, 17 April 2015 10:39 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
alter database enable row movement;
alter tablespace MYTABLESPACE shrink;

what results when you do as above?
Re: Can't resize datafile: ORA-03297 [message #636145 is a reply to message #636141] Fri, 17 April 2015 13:43 Go to previous message
mark_valley
Messages: 9
Registered: April 2015
Junior Member
Can I use those commands with a tablespace? I use it a lot with tables but never tried something like that.

alter database enable row movement;

SQL error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:


alter tablespace MYTABLESPACE shrink;

SQL error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:


Well, I just finished to send all data to other tablespaces. My major problems was some columns with the deprecated "long" datatype. I created a new blob tablespace to help organize the things. Now data+blob tablespaces are occupying about 32GB.

Do you think it is more secure to set the old tablespace offline for some time before dropping it? Just to be sure that nothing will "go wrong"?

And thank you so much! I Dont't know if it is worthwhile to continue trying to figure out what is happening. If you are curious we can continue.

Regards!
Previous Topic: Database Patch (20127071) - version 11.2.0.4.12 Contradicting Instructions
Next Topic: prevent high cost queries
Goto Forum:
  


Current Time: Mon Oct 22 00:15:59 CDT 2018