0

Quick question that I cannot seem to find the answer to. I have some indexes that I want to rebuild at a scheduled interval, but they are fairly large and fill up the archive_log_dest_3 if I leave them unattended. So, I wrote a script that changes the dest_3 parameter in the pfile to a much larger mount point, and then changes it back afterwards, but the script overwrites the PFILE with an updated version. Would overwriting the PFILE while the database is on and doing these index rebuilds be dangerous or not recommended? Thanks!

1
  • Rebuilding indexes on a regular basis is not a good idea; if the original allocations are good, or you let Oracle manage their size, all will be well. Indexes will get bigger as time goes by; if you are rebuilding more than a few times a year, something else is wrong. Commented Oct 28, 2015 at 0:02

2 Answers 2

1

Why do you need it? pfile is read only once when database starts. Use spfile and alter system set parameter = value scope=memoryor alter system set parameter = value scope=both.

1

Question: Is overwriting the PFILE with the database ON dangerous?

Answer: No. The PFILE is read only when the database starts, and that's if no SPFILE is there or if the PFILE argument is passed when invoking the startup command. Otherwise, when you start the database, the SPFILE is read for initial memory values, control files location and such basic information. After that, everything will remain in memory.

Note: If you change the PFILE at the OS level, you need to bounce the database to make the changes take effect.

On the other hand, using the ALTER SYSTEM SET ... SCOPE=MEMORY may be used as @ibre5041 suggested and achieve the result you want. Nevertheless, you're "losing" those archives from the original location, which makes me think you could as well add the NOLOGGING clause and avoid the generation of the logs that are filling up your storage.

So my personal suggestion would be to use the command

alter index <INDEX_NAME> rebuild parallel <#_OF_PROCS> NOLOGGING;

That will rebuild the index without generating archived log files.

Hope this helps you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.