ORA-25153 临时表空间错误

SQL> @e:\oracle\product\10.2.0\db_1\RDBMS\admin\awrextr.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script. should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script. will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script. will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 2713574263 ARVIN        ISSUSER-B31E
                          43D

* 2713574263 ARVIN        SINGLEMICE-H
                          C

The default database id is the local one: '2713574263'.  To use this
database id, press to continue, otherwise enter an alternative.

Enter value for dbid:

Using 2713574263 for Database ID

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 5

Listing the last 5 days of Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ARVIN               82 14 12月 2009 16:26
                    83 14 12月 2009 17:00
                    84 14 12月 2009 18:01
                    85 15 12月 2009 12:44
                    86 15 12月 2009 14:00
                    87 15 12月 2009 15:00
                    88 15 12月 2009 16:00
                    89 15 12月 2009 17:00
                    90 15 12月 2009 18:00
                    91 16 12月 2009 10:23
                    92 16 12月 2009 11:00
                    93 16 12月 2009 12:00
                    94 16 12月 2009 13:00
                    95 16 12月 2009 14:00
                    96 16 12月 2009 15:00
                    98 16 12月 2009 16:00

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 82
Begin Snapshot Id specified: 82

Enter value for end_snap: 98
End   Snapshot Id specified: 98

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------

ADMIN_DIR                      C:\ADE\aime_10.2_nt_push\oracle/md/admin
DATA_FILE_DIR                  E:\oracle\product\10.2.0\db_1\demo\schema\sales_h

                               istory\

DATA_PUMP_DIR                  E:\oracle\product\10.2.0\admin\arvin\dpdump\
LOG_FILE_DIR                   E:\oracle\product\10.2.0\db_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\10.2.0\db_1\demo\schema\product

                               _media\

SUBDIR                         E:\oracle\product\10.2.0\db_1\demo\schema\order_e

                               ntry\/2002/Sep

WORK_DIR                       C:\ADE\aime_10.2_nt_push\oracle/work
XMLDIR                         E:\oracle\product\10.2.0\db_1\demo\schema\order_e

                               ntry\

Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_82_98.
To use this name, press to continue, otherwise enter
an alternative.

Enter value for file_name:

Using the dump file prefix: awrdat_82_98
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   E:\oracle\product\10.2.0\admin\arvin\dpdump\
|   awrdat_82_98.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   E:\oracle\product\10.2.0\admin\arvin\dpdump\
|   awrdat_82_98.log
|
Starting "SYS"."SYS_EXPORT_TABLE_03":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS w
hile calling
DBMS_LOB.CREATETEMPORARY []
ORA-25153: Temporary Tablespace is Empty

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6235

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
23BE05E0     14916  package
body SYS.KUPW$WORKER
23BE05E0      6300  package body SYS.KUPW$WORKER
23BE05E0      9120  package body
SYS.KUPW$WORKER
23BE05E0      1880  packag
e body SYS.KUPW$WORKER
23BE05E0      6861  package body SYS.KUPW$WORKER
23BE05E0      1262  package body
SYS.KUPW$WORKER
23B0A800         2  anonymous block

Job "SYS"."SYS_EXPORT_TABLE_03" stopped due to fatal error at 16:30:59
SQL>

 

 

提法临时表空间错误

select tablespace_name,status from dba_tablespaces

SYSTEM                         ONLINE  
UNDOTBS1                       ONLINE  
SYSAUX                         ONLINE  
TEMP                           ONLINE  
USERS                          ONLINE  
EXAMPLE                        ONLINE  
ARVIN                          ONLINE  

7 rows selected.

发现temp表空间状态正常

select * from dba_temp_files

 

查询为空，没有临时表空间文件 ，不知道是如何丢失

 

alter tablespace temp add tempfile 'E:\oracle\product\10.2.0\oradata\arvin\TEMP01.DBF'

 

select file_name,tablespace_name,status from dba_temp_files

 

FILE_NAME                                                                      
--------------------------------------------------------------------------------
TABLESPACE_NAME                STATUS  
------------------------------ ---------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARVIN\TEMP01.DBF                              
TEMP                           AVAILABLE
                                                                               
1 row selected.

 

再次执行@e:\oracle\product\10.2.0\db_1\RDBMS\admin\awrextr.sql

OK成功了

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14673072/viewspace-622587/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14673072/viewspace-622587/