ORA-39142: incompatible version number 3.1 in dump file "/amit/amit.dmp"

Few days ago I was trying to import some tables from one database to another through datapump utility and I faced below mentioned errors :-

bash-3.2$ impdp amit/amit directory=datapump dumpfile=EXPDP_AMIT_TABLES.DMP remap_schema=source:amit

Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 18 December, 2012 10:14:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/amit/amit.dmp"

When I searched regarding this error, I find out that I was trying to transfer data from a higher database version to a lower database version. PFB details of DB version's :-

Oracle Database 11g Enterprise edition Release 11.2.0.1.0 ----- Windows

Oracle Database 11g Enterprise edition Release 11.1.0.7.0 ------ Aix

This problem is not related to different platforms , its due to different version numbers

Solution :-

1. Export the required tables in the source database having higher version number by using version=11.1

2. Now import the required tables in target database and its completes without any errors.

Export :-

expdp amit/amit directory=datapump dumpfile=EXPDP_AMIT_TABLES.DMP logfile=EXPDP_AMIT_TABLES.log version=11.1 tables=A,B,C compression=all

Import :-

impdp amit/amit directory=datapump dumpfile=EXPDP_AMIT_TABLES.DMP remap_schema=source:amit 

It will import the tables without any error.

I hope this article helped you.

Regards,
Amit Rath

Temporary Tablespaces in Oracle Database

What are Temporary Tablespace

Temporary Tablespace contains transient data that persists only for the duration of the session.

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation.

Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

Note:- A temporary tablespace does not contain permanent objects and therefore doesn't need to be backed up.

How to create a temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

Tablespace created.

Default Temporary Tablespace

Default Temporary tablespace can be defined at the database creation time or by issuing an "ALTER DATABASE"

SQL> alter database default temporary tablespace temp1;

Database altered.


Restrictions :-
1. Default Temporary tablespace cannot be dropped till you create another one.
2. Default Temporary tablespace cannot be taken off-line.

If you define a default temporary tablespace , it's automatically assigned to users.

How to find default temporary tablespace of database


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE          DESCRIPTION
------------------------------                           ------------------------------           ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                               Name of default temporary tablespace

How to change default Temporary Tablespace

1. create a new temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

2. Assign new temporary tablespace as the default temporary tablespace.


SQL> alter database default temporary tablespace temp1;

Database altered.

3. Drop the OLD default temporary tablespace


SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

4. Check that default temporary tablespace changed or not


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE         DESCRIPTION
------------------------------                           ------------------------------         ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP1                           Name of default temporary tablespace

What are Temp Files

Details of Temp files in the database are not recorded in the control file, means we can recreate them whenever we restore the database or after deleting them accidently.

We cannot remove datafiles from a tablespace till we won't drop the entire tablespace. But we can remove tempfiles from a database :-

How to remove tempfiles from a database :-


SQL> alter database tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' drop including datafiles;

Database altered.

How to add tempfiles to a database:-

SQL> alter tablespace temp add tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' size 100m;

Getting Usage Details of Temp tablespace in a database :-

##################### RAC Database ###############################

SQL> Select INST_ID,TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from gv$temp_space_header group by INST_ID,TABLESPACE_NAME;

##################### StandAlone Database ###############################

SQL> Select TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from v$temp_space_header group by TABLESPACE_NAME;

######################################################################

SQL> Select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE      FREE_SPACE
------------------------------                   ---------------                     ---------------                     ----------
TEMP                                       104857600                     8388608                          103809024

########################## INSTANCE WISE ##############################


 SQL> select tablespace_name,
 (free_blocks*8)/1024/1024 FreeSpaceGB,
 (used_blocks*8)/1024/1024 UsedSpaceGB,
 (total_blocks*8)/1024/1024 TotalSpaceGB,
 i.instance_name,i.host_name
 from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
 i.inst_id=ss.inst_id;


TABLESPACE_NAME          FREESPACEGB     USEDSPACEGB    TOTALSPACEGB     INSTANCE_NAME    HOST_NAME
------------------------------            -----------                   -----------                  ------------                    ----------------               ------------------------------
TEMP                                 .007                       .000                      .007                           orcl                          AMIT-PC

I hope this article hepled you.

Regards,
Amit Rath