Oracle database table space sorting, recycling and release operations

This article brings you relevant knowledge about Oracle, which mainly introduces issues related to database table space sorting, recycling and release operations, including lowering the high water level of table space and deleting Release and other contents, let’s take a look at it below, I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

When using Oracle for input testing, we will create a lot of test data, However, after deletion, the high water level of the table space still cannot be automatically lowered, resulting in blank data in the table space, which affects the performance of the table space and also takes up too much storage space.

1. Reduce the high water level of table space

1. View the table space and corresponding physical file name

The code is as follows:

select 
b.file_id 物理文件号,
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.file_name,b.bytes 
order by b.tablespace_name

It can be intuitive based on the utilization rate Determine which table spaces can be released.

2. Empty the Recycle Bin

The code is as follows:

-- 清除用户回收站
purge recyclebin;
-- 清除全库回收站
purge dba_recyclebin;

Clear the data left during the deletion process.

3. Organize the table space

The code is as follows:

alter tablespace tablespace_name coalesce;

Integrate the fragments of the table space to increase the continuity of the table space

4.Generate RESIZE code

The code is as follows:

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents where file_id in
(select b.file# From v$tablespace a ,v$datafile b
where a.ts#=b.ts# and a.name='tablespace_name')
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5

After the above code is executed, a SQL code corresponding to table space RESIZE can be generated. At this time, just run the corresponding SQL code.

2. Release when deleting

1. Clear the table data and release the table space

The code is as follows:

-- 清空表数据
truncate table table_name;
-- 释放表空间
alter table table_name deallocate UNUSED KEEP 0;

Clear the table data first, then The table still exists, Truncate does not support rollback, and you cannot truncate a table with foreign keys. If you want to delete it, you must first cancel the foreign key, and then delete it;
Note that if KEEP 0 is not added, the table space will not be deleted. will be released.

2. Migrate table data

When the following errors occur, we can not only solve the problem by organizing the table space or clearing the recycle bin, but also migrate all the table data in the table space to be released. to other table spaces, release the space, and then migrate back to the original table space.

	--需移动的表数据
   select DISTINCT 'alter table '|| owner||'.'||segment_name || ' move tablespace user_test;' from dba_extents where segment_type='TABLE' and file_id=4;
	--需移动的索引数据
   select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild tablespace user_test;' from dba_extents where segment_type='INDEX' and file_id=4;
   --需移动的分区表数据
   select DISTINCT 'alter table '|| owner||'.'|| segment_name || ' move partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='TABLE PARTITION' and file_id=4;
   --需移动的分区表索引数据
   select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='INDEX PARTITION' and file_id=4;

The file_id in the above code can be compared and changed by viewing the table space. After the above code is executed, the corresponding automatically generated SQL code can be obtained. At this time, the corresponding SQL code can be run to migrate the table data

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Oracle database table space sorting, recycling and release operations. For more information, please follow other related articles on the PHP Chinese website!