Solved

import .dmp file

Posted on 2003-11-17
16
47,249 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
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I think the tempfile is missing

YOu can add files to the temp tablespace using ADD TEMPFILE then exp again
0
 

Author Comment

by:xoxomos
Comment Utility
TEMP TS is already the default for the user.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
Comment Utility
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
Comment Utility


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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:catchmeifuwant
Comment Utility
Try bringing the file online and check again...

alter database tempfile 'location of tempfile' online;

0
 

Author Comment

by:xoxomos
Comment Utility

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
Comment Utility
did you check if the file exists and it is online?
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now