SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE '/oracle/<SID>/sapdata<n>/temp2_1/temp2.data1' SIZE 5M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
SQL> DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE PSAPTEMP
2 TEMPFILE '/oracle/<SID>/sapdata<n>/temp_1/temp.data1' SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP;
Database altered.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Jesse Josserand
said:
 |
... This is a nice procedure; however, it ignores the bug in temp files that requires them to be pre-created in the unix (aix/linux/whatever) filesystem in advance using dd. For instance, this command will create a 5G temp file when THEN can be added in sqlplus... dd if=/dev/zero of=temp2.data1 bs=1m count=5120 |
Jesse Josserand
said:
|
... It's also different in 11g... normal procedures create only sparse files... so... try this... 11G temp space rebuild ----------------------- We are basically forcing a normal Oracle file to use a cloned DD file while also getting around the 11g limitation to pre-create a tempfile using DD and then add to Oracle. By using the clone it gets created at the right file size on the file system. File sizes from the start all match the same on disk and Oracle, but the du command will show otherwise. Once done this way no tempfile problems running the file system out of space. --------- -- -- Pre-Check Steps -- df -g | grep sapdata | grep GGQ mkdir /oracle/GGQ/sapdata1/x ls -ltr `find /oracle/GGQ/sapdata* -name "temp.data*"` (using 33G on sapdata1) select tablespace_name from dba_temp_files; -- -- Create DUMMY TEMP Tablespace -- create temporary tablespace x tempfile '/oracle/GGQ/sapdata1/x/x.dbf' size 1G reuse; alter database default temporary tablespace x; drop tablespace psaptemp including contents and datafiles; -- -- Create PSAPTEMP -- mkdir /oracle/GGQ/sapdata1/temp_1 create temporary tablespace PSAPTEMP tempfile '/oracle/GGQ/sapdata1/temp_1/temp.data1' size 20G reuse; exit; echo "select file_name, status, bytes/1024/1024 from dba_temp_files;" | sqlplus -s / as sysdba -- -- Fix the tempfile size from the OS level -- cd /oracle/GGQ/sapdata1/temp_1 du -m . dd if=temp.data1 of=temp.data1.tmp bs=1m (for AIX) giving a 1M blocksize to the file. dd if=temp.data1 of=temp.data1.tmp bs=1024 (for Linux) same 1M blocksize. du -m . mv temp.data1.tmp temp.data1 ls -ltr du -m . -- -- Reinstate the PSAPTEMP tablespace again and Drop the DUMMY temporary tablespace. -- alter database default temporary tablespace PSAPTEMP; exit; echo "select file_name, status, bytes/1024/1024 from dba_temp_files;" | sqlplus -s / as sysdba drop tablespace X including contents and datafiles; -- --Add an additonal tempfile -- mkdir /oracle/GGQ/sapdata1/temp_2 alter tablespace PSAPTEMP add tempfile '/oracle/GGQ/sapdata1/temp_2/temp.data2' size 10G reuse; cd /oracle/GGQ/sapdata1/temp_2 du -m . dd if=temp.data2 of=temp.data2.tmp bs=1m (for AIX) giving a 1M blocksize to the file. dd if=temp.data2 of=temp.data2.tmp bs=1024 (for Linux) same 1M blocksize. du -m . mv temp.data2.tmp temp.data2 ls -ltr du -m . |
