import .dmp file

I made a full database export from which i'm trying to import some users.
Getting errors:
psdwd /ustage/backup/dwtestdb $ exp warehouse/warehouse FILE=outhouse.dmp OWNE
R=warehouse ROWS=y

Export: Release 9.2.0.3.0 - Production on Mon Nov 17 21:23:57 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user WAREHOUSE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WAREHOUSE
About to export WAREHOUSE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 424
ORA-06512: at "SYS.DBMS_METADATA", line 1140
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
 How do I fix it?

























xoxomosAsked:
Who is Participating?
 
catchmeifuwantCommented:
From Oracle documentation
--------------------------

ORA-25153 Temporary Tablespace is Empty

Cause: An attempt was made to use space in a temporary tablespace with no files.

Action: Add files to the tablespace using ADD TEMPFILE command.

*********************************************************

If you alread have a Temporary tablespace,then assign the Temp. TS to user warehouse

1)Finding the Temp TS in your DB
--------------------------------

select tablespace_name
from dba_tablespaces
where contents = 'TEMPORARY';

Say output is "TEMP".

2)Assigning the TEMP TS as the default to the user
------------------------------------------------

ALTER USER WAREHOUSE TEMPORARY TABLESPACE TEMP;
0
 
catchmeifuwantCommented:
In order to add files to the Temporary Tablespace...use

alter tablespace <temp tablespace name> add tempfile 'location_of_file'

For eg.,

alter tablespace temp add tempfile 'E:\Temp01.dbf'
0
 
baonguyen1Commented:
I think the tempfile is missing

YOu can add files to the temp tablespace using ADD TEMPFILE then exp again
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
xoxomosAuthor Commented:
TEMP TS is already the default for the user.
0
 
catchmeifuwantCommented:
Check if the file used by temp ts is present or not...

select file_name from dba_temp_files
where tablespace_name = 'TEMP';

Check if the file is physically present in the location !!!
0
 
xoxomosAuthor Commented:


SQL> select file_name from dba_temp_files
  2  where tablespace_name = 'TEMP';

FILE_NAME
----------------------------------------------------
/u02/app/oracle/oradata/dwtestdb/temp01.dbf

SQL>
0
 
baonguyen1Commented:
The temp file is present but may not be accessable. In that case your database can be startup as normal:
0
 
catchmeifuwantCommented:
1)Check if the file exists in the location

/u02/app/oracle/oradata/dwtestdb/temp01.dbf


2)To find out the Temp TS assigned to Warehouse and the location of it...try this..

select file_name
from dba_temp_files
where tablespace_name in (select temporary_tablespace
from dba_users
where username = 'WAREHOUSE')

Check if the file in the output exists

3)If you still have problems,try altering the user and assigning a different Temporary Tablespace

HTH
0
 
catchmeifuwantCommented:
Try bringing the file online and check again...

alter database tempfile 'location of tempfile' online;

0
 
xoxomosAuthor Commented:

SQL> select file_name from dba_temp_files
  2  where tablespace_name in(select temporary_tablespace
  3  from dba_users
  4  where username = 'WAREHOUSE');

FILE_NAME
-------------------------------------------------------------------
/u02/app/oracle/oradata/dwtestdb/temp01.dbf
0
 
catchmeifuwantCommented:
did you check if the file exists and it is online?
0
 
seazodiacCommented:
When your logic temp file is missing, you will get ORA-25153: Temporary Tablespace is Empty.

To fix this:

=> in 8i:
 
1. SQL> alter database tempfile '/u02/app/oracle/oradata/dwtestdb/temp01.dbf' drop;
2. then delete the OS file manually.
3. then add a new temp file by using:
SQL>alter tablespace temp add tempfile '/u02/app/oracle/oradata/dwtestdb/temp01.dbf';


=> in 9i: you can use the new clause INCLUDING DATAFILES to remove OS files
 
1. SQL> alter database tempfile '/u02/app/oracle/oradata/dwtestdb/temp01.dbf' drop including datafiles;

2. SQL>alter tablespace temp add tempfile '/u02/app/oracle/oradata/dwtestdb/temp01.dbf';



after that, try to exp again.
0
 
xoxomosAuthor Commented:
This is happening now.  Still getting that tablespace message.


. . importing table   "DEPARTMENT_FIN_CURRXLAT_TB"        116 rows imported
. . importing table       "DEPARTMENT_FIN_XLAT_TB"        122 rows imported
. . importing table          "FACULTY_CONTRACT_TB"       6330 rows imported
. . importing table    "FIN_ACTUAL_TRANSACTION_TB"     289381 rows imported
IMP-00017: following statement failed with ORACLE error 12801:
 "CREATE INDEX "FIN_ACTUAL_TRANSACTION_IDX1" ON "FIN_ACTUAL_TRANSACTION_TB" ("
 ""FISCAL_YEAR" , "ACCOUNTING_PERIOD" , "BUSINESS_UNIT" , "CF_FUND_CODE" , "C"
 "F_DEPTID" , "CF_ACCOUNT" , "CF_PROGRAM_CODE" , "CF_PROJECT_ID" , "CF_CLASS_"
 "FLD" , "STATISTICS_CODE" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INIT"
 "IAL 1048576 FREELISTS 1 FREELIST GROUPS 1)                        LOGGING P"
 "ARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P000
ORA-25153: Temporary Tablespace is Empty
. . importing table       "FIN_AP_SUMMARY_CTRL_TB"     134783 rows imported
IMP-00017: following statement failed with ORACLE error 12801:
 "CREATE INDEX "FIN_AP_SUMMARY_CTRL_IDX1" ON "FIN_AP_SUMMARY_CTRL_TB" ("CF_AC"
 "COUNT" , "CF_DEPTID" , "CF_FUND_CODE" , "CF_CLASS_FLD" , "CF_PROGRAM_CODE" "
 ", "CF_PROJECT_ID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 104"
 "8576 FREELISTS 1 FREELIST GROUPS 1)                        LOGGING PARALLEL"
 " ( DEGREE DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P002
ORA-25153: Temporary Tablespace is Empty
. . importing table            "FIN_AP_SUMMARY_TB"    







 "ARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P000
ORA-25153: Temporary Tablespace is Empty
. . importing table       "FIN_AP_SUMMARY_CTRL_TB"     134783 rows imported
IMP-00017: following statement failed with ORACLE error 12801:
 "CREATE INDEX "FIN_AP_SUMMARY_CTRL_IDX1" ON "FIN_AP_SUMMARY_CTRL_TB" ("CF_AC"
 "COUNT" , "CF_DEPTID" , "CF_FUND_CODE" , "CF_CLASS_FLD" , "CF_PROGRAM_CODE" "
 ", "CF_PROJECT_ID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 104"
 "8576 FREELISTS 1 FREELIST GROUPS 1)                        LOGGING PARALLEL"
 " ( DEGREE DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P002
ORA-25153: Temporary Tablespace is Empty
. . importing table            "FIN_AP_SUMMARY_TB"     134783 rows imported
IMP-00017: following statement failed with ORACLE error 12801:
 "CREATE INDEX "FIN_AP_SUMMARY_IDX1" ON "FIN_AP_SUMMARY_TB" ("CF_ACCOUNT" , ""
 "CF_DEPTID" , "CF_FUND_CODE" , "CF_CLASS_FLD" , "CF_PROGRAM_CODE" , "CF_PROJ"
 "ECT_ID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREEL"
 "ISTS 1 FREELIST GROUPS 1)                        LOGGING PARALLEL ( DEGREE "
 "DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P001
ORA-25153: Temporary Tablespace is Empty
. . importing table    "FIN_BUDGET_TRANSACTION_TB"                                                    





 ""FISCAL_YEAR" , "ACCOUNTING_PERIOD" , "BUSINESS_UNIT" , "CF_FUND_CODE" , "C"
 "F_DEPTID" , "CF_ACCOUNT" , "CF_PROGRAM_CODE" , "CF_PROJECT_ID" , "CF_CLASS_"
 "FLD" , "STATISTICS_CODE" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INIT"
 "IAL 1048576 FREELISTS 1 FREELIST GROUPS 1)                        LOGGING P"
 "ARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P002
ORA-25153: Temporary Tablespace is Empty
. . importing table "FIN_ENCUMBRANCE_TRANSACTION_TB"      54746 rows imported
IMP-00017: following statement failed with ORACLE error 12801:
 "CREATE INDEX "FIN_ENCUMBRANCE_TRANSACTN_IDX1" ON "FIN_ENCUMBRANCE_TRANSACTI"
 "ON_TB" ("FISCAL_YEAR" , "ACCOUNTING_PERIOD" , "BUSINESS_UNIT" , "CF_FUND_CO"
 "DE" , "CF_DEPTID" , "CF_ACCOUNT" , "CF_PROGRAM_CODE" , "CF_PROJECT_ID" , "C"
 "F_CLASS_FLD" , "STATISTICS_CODE" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR"
 "AGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1)                        L"
 "OGGING PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)"
IMP-00003: ORACLE error 12801 encountered
ORA-12801: error signaled in parallel query server P000
ORA-25153: Temporary Tablespace is Empty
. . importing table       "FIN_FY_SUMMARY_CTRL_TB"
IMP-00009: abnormal end of export file
IMP-00028: partial import of previous table rolled back: 34944 rows rolled back
Import terminated successfully with warnings.
psdwd /ustage/backup/dwtestdb $
psdwd /ustage/backup/dwtestdb $                                                
0
 
seazodiacCommented:
Xoxomos:

the solution to this:

1. shutdown the databae
2. edit the init.ora file , set the paramter: PARALLEL_MAX_SERVERS = 0
3. restart the database
4. do the import again
0
 
xoxomosAuthor Commented:
That was strange.  I looked at that temp01.dbf on the os using ls -la, I looked at it in oem, i even deleted it and put it back.  Same problem.  I deleted it the second time and put it back , now everything is ok.  Still don't know why, but it had something to do with that temp01.dbf.
0
 
xoxomosAuthor Commented:
Whoops....that assisted answer from seazodiac should not have been under the max-servers comment.  It should have been under the delete and add it back for temp01 comment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.