Reduce tablespace files and save disk space

In my tube system, the database disk is often only about 20G, the previous is to delete the database server log, the log did a scheduled cleanup, found that table space is often insufficient table space, each time through the expansion of table space to solve the problem, but after several times, the server's disk is not enough, Start cleaning up the data in the database. After clearing the data, it was found that the actual occupancy of the table space is very small, with less than the disk, and then think of the disk used to release.

In the process of processing, found that the TRUNCATE table can only reduce the tablespace occupancy rate, and can not release the disk by consulting others, understand that there are the following methods to free the disk.

Method One:

To view the data file size for a tablespace

Select file_name,file_id,tablespace_name,bytes/1024/1024 mb,blocks from Dba_data_files where

Tablespace_name= ' Tfr_data ';

View data in the data file in the largest location

Select Max (block_id) from dba_extents where file_id=9;

How much space is actually needed to calculate the tablespace

Select 1354880*8/1024 from dual;

Set table space size to this value

ALTER DATABASE datafile '/u01/oradata/fossdb/datafile/o1_mf_tfr_data_9ymk9p5n_.dbf ' RESIZE 10600M;

This method has been written in my previous article. This method is also flawed, the table space actually does not have a lot of data, such as only 100M data, but this method resize, found only can resize to about 10G, and did not release a lot of space.

Method Two:

How to reduce the size of data file ORA-03297 processing method

ORA-03297: The file contains data that is used outside the requested RESIZE value

Recent history Database disk space is not enough, and there is a table space with 50 g capacity, but actually only occupies 100m of space,

Most data files can be adjusted using the ALTER TABLE table SHRINK SPACE Cascad when trying to adjust the size of one of the data files

Ra-03297:file contains used data beyond requested RESIZE value

This means that the file cannot be freed by lowering the HWM. ******************************************

Database version: Oracle 10.2.1

--Find the file number corresponding to the data file *********

Sql>select file#,name from V$datafile where name is like '%baseinfo.dbf ';

5/data/eucpdb/eucpdb/baseinfo.dbf

Find the largest block number in a file

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

1213833

--View database block size

Sql>show parameter Db_block_size

Db_block_size integer 8192

Calculate the position of the largest use block in the file

Sql>select 1213833*8/1024 from dual;

9483.0703125 M

--To verify the accuracy of the above procedure, do a test below

--Pre-adjustment data file size is 10000M

--Now adjust the database file to 9500M

Sql>alter DATABASE datafile '/data/eucpdb/eucpdb/baseinfo.dbf ' RESIZE 9500M

2/

Database has changed

--Adjust file to 9400m

Sql>alter DATABASE datafile '/data/eucpdb/eucpdb/baseinfo.dbf ' RESIZE 9400M

2/

sql> ALTER DATABASE datafile '/data/eucpdb/eucpdb/baseinfo.dbf ' RESIZE 9400M;

ALTER DATABASE datafile '/data/eucpdb/eucpdb/baseinfo.dbf ' RESIZE 9400M

*

An error occurred on line 1th:

ORA-03297: The file contains data that is used outside the requested RESIZE value

--it looks like the calculations above are accurate.

Workaround

See what objects are in the Tablespace

SELECT * from dba_segments where where tablespace_name= ' baseinfo ';

SELECT * from Dba_tables;

Select file_name,file_id,tablespace_name,bytes/1024/1024 mb,blocks from Dba_data_files where

Tablespace_name= ' Tfr_data ';

View file_id

Sql> Select Segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=5 and Block_id= ' 1213833 ';

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 changed.

Create a new tablespace and move the block_id higher tables out of the table space

sql> CREATE smallfile tablespace "Baseinfo_bak" datafile '/data/eucpdb/eucpdb/baseinfo_bak.dbf ' SIZE 100M 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. Enables the owning user of a table in the database to manipulate the newly created table space. Otherwise, you cannot move the table to a new table space

Move several tables with a higher block_id to the new table space

SELECT DISTINCT segment_name from dba_extents WHERE tablespace_name= ' Baseinfo ' and file_id=5 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 segment_type= ' TABLE ';

' Altertable ' | | segment_name| | ' Movetablespacebaseinfo_bak; '

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

ALTER TABLE enterpriseserviceinfo move tablespace baseinfo_bak;

ALTER TABLE Registryfeedetailex move tablespace baseinfo_bak;

ALTER TABLE Enterpriseroute move tablespace baseinfo_bak;

ALTER TABLE registryauthinfo move tablespace baseinfo_bak;

ALTER TABLE enterprisebaseinfo move tablespace baseinfo_bak;

Sql> ALTER TABLE enterpriseserviceinfo move tablespace baseinfo_bak;

ALTER TABLE Registryfeedetailex move tablespace baseinfo_bak;

ALTER TABLE Enterpriseroute move tablespace baseinfo_bak;

ALTER TABLE registryauthinfo move tablespace baseinfo_bak;

ALTER TABLE enterprisebaseinfo move tablespace baseinfo_bak;

The table has changed.

Sql>

The table has changed.

Sql>

The table has changed.

Sql>

The table has changed.

The following content appears in the alarm log, and the index needs to be rebuilt

Thu APR 24 14:20:21 2008

Some indexes or index [sub]partitions of table Eucpmanager. Enterprisebaseinfo have been marked unusable

Execute all rebuilds with the following statement of execution results

SELECT distinct ' alter INDEX ' | | segment_name| | ' REBUILD tablespace Baseinfo_bak; ' From dba_extents WHERE tablespace_name= ' Baseinfo ' and file_id=5 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/eucpdb/baseinfo.dbf ' RESIZE 1000m;

The database has changed.

The space for the database file has been adjusted successfully.

Sql>

--to move the watch back.

Sql> ALTER TABLE enterpriseserviceinfo move tablespace baseinfo;

ALTER TABLE Registryfeedetailex move tablespace baseinfo;

ALTER TABLE Enterpriseroute move tablespace baseinfo;

ALTER TABLE registryauthinfo move tablespace baseinfo;

ALTER TABLE enterprisebaseinfo move tablespace baseinfo;

ALTER TABLE registryinfo move tablespace baseinfo;

The table has changed.

Sql>

The table has changed.

--Rebuilding the index

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_enterpriseserviceinfo REBUILD tablespace baseinfo;

ALTER INDEX uni_registryauthinfo REBUILD tablespace baseinfo;

ALTER INDEX Pk_registryfeedetailex REBUILD tablespace baseinfo;

The index has changed.

Sql> SELECT COUNT (*) from dba_segments WHERE tablespace_name= ' Baseinfo_bak ';

COUNT (*)

----------

0

There are no more objects in the newly created table space. Delete now

sql> drop tablespace Baseinfo_bak;

Table space has been deleted.

The adjustment is over.

In fact, there are many adjustment methods, such as with Imp/exp

Reduce tablespace files and save disk space