Tag Archives: temporary

11g, hp-ux, linux, Oracle 10g, oracle 9i, unix

Oracle Error ORA-25153

Leave a comment

Oracle Error ORA-25153 is a regular occurrence during the modification of location or recreation of an Oracle database. It indicates that the administrator neglected to add datafiles to a temporary tablespace. This article provides a solution the error ORA-25153, if it occurs.

Typical Error message:

Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_j001_25535.trc:
ORA-25153: Temporary Tablespace is Empty
Sun Sep 19 01:31:00 2010
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /u05/oradata/orcl/group_3.dbf

Solution:

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Sep 19 08:08:39 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Add a temp file to your default temporary tablespaces with the following syntax: alter tablespace add tempfile < 'directory and file name'> size ;

example:


SQL> alter tablespace temp add tempfile ‘/u05/oradata/orcl/temp01.dbf’ size 2048m;

Tablespace altered.

SQL>

4. This completes the resolving of Oracle error ORA-25153.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Oracle Temporary Tablespace Movement

Leave a comment

Temporary tablespace’s in the Oracle environment are used for sorting and other non-permanent data operations. Although the space in a temporary tablespace is not permanent, a database of any size cannot properly function without the use of very large amounts of temporary tablespace. This procedure covers the movement of a temporary tablespace after space on the original storage device becomes insufficient. We will cover the movement of the TEMPORARY tablespace from the mount point data1 to mount point data3.

1. Find all tablespaces defined as TEMPORARY

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’;

TABLESPACE_NAME
——————————
TEMP
TEMPORARY

SQL>

2. Locate the temp_files in the temporary tablespace.

SQL> select file_name from dba_temp_files where tablespace_name = ‘TEMPORARY’;

FILE_NAME
/data1/temporary01.dbf
/data1/temporary02.dbf
/data1/temporary03.dbf

SQL>

3. Find size of current files

SQL> select file_name, bytes/1024/1024 from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME BYTES/1024/1024
—————— —————
/data1/temporary01.dbf 4096
/data1/temporary02.dbf 2048
/data1/temporary03.dbf 2048

SQL>

4. Add new temp files on your new mount point to the tablespace temporary with the command ‘alter tablespace’.

SQL> alter tablespace temporary add tempfile ‘/data3/temporary01.dbf’
size 4096m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary02.dbf’
size 2048m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary03.dbf’
size 2048m;
Tablespace altered.

5. Shutdown database to free all in-use segments of the temporary tablespace TEMPORARY with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

6. Startup the database in restricted mode using the command ‘startup restict’ so no user activity can pickup segments from the tablespace TEMPORARY.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

7. Drop the old datafiles using the command ‘alter database’

SQL> alter database tempfile ‘/data1/temprary01.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary02.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary03.dbf’
drop including datafiles;
Database altered.

8. View the new temp files with the following command.

SQL> select file_name from dba_temp_files where tablespace_name=’TEMPORARY’;
FILE_NAME
———————–
/data3/temprary01.dbf
/data3/temprary02.dbf
/data3/temprary03.dbf

SQL>

9. Open the database for normal use with ‘alter system’ command.

SQL> alter system disable restricted session;

System altered.

SQL>

This completes the movement of temporary tablespaces tempfiles.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com