DBA Interview Questions

[ahmadbilal]

# How many memory layers are in the shared pool?
# How do you find out from the RMAN catalog if a particular archive log has been backed-up?
# How can you tell how much space is left on a given file system and how much space each of the file system?s subdirectories take-up?
# Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?
# What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
# Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
# How do you tell what your machine name is and what is its IP address?

# How would you go about verifying the network name that the local_listener is currently using?
# You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
# What view(s) do you use to associate a user?s SQLPLUS session with his o/s process?
# What is the recommended interval at which to run statspack snapshots, and why?
# What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
# Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
# How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
# What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
# In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
# How would you best determine why your MVIEW couldn?t FAST REFRESH?
# How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
# Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
# How would you begin to troubleshoot an ORA-3113 error?
# Which dictionary tables and/or views would you look at to diagnose a locking issue?
# An automatic job running via DBMS_JOB has failed. Knowing only that ?it?s failed?, how do you approach troubleshooting this issue?
# How would you extract DDL of a table without using a GUI tool?
# You?re getting high ?busy buffer waits? - how can you find what?s causing it?
# What query tells you how much space a tablespace named ?test? is taking up, and how much space is remaining?
# Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
# Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle?s. What database recovery options are available? Database is in archive log mode.
# Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
# How do you increase the OS limitation for open files (LINUX and/or Solaris)?
# Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
# Explain how you would restore a database using RMAN to Point in Time?
# How does Oracle guarantee data integrity of data changes?
# Which environment variables are absolutely critical in order to run the OUI?
# What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
# Why does Oracle not permit the use of PCTUSED with indexes?
# What would you use to improve performance on an insert statement that places millions of rows into that table?
# What would you do with an ?in-doubt? distributed transaction?
# What are the commands you?d issue to show the explain plan for ?select * from dual??
# In what script is ?snap$? created? In what script is the ?scott/tiger? schema created?
# If you?re unsure in which script a sys or system-owned object is created, but you know it?s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
# How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
# You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
# I have my backup RMAN script called ?backup_rman.sh?. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
# Explain the concept of the DUAL table.
# What are the ways tablespaces can be managed and how do they differ?
# From the database level, how can you tell under which time zone a database is operating?
# What?s the benefit of ?dbms_stats? over ?analyze??
# Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
# You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
# How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.