Alter database datafile resize (+ release space)

Resize datafile fails because some object extended to the edge of datafile (the largest place ).
The following SQL allows us to find the first five objects at the edge.
Select *
From (
Select owner, segment_name,
Segment_type, block_id
From dba_extents
Where file_id =
(Select file_id
From dba_data_files
Where file_name =: File) -- Use Your datafile instead
Order by block_id DESC
)
Where rownum <= 5

-- The data is not found in my database. It is stored here first and will be used later.

 

Run the following script to obtain the resize command for the corresponding file.

SQL> variable blocksize number;
SQL> begin execute immediate 'select value from V $ parameter where name = ''db _ block_size ''' into: blocksize; end;
2/
SQL> Print: blocksize;
SQL> select 'alter database datafile' |
2 file_name | '''resize' |
3 Ceil (nvl (hwm, 1) *: blocksize/1024/1024) |'m; 'cmd
4 From dba_data_files,
5 (select file_id,
6 max (block_id + blocks-1) hwm
7 from dba_extents
8 group by file_id) B
9 where a. file_id = B. file_id (+) and B. file_id in (7, 8, 10 );

 

The following is a summary. Similar to the above script function, you can obtain alter cmd.

 

Select
A. file_id,
A. file_name
File_name,
Ceil (nvl (hwm, 1) * blksize)/1024/1024) smallest,
Ceil (blocks * blksize/1024/1024) currsize,
Ceil (blocks * blksize/1024/1024 )-
Ceil (nvl (hwm, 1) * blksize)/1024/1024) savings,
'Alter database datafile' | file_name | '''resize' |
Ceil (nvl (hwm, 1) * blksize)/1024/1024) |'m; 'cmd
From
Dba_data_files,
(
Select file_id, max (block_id + blocks-1) hwm
From dba_extents
Group by file_id
) B,
(
Select to_number (value) blksize
From v $ Parameter
Where name = 'db _ block_size'
)
Where
A. file_id = B. file_id (+)
And
Ceil (blocks * blksize/1024/1024)-Ceil (nvl (hwm, 1) * blksize)/1024/1024)> 0
Order by 5 DESC

 

 

After alter
To release the bucket,

You can also export exp/IMP expdp/impdp (no practice for self-understanding: you can export the entire database, or export the data of the tablespace to be cleared, and then drop tablespace data01 including contents and datafiles;

)

Or
If a table or a small number of tables are in that tablespace, you can use creat table as select... or move. After the move operation is completed, the tablespace is killed. When the tablespace is killed, the including contents and datefiles can be used.