20 most common Oracle Database interview questions

18. How to Increase the buffercache hit rate? A: When the database is busy, use the buffercacheadvisory tool to query v $ db_cache_advice. If yes

18. How to Increase the buffer cache hit rate? A: When the database is busy, use the buffer cache advisory tool to query v $ db_cache_advice. If yes

1. Explain the differences between cold backup and hot backup and their respective advantages

Answer: Hot Backup is applicable to databases in the archive mode. When the database is still in the working state, it is backed up. Cold backup refers to the backup after the database is closed, applicable to all databases. The advantage of hot backup is that the database can still be used during Backup and can be restored to any point in time. The advantage of cold backup is that its backup and recovery operations are quite simple, and because cold backup databases can work in non-archive mode, the database performance will be slightly better than the archive mode. (Because archive log is not necessarily written to the hard disk)

2. You must use backup to restore the database, but you do not have control files. How can this problem be solved?

A: recreate the control file and use the recover command with the backup control file clause to restore the database.

3. How to convert init. ora to spfile?

A: Use the create spfile from pfile command.

4. Explain the differences between data block, extent, and segment (the term here is recommended)

A: data block is the smallest logical storage unit in the database. When database objects require more physical storage space, continuous data blocks constitute extent. All extents owned by a database object are called the segment of this object.

5. Two Methods of checking table structure are provided.

Answer: 1. DESCRIBE command

2. DBMS_METADATA.GET_DDL package

6. How to view database engine errors

Answer: alert log.

7. Compare the truncate and delete commands

A: both of them can be used to delete all records in the table. The difference is that truncate is a DDL operation, which moves HWK and does not require rollback segment. Delete is a DML operation, which requires rollback segment and takes a long time.

8. Reasons for using Indexes

A: quick access to the data block in a table

9. Two types of tables in star schema and their data are given respectively.

Answer: Fact tables and dimension tables. fact table contain a large amount of major information, while dimension tables stores information about certain attribute descriptions of fact table.

10. What indexes should be created on the FACT Table?

A: bitmap index)