Solved

Standby Temp Datafiles

Posted on 2014-11-20
5
214 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 36

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 34

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.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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

20 Experts available now in Live!

Get 1:1 Help Now