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

Oracle table space contraction method is what, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Application background:

In some cases, due to the lack of comprehensive consideration in the previous design, the pre-construction of the tablespace is too large, far beyond the actual size. As a result, there is a need to shrink the tablespace, that is, to shrink the occupied space of the tablespace.

Treatment plan:

For tablespace shrinkage, Oracle only provides expanded functionality, not contraction. Therefore, to achieve this requirement, we can only create an intermediate tablespace and then migrate the data in the tablespace to be shrunk to this tablespace.

Treatment method:

1. Find all the data objects under the tablespace

Select segment_type, partition_name, segment_name from dba_segments

2. Create a target space (not mandatory, but recommended).

Create tablespace dbs_temp datafile'd:\ dbs_temp01.dbf' size 100m

3. Reconstruct or transfer the corresponding data according to the object type

For table:

Alter table xx move tablespace dbs_temp

For partition table:

Alter table xx move partition xx1 tablespace dbs_temp

For index:

Alter index ixx rebuild tablespace dbs_temp

For the lob field:

Alter table xx move lob (col_name) store as (tablespace dbs_temp)

In general, the following statement can cover almost all data objects:

Select segment_type, segment_name, partition_name

Case segment_type

When 'TABLE' then' alter table'| | owner | |'. | | segment_name | | 'move tablespace dbs_temp;'

When 'INDEX' then' alter index'| | owner | |'. | | segment_name | | 'rebuild tablespace dbs_temp;'

When 'INDEX PARTITION' then' alter index'| | owner | |'. | | segment_name | | 'rebuild tablespace dbs_temp;'

When 'TABLE PARTITION' then' alter table'| | owner | |'. | | segment_name | | 'move partition' | | partition_name | | 'tablespace dbs_temp;' sqltext

From dba_segments

Where tablespace_name = 'FUND_TABLE'

And segment_type not like 'LOB%'

Here is the migration of the lob field

Select table_name, column_name, 'alter table' | | owner | |'. | | table_name | | 'move lob (' | column_name | |') store as (tablespace dbs_temp); 'sqltext

From dba_lobs

Where tablespace_name = 'FUND_TABLE'

4. Delete the empty tablespace

Drop tablespace dbs_old including contents and datafiles

The following steps are simple, so we will not enumerate them one by one.

5. If a tablespace name is required, create a tablespace of the appropriate size again with the original tablespace name.

6. Migrate the data in the target tablespace back to the new final tablespace.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.