Wednesday, September 16, 2009

How to Change Static parameters in SPFILE and PFILE

Change Static parameters in SPFILE:
Changing static parameters requires the SPFILE option for the SCOPE clause with ALTER SYSTEM Statement and changes applies to SPFILE only. The changes cannot be applied to active Instance and the database needs to be restarted to take effect the modified parameters.

You can identify the static parameters using below query
SQL> select name, value, issys_modifiable from v$parameter
where name='processes';
NAME                     VALUE          ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 FALSE

All parameter that have the column ISSYS_MODIFIABLE value FALSE in the V$PARAMETER view are STATIC parameters and remaining are DYNAMIC parameters in Oracle database.

The ALTER SYSTEM command with SCOPE=SPFILE the will not update the value in the V$PARAMETER view but it will show the new value in the V$SPPARAMETER view as Oracle SPFILE parameter is updated.

For ex:-
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
               *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Above statement failed because “processes” is Static parameter and can be used with SCOPE=SPFILE only.

SQL> alter system set processes=200 scope=spfile;
System altered.

You can view the new value in V$SPPARAMETER view as the SPFILE is updated.

SQL> select name, value, isspecified from v$spparameter
where name ='processes';
NAME                     VALUE         ISSYS_MODIFIABLE
------------------- -------------- ---------------------------
Processes               150                 TRUE

Changing Static parameters in PFILE:
Edit the “$ORACLE_HOME/dbs/init.ora” file with new value and bounce the database to take effect the modified parameters.

Identify weather using PFILE or SPFILE:
To check SPFILE or PFILE used by Database or instance, run below commands.

You can see the Oracle SPFILE location.

SQL > show parameter spfile;
NAME                    TYPE        VALUE
------------------ ----------- -----------------------------------------
spfile                     string          /oracle/v10201/dbs/spfileOradb.ora

SQL > select name, value from v$parameter where name = 'spfile';
NAME          VALUE
------------ ------------------------------------------------------------
spfile             /oracle/v10201/dbs/spfileOradb.ora

SQL> select count(1) from v$spparameter where isspecified = 'TRUE';

Returns a NON-ZERO value if SPFILE is in use.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

106 comments:

  1. A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file. An SPFILE is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

    2gb micro sd karte

    ReplyDelete
  2. Replies
    1. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Project Centers in Chennai.

      Software management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.

      Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully

      Delete
  3. Very nice article. I learned some knowledgeable information. Thanks for sharing.

    html training in chennai

    ReplyDelete
  4. We Develop app for your company or build website for your company and much more.....

    Applications For Android

    ReplyDelete
  5. Well, even after changing the process to 200, the query gave the output as 150 where you mentioned it to be having the new value. Because the database needs to be restarted.

    ReplyDelete
  6. http://transferefurniture.bcz.com/ شركة نقل عفش | http://transferefurniture.bcz.com/ شركة نقل اثاث بجدة | http://transferefurniture.bcz.com/ شركة نقل عفش بالرياض | http://transferefurniture.bcz.com/ شركة نقل عفش بالمدينة المنورة | http://transferefurniture.bcz.com/ شركة نقل عفش بالدمام
    http://khairyayman.inube.com/blog/5015576// شركة نقل عفش بجدة
    http://khairyayman.inube.com/blog/5015578// شركة نقل عفش بالمدينة المنورة

    ReplyDelete
  7. I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site. digital marketing training in chennai. data science training in bangalore.

    ReplyDelete
  8. Nice blog has been shared by you. it will be really helpful to many peoples who are all working under the technology.thank you for sharing this blog.
    big data training in chennai|

    ReplyDelete
  9. Its a very useful article and excellent information. Thanks for sharing such a nice article. Top cloud solution Services company| Austere Technologies

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Very interesting article with good informative content, thanks for sharing. ORACLE CLOUD SERVICES FOR APPLICATION DEVELOPMENT | MASSIL TECHNOLOGIES

    ReplyDelete
  12. Very good informative blog. Thanks for sharing such nice article.
    Best IT Security Services | Austere Technologies

    ReplyDelete
  13. Nice blog has been shared by you. it will be really helpful to many peoples who are all working under the technology.

    Weblogic Administration Training

    ReplyDelete
  14. Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us. Digital Marketing Training in chennai

    ReplyDelete
  15. Excellent information you made in this blog, very helpful information. Thanks for sharing.

    chartered accountant | Avinash college of commerce

    ReplyDelete
  16. This Blog Provides Very Useful and Important Information. I just Want to share this blog with my friends and family members. United States Medical Licensing Examination

    ReplyDelete
  17. Great article, really very helpful content you made. Thank you, keep sharing.

    Best Degree Colleges Hyderabad | Avinash College of Commerce

    ReplyDelete
  18. Finally I have found what I have been looking for since 2 hours. I really like your blog and articles you are writing for us. Independence Day Speech for Students in Tamil

    ReplyDelete
  19. Hi Thanks for the nice information its very useful to read your blog. We provide best Block Chain Services

    ReplyDelete
  20. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book ANDAMAN HOLIDAY PACKAGES @ 35999

    ReplyDelete
  21. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book CHEAP ANDAMAN PACKAGES @9999

    ReplyDelete
  22. Great blog, Its really give such wonderful information, that was very useful for me. Thanks for sharing with us.

    Dot Net Training in Chennai

    ReplyDelete
  23. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Best Travel Agency In India

    ReplyDelete
  24. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Andaman Tourism

    ReplyDelete
  25. Hi Thanks for the nice information its very useful to read your blog. We provide About Best Software Development Services

    ReplyDelete
  26. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book ANDAMAN TOUR PACKAGE@14599/

    ReplyDelete
  27. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    Digital Marketing Course in Chennai
    Digital Marketing Training in Chennai
    Online Digital Marketing Training
    SEO Training in Chennai
    Digital Marketing Course
    Digital Marketing Training
    Digital Marketing Courses

    ReplyDelete
  28. Hi Thanks for the nice information its very useful to read your blog. We provide best Find All Isfs Courses

    ReplyDelete
  29. Hi Thanks for the nice information its very useful to read your blog. We provide best Massil Technologies

    ReplyDelete

  30. This has been the best so far, how much intense and deeply written content. I am so confident this post will be so much famous. Kudos to the blogger.

    Oneplus Service Center in Chennai | Oneplus Service Centre | Oneplus Service Center | Oneplus Service Center near me | Oneplus Service Center in velachery | Oneplus Service Chennai

    ReplyDelete
  31. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us. Do check Six Sigma Training in Bangalore | Six Sigma Training in Dubai & Get trained by an expert who will enrich you with the latest trends.

    ReplyDelete
  32. Hi Thanks for the nice information its very useful to read your blog. We provide best Association Of Chartered Certified Accountants

    ReplyDelete
  33. Your article gives lots of information to me. Thanks for sharing.
    cima courses in hyderabad | ISFS

    ReplyDelete
  34. It's really a nice experience to read your post. Thank you for sharing this useful information. If you are looking for more about Best Machine learning training in chennai
    machine learning with python course in Chennai
    machine learning classroom training in chennai

    ReplyDelete
  35. Wow...What an excellent informative blog, really helpful. Thank you. Best Oracle DBA Course Training| orskl

    ReplyDelete
  36. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!!
    Blue Prism Training in Chennai
    Blue Prism Training Chennai
    Blue Prism Training in Tambaram
    Blue Prism Training in Velachery
    Blue Prism Training in OMR
    Blue Prism Training in TNagar

    ReplyDelete
  37. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
    machine learning course in bangalore

    ReplyDelete
  38. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    machine learning course bangalore

    ReplyDelete
  39. Nice Blog, When i was read this blog i learnt new things & its truly have well stuff related to developing technology, Thank you for sharing this blog.
    Oracle Training Institute in Chennai | Oracle Training in Chennai

    ReplyDelete
  40. Your info is really amazing with impressive content..Excellent blog with informative concept. Really I feel happy to see this useful blog, Thanks for sharing such a nice blog..
    If you are looking for any Big data Hadoop Related information please visit our website Big Data Training In Bangalore page!

    ReplyDelete
  41. I learned World's Trending Technology from certified experts for free of cost. I got a job in decent Top MNC Company with handsome 14 LPA salary, I have learned the World's Trending Technology from python training in btm layout experts who know advanced concepts which can help to solve any type of Real-time issues in the field of Python. Really worth trying Freelance SEO expert in Bangalore

    ReplyDelete
  42. Thank you for excellent article.You made an article that is interesting.
    AWS Solutions Architect courses in Bangalore with certifications.
    https://onlineidealab.com/aws-training-in-bangalore/

    ReplyDelete
  43. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing.. i Want to share Some data regarding the websphere message broker videos with free bundle videos is provided.

    ReplyDelete
  44. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. Machine Learning Final Year Projects In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    ReplyDelete
  45. keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore

    ReplyDelete
  46. Nice and useful information.Thanks for the effort and the results,impressed with your post.
    big data training in btm layout

    ReplyDelete
  47. This comment has been removed by a blog administrator.

    ReplyDelete
  48. I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic also visit Tableau Training videos l page and check for training

    ReplyDelete


  49. 2 of 65
    comment

    Tuhin Pabna
    Attachments
    Nov 28, 2019, 9:29 AM
    to noyon0461


    2 Attachments

    keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore / https://www.excelr.com/digital-marketing-training-in-bangalore

    ReplyDelete
  50. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!!
    SEO Training in Bangalore
    SEO Course in Bangalore
    SEO Training Institute in Bangalore
    Best SEO Training Institute in Bangalore
    SEO Training Bangalore
    SEO Training in Madurai
    SEO Training in Coimbatore
    SEO Course in Coimbatore
    SEO Training Courses in Coimbatore

    ReplyDelete
  51. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!!
    SEO Training in Chennai
    SEO Course in Chennai
    SEO Training Institute in Chennai
    SEO Classes in Chennai
    Best SEO Training in Chennai

    ReplyDelete
  52. I think I have never seen such blogs ever before that has complete things with all details which I want. So kindly update this ever for us.
    360DigiTMG data science course
    360DigiTMG big data analytics course malaysia
    360DigiTMG data analytics courses
    360DigiTMG

    ReplyDelete
  53. Hey, thanks for this great article I really like this post and I love your blog and also Check Python course Training in 360DIGITMG. Python Training certification program provides an overview of how Python and R programming can be employed in Data Mining of structured (RDBMS) and unstructured (Big Data) data. Comprehend the concepts of Data Preparation, Data Cleansing and Exploratory Data Analysis. Perform Text Mining to enable Customer Sentiment Analysis. Learn Machine learning and developing Machine Learning Algorithms for predictive modeling using Regression Analysis. Assimilate various black-box techniques like Neural Networks, SVM and present your findings with attractive Data Visualization techniques.
    360Digitmg Python Training institute

    ReplyDelete
  54. Hello Admin!

    Thanks for the post. It was very interesting and meaningful. I really appreciate it! Keep updating stuffs like this. If you are looking for the Advertising Agency in Chennai | Printing in Chennai , Visit Inoventic Creative Agency Today..

    ReplyDelete
  55. Nice blog,I understood the topic very clearly,And want to study more like this.
    Data Scientist Course

    ReplyDelete
  56. Attend The Machine Learning courses in Bangalore From ExcelR. Practical Machine Learning courses in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Machine Learning courses in Bangalore.
    Machine Learning courses in Bangalore

    ReplyDelete
  57. Hey, thanks for this great article I really like this post and I love your blog and also Check machine learning course in hyderabad at 360DIGITMG.
    360Digitmg machine learning course in hyderabad

    ReplyDelete
  58. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    Data-science training in chennai

    ReplyDelete