How to clean up disk space for Oracle 11g database server

Jephe Wu - http://linuxtechres.blogspot.com

Environment: OEL5 64bit OS and Oracle 11g 64bit database server
Objective:  temp and undo tablespace occupied too much disk space, need to clean up to save space.


Steps:
 1. For shrinking undo tablespace:
$ sqlplus / as sysdba
select name from v$datafile

create undo tablespace UNDOTBS2 datafile '/path/to/undotbs2.dbf' size 100m autoextend on next 20m flashback off;
alter system set undo_tablespace=undotbs2


then go to Enterprise Manger to find out the old undo tablespace name, e.g. undotbs1
drop tablespace undotbs1 including contents and datafiles;

Most likely, you have to bounce database to get the disk space back to OS. So, do this:


sqlplus / as sysdba
alter system checkpoint;
shutdown immediate;




note: how to find out tablespace_name,username and datafile name


select distinct s.owner,s.tablespace_name,d.file_name from dba_segments s,dba_data_files d where s.tablespace_name = d.tablespace_name;

Reference:

metalink How to Shrink the datafile of Undo Tablespace [ID 268870.1] 

SQL> create temporary tablespace TEMP1 tempfile 'c:\path\to\temp02.dbf' size 100M extent management 
local uniform size 128K;

SQL> alter database default temporary tablespace TEMP1;

SQL> alter user <username> temporary tablespace TEMP1; if required

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
   

2. For shrinking temp tablespace;
Oracle 11g supports to shrink temporary tablespace online, just run this:

alter tablespace temp shrink space; 

to shrink the temp tablespace to minimum possible size.

before that, you can use command below to find out the minimum size and which are the 

temp files:

select file_name,bytes,blocks from dba_temp_files;

SELECT * FROM dba_temp_free_space;

Now you can the minimum possible size, you can use commands below also:

alter tablespace temp shrink space keep 10m;
alter tablespace temp shrink tempfile '/path/to/file.dbf' [keep 20m];

select username,temporary_tablespace from dba_users where username = 'SCHEMA_NAME' 

For shrinking temp tablespace for database 10g, refer to 

 How to Shrink the datafile of Temporary Tablespace [ID 273276.1] 

SQL> create temporary tablespace TEMP1 tempfile 'c:\temp01.dbf' size 100M extent management
local uniform size 128K;

 SQL> alter database default temporary tablespace TEMP1;

SQL> alter user <username> temporary tablespace TEMP1; - optional

sql> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

and

Resizing (or Recreating) the Temporary Tablespace [ID 409183.1] 

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING 
DATAFILES; 

Database altered. 

SQL> ALTER TABLESPACE temp ADD 
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m 
2 AUTOEXTEND ON NEXT 
250m MAXSIZE UNLIMITED; 

Tablespace altered. 

On some platforms 
(i.e. Windows 2000), it is possible for the tempfile to be deleted from 
DBA_TEMP_FILES but not from the hard drive of the server. 
If this occurs, 
simply delete the file using regular O/S commands. 

SQL> SELECT 
tablespace_name, file_name, bytes 
2 FROM dba_temp_files WHERE 
tablespace_name = 'TEMP'; 

TABLESPACE_NAME FILE_NAME BYTES 

----------------- -------------------------------- -------------- 
TEMP 
/u02/oradata/TESTDB/temp01.dbf 536,870,912 

If users are currently 
accessing the tempfile that you are attempting to drop, you may receive the 
following error: 

SQL> ALTER DATABASE TEMPFILE 
'/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES; 
ALTER DATABASE 
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES 
* 

ERROR at line 1: 
ORA-25152: TEMPFILE cannot be dropped at this time 

How to use command line to generate AWR and ADDM report?

@$ORACLE_HOME/rdbms/admin/awrrpt.sql 

@$ORACLE_HOME/rdbms/admin/addmrpt.sql  

@?/rdbms/admin/ashrpt.sql

How to create temporary tablespace

create temporary tablespace temp2 tempfile '/data/tb_temp/temp2.dbf' size 100m autoextend on next 10m flashback off; 

ALTER USER "user1"  TEMPORARY TABLESPACE "TEMP2"