Standby Temp Datafiles

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?
Who is Participating?
MikeOM_DBAConnect With a Mentor Commented:

But an alternative is to create a Reporting Database based on standby.
Geert GConnect With a Mentor Oracle dbaCommented:
i would recreate them
> adding new tempfiles with autoextend off
and then dropping the old ones
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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.
xoxomosAuthor Commented:
xoxomosAuthor Commented:
Yeps, that zero data reporting database sounds just the ticket :-)
Thanx again.,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.