Solved

Standby Temp Datafiles

Posted on 2014-11-20
5
216 Views
Last Modified: 2014-11-21
I put standby in read only mode to allow some queries to run there instead of on primary.  Queries had a bunch of aggregation and sorts.  Now i'm noticing the temp01.dbf and temp02.dbf datafiles are much larger than those on primary and i'm about to run out of space.
Is there a way  to shrink temp datafiles on standby?
0
Comment
Question by:xoxomos
5 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
ID: 40456663
No.

But an alternative is to create a Reporting Database based on standby.
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 125 total points
ID: 40456801
i would recreate them
> adding new tempfiles with autoextend off
and then dropping the old ones
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
ID: 40457614
You will likely have to create a new temporary tablespace and to an "alter database..." command to make this the default temp tablespace and/or do "alter user ..." commands for each user to change their temporary tablespace to the new one.  Then you may have to wait a few minutes (or hours or days) or do a database shutdown and restart to get all activity out of the current temp tablespace.  Then you can drop the current temp tablespace ("including contents and datafiles") to recover that disk space.

I prefer to *NOT* use "autoextend" on temp tablespace files for this reason.  You can't prevent users or developers from creating an accidental cartesion join that could quickly fill up all of the space on a disk (or file system).  With fixed-size temp files, any users who do this will get an Oracle error and they may come complaining to you.  But, at least they won't fill up a disk and cause more problems.
0
 

Author Closing Comment

by:xoxomos
ID: 40457648
Thanks.
0
 

Author Comment

by:xoxomos
ID: 40457996
Yeps, that zero data reporting database sounds just the ticket :-)
Thanx again.,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
PL/SQL Multiple PAYER_IDs with PAYER_TYPES are these types 3 38
SQL Retrieve Values 4 55
Export table into csv file in oracle 10 41
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now