Skip to content

How Oracle Resize Temp Tablespace

  • by

Alter Database Tempfile Resize

Upsizing

Upsizing a tempfile is easy, just make sure you set a larger size than the original but no more than the maximum size of a data file. For example:

SQL> alter database tempfile '+DATA/ORCLCDB/ORCLPDB/temp01.dbf' resize 24g;

Database altered.

Downsizing

As for downsizing a tempfile, it needs to be careful. Downsizing from current size to a smaller size does not always succeed, but you can try it.

SQL> alter database tempfile '+DATA/ORCLCDB/ORCLPDB/temp16.dbf' resize 10m;

Database altered.

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.

Shrinking

Before we see how to shrink tempfile. Let's see how much size of the temporary tablespace is currently.

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 2 ways that can reduce the overall size of a temporary tablespace.

  1. Shrink Temp Tablespace
  2. Drop Tempfiles

A. Shrink Temp Tablespace

1. 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.

2. Shrink TempFiles

First, we have to prepare ALTER TABLESPACE TEMP SHRINK TEMPFILE statement for each tempfile.

Compose SHRINK TEMPFILE Statements

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 '+DATA/ORCLCDB/ORCLPDB/temp01.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp02.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp03.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp04.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp05.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp06.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp07.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp08.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp09.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp10.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp11.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp12.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp13.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp14.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp15.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp16.dbf' KEEP 10M;

16 rows selected.

Execute SHRINK TEMPFILE Statements

Now we can shrink them one by one.

SQL> set timing on;
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp01.dbf' KEEP 10M;

Tablespace altered.

Elapsed: 00:04:16.57
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ORCLPDB/temp02.dbf' KEEP 10M;

Tablespace altered.

Elapsed: 00:00:01.42
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/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

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

3. 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.

B. 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 '+DATA/ORCLCDB/ORCLPDB/temp16.dbf' offline;

Database altered.

SQL> alter database tempfile '+DATA/ORCLCDB/ORCLPDB/temp16.dbf' drop including datafiles;

Database altered.

SQL> alter database tempfile '+DATA/ORCLCDB/ORCLPDB/temp15.dbf' offline;

Database altered.

SQL> alter database tempfile '+DATA/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.

Leave a Reply

Your email address will not be published.