Solved

import .dmp file

Posted on 2003-11-17
16
47,267 Views
Last Modified: 2012-05-04
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?

























0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 250 total points
ID: 9769206
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
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9769214
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
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 150 total points
ID: 9769280
I think the tempfile is missing

YOu can add files to the temp tablespace using ADD TEMPFILE then exp again
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:xoxomos
ID: 9769287
TEMP TS is already the default for the user.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9769400
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
 

Author Comment

by:xoxomos
ID: 9769414


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

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

SQL>
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9769442
The temp file is present but may not be accessable. In that case your database can be startup as normal:
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9769443
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
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9769452
Try bringing the file online and check again...

alter database tempfile 'location of tempfile' online;

0
 

Author Comment

by:xoxomos
ID: 9769608

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
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9769625
did you check if the file exists and it is online?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9771031
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
 

Author Comment

by:xoxomos
ID: 9773764
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
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 100 total points
ID: 9773808
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
 

Author Comment

by:xoxomos
ID: 9784996
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
 

Author Comment

by:xoxomos
ID: 9785011
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question