Recover some HD, HUGE temporary file 
Author Message
 Recover some HD, HUGE temporary file

Got an issue.  Oracle 8.1.5 on Win NT4

I need to recover a large amount of space on the hard drive.  We had a
mishap with the temporary tablespace.  The datafile in the last day has
grown over 3 Gig.  I have coalesced the free extents and it is now 99%
free.  Needless to say, it has caused a space crunch.

I need to desperately resize the datafile down to something more
concise.  The tablespace is a temporary type.

Can I add another datafile to the TEMPORARY_DATA tablespace, then take
the large datafile offline, and subsequently delete it?  Being that it
is a 'temporary', once I add anther file I should be able to safely
remove it, right?

Will this work?
* ALTER TABLESPACE "TEMPORARY_DATA" ADD DATAFILE 'tmp2orcl.ora'  SIZE
5M

* ALTER DATABASE DATAFILE 'E:\ORANT81\DATABASE\TMP1ORCL.ORA' OFFLINE

* Delete the file tmp1orcl.ora

If not, what are my other options?
Erik Dantes



Tue, 23 Mar 2004 21:04:33 GMT
 Recover some HD, HUGE temporary file

Erik,

If you have been able to free up the excess space, you should be able to
resize the file; e.g:
ALTER DATABASE DATAFILE 'filename' RESIZE 5M;

The solution you have posted below would probably work for you, but you
would also have to drop and recreate the tablespace in order to get rid
of the newly removed file. OFFLINE DROP doesn't remove a file from a
tablespace - it leaves it there in 'Recovery' mode forever.

Hope that helps,
Paul.

Quote:

> Got an issue.  Oracle 8.1.5 on Win NT4

> I need to recover a large amount of space on the hard drive.  We had a
> mishap with the temporary tablespace.  The datafile in the last day has
> grown over 3 Gig.  I have coalesced the free extents and it is now 99%
> free.  Needless to say, it has caused a space crunch.

> I need to desperately resize the datafile down to something more
> concise.  The tablespace is a temporary type.

> Can I add another datafile to the TEMPORARY_DATA tablespace, then take
> the large datafile offline, and subsequently delete it?  Being that it
> is a 'temporary', once I add anther file I should be able to safely
> remove it, right?

> Will this work?
> * ALTER TABLESPACE "TEMPORARY_DATA" ADD DATAFILE 'tmp2orcl.ora'  SIZE
> 5M

> * ALTER DATABASE DATAFILE 'E:\ORANT81\DATABASE\TMP1ORCL.ORA' OFFLINE

> * Delete the file tmp1orcl.ora

> If not, what are my other options?
> Erik Dantes


--
=====================================================================
Paul Harrington - Oracle DBA/Developer, Orbiscom, Dublin.

=====================================================================


Tue, 23 Mar 2004 21:42:27 GMT
 Recover some HD, HUGE temporary file
Thank you, that worked perfectly...and without hassle.

Erik

Quote:

> Erik,

> If you have been able to free up the excess space, you should be able to
> resize the file; e.g:
> ALTER DATABASE DATAFILE 'filename' RESIZE 5M;

> The solution you have posted below would probably work for you, but you
> would also have to drop and recreate the tablespace in order to get rid
> of the newly removed file. OFFLINE DROP doesn't remove a file from a
> tablespace - it leaves it there in 'Recovery' mode forever.

> Hope that helps,
> Paul.


> > Got an issue.  Oracle 8.1.5 on Win NT4

> > I need to recover a large amount of space on the hard drive.  We had a
> > mishap with the temporary tablespace.  The datafile in the last day has
> > grown over 3 Gig.  I have coalesced the free extents and it is now 99%
> > free.  Needless to say, it has caused a space crunch.

> > I need to desperately resize the datafile down to something more
> > concise.  The tablespace is a temporary type.

> > Can I add another datafile to the TEMPORARY_DATA tablespace, then take
> > the large datafile offline, and subsequently delete it?  Being that it
> > is a 'temporary', once I add anther file I should be able to safely
> > remove it, right?

> > Will this work?
> > * ALTER TABLESPACE "TEMPORARY_DATA" ADD DATAFILE 'tmp2orcl.ora'  SIZE
> > 5M

> > * ALTER DATABASE DATAFILE 'E:\ORANT81\DATABASE\TMP1ORCL.ORA' OFFLINE

> > * Delete the file tmp1orcl.ora

> > If not, what are my other options?
> > Erik Dantes

> --
> =====================================================================
> Paul Harrington - Oracle DBA/Developer, Orbiscom, Dublin.

> =====================================================================



Tue, 23 Mar 2004 22:46:40 GMT
 Recover some HD, HUGE temporary file
Also erik set the datafile NOT to autoextend. You expect the temporary
tablespace to fill and then reuse existing empty segments. however if
autoextend is on I believe that the file just keeps growing.

regards

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Quote:
> Thank you, that worked perfectly...and without hassle.

> Erik


> > Erik,

> > If you have been able to free up the excess space, you should be able to
> > resize the file; e.g:
> > ALTER DATABASE DATAFILE 'filename' RESIZE 5M;

> > The solution you have posted below would probably work for you, but you
> > would also have to drop and recreate the tablespace in order to get rid
> > of the newly removed file. OFFLINE DROP doesn't remove a file from a
> > tablespace - it leaves it there in 'Recovery' mode forever.

> > Hope that helps,
> > Paul.


> > > Got an issue.  Oracle 8.1.5 on Win NT4

> > > I need to recover a large amount of space on the hard drive.  We had a
> > > mishap with the temporary tablespace.  The datafile in the last day
has
> > > grown over 3 Gig.  I have coalesced the free extents and it is now 99%
> > > free.  Needless to say, it has caused a space crunch.

> > > I need to desperately resize the datafile down to something more
> > > concise.  The tablespace is a temporary type.

> > > Can I add another datafile to the TEMPORARY_DATA tablespace, then take
> > > the large datafile offline, and subsequently delete it?  Being that it
> > > is a 'temporary', once I add anther file I should be able to safely
> > > remove it, right?

> > > Will this work?
> > > * ALTER TABLESPACE "TEMPORARY_DATA" ADD DATAFILE 'tmp2orcl.ora'  SIZE
> > > 5M

> > > * ALTER DATABASE DATAFILE 'E:\ORANT81\DATABASE\TMP1ORCL.ORA' OFFLINE

> > > * Delete the file tmp1orcl.ora

> > > If not, what are my other options?
> > > Erik Dantes

> > --
> > =====================================================================
> > Paul Harrington - Oracle DBA/Developer, Orbiscom, Dublin.

> > =====================================================================



Tue, 23 Mar 2004 23:06:04 GMT
 Recover some HD, HUGE temporary file
I think the temp tablespace datafile only grows (if autoextend on)
when all extents of the sort segment are currently being used and a
new disk sort is requested. So it's not that scary. It's just probably
80% of the DBAs' common practice to keep autoextend off for all
datafiles. Temp datafiles are not special in this sense.

Yong Huang

Quote:

> Also erik set the datafile NOT to autoextend. You expect the temporary
> tablespace to fill and then reuse existing empty segments. however if
> autoextend is on I believe that the file just keeps growing.

> regards

> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK



Wed, 24 Mar 2004 12:31:14 GMT
 Recover some HD, HUGE temporary file

Quote:

> Got an issue.  Oracle 8.1.5 on Win NT4

> I need to recover a large amount of space on the hard drive.  We had a
> mishap with the temporary tablespace.  The datafile in the last day has
> grown over 3 Gig.  I have coalesced the free extents and it is now 99%
> free.  Needless to say, it has caused a space crunch.

> I need to desperately resize the datafile down to something more
> concise.  The tablespace is a temporary type.

> Can I add another datafile to the TEMPORARY_DATA tablespace, then take
> the large datafile offline, and subsequently delete it?  Being that it
> is a 'temporary', once I add anther file I should be able to safely
> remove it, right?

> Will this work?
> * ALTER TABLESPACE "TEMPORARY_DATA" ADD DATAFILE 'tmp2orcl.ora'  SIZE
> 5M

> * ALTER DATABASE DATAFILE 'E:\ORANT81\DATABASE\TMP1ORCL.ORA' OFFLINE

> * Delete the file tmp1orcl.ora

> If not, what are my other options?
> Erik Dantes


  Eric,

  You are right, that is the one way of doing it , but with some
corrections.

  Once you created another TEMP tabpespace you have to change the users
temporary tablespaces to the new one so when they need it they are not
going to allocate space in the old one.

  Once you take the old TEMP tablespace offline, DROP the tablespace
before deleting the datafile. Then recreate the tablespace and switch the
users again to the old TEMP tablespace.

  Good Luck.

  Trifon Anguelov
   Senior Oracle DBA

  http://www.dbaclick.com



Mon, 29 Mar 2004 12:46:47 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Recover data from crashed HD

2. temporary tables on hd or in memory?

3. On the fly: preventing huge temporary tables?

4. Word doc data files and folders mereged into huge files and changed names

5. How tp position DB files on two physical HD

6. HELP!!!! sql HD crashed, have .dat files

7. Creating new database with existing Physical file name on HD

8. How to spool query result to file on HD

9. Redolog file can not be written to HD

10. v$xatrans$ - .trc files fill up my HD

11. Recover mdf file without ldf file

12. Recovering Files and opening files


 
Powered by phpBB® Forum Software