Resizing a temporary tablespace may have 2 meanings in Oracle, one is to increase its size, the other is to shrink its size. In this post, we'll introduce these sections below:
Increase Temp Tablespace
When the temp tablespace is full or nearly full, you may consider to raise its size. In this post, there're 2 ways to increase a temporary tablespace.
Upsize Tempfile
Upsizing a tempfile is easy, just make sure you set a larger size than the original one, but no more than the maximum size of a data file. Here we use ALTER DATABASE TEMPFILE RESIZE statement.
Alter Database Tempfile Resize
SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/temp01.dbf' resize 24g;
Database altered.
In which, 24G is larger than current size and less than 32G.
Add Tempfile
If your temporary tablespace is usually full, you need more tempfiles to support queries.
Alter Database Add Tempfile
SQL> alter tablespace systemp add tempfile '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace altered.
Shrink Temp Tablespace
Before we see how to shrink tempfile. Let's see how much size of the temporary tablespace currently is.
SQL> column gb format 9999.99;
SQL> select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = 'TEMP';
GB
--------
464.20
As you can see, it's almost 500 GB among 16 tempfiles.
There're 3 ways that can reduce the overall size of a temporary tablespace.
Downsize Tempfile
As for downsizing a tempfile, it needs to be careful. Here we use ALTER DATABASE TEMPFILE RESIZE statement.
Alter Database Tempfile Resize
SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/temp16.dbf' resize 10m;
Database altered.
Downsizing from current size to a smaller size does not always succeed, but you can try it. If you saw ORA-03297 in RESIZE statement, you may try another way around.
A safer way to reduce overall temp tablespace size is to use shrinking instead of downsizing.
Shrink Tempfile
We can shrink a whole temp tablespace in one statement or shrink individual tempfile.
Shrink Whole Tablespace
I know I can shrink the whole tablespace as small as it can like this:
SQL> alter tablespace temp shrink space;
In this case, I'd like to shrink all tempfiles back to the initial size which is 10 MB for each.
Shrink Individual TempFile
First, we have to prepare ALTER TABLESPACE TEMP SHRINK TEMPFILE statement for each tempfile. So we compose their executable statements as followings.
SQL> set linesize 120 pagesize 0;
SQL> select 'ALTER TABLESPACE ' || tablespace_name || ' SHRINK TEMPFILE ''' || file_name || ''' KEEP 10M;' stmt from dba_temp_files where tablespace_name = 'TEMP';
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp01.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp03.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp04.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp05.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp06.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp07.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp08.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp09.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp10.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp11.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp12.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp13.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp14.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp15.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp16.dbf' KEEP 10M;
16 rows selected.
Now we can shrink them one by one.
SQL> set timing on;
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp01.dbf' KEEP 10M;
Tablespace altered.
Elapsed: 00:04:16.57
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp02.dbf' KEEP 10M;
Tablespace altered.
Elapsed: 00:00:01.42
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/oradata/ORCLCDB/ORCLPDB/temp03.dbf' KEEP 10M;
Tablespace altered.
Elapsed: 00:00:01.41
...
As you can see, it consumed considerably more time to shrink the first tempfile than others. If it took over 10 minutes to shrink a tempfile, you may consider to cancel the operation, restart the database then do it again.
DFS Lock Handle in RAC
If the shrinking operation is staying in DFS Lock Handle wait event for a long time in a RAC environment, you may consider to restart the RAC database with only one open instance, which can avoid global lock problem, then do the operation again.
[oracle@primary01 ~]$ srvctl stop database -d ORCLCDB
[oracle@primary01 ~]$ srvctl start instance -d ORCLCDB -i ORCLCDB1
Check Current Size of Temporary Tablespace
To verify the result, we calculate the temporary tablespace again.
SQL> column mb format 9999.99;
SQL> select sum(bytes)/1024/1024 mb from dba_temp_files where tablespace_name = 'TEMP';
MB
--------
161.63
Yes, it's shrunk.
Drop Tempfile
Sometimes, downsizing a tempfile throws ORA-03297 to alert you that user's data is still beyond the requested value of size. Moreover, the wait event DFS Lock Handle is really painful, especially for DBA who have a lot of things to do.
Luckily, we can also drop some tempfiles from the temporary tablespace. Usually, we drop tempfiles from the last one. The standard procedure is to offline the target tempfile then drop it.
SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/temp16.dbf' offline;
Database altered.
SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/temp16.dbf' drop including datafiles;
Database altered.
SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/temp15.dbf' offline;
Database altered.
SQL> alter database tempfile '/oradata/ORCLCDB/ORCLPDB/temp15.dbf' drop including datafiles;
Database altered.
...
Please note that, it does not always need to offline the tempfile before dropping it, as long as the tempfile is not in use, otherwise users would complain about error ORA-01135.