text: Interview Questions for Oracle, DBA, Developer Candidates?
  Level: Intermediate to high?
  Level:Intermediate to high
  Expected answer::
  Level:Intermediate
  Expected Answer: There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.
  2. What is the purpose of the IMPORT option IGNORE? What is it's default setting?
  Expected Answer:?
  Expected answer: Use the ALTER TABLESPACE ..... SHRINK command.
  4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?
  Expected answer:?
  Level: Intermediate to High
  Expected answer:?
  Expected answer:?
  Expected answer:?
  Expected answer: You use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys.
  9. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
  Expected answer:?
  Level: High
  Expected answer:?
  Expected answer: You can't use hot backup without being in archivelog mode. So no, you couldn't recover.
  12. What causes the "snapshot too old" error? How can this be prevented or mitigated?
  Expected answer:?
  Expected answer:?
  Expected answer: You need to check that the user has specified the full name of the object (SELECT empid FROM scott.emp; instead of SELECT empid FROM emp;) or has a synonym that points?
  Level: Intermediate
  Expected answer: You need to verify the developer has direct grants on all tables used in the view. You can't create a stored object with grants given through a role.
  16. If you have an example table, what is the best way to get sizing data for the production table implementation?
  Expected answer:?
  Level: high
  Expected answer:?
  Expected answer: Somehow two values have been inserted into the dual table. This table is a single row, single column table that should only have one value in it.
  19. How can you determine if an index needs to be dropped and rebuilt?
  Expected answer:?
  Expected answer:.
  Alternately, if you are using PL/SQL, you can do
  ls_sql_string := 'CREATE TABLE FRED AS SELECT SYSDATE A_DATE FROM DUAL';
  EXECUTE IMMEDIATE ls_sql_string;
  6. What SQLPlus command is used to format output from a select?
  Level: low
  Expected answer: This is best done with the COLUMN command.
  7..
  8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
  Level: Intermediate to high
  Expected answer:?
  Expected answer:?
  Expected answer:?
  Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
  12. What is the default ordering of an ORDER BY clause in a SELECT statement?
  Expected answer: Ascending
  13. What is tkprof and how is it used?
  Level: Intermediate to high.
  14. What is explain plan and how is it used?
  Expected answer:.
  15. How do you set the number of lines on a page of output? The width?
  Expected answer:.
  16. How do you prevent output from coming to the screen??
  Expected answer: Ensure that users don?t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
  4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
  Expected answer:?:?
  Expected answer:?
  Expected answer:?
  Level: high
  Expected answer:?
  Expected answer:?:?
  Expected answer: Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.
  20. What can cause a high value for recursive calls? How can this be fixed?
  Expected answer:?
  Expected answer: This indicate that the shared pool may be too small. Increase the shared pool size.
  22. If you see the value for reloads is high in the estat library cache report is this a matter for concern?
  Expected answer:?
  Expected answer:?
  Expected answer:?: OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.
  2. How do you set up your tablespace on installation?
  Level: Low
  Expected answer:.
  3. What should be done prior to installing Oracle (for the OS and the disks)?
  Expected Answer: adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.
  4. You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem?
  Expected Answer: Check to make sure that the archiver isn?t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.
  5. When configuring SQLNET on the server what files must be set up??
  Expected answer: You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
  13. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces?
  Expected answer: At least 7, see disk configuration answer above.
  1. Describe third normal form?
  Expected answer: Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
  2. Is the following statement true or false:
  "All relational databases must be in third normal form"
  Why or why not?
  Level: Intermediate
  Expected answer: False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
  3. What is an ERD?
  Level: Low
  Expected answer: An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.
  4. Why are recursive relationships bad? How do you resolve them?
  Level: Intermediate.
  5. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
  Level: Low to intermediate
  Expected answer: This means the two entities should probably be made into one entity.
  6. How should a many-to-many relationship be handled?
  Level: Intermediate
  Expected answer: By adding an intersection entity table
  7. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?
  Level: Intermediate
  Expected answer: A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
  8. When should you consider denormalization?
  Level: Intermediate
  Expected answer: Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
  1. How can you determine the space left in a file system?
  Level: Low
  Expected answer: There are several commands to do this: du, df, or bdf
  2. How can you determine the number of SQLNET users logged in to the UNIX system?
  Level: Intermediate
  Expected answer: SQLNET users will show up with a process unique name that begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the number of users.
  3. What command is used to type files to the screen?
  Level: Low
  Expected answer: cat, more, pg
  4. What command is used to remove a file?
  Level: Low
  Expected answer: rm
  5. Can you remove an open file under UNIX?
  Level: Low
  Expected answer: yes
  6. How do you create a decision tree in a shell script?
  Level: intermediate
  Expected answer: depending on shell, usually a case-esac or an if-endif or fi structure
  7. What is the purpose of the grep command?
  Level: Low
  Expected answer: grep is a string search command that parses the specified string from the specified file or files
  8. The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program?
  Level: intermediate
  Expected answer: ps -ef|grep *nocomp*|wc -l
  9. What is an inode?
  Level: Intermediate
  Expected answer: an inode is a file status indicator. It is stored in both disk and memory and tracts file status. There is one inode for each file on the system.
  10. The system administrator tells you that the system hasn?t been rebooted in 6 months, should he be proud of this?
  Level: High
  Expected answer: Maybe. Some UNIX systems don?t clean up well after themselves. Inode problems and dead user processes can accumulate causing possible performance and corruption problems. Most UNIX systems should have a scheduled periodic reboot so file systems can be checked and cleaned and dead or zombie processes cleared out.
  11. What is redirection and how is it used?
  Level: Intermediate
  Expected answer: redirection is the process by which input or output to or from a process is redirected to another process. This can be done using the pipe symbol "|", the greater than symbol ">" or the "tee" command. This is one of the strengths of UNIX allowing the output from one command to be redirected directly into the input of another command.
  12. How can you find dead processes?
  Level: Intermediate
  Expected answer: ps -ef|grep zombie -- or -- who -d depending on the system.
  13. How can you find all the processes on your system?
  Level: Low
  Expected answer: Use the ps command
  14. How can you find your id on a system?
  Level: Low
  Expected answer: Use the "who am i" command.
  15. What is the finger command?
  Level: Low
  Expected answer: The finger command uses data in the passwd file to give information on system users.
  16. What is the easiest method to create a file on UNIX?
  Level: Low
  Expected answer: Use the touch command
  17. What does >> do?
  Level: Intermediate
  Expected answer: The ">>" redirection symbol appends the output from the command specified into the file specified. The file must already have been created.
  18. If you aren?t sure what command does a particular UNIX function what is the best way to determine the command?
  Expected answer: The UNIX man -k command will search the man pages for the value specified. Review the results from the command to find the command of interest.
  1. How can you determine if an Oracle instance is up from the operating system level?
  Level: Low
  Expected answer: pmon will show what instances are up.
  2. Users from the PC clients are getting messages indicating :
  Level: Low
  ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)
  What could the problem be?
  Expected answer: The instance name is probably incorrect in their connection string.
  3. Users from the PC clients are getting the following error stack:
  Level: Low
  ERROR: ORA-01034: ORACLE not available
  ORA-07318: smsget: open error when opening sgadef.dbf file.
  HP-UX Error: 2: No such file or directory
  What is the probable cause?
  Expected answer: The Oracle instance is shutdown that they are trying to access, restart the instance.
  4. How can you determine if the SQLNET process is running for SQLNET V1? How about V2?
  Level: Low
  Expected answer:".
  5. What file will give you Oracle instance status information? Where is it located?
  Level: Low
  Expected answer: The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.
  6. Users aren?t being allowed on the system. The following message is received:
  Level: Intermediate
  ORA-00257 archiver is stuck. Connect internal only, until freed
  What is the problem?
  Expected answer: The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.
  7. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?
  Level: Intermediate
  Expected answer: There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.
  8. You attempt to add a datafile and get:
  Level: Intermediate
  ORA-01118: cannot add anymore datafiles: limit of 40 exceeded
  What is the problem and how can you fix it?
  Expected answer:.
  9. You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem?
  Level: High
  Expected answer: Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
  10. Your users get the following error:
  Level: Intermediate
  ORA-00055 maximum number of DML locks exceeded
  What is the problem and how do you fix it?
  Expected answer:.
  11. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?
  Level: High
  Expected answer:.