If you receive errors like this during data pump export
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 204: '/datafiles/oradata/proddb/temp.dbf'
ORA-06512: at "SYS.DBMS_LOB", line 724
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4546
ORA-06512: at line 1
you need to drop and recreate your temporary tablespace(s).
If you only use bigfile temporary tablespaces, you can use the following script to rectify the situation:
set trimspool on
set lines 200
set feedback off
set verify off
set heading off
set echo off
spool 1.drop_non_default_temp.sql
select 'spool 1.drop_non_default_temp.log' from dual;
select 'drop tablespace ' || ts.name || ' including contents and datafiles;'
from v$tempfile tf join v$tablespace ts
on (ts.ts# = tf.ts#)
and ts.name not in(
select property_value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
);
select 'exit' from dual;
spool 2.recreate_temp.sql
select 'spool 2.recreate_temp.log' from dual;
select 'create bigfile temporary tablespace ' || ts.name || ' tempfile ''' || tf.name || ''' SIZE 32M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;'
from v$tempfile tf join v$tablespace ts
on (ts.ts# = tf.ts#)
and ts.name not in(
select property_value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
);
select 'exit' from dual;
spool off;
spool 3.alter_default_temp.sql
select 'spool 3.alter_default_temp.log' from dual;
select 'alter database default temporary tablespace TEMP;' from dual;
select 'exit' from dual;
spool off
spool 4.drop_default_temp.sql
select 'spool 4.drop_default_temp.log' from dual;
select 'drop tablespace ' || ts.name || ' including contents and datafiles;'
from v$tempfile tf join v$tablespace ts
on (ts.ts# = tf.ts#)
and ts.name in(
select property_value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
);
select 'exit' from dual;
spool off;
spool 5.create_default_temp.sql
select 'spool 5.create_default_temp.log' from dual;
select 'create bigfile temporary tablespace ' || ts.name || ' tempfile ''' || tf.name || ''' SIZE 32M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;'
from v$tempfile tf join v$tablespace ts
on (ts.ts# = tf.ts#)
and ts.name in(
select property_value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
);
select 'exit' from dual;
spool off;
spool 6.reset_default_temp.sql
select 'spool 6.reset_default_temp.log' from dual;
select 'alter database default temporary tablespace ' || ts.name || ';'
from v$tempfile tf join v$tablespace ts
on (ts.ts# = tf.ts#)
and ts.name in(
select property_value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'
);
select 'exit' from dual;
spool off;
exit
Run the script in your database, and it will product 6 new sql files, numbered 1 to 6. Run them sequenctially and you should have recreated your temporary tablespaces.
After this your data pump export will start as expected.
No comments:
Post a Comment