Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: extending tablespace TEMP

Re: extending tablespace TEMP

From: koert54 <koert54_at_nospam.com>
Date: Wed, 19 Dec 2001 22:33:02 GMT
Message-ID: <yA8U7.659$9D7.135873653@hebe.telenet-ops.be>

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 Wed Dec 19 2001 - 16:33:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US