1. What DBA activities did you to do today?
Wow, this is a loaded question and almost begs for you to answer it with "What DBA activities do you LIKE to do on a daily basis?." And that is how I would answer this question. Again, do not get caught up in the "typical" day-to-day operational issues of database administration. Sure, you can talk about the index you rebuilt, the monitoring of system and session waits that were occurring, or the space you added to a data file, these are all good and great and you should convey that you understand the day-to-day operational issues. What you should also throw into this answer are the meetings that you attend to provide direction in the database arena, the people that you meet and talk with daily to answer adhoc questions about database use, the modeling of business needs within the database, and the extra time you spend early in the morning or late at night to get the job done. Just because the question stipulates "today" do not take "today" to mean "today." Make sure you wrap up a few good days into "today" and talk about them. This question also begs you to ask the question of "What typical DBA activities are performed day to day within X Corporation?"
2. What is your typical day like?
If you spend enough time on question 1, this question will never be asked. It is really a continuation of question 1 to try and get you to open up and talk about the type of things you like to do. Personally, I would continue with the theme of question 1 if you are cut short or this question is asked later in the interview process. Just note that this question is not all geared toward the day-to-day operational issues you experience as a DBA. This question also gives you the opportunity to see if they want to know about you as an individual. Since the question did not stipulate "on the job" I would throw in a few items like, I get up at 5:00am to get into work and get some quiet time to read up on new trends or you help coach your son/daughter's soccer team. Just test the waters to what is acceptable. If the interviewer starts to pull you back to "job" related issues, do not go to personal. Also, if you go to the office of the interviewer please notice the surroundings, if there are pictures of his/her family, it is probably a good idea to venture down the personal path. If there is a fly-fishing picture on the wall, do not say you like deep-sea fishing. You get the picture.
3. What other parts of your organization do you interact with and how?
Again, if you have exhausted question 1 and 2 you may never get to this question. But if you have been apprehensive to opening up and explaining yourself, take note that you may have an issue and the interviewer might also be already getting tired of the interview process. If you get to this question consider yourself in trouble. You really need to forget all your hang-ups and start explaining what it is that you like to do as a DBA, and why you want to work for this particular company. You are going to have to reel this interviewer back into the interview process or you might not get to the true technical question part of the interview.
4. Do you consider yourself a development DBA or a production DBA and why?
I take this as a trick question and explain it that way. Never in my database carrier have I distinguished between "development" and "production." Just ask your development staff or VP of engineering how much time and money is lost if development systems are down. Explain to the interviewer that both systems are equally important to the operation of the company and both should be considered as production systems because there are people relying on them and money is lost if either one of them is down. Ok you may be saying, and I know you are, that we lose more money if the production system is down. Ok, convey that to the interviewer and you won't get anyone to disagree with you unless your company sells software or there are million dollar deals on the table that are expecting the next release of your product or service.
5. Are you a nuts-n-bolts DBA or a tools-n-props DBA
This question begs for me to give definition around the terms I basically group DBAs into. These are not good or bad groups but something I like to think about when talking to DBAs. A nuts-n-bolts DBA is the type that likes to figure out every little item about how the database works. He/she is a DBA who typically hates a GUI environment and prefers the command line to execute commands and accomplish tasks. A nuts-n-bolts DBA like to feel in control of the database and only feels comfortable at the command line and vi as an editor. The tools-n-props DBA is mostly the opposite of a nuts-n-bolts DBA, they like the feel of a GUI, the ease at which things can be accomplished without knowing much about the database. They want to get the job done with the least amount of intervention from having to figure out what everything is doing behind the scenes. Now the answer, I would explain myself as a combination of the two. I, having been in this business for over 20 years, have grown up in a command line era where the GUIs never seemed to work. There was high complexity in systems and not much good documentation on how things worked. Thus, I had to learn everything about most aspects of the database environment I was working in and thus became a nuts-n-bolts DBA. I was a true command line and vi bigot. Times have changed and the GUIs are very reliable, understand the environment they are installed on, and can generally get the job done quicker for individuals new to database administration. I too am slowly slipping over to the dark side of GUI administration. If you find yourself as a tools-n-props DBA, try to convey that you are aware of some tasks that require you to be a nuts-n-bolts DBA.
Technical – Oracle
This is the part you have all been waiting on. Please if you have just skipped to this section, go back to the personal section and read it. There is much to be gained by the personal section and conveying to your interviewer who you are and how you tick from day to day. Also, the answers I am giving here are off the cuff and are not intended to be the definitive answer to these questions. There are many aspects to these questions that just cannot be answered here and honestly, you will not have time to explain any of these questions fully in the interview process. It is up to you to make sure your interviewer understands that you understand the question and have given enough information that they know you understand the concept.
1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
3. How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.
4. Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
5. Give two examples of how you might determine the structure of the table DEPT.
describe
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
*_tab_columns or *_tab_cols
6. Where would you look for errors from the database engine?
In the alert log.
7. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a no rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
8. Give the reasoning behind using an index.
Faster access to data blocks in a table.
9. Give the two types of tables involved in producing a star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
10. . What type of index should you use on a fact table?
A Bitmap index.
11. Give two examples of referential integrity constraints.
A primary key and a foreign key.
12. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
14. What command would you use to create a backup control file?
Alter database backup control file to trace.
15. Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened
16. What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.
17. How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql
18. How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
19. Explain an ORA-01555
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Well, we have gone through the first 25 questions as I would answer them during an interview. Please feel free to add your personal experiences to the answers as it will always improve the process and add your particular touch. As always remember these are "core" DBA questions and not necessarily related to the Oracle options that you may encounter in some interviews. Take a close look at the requirements for any job and try to come up with questions that the interviewer may ask. Next time we will tackle the rest of the questions. Until then, good luck with the process.
21. How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual; 22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
23. What command would you use to encrypt a PL/SQL application?
WRAP
24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
25. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
26. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
27. Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
28. Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
29. When a user process fails, what background process cleans up after it?
PMON
30. What background process refreshes materialized views?
The Job Queue Processes.
31. How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT
32. Describe what redo logs are.
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
33. How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE; 34. Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
35. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
37. Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
38. When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
39. How do you add a data file to a tablespace?
ALTER TABLESPACE ADD DATAFILE SIZE 40. How do you resize a data file?
ALTER DATABASE DATAFILE RESIZE ; 41. What view would you use to look at the size of a data file?
DBA_DATA_FILES 42. What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE 43. How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
44. How can you rebuild an index?
ALTER INDEX REBUILD; 45. Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
46. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS 47. How can you gather statistics on a table?
The ANALYZE command.
48. How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
49. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
50. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
Technical – UNIX
Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.
1. How do you list the files in an UNIX directory while also showing hidden files?
ls -ltra 2. How do you execute a UNIX command in the background?
Use the "&"
3. What UNIX command will control the default file permissions when files are created?
Umask
4. Explain the read, write, and execute permissions on a UNIX directory.
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.
5. the difference between a soft link and a hard link?
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.
6. Give the command to display space usage on the UNIX file system.
df -lk 7. Explain iostat, vmstat and netstat.
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
8. How would you change all occurrences of a value using VI?
Use :%s/
9. Give two UNIX kernel parameters that effect an Oracle install
SHMMAX & SHMMNI
10. Briefly, how do you install Oracle software on UNIX.
Basically, set up disks, kernel parameters, and run orainst.
I hope that these interview questions were not too hard. Remember these are "core" DBA questions and not necessarily related to the Oracle options that you may encounter in some interviews. Take a close look at the requirements for any job and try to extract questions that interviewers may ask from manuals and real life experiences. For instance, if they are looking for a DBA to run their databases in RAC environments, you should try to determine what hardware and software they are using BEFORE you get to the interview. This would allow you to brush up on particular environments and not be caught off-guard. Good luck!
DBA FAQ’s
1. How many memory layers are in the shared pool?
Answer The shared pool portion of the SGA contains three major areas: library cache, dictionary cache, buffers for parallel execution messages, and control structures
2. How do you find out from the RMAN catalogue if a particular archive log has
been backed-up?
Answer :
3. 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?
Answer : df -k in Solaris
4. Define the SGA and:
• How you would configure SGA for a mid-sized OLTP environment?
• What is involved in tuning the SGA?
Answer
System Global Area (SGA), which is shared by all server and background
processes and holds the following:
o Database buffer cache
o Redo log buffer
o Shared pool
o Large pool (if configured)
• Program Global Areas (PGA), which is private to each server and background
process; there is one PGA for each process. The PGA holds the following:
o Stack areas
o Data areas
What is involved in tuning the SGA?
Answer :Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads
5. What is the cache hit ratio, what impact it has on performance of
an Oracle database and what is involved in tuning it?
Answer: The cache miss ratio is the the number of cache misses compared to the total number of cache read attempts. This is calculated as follows:
Select sum(getmisses) / sum(gets) "Miss ratio" From v$rowcache;
The miss ratio should be less than 15%. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE.
6. Other than making use of the stats pack utility, what would you check when
you are monitoring or running a health check on an Oracle 8i or 9i database?
Answer:
UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring or
Oracle Enterprise Manager - Tuning Pack
TKProf
7. How do you tell what your machine name is and what is its IP addresses?
Answer
Windows: ipconfig
UNIX: ifconfig -a
HP-UX : ifconfig 1an0
Linux : ifconfig
8. How would you go about verifying the network name that the local_listener
is currently using?
Answer: With the command: Show parameter LISTENER. After executing this command in SQL, the result that you will get for HOSTNAME is the network name of the local host.
9. You have 4 instances running on the same UNIX box. How can you determine
Which shared memory and semaphores are associated with which instance?
Answer :
10. What view(s) do you use to associate a user's SQLPLUS session with his
o/s process?
Answer : v$session
11. What is the recommended interval at which to run stats pack snapshots,
and why?
Answer :
12. 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?
Answer: optimizer_index_cost_adj
13. 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.
Answer: dbms_job.submit(
:jobno, 'statspack.snap;',
trunc(sysdate)+9/24,
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);
14. How would you edit your CRONTAB to schedule the running of /test/test.sh
to run every other day at 2PM?
Answer: Use vi to edit Crontab file append a line on the file
00 2 * * * /test/test.sh
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
Answer: DBMS_STANDARD package provides language facilities that help your application interact with Oracle.
Function sql_txt (sql_text out ora_name_list_t) return binary_integer;
16. In which dictionary table or view would you look to determine at which
time a snapshot or MVIEW last successfully refreshed?
Answer:
select owner, mview_name, last_refresh_type, last_refresh_date
from dba_mviews;
17. How would you best determine why your MVIEW couldn't FAST REFRESH?
Answer: from dba_mview_refresh_times
18. How does propagation differ between Advanced Replication and Snapshot
Replication (read-only)?
Answer:
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
Answer: dba_repcatlog ; dba_repsites_new ; dba_repextensions
20. How would you begin to troubleshoot an ORA-3113 error?
Answer: End Of File on Communication Channel
The Ora-3113 error is a general error reported by Oracle client tools, which signifies that they cannot communicate with the Oracle shadow process.
Solution
• Increase permanent swap file and test with increasing larger sizes, especially if you are getting 3113 in conjunction with a very large data transfer between client and server.
• (settings -> control panel -> SYSTEM ->performance -> change)
• Sql*Net vs. Database versions mismatch
• Delete unwanted files (like core, .lst, .trc, .log etc) if your executables partition becomes full, especially $ORACLE_HOME
• Set sqlnet.expire_time = 0 in sqlnet.ora file
• If your database is running in archive log mode make sure LOG_ARCHIVE_START = TRUE is set in your init.ora file
• If you have installed Oracle parallel server option but do not have distributed Lock manager installed or running, the 3113 may be encountered
o select * from v$option;
o if you find the two conditions stated above are true, you should de-install the parallel server option. It is recomended to go through Oracle installer to de-install any product, else folow this approach.
o shutdown any Oracle instances
o %script /tmp/relink.out
o %cd $ORACLE_HOME/rdbms/lib
o %rm -f oracle
o %make -f oracle.mk no_parropt ioracle
o %exit
• Try using the SQL*Net V1 driver for local connections:
o setenv TWO_TASK P:
• Your 'Oracle' executable may be corrupt. Relink it
o shutdown any Oracle instances
o %script /tmp/relink.out
o %cd $ORACLE_HOME/rdbms/lib
o %rm -f oracle
o %make -f oracle.mk ioracle
o %exit
• Some unix platform need LD_LIBRARY_PATH to be set correctly to resolve any dynamically linked libraries problems.
• Check the SQL*Net protocol you are using
drivers oracle
if you are using TCP/IP it should list TCP/IP if driver command does not exists on your machine, then try
o %script /tmp/symbols.out
o %cd $ORACLE_HOME/bin
o %nm oracle | grep -i SYMBOL # where symbol can be osnptt, osnasy, osnttt, osntlitcp, osntlispx, osndnt
o %exit
21. Which dictionary tables and/or views would you look at to diagnose a
locking issue?
Answers: sys.v_$lock, sys.v_$session sys.obj$ , sys.user$ ,
sys.v_$process
22. An automatic job running via DBMS_JOB has failed. Knowing only that
"it's failed", how do you approach troubleshooting this issue?
Answers:
23. How would you extract DDL of a table without using a GUI tool?
Answers:
24. You're getting high "busy buffer waits" - how can you find what's
causing it?
Answers:
25. What query tells you how much space a tablespace named "test" is taking
up, and how much space is remaining?
Answers:
26. Database is hung. Old and new user connections alike hang on impact.
What do you do? Your SYS SQLPLUS session IS able to connect.
Answers:
27. 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.
Answers:
28. Illustrate how to determine the amount of physical CPUs a Unix Box
possesses (LINUX and/or Solaris).
Answers:
29. How do you increase the OS limitation for open files (LINUX and/or
Solaris)?
Answers:
30. 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.
Answers: Create a file.sh using vi then type the following line
sqlplus -s sys/*******@service_name
select sysdate from dual;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD MI:SS'
drop table xx;
select sysdate from dual;
exit;
31. Explain how you would restore a database using RMAN to Point in Time?
Answer: Rman page no 4 – 26 --- 29
32. How does Oracle guarantee data integrity of data changes?
33. Which environment variables are absolutely critical in order to run the
OUI?
Answer: On UNIX: Oracle_Home , Path , ORA_NLS33 AND LD_LIBRARY_PATH
34. What SQL query from v$session can you run to show how many sessions are
Logged in as a particular user account?
Answer: Select count (*) from v$session where username=’User_account_name’
35. Why does Oracle not permit the use of PCTUSED with indexes?
36. What would you use to improve performance on an insert statement that
Places millions of rows into that table?
37. What would you do with an "in-doubt" distributed transaction?
38. What are the commands you'd issue to show the explain plan for "select *
from dual"?
39. In what script is "snap$" created? In what script is the "scott/tiger" schema created?
Answer :
40. 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?
Answer :
41. How would you configure your networking files to connect to a database
by the name of DSS which resides in domain icallinc.com?
Answer : In tnsnames use host=DSS.icallinc.com
42. You create a private database link and upon
connection, fails with: ORA-2085: connects to
error?
Answer : This happens after the DB_NAME of the database was changed (as in note# 15390.1 ie re-create controfile with set database option etc ...).After This, the NAME column value is the new DB_NAME in V$DATABASE. The problem comes from the PROPS$ where GLOBAL_DB_NAME is still the old database name. So the WA is to execute the ALTER DATABASE RENAME GLOBAL_NAME (global_name=false is not a WA if global_naming is required).
43. 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?
Answer : backup_rman.sh >&1&
44. Explain the concept of the DUAL table.
Answer : Dual is a table which is created by oracle along with the data dictionary.
It consists of exactly one column whose name is dummy and one record.
The value of that record is X.
sql> desc dual
Name Null? Type
----------------------- -------- ----------------
DUMMY VARCHAR2(1)
sql> select * from dual;
D
-
X
The owner of dual is SYS but dual can be accessed by every user.
As dual contains exactly one row (unless someone fiddled with it),
it is guaranteed to return exactly one row in select statements if a
constant expression selected against dual, such as in:
select sysdate from dual
Although it is possible to delete the one record, or insert additional records,
one really should not do that!.
45. What are the ways tablespaces can be managed and how do they differ?
Answer : 2 ways Locally Managed or Managed in the dictionary
Locally-managed tablespaces have the following advantages over dictionary-managed tablespaces:
Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
46. From the database level, how can you tell under which time zone a
Database is operating?
Answer : select DBTIMEZONE from dual;
47. What's the benefit of "dbms_stats" over "analyze"?
Answer : Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don't always capture high-quality information about tables and indexes. The CBO uses object statistics to choose the best execution plan for all SQL statements.
The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.
48. Typically, where is the conventional directory structure chosen for
Oracle binaries to reside?
Answer : \oracle\ora91\bin ( windows
$Oracle_Home\
49. You have found corruption in a tablespace that contains static tables
That is part of a database that is in NOARCHIVE log mode. How would you
Restore the table space without losing new data in the other table spaces?
Answer:
50. How do you recover a data file that has not been physically been backed
up since its creation and has been deleted. Provide syntax example
Answer: Assume that the file ‘/u01/ORADATA/data/data01.dbf’ of tablespace DATA is damaged and you don't have a backup of the data file.
• SVRMGRL
• SVRMGRL> connect internal
• connected
• SVRMGRL> shutdown abort
• Oracle Instance Aborted
• SVRMGRL> startup mount
• Oracle Instance Started
• SVRMGRL> alter database create datafile ‘/u01/ORADATA/data/data01.dbf’;
• Statement Processed
• SVRMGRL> recover datafile ‘/u01/ORADATA/data/data01.dbf’;
• Statement Processed
• SVRMGRL> select * from v$datafile;
• You will see a list of datafiles with their status. If the status of the concerned file has not been reset to ONLINE, issue the following command.
• SVRMGRL> alter database datafile ‘/u01/ORADATA/data/data01.dbf’ online;
• Statement Processed
• SVRMGRL> select * from dba_tablespaces;
• If the status of the tablespace CASE is not ONLINE, then issue the following command.
• SVRMGRL> alter tablespace CASE online;
• Statement Processed
• SVRMGRL> alter database open;
Statement Processed
Posts
0 comments:
Post a Comment