Re: [Q] how to online drop tempfile???

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 28 Aug 2004 15:00:12 -0400
Message-ID: <pan.2004.08.28.19.00.03.864086@sbcglobal.net>

On Sat, 28 Aug 2004 06:29:56 -0700, aaa wrote:

> alter database tempfile

Here is how you do it. I corrupted my temporary tablespace and here is the outcome:

SQL> alter database tempfile '/oradata/10g/oracle/temp01.dbf' resize 150M; alter database tempfile '/oradata/10g/oracle/temp01.dbf' resize 150M *
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests ORA-01110: data file 201: '/oradata/10g/oracle/temp01.dbf'  

This was caused by deliberate corruption.  

SQL> create temporary tablespace temp1
  2 tempfile '/oradata/10g/oracle/temp101.dbf' size 64M;  

Tablespace created.  

SQL> alter database default temporary tablespace temp1;  

Database altered.  

SQL> drop tablespace temp ;  

Tablespace dropped.  

SQL> !rm /oradata/10g/oracle/temp01.dbf  

SQL> ed
SP2-0110: Cannot create save file "/tmp/buffer.sql" SQL> create temporary tablespace temp
  2 tempfile '/oradata/10g/oracle/temp01.dbf'   3 size 128M autoextend on next 64M maxsize 257M   4 extent management local;  

Tablespace created.  

SQL> alter database default temporary tablespace temp;  

Database altered.  

SQL> drop tablespace temp1 including contents dand datafiles; drop tablespace temp1 including contents dand datafiles

                                         *

ERROR at line 1:
ORA-02173: invalid option for DROP TABLESPACE

Stupid typing error....    

SQL> drop tablespace temp1 including contents and datafiles;  

Tablespace dropped.  

SQL>
Now comes a clean restart:

SQL*Plus: Release 10.1.0.3.0 - Production on Sat Aug 28 14:59:15 2004  

Copyright (c) 1982, 2004, Oracle. All rights reserved.  

Connected to an idle instance.  

SQL> startup
ORACLE instance started.  

Total System Global Area 335544320 bytes

Fixed Size                   779016 bytes
Variable Size             145759480 bytes
Database Buffers          188743680 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.
SQL> We're done.

-- 
A city is a large community where people are lonesome together.

Received on Sat Aug 28 2004 - 14:00:12 CDT