Wednesday, April 18, 2007

Upgrading to Oracle Database R2 10.2.0.1 and the TEMP tablespace

When running utlu102i.sql you get
WARNING: --> TEMP tablespace is not large enough for the upgrade.

This is because your current default TEMP tablespace is not locally managed. The error could certainly be better! Here is what you need to do.

1. Create a new temporary tablespace that is locally managed:
create temporary tablespace temp2
tempfile '/opt/oradata/orcl/temp201.dbf' size 64m
extent management LOCAL uniform size 1m
autoextend on next 32m maxsize 128m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

2. Now, if you want to have the default tablespace you need to drop the TEMP tablespace, recreate is as locally managed, alter the database to have it as the default temporary tablespace, then drop TEMP2.

drop tablespace temp including contents;

create temporary tablespace temp
tempfile '/opt/oradata/orcl/temp01.dbf' size 64m
extent management LOCAL uniform size 1m
autoextend on next 32m maxsize 128m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

drop tablespace temp2 including contents;

No comments: