Embedded Files
Check
Check
CREATE
CREATE
CREATE TEMPORARY TABLESPACE &mytemp;
Add Tempfile
Add Tempfile
ALTER TABLESPACE &mytemp ADD TEMPFILE;
ALTER TABLESPACE &mytemp ADD TEMPFILE '+DATA1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
Make Default
Make Default
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE &mytemp;
Changing Default
Changing Default
If you have changed which TEMP tablespace is the default, then you should make sure that no users have the old TEMP tablespace explicitly defined as their default...
SELECT username, temporary_tablespace
FROM dba_users
ORDER by temporary_tablespace, username;
ALTER USER &username TEMPORARY TABLESPACE &mytemp;
DROP
DROP
To drop a temporary tablespace...
DROP TABLESPACE &mytemp INCLUDING CONTENTS;
NOTE: In 11.2.0.3 there is a bug that prevents DROP TABLESPACE temp... from working (it just hangs). I tried doing an online patch for bug 15913577 but this did not solve the issue. As a workaround I added a new, minimally sized tempfile to the temp tablespace and dropped all other tempfiles.
You cannot drop a TEMP tablespace that is in use. Check what is using it and, if necessary, arrange for sessions to disconnect/reconnect.
@/u01/dba/oratempusagebysession.sql
You can also drop just tempfiles without dropping the whole tablespace...
ALTER TABLESPACE &mytemp DROP TEMPFILE '/u02/MYSID/datafile/o1_mf_temp_mybzkh2z_.tmp';
Autoextend
Autoextend
Check Autoextend
Check Autoextend
@/u01/dba/oratemptscheck.sql
Enable Autoextend
Enable Autoextend
ALTER DATABASE TEMPFILE '/u02/oradata/MYSID/datafile/MYTEMPDF.tmp' AUTOEXTEND ON;
Disable Autoextend
Disable Autoextend
ALTER DATABASE TEMPFILE '/u02/oradata/MYSID/datafile/MYTEMPDF.tmp' AUTOEXTEND OFF;
Bibliography
Bibliography
Page updated
Google Sites
Report abuse