Freeing data files for disk space after Oracle deletes data ____oracle

A large amount of data was inserted into the database at the time of the test, and the test user and all its data were deleted after the test was completed, but the data file did not shrink. After looking at the data to find that this is the Oracle "high water level" caused by, then how to the size of these data files down. The solution is as follows:

Concept:

Table space related knowledge see here for a detailed description of the storage structure of the Oracle database.

High water level: Hi Water Mark (HWM) is an indicator of paragraph (Segment) that defines the block level (Segment) that was once configured.

It is said that with the insert of the data, the block of data used (Segment) is also increasing, when the high water level (HWM) also increases. When data is deleted (either delete or TRUNCATE table) while the data block that is occupied has been reduced, the high water level (HWM) does not fall. When a large number of blank chunks (data blocks) exist under high water levels (HWM), a full table scan (Scan, FTS) results in a lot of additional IO. The data block in the read segment (Segment) reads until the High water level (HWM) is completed when the full table scan (FTS). High water level (HWM) is the segment (Segment) in the data block is not used by the dividing line, so the full table scan (FTS) time will not be reduced because of the deletion of data, but will increase. (The content of this query efficiency needs to be verified, the author did not personally verify.) However, it is certain that the high water level does not fall as the data is deleted. )

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

More data files, we use one of the larger file as a Demo, 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/password@hostname:port/sid

Example: Sqlplus SYSTEM/ORCL@LOCALHOST: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. According to the file ID query the data file maximum data block number: (It seems that this maximum number can represent the number of blocks in the data file, this is still to be verified.) )

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

--then compute the physical space occupied by the table space

Sql> Select 65673 * 8/1024 from dual;

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

--The physical space that is actually occupied is more than 513MB.

4. The final step is to modify the size of our data file to a larger extent than the physical space actually occupied by the table space:

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 to 600MB before it was modified. For other data files, you know how to shrink.

Reference documents:

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

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

Modify Oracle data File size http://www.2cto.com/database/201204/126864.html

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