Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: extending tablespace TEMP
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