Resizing the Temp tablespace in Oracle

No Comments

In much the same way as with the Undo tablespace, you may want to reduce the size of the Temp tablespace in Oracle to reclaim space.

 

11g

—-
ALTER TABLESPACE [tbsp_name] SHRINK SPACE KEEP 30M;

10g and earlier

——————

Unfortunatly, if the temp tablespace is full (as will be the case if it has been expanding) you will not be able to reduce he size directly. Also, restarting the database will not clear out the data.

The process or doing this is similar to that of reducing the size of the undo tablespace.

——

1. First, find the location of the temp tablespace datafile:

select * from dba_temp_files;

If there is more than one, you can check which one is the default with the following query:

select * from database_properties
where property_name like ‘%TABLESPACE’;

2. Next, generate a script to create the existing temp tablespace:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘TEMP’) from dual;

i.e.
CREATE TEMPORARY TABLESPACE “TEMP” TEMPFILE
‘/TESTDB/oradata/TESTDB/temp01.dbf’ SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

save this.

3. Use that script to create a new temporary tablespace. Alter the tablespace anme, file name and size as appropriate.

You only need to create a small file which you will be deleting.

CREATE TEMPORARY TABLESPACE “TEMP2” TEMPFILE ‘/TESTDB/oradata/TESTDB/temp02.dbf’ SIZE 50M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

4. Make the new temporary tablespace the default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

5. Make sure that the new tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

*** If Database version is < 9i see below ***

6. Drop the old tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

7. Re-create the old tablespace using the script from step 2 – changing the size as appropriate.

8. Set the re created tablespace to be the default

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

9. Make sure that the correct tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

10. Drop the other tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

If you are using a database version below 9i then you will need to do this step before step 6:

Use the below query to identify any residual dead connections still alloctaed to the first TEMP tablespace. Dropping the TEMP tablespace (Step 7) will not complete until these dead session dissapear or are killed. The dead connections can be killed using the ALTER SYSTEM KILL command with the sid and Serial# returned from the query.

SELECT b.tablespace
, b.segfile#
, b.segblk#
, b.blocks
, a.sid
, a.serial#
, a.username
, a.osuser
, a.status
FROM v$session a
, v$sort_usage b
WHERE a.saddr = b.session_addr

Categories: Oracle Tasks Tags: Tags: ,

How to Reduce the Size of the Undo Tablespace in Oracle

No Comments

There is no function in Oracle (up to 11g currently) that will allow you to reduce the size of the undo tablespace.

Sometimes you will need to free up space and the best way to get it will be to reduce the size of the undo tablespace.

The way to do this is basically to create a new undo tablespace, drop the old one and recreate it with less space.

Here are the exact steps:

1. First, create a script to recreate the undo tablespace exactly as it was (you will need to tweak it to use less space)

In sqlplus:

You can find the location of the undo tablespace datafile with:

select * from dba_data_files;

set long 20000 (to make sure that the output isnt truncated.)

Create the SQL to recreate the tablespace later:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘UNDOTBS1’) from dual;

2. Create a second undo tablespace to use temporarily while you remove the first.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘[FILELOCATION/FILENAME’ SIZE 50 M AUTOEXTEND OFF;

3. You need to set the new Undo tablespace as default otherwise you wont be able to delete the first;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

4. Make sure that the default has been set correctly:

SHOW PARAMETER undo_tablespace;

5. Now drop the original tablespace:

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

6. Use you generated SQL to create the new undo tablespace. Make sure that you change the script to set the size correctly.

7. Next, you need to set the new tablespace as the default:

ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

8. Make sure that the new tablespace is now the default:

SHOW PARAMETER undo_tablespace;

9. Drop the second undo tablespace.

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

Categories: Featured, Oracle Tasks Tags: Tags: ,

Oracle Undo

No Comments

When data is changed in the oracle database, the information needed to undo the changes is recorded so that the change can be undone (Rolled back). The record is basically just the original information.

Information is recorded in rollback segments and the undo tablespaces.

This information is also used to provide read consistency which allows long running transactions to always obtain the data as it was at the time that it began.
The recommended method of managing Undo is system managed Undo (SMU)
Set the UNDO_MANAGEMENT initialisation parameter to AUTO

Create an Undo Tablespace

CREATE UNDO TABLESPACE

You must also set the UNDO_TABLESPACE initilization parameter

To size the undo tablespace, you need to know how long the data is kept for: UNDO_RETENTION parameter
as well as how much undo is generated per second.

Find the undo rate:

You can use the dynamic performance view v$UNDOSTAT

If you recieve the error: “Snapshot too old”, increasing the retention peroid will help.
Retention is set to 30 seconds by default which is nrmally long enough but you can set this higher if necessary.

Categories: Oracle Basics Tags: Tags: , ,