Shulou(Shulou.com)06/01 Report--

In my system, the database disk is often only about 20g. In the past, the log in the database server was deleted. After the log was cleaned regularly, it was found that the tablespace was often insufficient. Every time, the problem is solved by expanding the tablespace, but after adding several times, the server's disk is not enough, so we begin to clean up the data in the database. After cleaning up the data, I found that the actual occupancy of the table space was very small, and I didn't need so many disks, so I thought of releasing the disks I used.

In the process of processing, it is found that the truncate table can only reduce the table space occupancy, but not release the disk. By consulting others, we learned that there are the following ways to free the disk.

Method 1:

View the data file size corresponding to the tablespace

Select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 mb,BLOCKS from dba_data_files where

TABLESPACE_NAME='TFR_DATA'

View the data in the data file in the largest location

Select max (block_id) from dba_extents where file_id=9

Calculate how much space a tablespace actually needs

Select 1354880mm 8amp 1024 from dual

Set the tablespace size to this value

ALTER DATABASE DATAFILE'/ u01 RESIZE oradataUniverse FOSSDB qqdatafile.o1mfqnxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

This method has been written about in my previous article. This method is also flawed, there is actually not a lot of data in the table space, for example, only 100m data, but when this method resize, it is found that it can only resize to about 10G, and does not release a lot of space.

Method 2:

The processing method of reducing the size of data File and reporting to ORA-03297

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

Recently, the history database is short of disk space, and one table space has 50 G capacity, but actually occupies only 100 m of space.

Most of the data files can be adjusted after using ALTER TABLE table SHRINK SPACE CASCAD. When trying to resize one of the data files, report

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

Indicates that this file cannot free up space 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 like'% BASEINFO.dbf'

5 / data/eucpdb/eucpdb/BASEINFO.dbf

Find the largest block number in the file

SQL > select max (block_id) from dba_extents where file_id=5; *

1213833

-- View the database block size

SQL > show parameter db_block_size

Db_block_size integer 8192

Calculate the location of the maximum usage block in the file

SQL > select 12138333.8ax 1024 from dual

9483.0703125 M

In order to verify the accuracy of the above, let's do an experiment.

-- the data file size is 10000m before adjustment

-- now adjust the database file to 9500m

SQL > ALTER DATABASE DATAFILE'/ data/eucpdb/eucpdb/BASEINFO.dbf' RESIZE 9500m

2 /

The database has changed

-adjust the 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 1:

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

It seems that the above calculation is accurate

Solution method

See what objects are in the tablespace

Select * from DBA_SEGMENTS 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 table space and move several tables with higher block_id 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 owner of the table in the database to manipulate the newly created tablespace. Otherwise, the table cannot be moved to the new table space.

Move several tables with higher block_id to the new tablespace

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

Perform all rebuilds with the following statement that executes the result

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 of the database file has been adjusted successfully.

SQL >

-- move the removed 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.

-- rebuild 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 objects in the new tablespace. Now delete it.

SQL > drop tablespace baseinfo_bak

The tablespace was deleted.

However, in the process of operation, there is a very difficult problem.

SELECT COUNT (*) FROM DBA_EXTENDS WHERE TABLESPACE_NAME='USERS'

COUNT (*)

-

0

Select max (block_id) from dba_extents where file_id=9

There is nothing to look at in this way, but an error is reported when resize, and the ORA-03297: the file contains data used outside the requested RESIZE value.

Treatment method:

SQL > SELECT COUNT (*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='USERS'

To view the DBA_SEGMENTS,DBA_TABLES,DBA_INDEX tables here, DBA_ extensions is too small to be comprehensive. When viewing, we find that although the data has been deleted, it remains in the Recycle Bin. Execute the following command purge dba_recyclebin in the PLSQL command window. After execution, you can resize.

This is absolutely less than Baidu, it is not easy to ask others to think of here. We still need experience.

To see that the object in the tablespace is 0

The adjustment is over at this point.

In fact, there are many ways to adjust, such as using imp/exp and so on.

Exception handling

Check which user a table belongs to

Select owner from dba_tables where table_name=' table name'

SQL > create tablespace wulili datafile'/ u01 size autoextend on

Create tablespace wulili datafile'/ u01 size autoextend on

ERROR at line 1:

ORA-01119: error in creating database file'/ u01max appAccord oradatascarp dpwebscarp wulili.dbf'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

Solution:give exact path of datafile inplace of $ORACLE_HOME.

Like

Create Tablespace CLOUD_DATA datafile'/ u01 size applink oradata size 1000m

SQL > alter table tb_auto shrink space

Alter table tb_auto shrink space

*

An error occurred on line 1:

ORA-10636: ROW MOVEMENT is not enabled

SQL > alter table tb_auto enable row movement

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.