Jump to content


  • Content count

  • Joined

  • Last visited

Everything posted by burleson

  1. Hi Sanjay, Yes, setting OICA to 10 should force index usage, but ONLY until you find and fix the real cause of the problem. This is only a stopgap measure.
  2. burleson

    Which one is better, Oracle DBA or SQL DBA?

    Traditionally, Oracle DBA’s earn far more because Oracle is robust and complex.
  3. Hi Sanjay, Why did you did not notice this when you upgraded your test instance? First, you were right in disabling OPTIMIZER_ADAPTIVE_FEATURES! This is a common issue, and I have notes here: http://www.dba-oracle.com/t_12c_upgrade_slow_performance.htm As a temporary stopgap, you can set optimizer_index_cost_adj to a value of 10: Please read this carefully: http://www.dba-oracle.com/oracle_tips_cost_adj.htm Good luck!
  4. burleson

    alter user sys password question in 12cR2 database

    Hi Rich, Many thanks for posting the answer! Take care . . .
  5. Hi Arasu, Are you seeking someone to write this for you? If so, call 800-766-1884.
  6. Hi Arasu, and welcome to the forum! You can easily achieve this using PL/SQL by looping through with a FOR loop, querying all_tab_columns, for all columns that have a string datatype. For each table name, you use “execute immediate”, building the SQL statement that looks for your string, using the table_name and column_name. See example here: https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/ As to performance, there will be a full table scan of strings in all_tab_columns. This can be made faster using parallel hints in the generated SQL. Good luck!
  7. burleson

    alter user sys password question in 12cR2 database

    Hi Rich, Yeah, I understand your issue, and your real-world observation demonstrates that Oracle has changed the mechanism for tracking password changing for the SYS user. Again, I have not seen this, but I believe you! I researched this on the web and I found nothing, sorry. Can you use the MOSC bug database? It could be a bug, but I recommend that you report your observation to MOSC via a service request. Again, sorry that I could not help, but i’m Sure that Oracle support can help: http://support.oracle.com Let us know what you find. Good luck!
  8. burleson

    What hardware to practice on?

    Hi, ideally, a server with multiple CPU’s would help you study parallel operations and data warehouse operations. As for RAM, it’s cheap to get many gig of storage, and getting SSD would also be useful! If you want to learn RAC you will need two little servers. Be careful, they must meet the certification requirements: http://www.dba-oracle.com/t_rac_hardware_compatibility.htm Server specs change all the time, and I don’t know of mini server technology! Sorry that I cannot be more help. Good luck!
  9. burleson

    Pre insttal of Oracle 18c

    Hi, The 18c preinstall steps for Windows are here https://docs.oracle.com/en/database/oracle/oracle-database/18/ntdbi/index.html Good luck!
  10. burleson

    alter user sys password question in 12cR2 database

    Hi Rich, Sorry, I cannot find any changed behaviors in 12c r2 for the prime column in SYS.user$. Could this be a PDB vs CDB issue? This query has always worked for me: select name, ctime, ptime FROM sys.user$ WHERE name = 'SYS'; Please see if you can document this new behavior in a script, and share it with us! Good luck!
  11. burleson

    How to login

    Hi, You make a good point about the degree requirements, but the median Oracle DBA job pays over $100,000 per year, and it’s rare to snag a good job without a four year degree. It’s competitive! i’m sorry If I sounded insulting, but there are loads of kids who take two week DBA boot camps and then are told that they can expect high paying jobs! I assume that as a CPA you have a degree. As for certification, the OCP used to be designed such that you would not pass without work experience. When I got certified, Oracle required proof of at least fours of full time job experience, but that was dropped. FYI, here are the minimum requirements to consult for us: http://www.dba-oracle.com/jobs.htm Note that a BS in business satisfies our requirements. Please feel free to post new questions here! Good luck! PS - With a CPA, you could be a stellar “Oracle Applications DBA”. The only barrier to entry is finding food eBusiness Suite training. I have some notes here: http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=Dba+applications+&sa=Search&siteurl=www.dba-oracle.com%2Ft_roles_granted_user.htm&ref=www.google.com%2F&ss=4829j3061175j12
  12. burleson

    How to login

    Hi, First, be aware that becoming an Oracle DBA requires an advanced college degree plus almost six months of classes from Oracle University. Oracle is not appropriate for dilettantes and those without a strong academic background. Please read: http://www.dba-oracle.com/t_how_to_become_an_oracle_dba.htm To answer your question, you need to create an OS DBA Group, and grant the DBA role to those users: http://www.dba-oracle.com/concepts/setting_roles.htm Good luck!
  13. burleson

    How to apply correctly some known patches

    Hi, i have never run the patch rollback scripts, but the safe thing to do is to pull a list of all successfully applied patches, and compare that to the patch list from the PSU: See details here: http://www.dba-oracle.com/t_list_oracle_patches.htm Good luck!
  14. burleson

    Add member to standby redo log file

    Hi Phat, Can you post the exact ORA error number please? Please peruse this, it may help: http://www.dba-oracle.com/t_oracledataguard_192_standby_redo_logs.htm
  15. burleson

    Whe user IOT table

    Hi Pedro, I have never had cause to use a IOT, but they are good for third normal form tables where every column requires an index!
  16. Hi, As you know ( from double posting this question on multiple forums, your solution can be found in MOSC note 811659.1: http://support.oracle.com Please post your solution. Good Luck!
  17. burleson

    AWS Oracle RDS Instance Maintenance

    Hi Siva, Have you measured the rate that you are generating archived redo logs? See here: http://www.dba-oracle.com/tips_oracle_v$_log_history.htm What’s the issue? Archived redo logs that you don’t need (because of a recent RMAN backup) only consume disk space, and disk is insanely inexpensive these days . . .
  18. burleson

    AWS Oracle RDS Instance Maintenance

    Hi Siva, Welcome to the perils of the cloud! As you know, there is no way to remove OS files without access to the server file system! We would expect that AWS would monitor for full mount points, but they may just wait until your DB licks up, trying to write a new archived redo log. Amazon sez that you can use these SQL*Plus commands to adjust the archived redo log retention period: set serveroutput on exec rdsadmin.rdsadmin_util.show_configuration; See details here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Log.html Good luck!
  19. Hi, Like I said, the network_link ONLY works from expdp launched from the remote server. Good luck!
  20. burleson

    DB Instance went down

    Hi, You should consider opening a service request on MOSC: Http://support.oracle.com Let is know what they say! Good luck!
  21. Hi Kmuthu, You need to insure that your PATH and ORACLE_HOME are set properly! Go into the Windows registry and turn of the service user authentication. You can go into HKEY_LOCAL_MACHINE>SOFTWARE>ORACLEThen go to KEY_OraDB12Home1 (or whatever home you’re needing to update) and then change the Data Value in the ORACLE_SVCUSER_PWDREQ Key from 1 to 0. Good Luck!
  22. burleson

    DB Instance went down

    Hi Kmuthu, Of course it will restart! You just need to remove the held memory segments: http://www.dba-oracle.com/t_windows_remove_shared_memory segments.htm Or for Linux or UNIX use the ipcs command. Where did you get the idea that the-installing Oracle would help
  23. Hi Knuthu, All expdp dumpfiles must be on the local server! You cannot push-out a table using expdp. The network_link part is for pulling-in a remote table to the local server! Better, consider using CTAS with a database link, launched from the remote server: http://www.dba-oracle.com/t_sql_dblink_performance.htm Good luck!
  24. This is a great question! Let me research this and get back to you.