Free disk space for data files after Oracle deletes data

 

A large amount of data was inserted into the database during testing, and the test user and all of its data were deleted after the test, but the data file was not scaled down. After reviewing the data and finding that this is the result of Oracle's "high water level", how do you lower the size of these data files? The solution is as follows:

Concept:

For more information about table spaces, see here for a detailed description of the storage structure of the Oracle database.

High water level: water Mark (HWM) is an indicator of segment (Segment) that defines the block level that the segment (Segment) has ever configured.

It is said that with the data insert, the data block of the segment used (Segment) is also increasing, when the high water level (HWM) increases. When the data is deleted (either delete or TRUNCATE table), the data block is reduced, but the high water level (HWM) does not decrease. When a large number of blank blocks (data block) exist under the High water level (HWM), a full table scan ("FTS") causes a lot of additional IO. Because the data block in the read segment (Segment) is read from the full table scan (FTS) to the High water level (HWM) before it ends. The High water level (HWM) is the dividing line (data block) in the segment (Segment), so the time spent in full-table scanning (FTS) is not reduced by the deletion of data, but increases. (The content of this query efficiency is still to be verified, the author did not personally verify .) However, it is certain that the high water level does not degrade as data is deleted. )

The correct way to lower the high water level is to first lower the HWM, then determine the actual occupancy size, and then resize the data file.

More data files, we use one of the larger files as a Demo, and other data files to do the same. The file I chose is: D:\oracle\product\10.2.0\oradata\orcl\USERS01. DBF 1.4GB or so.

1. Login Sqlplus:

Syntax: Sqlplus username/[email protected]:p Ort/sid

Example: Sqlplus system/[email PROTECTED]:1521/ORCL

2. Query the number of this data file:

Sql> Select file#, name from V$datafile;

file# NAME
------------------------------------------------------------------------------------------

1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf

2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf

3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf

4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf

As you can see, the number of the data file we want to manipulate is 4.

2. Query the number of the maximum data block for this data file according to the file ID: (It seems to be verified that this maximum number represents the number of data blocks in the data file.) )

Sql> select Max (block_id) from dba_extents where file_id=4;

MAX (block_id)
-------------
65673

3. Calculate the actual space occupied by the table space:

--Query the size of the data block, in bytes

Sql> Show parameter db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_block_size integer 8192

--8192 byte = 8 kb

--Next calculate the physical space occupied by the table space

Sql> Select 65673 * 8/1024 from dual;

65673*8/1024
------------
513.070313

--The actual physical space occupied is 513MB multipoint

4. The final step is to modify the size of our data file to be larger than the physical space actually occupied by this tablespace:

sql> ALTER DATABASE DataFile ' D:\oracle\product\10.2.0\oradata\orcl\USERS01. DBF ' Resize 600m;

The database has changed.

OK, the data file changed from 1.4GB before the change to 600MB. For other data files, do you know how to shrink?

Reprint: http://www.cnblogs.com/chuyuhuashi/p/3548260.html

Reference documents:

Oracle table Space (tablespaces) http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html

Oracle reduces HWM's centralized approach http://hi.baidu.com/wschao2005/item/5383388fb2e21fc8b17154b1

Modifying the Oracle data file size http://www.2cto.com/database/201204/126864.html

After Oracle deletes data, the space does not release how to resolve the??? http://bbs.csdn.net/topics/310046810

Free disk space for data files after Oracle deletes data