how to restrict usage of datafile in Oracle

Question

We have several datafiles with a big amount of a free space. How to shrink those files on production system. I tried to move all objects from this files into another tablespace, but RDBMS is still putting something there near MAX block number that makes "ALTER DATABASE DATAFILE RESIZE" impossible. is there any option to block file to get create segments to make it possible to have resize as an empty file?

Answer 1

First, I'd check that it is not specified as the default TS for any user:

select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;

select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';

-- Edit - add comments about quota

One other thing to check and that is user quota. If a user has available quota on a TS, they can create segments in it even if it is not their default TS.

select *
from dba_ts_quotas
where tablespace_name='nameofyourtablespace';