Oracle interview questions and answers easy answer to the interviewer

This article originates from: http://www.dbasupport.com

The answer here is not very comprehensive, these questions can be explained through a number of angles, perhaps you do not have to give a full answer in the interview process, only through your answers to the interviewer to understand your understanding of the Oracle concept.

1. Explain the different points of cold and hot backup and their advantages

Answer: Hot-backed databases for archive mode, backed up while the database is still working. A cold backup is a database that is backed up after the database is closed and applies to all schemas. The advantage of hot backup is that when the backup is in place, the database can still be used and the database can be restored to any point in time. The advantage of a cold backup is that its backup and recovery operations are fairly simple, and because a cold backed up database can work in a non-archive mode, the database performance is slightly better than the archive mode. (Because the archive log will not be written to the hard drive)

2. You have to use Backup to restore the database, but you do not have control files, how to solve the problem?

Answer: Rebuild the control file and restore the database with the recover command with the backup controls file clause.

3. How to convert Init.ora to SPFile? Answer: Use the Create SPFile from Pfile command.

4. Explain the difference between data block, extent and segment (English terminology is recommended here)

Answer: Data block is the smallest logical storage unit in a database. When the object of the database needs more physical storage space, the continuous data block is composed of extent. All extents owned by a database object are called segment of that object.

5. A method for the construction of two inspection tables

Answer: 1. Describe command

2.dbms_metadata. GET_DDL Bag

6. How to view the database engine error

Answer: Alert log.

7. Compare truncate and DELETE commands

Answer: Both can be used to delete all the records in the table. The difference is that truncate is a DDL operation, it moves HWK and does not need rollback segment. And delete is a DML operation that requires rollback segment and takes a long time.

8. Reasons for using the index

Answer: Quick access to data block in a table

9. Given the two tables in the star schema and the data they contain separately

Answer: Fact tables and dimension tables. Fact table contains a large amount of primary information and dimension tables store information about some of the properties of fact table

What index do I need to establish on FACT table?

Answer: Bitmap index (bitmap index)

11. Give two kinds of related constraints?

Answer: Primary key and foreign key

12. How to rebuild a master table without affecting the child table

Answer: The foreign key of the child table enforces the effect, rebuilds the master table, activates the foreign key

13. Explain the differences between archive and non-archive patterns and their respective pros and cons

Answer: Archiving mode means that you can back up all the database transactions and restore to any point in time. Non-archive mode, on the contrary, cannot be restored to any point in time. However, non-archive mode can bring a little improvement in database performance.

14. How to create a backup control file?

Answer: Alter database backup control file to trace.

15. Give a few states that the database goes through normally?

Answer: Startup Nomount database instance started

STARTUP Mount Database Mount

The STARTUP Open database opens

16. Which column can be used to distinguish between v$ view and gv$ view?

Answer: INST_ID indicates a specific instance in the cluster environment.

17. How to generate explain plan?

Answer: Run Utlxplan.sql. Build the Plan table

For specific SQL statements, use the explain plan set statement_id = ' tst1 ' into plan_table

Run Utlxplp.sql or Utlxpls.sql view explain plan

18. How to increase the hit rate of buffer cache?

Answer: When the database is busy, apply buffer cache advisory tools, query V$db_cache_advice. If changes are necessary, you can use the ALTER system set DB_CACHE_SIZE command

ORA-01555 's coping methods?

Answer: The specific error message is snapshot too old within rollback SEG, which can often be solved by increasing rollback seg. Of course, you need to look at the SQL text that caused the error.

20. Explain the difference between $oracle_home and $oracle_base?

Answer: Oracle_base is the root directory of Oracle, Oracle_home is the directory of Oracle products.