Oracle Database Administrator Job Interview Preparation Questions

DBA Technical Interview Questions:

1. Provide a method to transfer a table from one schema to another:

There are several possible methods, export-import, CREATE TABLE… LIKE SELECT or COPY.

2. What is the purpose of the IMPORT IGNORE option? What is your default setting?

The IMPORT IGNORE option tells import to ignore “already existing” errors. If not specified, tables that already exist will be skipped. If specified, the error is ignored and the data from the table is inserted. The default is N.

3. If the DEFAULT and TEMPORARY table space clauses are left out of a CREATE USER command, what happens? Is this bad or good? Because?

The user is assigned the SYSTEM tablespace as the default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed in the SYSTEM table space, which results in fragmentation and incorrect table placement (only data dictionary objects and data segment). system rollback should be in SYSTEM).

4. What are some of the packages provided by Oracle that DBAs should be aware of?

Oracle provides a number of packages in the form of DBMS_ packages owned by the SYS user. Packages used by DBAs 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 as it makes it hard to keep track of which table the constraint belongs to or what the constraint does.

6. What happens if a table space clause is left out of a primary key constraint clause?

This results in the automatically generated index being placed in the users default tablespace. Since this will typically be the same table space that the table is created in, this can cause serious performance issues.

7. What is the proper method to disable and re-enable a primary key constraint?

Use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX clause and TABLESPACE 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 on the user’s default tablespace and all size information is lost. Oracle does not store this information as 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 was gone.

9. You are using a hot backup without being in archivelog mode, can it fail over? 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?

Occurs when the redo required for a consistent read view of the data is no longer available. To avoid or 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 enough disk to support both redos.

11. How can you tell if a database object is invalid?

Checking the STATUS column of DBA_, ALL_, or USER_OBJECTS views, depending on whether you own or only have permission on the view or are using a DBA account.

12. A user receives an ORA-00942 error but knows that he has been granted permission on the table, what else should he check?

You should verify 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 connected to the database? How can you find your 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 two values, his selection 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.

15. How can you determine if an index should be dropped and rebuilt? (The expected answer shouldn’t be that long. This is more for explanation purposes.)

There are two rules of thumb to help determine if the index needs to be rebuilt.

1. If the index has a height greater than four, rebuild the index.

2. The rows of sheets removed 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;

Declaration processed.

SQL> SELECT name, height, lf_rows, lf_blks, del_lf_rows FROM

STATISTICS_INDEX;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW

—————————- ———– ———- – —— — —————-

DX_GAM_ACCT 2 1 3 6

In this example, the HEIGHT column clearly shows the value 2. This is not a good candidate for rebuilding. If the value is greater than four, rebuild the rate in non-business hours using:

RECONSTRUCTION OF THE DISTURBANCE INDEX | REBULLED ONLINE

Example 2:

SQL>ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Declaration processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)

*100 as a 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 the rows of sheets removed to the total rows of sheets

It 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;

Declaration processed.

SQL>ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Declaration processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*

100 as ratio OF 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 all 62 deleted leaf rows were removed 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.