Shulou(Shulou.com)05/31 Report--

This article is about how to solve the problem of Oracle resizing tablespace ORA-03297 errors. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Oracle resize tablespace-ORA-03297: the file contains data used outside the requested RESIZE value

After restoring a large backup database to this machine, delete the large tables inside (such as file tables, log tables, etc.), Oracle data files can be automatically expanded when there is data, but can not be automatically shrunk, resulting in a waste of storage space.

It originated from the fact that I deleted a large table in database, which caused a lot of space waste and wanted to recycle space.

If you directly modify the size of the data file, you may encounter the following error: ORA-03297: the file contains data used outside the requested RESIZE value

Reprint website: http://blog.sina.com.cn/s/blog_54eeb5d901000bvg.html

SQL > ALTER DATABASE DATAFILE'D:\ ORACLE\ ORADATA\ ICAPP\ IC_DATA6.ORA' RESIZE 300m

ALTER DATABASE DATAFILE'D:\ ORACLE\ ORADATA\ ICAPP\ IC_DATA6.ORA' RESIZE 300m

*

ERROR is on line 1:

ORA-03297: the file contains data used outside the requested RESIZE value

But

SQL > select d.filewriting name as free_byte d.filewriting authoring d.bytesUniverse 1024According 1024 as dudes bytejiansum (f.bytes/1024/1024) as free_byte

2 from dba_data_files d,dba_free_space f

3 where d.file_id=f.file_id and d.file_id=18

4 group by d.file_name,d.file_id,d.bytes/1024/1024

FILE_NAME FILE_ID D_BYTE FREE_BYTE

D:\ ORACLE\ ORADATA\ ICAPP\ IC_DATA6.ORA 18 1536 1482.0625

You can see that in fact, ID=18 files only use about 50m, but the data is distributed in (in a certain order) 50m or even beyond 300m, so although we can see that only about 50m of space is used here, it cannot be resize datafile.

To do this, to change the small data file, we need to move the table and index on the file as follows:

1. Tidy up the table space before moving the table

SQL > alter tablespace ic_data coalesce

2. Find the tables and indexes related to the data files of ID=18 in dba_extents

SQL > select segment_name,partition_name,segment_type

2 from dba_extents

3 where file_id=18

3. Move the table and index on the id=18 file

SQL > set heading off

SQL > set echo off

SQL > set feedback off

SQL > set termout on

SQL > spool d:\ aaa.sql

/ / move the table

SQL > select DISTINCT 'alter table' | | segment_name | | 'move tablespace test_space;' from dba_extents where segment_type='TABLE' and file_id=18

/ / move the index

SQL > select DISTINCT 'alter index' | | segment_name | | 'rebuild tablespace test_space;' from dba_extents where segment_type='INDEX' and file_id=18

/ / move the partition table

SQL > select DISTINCT 'alter table' | | segment_name | | 'move partition' | | partition_name | | 'tablespace test_space;' from dba_extents where segment_type='TABLE PARTITION' and file_id=18

/ / move the partition index

SQL > select DISTINCT 'alter index' | | segment_name | | 'rebuild partition' | | partition_name | | 'tablespace test_space;' from dba_extents where segment_type='INDEX PARTITION' and file_id=18

SQL > spool off

Then execute aaa.sql, making sure that test_space has enough space to hold the data

First, create a new test_space space, move the data to the new tablespace, reduce the original tablespace, move the data back to the original tablespace, and delete the test_space space.

In fact, you don't have to move all the data, but the overall test is whether the data other than 300m has been moved, so it is still convenient to move all the data.

4. After moving all the data, you can datafile resize.

SQL > ALTER DATABASE DATAFILE'D:\ ORACLE\ ORADATA\ ICAPP\ IC_DATA6.ORA' RESIZE 300m

The database has changed.

5. Move the data in the original tablespace ic_data back, change the name of the tablespace in aaa.sql to ic_data and then execute it, and then drop tablespace test_space including contents and datafiles.

-Segmentation line-

After practice, the above method can deal with the problem of ORA-03297: file containing data used outside the requested RESIZE value.

However, if a tablespace is used by multiple Oracle users, specify the user when exporting the sql file, otherwise an error will be reported when executing the sql file.

Thank you for reading! On "how to solve the problem of Oracle resizing tablespace ORA-03297 error" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.