Compress persistent table space in oracle 11g

Question

I need to compress tablespace in oracle 11g. This is not a temporary tablespace, I cannot lose data in this tablespace. There is no space in one of my other table spaces, so I need to redistribute the remaining size of this table space. Shrink does not work on a constant table space. The current data file size is 1150 MB, and I want it to be 256 MB

Answer 1

You can resize the file this way: -

ALTER DATABASE DATAFILE '/your/path/your_file01.dbf' RESIZE 256M; 

Of course, if you have already used some space above 256M, then you will receive an error message

 ORA-03297: file contains used data beyond requested RESIZE value 

Then you can use this query to see the smallest size you can resize the data file:

 SELECT CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb" FROM dba_data_files f LEFT JOIN (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id WHERE f.file_name = '/your/path/your_file01.dbf' / 

* If the table space block size is not 8192, first change this value. Also note that it will take a long time to complete the request - this is normal - as an alternative, you can simply use the trial and error method, preferred by many, and slightly resize it until the errors are resolved.