Shrink Oracle10g data files and migrate data objects across tablespaces

1. shrinking of Oracle10g Database Files: 1.1 problem: a database contains millions of data records. After using the Delete statement to Delete most data

1. shrinking of Oracle10g Database Files: 1.1 problem: a database contains millions of data records. After using the Delete statement to Delete most data

1. Shrink Oracle10g Database files:

1.1 questions:

A database contains millions of data records. After the Delete statement is used to Delete most of the data, the space occupied by the database files will not be reduced to free up extra space.

For example, you can use the RESIZE statement to adjust the data file size as follows:

> Alter database datafile 'd:/Oracle10g/oradata/ORDB/NEWDB. ORA 'resize 100 M;

An error is reported: ORA-03297: The file contains the data used outside the requested RESIZE value.

This mainly involves Oracle logical storage and segment space management, and data distribution in database blocks.

Oralce has a High Water Mark (High Water level line), which indicates the maximum Water level line for Oracle Objects to use space.

After data is inserted, HWM increases. After the Delete statement deletes data, HWH does not decrease.

When the HWH database block distribution is out of the adjustment range, an error is reported using the RESIZE command.

1.2 method:

According to information found on the internet, after moving Tablespace to Move tables and indexes, You can reset the table's HWH and then use Resize to reduce the database files.

However, I tried it multiple times in Oracle10g, but it was not successful. In addition, if the database has a Long field, you cannot use the Move command.

Fortunately, it's Oracle10g and there's shrink! :)

The Shrink syntax is as follows:

--------------------

Alter table Shrink space [ | Compact | cascade];
Alter table Shrink space compcat;
Note: scale down the table and move the data in the block together, but the High Water Mark remains unchanged.

Alter table Shrink space;
Description: scale down the table to reduce the High Water Mark.

Alter table Shrink space cascade;
Note: scale down the table, lower the High Water Mark, and contract the related indexes.

Alter index idxname shrink space;
Description: Index reduction

--------------------

1.3 operation:

Run the shrink command once on all tables in all tablespaces. The shrink command must be constructed according to the shrink syntax.

To build the shrink command, you can use the following SQL statement to query the File_ID of the table space database file:

---------------------

Select * from dba_data_files;

---------------------

In addition, you must enable the row migration function of the data table before executing shrink. The SQL statement is as follows:

---------------------

Alter table Tbl_Name enable row movement;

---------------------
Note: This SQL statement will cause the objects (such as stored procedures, packages, views, etc.) that reference the table Tbl_Name to become invalid. You need to re-compile the statement to check whether the statement is valid.

Construct the shrink command and create the create_shrink. SQL file. Its content is as follows:

-------------------
Set heading off;
Set echo off;
Set feedback off;
Set termout on;
Spool c:/shrink. SQL;
Select 'alter table THAMS. '| table_name | 'Enable row movement;' from user_tables where tablespace_name = 'tops ';
Select 'alter table THAMS. '| table_name | 'shrink space CASCADE;' from user_tables where tablespace_name = 'thams ';
Select 'alter index THAMS. '| segment_name | 'shrink space;' from dba_extents where segment_type = 'index' and file_id = 14;
Spool off;
-------------------

Log on to the database using THAMS, the default user of the table space, and run create_shrink. SQL to obtain the shrink. SQL file.

Log on to the database as sys/system, run the shrink. SQL file, and view the tablespace in OEMC. The "used" ratio of the tablespace in the database is greatly reduced.

After EXP/IMP, a database file with a small footprint is obtained.