Oracle Database Administrator Job Interview Preparation Questions
DBA Technical Interview Questions:
1. Give one method for transferring a table from one schema to another:
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?
The IMPORT IGNORE option tells import to ignore “already exists” errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.
3. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
4. What are some of the Oracle provided packages that DBAs should be aware of?
Oracle provides a number of packages in the form of the DBMS_ packages owned by the SYS user. The packages used by DBA may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT.
5. What happens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
6. What happens if a tablespace clause is left off of a primary key constraint clause?
That results in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
7. What is the proper method for disabling and re-enabling a primary key constraint?
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.
8. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
9. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?
You cannot use hot backup without being in archivelog mode. So no, you couldn’t recover.
10. What causes the “snapshot too old” error? How can this be prevented or mitigated?
It occurs when the redo required for a read consistent view of the data is no longer available. To avoid or to prevent by increasing the number and size of redo segments so that the longest running transaction can be contained in one segment. – or – Increase the undo retention period to a value >= the longest running transaction in the database and make sure you have sufficient disk to support that much redo.
11. How can you tell if a database object is invalid?
By checking the STATUS column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.
12. A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?
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 to the object (CREATE SYNONYM emp FOR scott.emp;)
13. How can you find out how many users are currently logged into the database? How can you find their operating system id?
There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a “ps -ef|grep oracle|wc -l
14. A user selects from a sequence and gets back two values, his select is:
SELECT pk_seq.nextval FROM dual; What is the problem?
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.
15. How can you determine if an index needs to be dropped and rebuilt? (Expect answer shouldn’t be that long. This is more for explanation purpose).
There are two rules of thumb to help determine if the index needs to be rebuilt.
1. If the index has height greater than four, rebuild the index.
2. The deleted leaf rows should be less than 20%.
You can find the above values from the following SQL:
Example 1:
SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;
Statement processed.
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM
INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW
———————- ———– ———- ———- —————-
DX_GAM_ACCT 2 1 3 6
In this example, the HEIGHT column is clearly showing the value 2. This is not a good candidate for rebuilding. If the value is greater then four, then rebuild the index in non-business hours using:
ALTER INDEX REBUILD | REBULID ONLINE
Example 2:
SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;
Statement processed.
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)
*100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO
—————————— ———- ———- ———- ——-
IDX_GAM_FID 1 189 62 32.80
1 row selected.
In this example, the ratio of deleted leaf rows to total leaf rows
is clearly above 20%. This is a good candidate for rebuilding.
Let’s rebuild the index and examine the results
SQL> ANALYZE INDEX IDX_GAM_FID REBUILD;
Statement processed.
SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;
Statement processed.
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*
100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO
—————————— ———- ———- ———- ——-
IDX_GAM_FID 1 127 0 0
1 row selected.
Examining the INDEX_STATS table shows that the 62 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 189 to 127, which is a difference of 62 leaf rows (189-127). This index should provide better performance for the application.
sql
#Oracle #Database #Administrator #Job #Interview #Preparation #Questions
Will be pleased to have you visit my pages on social networking .
Facebook page here.
Twitter account is here.
Linkedin account here
Post byBedewy for info askme VISIT GAHZLY
