Solution to reduce the size of the data file and report the ORA-03297

[From] http://space.itpub.net/48361/viewspace-253106

 

Recent historyDatabaseThe disk space is insufficient. However, one tablespace has 50 GB of space, but it only occupies MB of space,
After using alter table table shrink space cascad, most data files can be adjusted. When trying to adjust the size of one of the data files, it is reported that
The RA-03297: file contains used data beyond requested RESIZE value
This file cannot be released by reducing hwm.

Database Version:Oracle10.2.1
-- Find the file number corresponding to the data file
SQL> select file #, name from v $ datafile where name like '% BASEINFO. dbf ';
5/data/eucpdb/BASEINFO. dbf

Find the largest block number in the file
SQL> select max (block_id) from dba_extents where file_id = 5;

1213833

-- View the database block size
SQL> show parameter db_block_size

Db_block_size integer 8192

Calculate the maximum block occupied by the file.
SQL> select 1213833*8/1024 from dual;

9483.0703125 m

-- In order to verify the accuracy of the above practice, we will conduct an experiment below

-- Adjust the data file size to 10000 MB
-- Adjust the database file to 9500 MB
SQL> alter database datafile '/data/eucpdb/baseinfo. dbf' resize 9500 m
2/

Database changed

-- Adjust the file to 9400 MB

SQL> alter database datafile '/data/eucpdb/baseinfo. dbf' resize 9400 m
2/

SQL> alter database datafile '/data/eucpdb/baseinfo. dbf' resize 9400 m;
Alter database datafile '/data/eucpdb/baseinfo. dbf' resize 9400 m
*
Row 3 has an error:
ORA-03297: The file contains data used outside the requested resize Value

-- It seems that the above calculation is accurate.

SQL> col segment_name format a30
SQL & gt; SET LIN 200
SQL> select segment_name, segment_type, tablespace_name, extent_id, bytes, blocks from dba_extents where file_id = 5 and block_id = '20140901 ';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
------------------------------------------------------------------------------------------------------------
Registryinfo table baseinfo 25 524288 64

SQL>

SQL> ALTER TABLE eucpmanager. registryinfo shrink space cascade;

The table has been changed.

Create a new tablespace and remove several tables with relatively high block_id from the tablespace.

SQL> Create smallfile tablespace "baseinfo_bak" datafile '/data/eucpdb/baseinfo_bak.dbf' size 100 m logging extent management local segment space management auto
2/

The tablespace has been created.

SQL> alter user eucpmanager quota unlimited on baseinfo_bak;

The user has changed.

Move several tables with relatively higher block_id to the new tablespace.

Select distinct segment_name from dba_extents where tablespace_name = 'baseinfo' and file_id = 5 and block_id> 1159985 and segment_type = 'table ';

SQL> select distinct 'alter table' | segment_name | 'move tablespace baseinfo_bak; 'From dba_extents where tablespace_name = 'baseinfo' and file_id = 5 and block_id> 1159985 and segment_type = 'table ';

'Altertable' | segment_name | 'movetablespacebaseinfo _ bak ;'
Bytes ----------------------------------------------------------------------------------------------------------------------------
Alter table paiiseserviceinfo move tablespace baseinfo_bak;
Alter table registryfeedetailex move tablespace baseinfo_bak;
Alter table incluiseroute move tablespace baseinfo_bak;
Alter table registryauthinfo move tablespace baseinfo_bak;
Alter table incluisebaseinfo move tablespace baseinfo_bak;

SQL> alter table into iseserviceinfo move tablespace baseinfo_bak;
Alter table REGISTRYFEEDETAILEX move tablespace baseinfo_bak;
Alter table incluiseroute move tablespace baseinfo_bak;
Alter table REGISTRYAUTHINFO move tablespace baseinfo_bak;
Alter table incluisebaseinfo move tablespace baseinfo_bak;
The table has been changed.

SQL>
The table has been changed.

SQL>
The table has been changed.

SQL>
The table has been changed.

The following content appears in the alarm log, and the index needs to be rebuilt.
Thu Apr 14:20:212008
Some indexes or index [sub] partitions of table EUCPMANAGER. incluisebaseinfo have been marked unusable

Re-execute all the statements in the following execution results
SELECT distinct 'alter Index' | SEGMENT_NAME | 'rebuild TABLESPACE BASEINFO_BAK; 'FROM DBA_EXTENTS WHERE TABLESPACE_NAME = 'baseinfo' AND FILE_ID = 5 and block_id> 1159985 and segment_type = 'index ';
 
 
Alter INDEX PK_ENTERPRISEBASEINFO rebuild tablespace BASEINFO_BAK;
Alter INDEX PK_REGISTRYINFO rebuild tablespace BASEINFO_BAK;
Alter INDEX PK_REGISTRYFEEDETAILEX rebuild tablespace BASEINFO_BAK;
Alter INDEX UNI_REGISTRYAUTHINFO rebuild tablespace BASEINFO_BAK;
Alter INDEX UNI_REGISTRYAUTHINFO rebuild tablespace BASEINFO_BAK;
Alter INDEX UNI_REGISTRYAUTHINFO rebuild tablespace BASEINFO_BAK;
Alter INDEX UNI_REGISTRYAUTHINFO rebuild tablespace BASEINFO_BAK;
Alter INDEX UNI_REGISTRYAUTHINFO rebuild tablespace BASEINFO_BAK;

Modify the data file size again

SQL> select max (block_id) from dba_extents where file_id = 5;

MAX (BLOCK_ID)
-------------
3209

SQL> ALTER DATABASE DATAFILE '/data/eucpdb/BASEINFO. dbf' RESIZE 1000 m;

The database has been changed.

The database file space has been adjusted successfully.

SQL>

-- Move the removed table back.

SQL> alter table into iseserviceinfo move tablespace baseinfo;
Alter table REGISTRYFEEDETAILEX move tablespace baseinfo;
Alter table incluiseroute move tablespace baseinfo;
Alter table REGISTRYAUTHINFO move tablespace baseinfo;
Alter table incluisebaseinfo move tablespace baseinfo;
Alter table registryinfo move tablespace baseinfo;
The table has been changed.

SQL>
The table has been changed.

-- Re-Indexing
SQL>
SQL>
SQL> ALTER INDEX PK_REGISTRYINFO REBUILD TABLESPACE BASEINFO;
Alter index AK_REGISTRYCODE_REGISTRYINFO rebuild tablespace baseinfo;
Alter index PK_ENTERPRISEBASEINFO rebuild tablespace baseinfo;
Alter index pk_policiseserviceinfo rebuild tablespace baseinfo;
Alter index UNI_REGISTRYAUTHINFO rebuild tablespace baseinfo;
Alter index PK_REGISTRYFEEDETAILEX rebuild tablespace baseinfo;

The index has been changed.

SQL> SELECT COUNT (*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'baseinfo _ Bak ';

COUNT (*)
----------
0

There are no objects in the new tablespace. Delete now

SQL> drop tablespace baseinfo_bak;

The tablespace has been deleted.

The adjustment has ended.
In fact, there are many adjustment methods, such as using imp/exp.
If you have any shortcomings in my practice, contact us. If you have any better solutions, please feel free to contact us.