ORA-00980: synonym translation is no longer valid
This error usually means that the synonym used is based on a table, view, or synonym that no longer exists. in order to fix it you will need to replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym. However, this is not always simple. following are some common causes and suggested solutions.
1) If you imported a specific schema, it is possible that one of the sysnonyms is pointing to an object on a different schema that is simply not there. run SELECT * FROM all_synonyms and verify that all the table name are there and reachable.
2) If you imported from a different version it is also possible that that the synonym is pointing to an object that does not exists in the new version. This could be due to the fact that this component was not installed on the new version or that the component is not in a valid state.
SELECT comp_name, version,status FROM dba_registry and make sure that all the components are valid.
3) it is also possible that object is there but you don’t have access to it. connect as system and do the SELECT * FROM dba_synonyms WHERE owner=’YOUR USER”. again, make sure that your user have access to all the objects under TABLE_NAME. If not, grant it.
4) if this issue happens during import or export, add trace=y to the imp/exp command (imp user/pass file=filename.dmp trace=y) This will create a trace file that will provide more information on what is failing.
When using Data Pump, you may encounter the following error:
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: Unable to extend table %s.%s by %s in tablespace %s
ORA-01691 unable to extend lob segment string.string by string in tablespace string
Both errors basically say that the table space you are using can not allocate more space. This could be because the datafile is full, the disk is full or autoextend is not set. This is simple to check by running the following query on DBA_DATA_FILE:
The bytes column will tell you how many bytes are used by Oracle in the tablespace. If AUTOEXTENSIBLE is set to NO, then Oracle will not extend the size of the tablespace.
In many cases, the Data Pump import may seem to hang. Data Pump will try to continue after some time but unless something is changed, you will keep getting the above errors.
The solution, however, is simple enough. All you need to do is use the Data Pump Restart Capability:
1) In the IMPDP window, click CTRL-C to stop the job.
2) In the command line type:
3) Use SQLPlus to make the required changes to the table space. You can:
a) Add DataFile ALTER TABLESPACE <tablespace name> ADD DATAFILE ‘<path to file name and file name>’ SIZE <integer>M;
b) Resize the Datafile:
ALTER DATABASE DATAFILE ‘< path to file name and file name >’ RESIZE <integer>M;
c) Enable autoextend:
ALTER DATABASE DATAFILE ‘< path to file name and file name >’ AUTOEXTEND ON MAXSIZE UNLIMITED;
4) Attach the Job.
>impdp system/manager attach=Job_Name
If you did not provide a specific job name when you first ran the IMPDP, Oracle will assign a default name for it. In order to find the System assigned name for the IMPDP job you can run the following query:
SELECT * FROM DBA_DATAPUMP_JOBS;
The result will probably be something like SYS_IMPORT_FULL_number.
5) Restart the job
6) You can check the status of the job by simply typing STATUS in the utility command prompt.
If you try to import a file and you get the following error:
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
The cause according to Oracle error description is “Either the file was not generated by Export or it was corrupted”. The first obvious reason might be that the file is corrupted. However, if you downloaded it correctly (using binary transfer in the FTP as opposed to ASCII transfer) and you have no other reason to believe that the file is indeed corrupted. You might want to explore other options.
The most common reason for this error is mismatch of IMP and EXP versions. While it should not be a problem to import a file that was created with an earlier version of EXP using a newer version of IMP, trying to use it the other way will generate the above error. In other words, you can import a file that was created with 10g version of EXP using a 9i version of IMP. If this is the case, try to export the file using a lower version of EXP or try to import the file to a higher version Database. Later you can export it again using a lower version of the EXP utility.
Another reason, over looked many times, is that the file was exported using Data Pump (EXPDP not EXP) . If you are not the one who exported the file, try to restore it using the Data Pump import utility IMPDP. Since Data Pump is gaining traction and it is creating .dmp files it is possible that the dmp file in your hands was created using it.