Hi
For a while ago, I moved tables, data, indexes etc to another tablespace (new tablespace is in another ASM diskgroup).
Before moving procedure, I changed current_tablespace_name to tablespace_old and then created new tablespace name as current_tablespace.
So, everything went fine regarding to moving procedure. I used table redifinition for one very big table (400+ gb) and another tables, indexes etc I moved with the following commands:
ALTER TABLE <OWNER>.<TABLE_NAME> MOVE TABLESPACE <NEW TABLESPACE>;
ALTER TABLE <OWNER>.<TABLE_NAME> MOVE TABLESPACE <NEW TABLESPACE> LOB(<COLUMN_NAME>) STORE AS (TABLESPACE <NEW TABLESPACE>);
ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD TABLESPACE <NEW TABLESPACE>;
After moving procedures, I checked if the tabelspace_old is indeed empty (https://blogs.oracle.com/mandalika/entry/oracle_database_how_to_figure ). So the query from DBA_SEGMENTS indeed returns 0 rows. I also know, that empty tables would not pop out from db_segments view, because, there is not segments created. I especially checked all tables, including empty ones, that should be moved to new tablespace.
So it would be safe to drop the tablespace. But I wasn't 100% sure, so first of all I placed this tablespace_old to offline, to see, if something still tries to access these datafiles.
And looks like, it does, in alert logs, I have these rows for basically all the datafiles, that are from tablespace_old.
...
Errors in file /oracle/11.2/base/diag/rdbms/digitoim/digitoim1/trace/digitoim1_m000_8870.trc:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6: '+DATA/digitoim/datafile/digitoimik.344.890751787'
....
Errors in file /oracle/11.2/base/diag/rdbms/digitoim/digitoim1/trace/digitoim1_ora_18656.trc:
ORA-01135: file 7 accessed for DML/query is offline
ORA-01110: data file 7: '+DATA/digitoim/datafile/digitoimik.345.890751803'
......
SO I would like to be 100% sure, that the oled tablespace indeed is empty and can be dropped..