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?
xoxomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
No.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
i would recreate them
> adding new tempfiles with autoextend off
and then dropping the old ones
0
Mark GeerlingsDatabase 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.
0
xoxomosAuthor Commented:
Thanks.
0
xoxomosAuthor Commented:
Yeps, that zero data reporting database sounds just the ticket :-)
Thanx again.,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.