Can I startup the database with a tablespace offline?

Hi All,

I have an oracle database running with NOARCHIVELOG mode. It has a big tablespace USER01_TS that I want to drop to reclaim the diskspace from the OS. But I need to make sure I really don't need anything inside that tablespace. Here is my approach:

1. execute: alter tablespace USER01_TS offline normal.
2. backup the associated physical datafiles of USER01_TS to tape, then remove those files from the OS level to reclaim the disk space.
3. Shutdown the database to do a cold backup.
4. Restart the database after the cold backup.

After step 4, I will run the database for 2 weeks before dropping the tablespace USER01_TS. If I find that I need the data in USER01_TS within 2 weeks, then I will restore those datafiles from tape and put the tablespace online again.

My question is: will this approach work? will I get any error in Step 3 or 4?

Thanks a lot,

Chris

You can..

Quote:

---

SQL> alter tablespace USERS offline;

Tablespace altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 124
Current log sequence 126
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 284008448 bytes
Fixed Size 2087944 bytes
Variable Size 167773176 bytes
Database Buffers 109051904 bytes
Redo Buffers 5095424 bytes
Database mounted.
Database opened.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS OFFLINE
TBSSPC ONLINE
EXAMPLE ONLINE
EXAMPLE2 ONLINE

8 rows selected.

---

Thanks,

Hi Thomas,

Thanks so much for your prompt reply. I have one more question: if the associated datafiles of that offline tablespace are not there anymore (since I will backup them up to tape, then remove them from the OS), will that cause any error in shutdown or startup?

Thanks a lot,

-- Chris

No, as Oracle will not check OFFLINE tablespaces while STARTUP/SHUTDOWN.

Thanks,

Thanks a lot for your answer :)

Regards,

-- Chris