Solved

import .dmp file

Posted on 2003-11-17
16
47,271 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
Industry Leaders: 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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

690 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