Ora-01652 : Unable to extend temp segment by 128 in tablespace

SOURCE:http://akdora.wordpress.com/2008/01/24/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace/

Ora-01652 : Unable to extend temp segment by 128 in tablespace

January 24, 2008 at 2:49 pm (Oracle & PL/SQL)

That error occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
So we can this in two ways.

1.
Check the datafiles sizes..
SELECT * FROM DBA_DATA_FILES;

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
1 D:\ORACLEXE\ORADATA\XE\USERS.DBF 4 USERS 104857600 12800 AVAILABLE
2 D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 3 SYSAUX 450887680 55040 AVAILABLE
3 D:\ORACLEXE\ORADATA\XE\UNDO.DBF 2 UNDO 94371840 11520 AVAILABLE
4 D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 1 SYSTEM 629145600 76800 AVAILABLE

Then resize your datafile or add a new datafile to current tablespace
Resizing Datafile :
ALTER DATABASE DATAFILE ‘c:\oracle\oradata\orabase\USERS.DBF’ RESIZE 200M;
Addind Datafile to existing Tablespace:
ALTER TABLESPACE USERS ADD DATAFILE ‘c:\oracle\oradata\orabase\USERS2.DBF’ SIZE 50M;

2.
Change the user’s default tablespace to a bigger one :)
SELECT * FROM Dba_Users;

USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
1 ASD 36 OPEN SYSTEM
2 SYS 0 OPEN SYSTEM
3 SYSTEM 5 OPEN SYSTEM
4 ANONYMOUS 28 OPEN SYSAUX

ALTER USER asd DEFAULT TABLESPACE users;
SELECT * FROM Dba_Users;

USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
1 ASD 36 OPEN USERS
2 SYS 0 OPEN SYSTEM
3 SYSTEM 5 OPEN SYSTEM
4 ANONYMOUS 28 OPEN SYSAUX

References :
http://www.psoug.org/reference/tablespaces.html
http://www.psoug.org/reference/datafiles.html

Install/Uninstall Oracle in Fedora 9

The libaio.rpm

Oracle Database 10g Express Edition (Oracle XE) is used as the management data repository for Oracle VM Manager. As a result, you must ensure that the libaio.rpm package is installed. Find libaio.rpm from the ISO packages of Oracle Enterprise Linux. The libaio version varies depending on the Oracle Enterprise Linux version. For Oracle Enterprise Linux 4 Update 5, the libaio version is libaio-0.3.105-2.

1. Before installing libaio.rpm, check if it is already installed. Enter the following command:

   # /bin/rpm -q libaio.i386
   

   The following is an example of the returned information:

   libaio-0.3.106-3.2
   

   If no information is displayed, install libaio.rpm.

2. To install libaio.rpm, go to the directory where libaio.rpm is located, and enter the following command:

   # rpm -ivh libaio-0.3.105-2.i386.rpm
   

Fedora-9

yum install libaio




Install/Uninstall Oracle in Fedora 9

1.Remove/uninstall--->rpm -e filename (without .rpm)

2.Install ---> rpm -i filename.rpm

3.Configure --> /etc/init.d/oracle-xe configure

Http port -->8888 (Default:8080..We used 8080 for tomcat)
Listener->1521
Password--> (For user name system(DBA))
boot..y




The installation process does not configure the environment. To configure the environment add the following lines to the end of the .profile file:

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
export PATH=$PATH:$ORACLE_HOME/bin

To access the Database Home Page go to "http://127.0.0.1:8888/apex"

Install JAVA in Fedora 9/Linux

1.sh jdk-1_5_0_13-dlj-linux-i586.bin --unpack

to create .rpm

2.rpm -i jdk-1_5_0_11-linux-i586.rpm

In.rpm file

3. Set the environment variables:

echo 'export JAVA_HOME=/opt/jdk1.5.0_13' > /etc/profile.d/jdk.sh
echo 'export PATH=$JAVA_HOME/bin:$PATH' >> /etc/profile.d/jdk.sh

4. Source the file you just created:

source /etc/profile.d/jdk.sh

5. You can verify that the Java environment has been installed successfully installed by executing this command from the shell:

java -version

Set JAVA_HOME in Linux,Windows

Linux/Unix (bash)

export ANT_HOME=/usr/local/ant
export JAVA_HOME=/usr/local/jdk-1.5.0.05
export PATH=${PATH}:${ANT_HOME}/bin

Windows and OS/2

set ANT_HOME=c:\ant
set JAVA_HOME=c:\jdk-1.5.0.05
set PATH=%PATH%;%ANT_HOME%\bin

ORACLE DATABASE- ENABLE/DISABLE ALL TRIGGERS

ENABLE:
begin
for i in (select table_name from user_triggers
) LOOP
execute immediate 'alter table '||i.table_name||' enable all triggers '||'';
end loop;
end;

DISABLE:
begin
for i in (select table_name from user_triggers
) LOOP
execute immediate 'alter table '||i.table_name||' disable all triggers '||'';
end loop;
end;