Oracle database consumes a large amount of hard disk space general workaround

There are typically several reasons why Oracle databases take up too much hard disk space (for example, Oracle 10.2.0):

1. Oracle's installation directory ... \oracle\product\10.2.0\admin\orcl\udump directory, tracking log files occupy a lot of hard disk space;

2. Oracle's installation directory ... \oracle\product\10.2.0\oradata\orcl directory, undo tablespace files consume a lot of hard disk space.

For the above two questions, the workaround is as follows.

　　The first problem-solving method:

The Udump directory below is the Oracle tracking log file, which is recommended to remove if you don't normally care. Does not affect the database . In addition, enter in Sqlplus:

Alter system set Sql_trace =false;

If you need to keep the trace log file, you can set a limit. The command line is as follows:

Alter system set max_dump_file_size= ' 100m ';

Note: The max_dump_file_size default size value is a block of data, so if the size is limited to 100M, a single quotation mark is required.

　　Workaround for the second problem:

You need to reclaim the undo table space. First create a new, excessive tablespace UNDOTBS02, point the undo tablespace to UNDOTBS02, and drop the original tablespace UNDOTBS01, delete the undotbs01.dbf file, and free up disk space. Next, create a new tablespace UNDUTBS01, and then point the undo tablespace to the Undotbs01,drop excessive tablespace UNDOTBS02 of the new table space.

Here's how:

1) Use DBA to login Sqlplus

2) Build over-table space UNDOTBS02

Create undo tablespace undotbs02 datafile ' e:\undotbs02.dbf ' size 100M;

3) Toggle the undo Tablespace to the new undo table space

Alter system set UNDO_TABLESPACE=UNDOTBS02 Scope=both;

4) Delete the original undo table space

Drop tablespace undotbs01 including contents;

5) Delete the undotbs01.dbf file on the hard disk ... \oracle\product\10.2.0\oradata\orcl directory.

6) Create a new tablespace undotbs01, switch the undo tablespace to the new undotbs01, and delete the UNDOTBS02 table space. method as above.

At this point, undo table space Reclamation has been completed.

The above action is to temporarily resolve the undo table space too large method. Oracle's AUM (Auto Undo Management) has often been extended, not shrunk (shrink) since birth, and usually we can set enough Undo table space size and then cancel its auto-extended properties.