Database copy / restore - skipping tablespaces

Sometimes you want to copy (refresh) database without some tablespaces i.e. due to space
restrictions. These are general guidlines for skipping tablespaces when copying a database
(the same with RMAN restore, restore from cold backup or copy from a standby database).

1. Once the datafiles have been copied / restored

SQLPLUS> startup mount;

Run a rename datafile script if necessary

2. Use the script below to generate a drop datafile script

set head off pagesize 0 linesize 150
set echo off verify off feedback off

spool drop_files.sql

select 'set echo on verify on feedback on head on'
from dual
/

select 'spool drop_files.log'
from dual
/

--
-- This assumes that the names of your data files include
-- part of tablespace name
--
select 'alter database datafile ''' || name || ''' offline drop;' 
from v$datafile 
where name like '%SKIPPED_TABLESPACE%'
/

select 'spool off'
from dual
/

spool off

3. Run drop_files.sql script

SQLPLUS> @drop_files.sql

At this stage if you query v$datafile you will still see the files you've just dropped because
the tablespaces are still in the data dictionary, just ignore that.

4. Open the database

SQLPLUS> alter database open resetlogs;

5. Drop tablespaces that were skipped during restore

SQLPLUS> drop tablespace SKIPPED_TABLESPACE including contents;