Table Space Recycling Operation judgment

Just at work today, a friend asked me, " we have a table space to expand to 1.4T, but delete data is not used, this is not small?".

I explained this to him. "This data file; You delete the data is not affected by its size; This data file is equivalent to a bucket; the size of the bucket is 1.4T; Deleting the data is equivalent to pumping the water out of it. The size of the bucket is constant, and the water is less. The space available for the bucket is getting bigger, and more water can be loaded. "Want a table space to expand to more than 1T." There are two kinds of possible 1. There really is a lot of data on the volume of the business. 2. The table space must be set to auto-expand, causing the data file to skyrocket, in which 99% of the data is useless data, can be cleaned. It is not well maintained. Is there any way to reduce this data file? The way is: ' ALTER DATABASE datafile ' ****.dbf ' resize ***g '; Does this still need to be judged by the server environment at that time? Step one: Look at the usage of the table space

SELECTa.tablespace_name "Table space", A.bytes/ 1024x768 / 1024x768"tablespace size (M)", (A.bytes-B.bytes)/ 1024x768 / 1024x768"used Space (M)", B.bytes/ 1024x768 / 1024x768"free Space (M)",round(((A.bytes-B.bytes)/A.bytes)*  -,2) "Use ratio" from(SELECTTablespace_name,SUM(bytes) bytes fromDba_data_filesGROUP   byTablespace_name) A, (SELECTTablespace_name,SUM(bytes) bytes,MAX(bytes) Largest fromDba_free_spaceGROUP   bytablespace_name) bWHEREA.tablespace_name=B.tablespace_nameORDER   by((a.bytes-B.bytes)/A.bytes) Dese;

The situation is: about 250G is used, that is, it is not possible to resize to 500G, reduce the disk space 700G? Continue judging below

I let her execute select File#,name from V$datafile; What are the corresponding file numbers found? The answer is 9. Continue execution of select Max (block_id) from dba_extents where file_id=9;

Select file  from V$datafile; Select Max  from where file_id = 9;

It has been written to 175382656 dollars. I was in a mess, converted to t;175392656*8/(1024*1024*1024) =1.3t; The result is about 1.3T; it seems that the ' ALTER DATABASE datafile ' ****.dbf ' resize ***g ' is not feasible. If you do not understand, just the bucket to explain: just the bucket is 1.4T, the inside put 260G of water, but the water is not continuously placed, water is not directly into the bucket inside, the water is placed in the empty slot; empty slots are understood to be equivalent to segments in Oracle (table, index) Now there is an empty slot placed in the 1.3T position; now I want to compress this bucket; I can only compress it to 1.3T; Last: 1. The data file is backed up logically, the table space is deleted, a table space is re-built, the import is done, but the operation is not recommended; 1.4T data file; backup/restore is a huge project; the business needs to be stopped at the time of operation. 2. I suggest that it is better not to move, the current table space is 1.4T; only 260G is used, meaning that there is no possibility of disk expansion at this time. The/data1 directory also has 90G available; All table spaces are canceled and automatically expanded. Take care of it.

Table Space Recycling Operation judgment