This year, SearchOracle.com readers inundated our panel of experts with questions on topics ranging from Oracle security and development languages to licensing, database backups and more. Looking back on some of the most popular questions and answers, here is our list of the Top 10 expert responses for 2009.
|Decrypt an Oracle password using John the Ripper and checkpwd|
Q: How can I decrypt the user's password in Oracle 10g, which is already in an encrypted format?
A: It can be a bit tricky to decrypt an Oracle password. Before we head down this road, you may want to consider alternate ways of accomplishing your goals. Determining whether you can access the schema by logging in as a privileged user and simply changing the password to a known value can save you hours of work. If that is not possible, you may want to try getting in via another standard account (check for default passwords) and attempting privilege escalation. If that doesn't work, you still have options in 10g.
Learn more about the options for decryption for Oracle 10g.
|How to use DBMS_CRYPTO package for Oracle password encryption/hashing|
Q: I am working on a project with VB.NET 2008 as the front end and Oracle 10g Express Edition as the back end.
I am maintaining a table called USER, which stores userid and password. Now I want to encrypt the password and store it in the database, and the same encrypted password must be accessed and checked with the user-entered password in VB.NET 2008. How can this be done?
A: What you are looking for is all contained in the DBMS_CRYPTO package. Everything you need to encrypt, hash or obfuscate data is in there. With this standard Oracle-supplied package, the hardest part is determining which method is most appropriate for your application.
Read more to learn which methods you can use.
|Buying an Oracle license for a development database|
Q: Does Oracle require a license for a development database?
A: This is probably an issue that gets many companies in hot water during an Oracle audit. The short answer is yes, you must purchase a license in most cases. However, if you download the Oracle Standard Edition, Standard Edition One and the Enterprise Edition from the Oracle Technology Network (OTN), you can develop on the OTN software and you're covered under the OTN license as long as "the programs are used for any purpose except the development of a single prototype of your application," according to the OTN license terms and agreement on the Oracle Technology Network.
Read more about what expert Scott Rosenberg has to say about licensing a development database.
|Can I specify Oracle column order in my database table|
Q: Why is it not possible in Oracle to specify the location for any newly added column? Why does it always go last? Is there any option in the upcoming versions of Oracle?
A: In relational database theory, there is no concept to the order of rows or columns in any table. A table is a "set" of data, and there is no order in a set. As such, there is no ANSI or ISO SQL standard that defines an ordering of columns or rows. So RDBMS vendors are left to implement these sorts of things however they want. Oracle likes to put the newly added column "last." This is just the way Oracle decided to handle adding a new column.
Learn how to state the order in which you want your columns to appear.
|Three steps to help improve database security|
Q: What are three things we can do quickly and easily to increase the security posture of our Oracle databases?
A: First, secure your listener. Make sure the listener is password protected (this can be done via the lsnrctl utility or through the Netmanager GUI) and logging is enabled. Prevent people from modifying the listener remotely by adding the ADMIN_RESTRICTIONS_ = ON string to your listener.ora file. This is the default behavior in Oracle 10g and above unless you have disabled Local OS authentication by adding LOCAL_OS_AUTHENTICATION_ = OFF to your listener.ora file.
Read what else you can do to improve your Oracle database security with these tips from expert Brian Fedorko.
|Using wrapper scripts with cold backups|
Q: I'm new to DBA work, and I have an Oracle 8.1.7 database on a Windows 2000 server. I have to make a cold backup through bat files. I have scheduled the jobs in the Windows scheduler, and that part works fine. My question now is: How can I automatically check whether the backup has run successfully? I now check the scheduler log each day, but this is not really something I want to be doing manually every day. Something like an email would be easier. How is this normally done?
A: Today's Oracle DBA typically uses RMAN for backup and recovery. But cold backups from your own scripts are still perfectly acceptable. What I typically do in your scenario is to create a wrapper script for my cold backup script.
Learn how to create the wrapper script from expert Brian Peasland.
|When to use the WHERE parameter with the Oracle export utility|
Q: How can we complete an export backup of the previous 15 days' data? Is it possible by any backup mechanism?
A: The Oracle export utility is not "time aware," so it cannot do what you are asking by default. Just about the only way to accomplish this task with the export utility is to add to your tables a column containing a date when the row was inserted or updated.
Learn how to finish this task with the WHERE parameter.
|Substitution variables in PL/SQL|
Q: Can I use a variable in place of a table name in a SELECT Statement?
define a = 'EMP'
SELECT * FROM &A
A: Substitution variables work only in SQL*Plus or SQL*Worksheet because those tools know about substitution variables. This is OK for a one-off script you're running manually. For automation, it's not so good because it will sit there at a prompt waiting for input. Since you define the substitution variable first, simply "double-up" your ampersands.
Read an example script and learn how to use EXECUTE IMMEDIATE in this response from expert Dan Clamage.
|Finding the most accessed Oracle table|
Q: Is there a way to find the database table that is accessed the most?
A: You can query the V$SEGMENT_STATISTICS view for this information. Look for tables that have a high number of physical reads and logical reads. A query similar to the following might provide what you are looking for.
Read the example query to learn how to find the most accessed table.
|Cost-based optimizer vs. rule-based optimizer|
Q: What is the relationship between Oracle CBO, RBO and statistics?
A: When you submit a query, Oracle often has multiple methods to execute the query."Should I use an index or not? Which join method should I use?" Before Oracle can execute the query, it has to determine the answers to questions like these to determine an optimal execution path.
Older versions of Oracle used a set of rules to decide which execution path was the "optimal." One rule said: If an index exists, use it. These rules were ranked, so some rules trumped other rules. All of this was called the rule-based optimizer (RBO).
Read the rest of this response to learn about the relationship between the RBO and CBO.