How to open a database without its TEMP tablespaces

Home

Toolkit:

• Index
• Scripts
• Quick Tips
• Articles / Publications
• Links

My OraFAQ Blog

Contact me

Steps to open database without a backup of the temp tablespace in place. (Or with any other tablespace missing.)

Note: If you are using Oracle9i, use SQL*Plus instead of Server Manager.

Starting from a closed database:

Connect as SYSDBA. Mount the database.

SVRMGR> STARTUP MOUNT PFILE='{location_of_pfile}'; 

Offline drop the datafile.

SVRMGR> ALTER DATABASE DATAFILE '{full_path_file_name}' OFFLINE DROP; 

Open the database.

SVRMGR> ALTER DATABASE OPEN;

Drop the tablespace.

SVRMGR> DROP TABLESPACE {tablespace_name} INCLUDING CONTENTS;   

Recreate the tablespace.

SVRMGR> CREATE TABLESPACE  DATAFILE  
        '{full_path_file_name}' SIZE {size_in_bytes};

---

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...