Oracle tablespace shrinking Scheme

For Table space shrinking, Oracle only provides the extended function, but does not. Therefore, to achieve this requirement, you must first create an intermediate tablespace and then

For Table space shrinking, Oracle only provides the extended function, but does not. Therefore, to achieve this requirement, you must first create an intermediate tablespace and then

Application background:
In some cases, because the preliminary design is not fully considered, the tablespace pre-creation is too large, far exceeding the actual size. As a result, the need to shrink the space occupied by the tablespace arises.

Solution:
For Table space shrinking, Oracle only provides the extended function, but does not. Therefore, to achieve this requirement, you must first create an intermediate tablespace, and then migrate the data in the tablespace to this tablespace.

Solution:
1. Find all data objects in 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 100 m;

3. Rebuilding or transferring the corresponding data based on 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 basically cover 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 % ';

The following figure shows how to migrate 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 cleared tablespace;
Drop tablespace dbs_old including contents and datafiles;
The following steps are simple.

5. If the table space name is required, use the original tablespace name to create a suitable tablespace.

6. migrate the data in the destination tablespace back to the created tablespace.