Good Oracle DBA Interview Questions from Oracle-L discussion 10/22-24/03, Oracle-L discussion Aug 2004, etc Format mostly from Lisa Spory's interviewing framework. DW questions from Eric Opp - More interview questions from Oracle-L conversation 11/24/04 - 1/21/09: added in more http://www.geekinterview.com/Interview-Questions/Oracle - 7/14/09: added in Informatica questions ** favorite questions marked. Quickie top 5/6 to cover breadth of experience: 1. Oracle Features: What experience do you have with RAC, Data Guard, ASM, ASMM, Streams or other advanced Oracle features? 2. Backup/Recovery: You encounter a customer that is running no backups but says they want to be protected in case of a disaster. What would you recommend they do in the short term and long term? 3. Performance & Tuning: What are 5 places you'd look/tools you'd use/techniques you would attempt to diagnose and fix a slow running query? 4. General Administration: What are the first 5 things you change on a plain vanilla DBCA-run installation of an Oracle database? 5. PL/SQL coding: Describe a few DBMS_ packages you use on a regular basis while programming pl/sql. Describe some analytical functions you've used in the past and why you've used them while programming. 6. Data Warehousing: Describe the main differences between Kimball's approach and Inmon's approach to data warehousing. Which do you feel is superior? #--------------------------------------------------------------- Oracle Basic Basics 1. What is the difference between the database and the instance? 2. Describe the major parts of the SGA ** 3. What is the difference between PGA and SGA? 4. Describe some of the major oracle processes and what they do. ARC, SMON, PMON, CKPT, etc. ** 5. Describe for me the process by which Oracle writes data to disk upon commit. In what order are these objects accessed/updated: table, indexes on those tables, temp, undo, redo, archive logs, etc. #--------------------------------------------------------------- SQL/SQL*Plus 1. What is a Cartesian product? ** 2. Are you familiar with or have you used SQL analytical functions? If so, describe how you have used an analytical function in SQL. 3. How do you execute a host operating system command within SQL*Plus? 4. What data dictionary view would you use to see: - Text of your packages? - Column names of your views? 5. Consider table emp having 100 rows. What will happen if you issue the following query: "select * from emp where rownum between 2 and 5;"? 6. Give several examples of how you might determine the structure of the table EMP (without using any GUI tools). 7. What's the main difference between TRUNCATE and DELETE for a table? 8. What are the main sections of a select statement, in what order are the different clauses (where, group by, having, order by) processed and what are the implications of this order? ** 9. What are isolation levels? Can some other user see my modified but uncommitted data? What is Oracle's default isolation level? Can you change it? What is the difference between union and union all? #--------------------------------------------------------------- PL/SQL 1. What command would you use to encrypt a PL/SQL package? 2. Describe what is meant by the term "instrumentation" and how it relates to PL/SQL development. 3. Is there any advantage of using implicit cursors vs. explicit cursors? 4. What causes a mutating table error? 5. What is the advantage of using packages instead of standalone procedures? 6. What is an Instead-of-Trigger? 7. Are indexes automatically created for PK constraints? UK constraints? FK constraints? 8. You have just compiled a PL/SQL package but got errors, how would you see the errors? Follow up question: if "show errors" doesn't show any errors what else can you do? 9. How would you use package dbms_job? Utl_file? ** - What's a nested loop join? Sort-merge join? Hash join? semi-join? anti-join? Self-join? outer join? ** describe a situation where you've forced a an optimizer hint and it worked better? What is a subquery, what is a correlated subquery, and what is the difference? What is a deterministic function and how does it help? What is the difference between using "raise" and "raise_application_error?" #--------------------------------------------------------------- Tuning 1. What are the methods available for collecting statistics in the database? 2. A tablespace has a table with 300+ extents in it. Is this bad? Why or why not? 3. Is there any advantage of using the dbms_stats package over the analyze command? Why would you still want to use analyze command? 4. What is an Index-skip scan? 5. Explain the use of tkprof. What initialization parameter(s) should be turned on to get full tkprof output? ** 6. Describe situation(s) where an index would NOT help w/ a query? ** - Describe scenarios where it may or may not be appropriate to use a B*Tree or bitmap index. ** - Describe your approach to tuning SQL. What tools do you use? How do you approach the problem? How can you measure the improvement that's been achieved? - A user complains that the system is slow. What do you do? Where do you begin? 1. Most users complain at unpredictable times about performance, how do you find the problem? 2. Single users complain about a specific form/screen with performance issues and problem is re-creatable, how do you find the problem? ** 6. Are full table scans always bad? #--------------------------------------------------------------- Data Modeling 1. Is the following statement true or false? "All relational databases must be in third normal form." 2. When should you consider denormalization? 3. How should a many-to-many relationship be handled? ** 4. Distinguish between an OLTP model and an OLAP model; what are key differences in your approach to modelling for these two types of applications? 5. Are non-numeric primary keys bad? Why or why not? #--------------------------------------------------------------- Unix 1. What's the easiest method to create a file on UNIX? 2. What command is used to find all the processes on your system? 3. How do you execute a UNIX command in the background? 4. Explain how you set up read/write/execute permissions for the files on UNIX 5. Ask for level of programming expertise (perl, shell) and how you've integrated these skills into DBA world. 6. How do you schedule a job in Unix? how do you run a job at some point in the future? 7. When installing Oracle on a typical unix system, why is root access required? 8. Where are the startup configuration files on unix (answer varies by unix flavor). - What unix-level files NOT located in the $ORACLE_BASE/$ORACLE_HOME directories are important to an oracle installation running properly? (looking for oracle .environment files, the crontab, /etc/rc startup files, files modified by root.sh during install, etc). #--------------------------------------------------------------- Oracle over Windows ** 1. What programming language would you use to automate tasks in windows? 2. What is your biggest challenge to adminsitering Oracle over windows versus over unix answers include: lack of shell/perl, lack of telnet, use of RDC, lack of process monitoring and typical unix tools. #--------------------------------------------------------------- Administration/Operations 1. When creating a user, what system privilege must you grant to allow them to connect to the database? 2. What data dictionary view would you use to see free space in a tablespace? ** 3. Describe typical daily, weekly and monthly jobs to perform on the server. 4. If you had to write a monitoring program from scratch, what are the major tasks you'd monitor for on an ongoing basis on your server? ** 5. Describe the worst disaster scenario you've faced as a DBA and how you fixed it 6. Describe a performance impacted situation, and how you solved it. 7. Describe the DBA task you've done that you're most proud of. 8. What issues have you run into while doing major DB migrations (from 8i-9i ex) Similarly, what issues have you run into while patching a database? ** 9. What is the first thing you do when you encounter an ORA-600 or ORA-7445? #--------------------------------------------------------------- Backup, Recovery, Disaster Recovery 1. What is the difference between a hot and cold backup, and how do you do each? 2. Do you prefer rman or exports or both, and why? ** 3. What is the advantage to using an rman catalog server? ** 4. If you had rman full backups, is there any value to having exports as well? #--------------------------------------------------------------- Oracle Features/Concepts 1. What's the advantage of using Locally Managed Tablespaces (LMT) in the database? 2. Explain materialized views and how they are used. ** 3. What is a deadlock and when can this occur? 4. What is an Index-Organized table and why would you want to use one? 5. What is an application context? 6. What happens if the constraint name is not used in a constraint clause? 7. What happens if a tablespace clause is left off a create table statement? 8. Draw out the SGA, explaining how the different parts of the server work together. Include all the major processes that get started. (the Tom Kyte question) - Describe the various components that make up a database. Then, go into detail describing the various components that make up an instance. How do these components interact and work together? ** 9. What is your favorite enhancement made in 10g or 11i and why? ** 10. What features would you like to see improved in Oracle? ** What memory buffers are obsoleted/auto managed by ASMM in 10g? #--------------------------------------------------------------- RAC, ASM, Data guard, streams and other newer featuers ** 1. Describe the difference between a physical and logical failover? 2. What are the methods of replication available within Oracle? 3. Describe pros and cons to using ASM in an environment. 4. What is a situation where streams is better used than full replication? ** 5. What are the typical issues you are faced with day-to-day with administering a data guard installation? - how do you deal with gaps in the archive logs on the failover? How do you resolve these gaps in an ASM environment where you can't just copy the missing log files from one place to another? - Describe the two main methods of streaming and the pros/cons of each? - What are some of the pre-requisities to configure on the source database if you plan on streaming a set of tables or a schema? #--------------------------------------------------------------- Continuing Learning 1. What have you read (inre: Oracle) in the last three months, outside of the Oracle manuals? 2. What are some common sites online for Oracle reference? (i'd hope for at least a couple from: comp.databases.oracle.server, hotsos, asktom, metalink, ixora) ** 3. What do you do if you come across a problem you cannot solve? ** 4. What are the names of some Oracle experts in the field whose advice/writings you count on? Tom Kyte, Steve Adams, Don Burleson most common names. #--------------------------------------------------------------- Data Warehousing 1. What is the Difference between a Data Warehouse and an On-Line Transaction Processing (OLTP) System? 2. What is the difference between an Entity Relationship Diagram and a Dimensional Model? 3. What is the difference between a logical and a physical data model? 4. What is the difference between a snowflake schema, a star schema and a normal form schema? Why is a star schema important for a data warehouse? Why is a normal form schema important for an OLTP? 4a. Describe a situation where a snowflake schema makes sense? (many to many) 5. What are the "parts of speech" of SQL? 6. What is the difference between an inner join and an outer join? Why is this difference significant for the data warehouse environment? 7. What are staging tables and what role do they play in ETL? 8. What is an aggregate and why is it important to data warehouse performance? 9. What is the difference between Relational On-Line Analytical Processing (ROLAP) and Multi-Dimensional On-Line Analytical Processing? In what environments are ROLAP and MOLAP applicable? What are the pros and cons of each? 10. Why will filling up the memory slots in your PC improve your performance more important than going out and buying a new, faster processor for your PC? 11. What is memory interleaving? What is disk striping? How are they important to performance in the data warehouse? 12. What is the difference between the class of users of the data warehouse referred to as farmers and those referred to as explorers? How does each class affect data warehouse performance? 13. What is dormant data and how does it affect data warehouse performance? 14. Why does the evolution of a data warehouse engagement start with operational reporting and move into data exploration, strategic analysis and finally forecasting? 15. What is the difference between data warehousing and data mining? 16. What is data quality, what is information quality, and why are these important to the data warehouse? 17. What is a conformed dimension and why are conformed dimensions important to the data warehouse? 18. What is partitioning, what are column indices, and why are they important to the data warehouse? 19. What is an EIS, and what is the difference between an EIS and a data warehouse? 20. What is a query and reporting tool, and what are some of the basic features? 21. What are some general security considerations in the data warehouse environment? 22. What is Metadata? 23. What general test methodologies can be used in the data warehouse environment? 24. What is the difference between data quality and data cleansing? 25. What is referential integrity, and how do you enforce referential integrity within a data warehouse? - (DW): Describe in words what a fact is and what a dimension is. Can one table be both at the same time? - (DW): What are some DW best practices for design and database configuration? ** - What specific Oracle features would you expect to implement if in a DW reporting environment? Answers to include: 32k block sizes, keep/recycle pools, transportable tablespaces, bitmap indexes, parallelism, materialized views, partitioning. #--------------------------------------------------------------- Senority/Approach to DBA job ** ask all these. - When coming on board a new DBA position, what are some of the first questions you ask of your predecessor/colleagues? - Say you come into a job and there's no configuration management or change control of database artifacts. How do you go about implementing such processes? - What types of documentation do you like to see or expect to generate while at client sites doing dba work? (database inventory, machine inventory, parameter settings, DBA passwords, load program reviews, system architecture diagrams, etc). - How do you track your own work? Do you use todo lists? how do you track/communicate your progress on tasks? how do you prioritize tasks? - What is your stance on documenting your work? #--------------------------------------------------------------- Informatica Interview Questions: 1. Given an environment that utilizes lots of pl/sql functions and packages, how do you make a decision about which of these code sets to convert to 100% Informatica mappings versus leaving code within the database and using Informatica to call and process the records? 2. What (if any) differences do you have to take into account when connecting to one of many different data sources? CityDW uses Oracle, MS Sql Svr, flat files, XLS files, MS Access, Quickbase, XML and possibly other sources; what concerns would you have connecting to such a wide variety of data sources? 3. What method do you advocate for loading data directly to the Oracle fact tables? If the answer is "insert append" or truncate and reload, or use a merge, then ask about bitmap indexes and partition swapping. If unaware, then he's probably not implemented Informatica into a large scale Oracle DW before. 4. How would you design the Informatica architecture here to include workflow management, restart capabilities, data QC or bad-data reporting, and performance/time estimates? Would you use tables in a common schema to store such information? If not, how would you provide such information to customers/management when asked? 5. When designing end-to-end architecture, do you advocate keeping datasets solely in tables, utiziling flat files, utilizing multiple tables in the ETL process, or some other method and why? (Answer for flat files may be to utilize o/s level sorting or unix tools) 6. When you install an informatica server, what are some of the first things you do to the setup out of the box? What configuration changes, what performance-based changes and why? 7. When performance tuning mappings, how much can you take advantage of Oracle tuning options (index hints, forcing particular join methods, etc) to help make mappings run better? 7a. How do you performance tune mappings? Drop Indexes, change commit interval, tune the underlying sql query, check the transformations, check the delay in cachine, check the aggregators, look into partitioning source or target tables. 8. Do you advocate using Oracle sequences or Internal Informatica sequence generators to keep track of monotonically increasing sequential numbers. Why one or the other? 9. How do you recommend handling new dimensional data values as you encounter them in data sets? Do you differentiate between type1, type2 or other mixed-type dimensional updates? 10. How do design your informatica architecture to support rollbacks/backing out of datasets? How do you back out chagnes made to typeII dimensions? 11. How much experience do you have implementing over Windows? Does this change the way you would administer informatica? How would you code your admin scripts? 12. Case study: the fact_cycle_time timeout issue: Lets say you have an ETL process that merges a very large amount of data into a fact table nightly. How would you program Informatica to handle this better? 13. Case study: Comparing every field in a data set: an entire data set comes in nightly; at current we perform an if-then-else on every data field to determine if any fields have changed. Is there a better way to do this? (answer could be a CRC code or Change Data Capture). Also could be 14. How much administration versus development responsibilities are you comfortable with on an ongoing basis? How do you see your role on a project? Would you rather be primarily running the existing mappings or doing new development. 15. Data Warehouse terminology; ODS, Data Mart, type I, II dimensions, conformed dimension, factless fact, degenerative dimension. What is a type III dimension? What is a slowly changing dimension? DC environment specific 1. VBS, pl/sql experience 2. gis/spatial experience #--------------------------------------------------------------- Exadata specific " You're in charge of running OEDA: what are some key pieces of information you want to know before filling it out and handing it off to ACS? " Tell us about common Exadata-specific wait events and what they mean when you see them? " Describe Cell Off-loading/smart scanning; how do you tell if it is actually occurring? " Do you log direction into the cell servers to diagnose performance issues? What tools do you use? " Is it always better to have a query off-load to the storage servers? " Have you used HCC? What are some pros and cons to using it? " Bloom Filtering; can you explain the concept and why its good? " IORM/DBRM: do you recommend using them, why or why not? " Why does Exadata performance improve over time? " How would you diagnose an Exadata-borne SQL statement that suddenly runs 10x longer than expected? " What is your opinion on running gather_system_stats with EXADATA as an option? #--------------------------------------------------------------- Security Focused DBA - What are typical types of "sensitive" data that need to be protected in databases? Looking for PII fields such as names, addresses, SSNs, emails, phone numbers. Anything related to financial data. fields that may be subject to HIPAA compliance such as health records, disability codes, race/ethnicity codes, etc. HR fields (reviews, salary, etc). - What sort of "home grown" security methods have you employed as a DBA in your career (looking for things like Role-based Access Control (RBAC), views, grants, control via roles, synonyms, etc). - Describe what role Transparent Data Encryption plays in a well-formed Oracle Security model? - What does Data Vault do and do you feel its necessary in a typical Database environment? - Have you used Virtual Private Database and Oracle Label Security? - What do the newer Oracle products Data Redaction and Data Masking offer DBAs? - Do you have any experience with DB Firewall? What sorts of protections does it provide?