Re: extending tablespace TEMP

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Thu, 20 Dec 2001 00:26:04 -0800
Message-ID: <3C21A09C.ECFF5AF4@ntsource.com>

If you don't mind the dropped file remaining in file$, you can actually "offline drop" a datafile from a non-system tablespace without dropping the tablespace and without touching the data dictionary. The following was done on Windows 2000, Oracle 8.1.7:

SVRMGR> create tablespace testspace datafile 'c:\file1.dbf' size 64k; Statement processed.
SVRMGR> alter tablespace testspace add datafile 'c:\file2.dbf' size 64k; Statement processed.
SVRMGR> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

(At this point I removed the file c:\file2.dbf.)

SVRMGR> startup
ORACLE instance started.

Total System Global Area                        223119388 bytes
Fixed Size                                          75804 bytes
Variable Size                                    79622144 bytes
Database Buffers                                143343616 bytes
Redo Buffers                                        77824 bytes

Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file ORA-01110: data file 12: 'C:\FILE2.DBF'
SVRMGR> alter database datafile 'c:\file2.dbf' offline drop; Statement processed.
SVRMGR> alter database open;
Statement processed.

Trying this on the system tablespace will produce the results that Niall Litchfield documented.

I'm not sure that the above procedure has much benefit since usually there will be data in the datafile one wants to remove. However, it does show that statements like "one can never remove a datafile from a tablespace" are not quite correct.

Frank Hubeny

koert54 wrote:

> DO NOT DO THIS ON PRODUCTION DB !!!! NOT SUPPORTED AT ALL ! (however - i've
> seen it doing it a couple of times)
> This procedure only works if no extents are allocated in the datafile that
> you want to drop - this means there are no references in uet$
>
> Testcase:
> create tablespace foo datafile 'd:\utl\foo01.dbf' size 1M ;
> create table t (i integer) tablespace foo ;
> insert into t values (1) ;
> commit ;
>
> alter database backup controlfile to trace ;
>
> alter tablespace foo add datafile 'd:\utl\foo02.dbf' size 1M ;
> select name, file# from v$datafile ;
> D:\UTL\FOO02.DBF 7
>
> alter database datafile 'd:\utl\foo02.dbf' offline drop ; (in noarchivelog)
>
> delete from file$ where file#=7 ;
> delete from fet$ where file#=7 ;
> commit ;
>
> shutdown ;
>
> startup nomount ;
> recreate the controlfile (without foo02.dbf !!!)
> alter database open ;
> -> foo02.dbf never existed
> select * from t;
> -> 1
>
> "Mark D Powell" <mark.powell_at_eds.com> wrote in message
> news:178d2795.0112181316.3f4b1255_at_posting.google.com...
> > Jan Eliasen <eliasen_at_cs.auc.dk> wrote in message
> news:<Pine.GSO.4.33.0112181052020.4488-100000_at_borg.cs.auc.dk>...
> > > Hi there.
> > >
> > > How do I do that? I'm trying to do a simple
> > >
> > > select * from h2alle one, h2alle two where one.dato = two.dato;
> > >
> > > but I get an error;
> > >
> > > ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
> > >
> > > How do I fix this? I can't find any information about it.
> > >
> > > Oh, and I have added a datafile to the SYSTEM tablespace. Does anyone
> know
> > > how I delete it from the tablespace again? I have accidently deleted the
> > > file on the harddrive, but I can't seem to find a way to remove it from
> > > the tablespace?!?
> >
> > The last I knew if you drop a file from a tablespace the tablespace
> > becomes invalid and requires recovery. Since you dropped a file from
> > the system tablespace you must perform recovery. Someone should file
> > an enhancement request that if a file has no objects allocated to it
> > that you should be able to drop it without invalidating the
> > tablespace.
> >
> > -- Mark D Powell --
Received on Thu Dec 20 2001 - 02:26:04 CST