Solved

Standby Temp Datafiles

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

809 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