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;
|