Common Questions of Oracle DBA asked during interview
1. Explain the $ ORACLE_HOME and $ ORACLE_BASE the difference?
Answer: ORACLE_BASE is the root directory of oracle, ORACLE_HOME is the oracle product directory.
2. How to increase buffer cache hit rate?
Answer: more busy in the database, the application buffer cache advisory tool, query v $ db_cache_advice. If necessary changes, you can use the command alter system set db_cache_size.
3. How to generate explain plan?
Answer: Run utlxplan.sql. Create plan table for specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table run utlxplp.sql or utlxpls.sql View explain plan.
4. Which column can be used to distinguish the V $ views, and GV $ views?
Answer: INST_ID indicate a specific cluster environment instance.
5. Gives the database starts normally experienced several states?
Answer: STARTUP NOMOUNT - database instance startup. STARTUP MOUNT - Database mounted. STARTUP OPEN - database open.
6. How to create a backup control file?
Answer: Alter database backup control file to trace.
7. Explain the archive and the differences between non-archive mode and their respective advantages and disadvantages. Answer: archive mode means that you can back up all database transactions and restore to any point in time. Non-archive mode, by contrast, can not restore to any point in time. But the non-archive mode can bring a little on the database to improve performance.
8. How the premise does not affect the child table, the reconstruction of a parent table.
Answer: the child table foreign key force effectiveness, re-master table, activate the foreign key.
9. Give two related constraints?
Answer: primary keys and foreign keys.
10. In the STAR SCHEMA given in two tables and the data they contain, respectively.
Answer: Fact tables and dimension tables. Fact table contains a number of major information and dimension tables on the fact table storing the information described some of the properties.
11. FACT Table on the need to establish what kind of index?
Answer: Bitmap indexes (bitmap index).
12. Use reason for the index.
Answer: quick access to table data block.
13. Comparison of truncate and delete command.
Answer: Both can be used to delete all records in the table. The difference is: truncate is a DDL operation, it moves HWK, do not need to rollback segment. And Delete is a DML operation, you need to rollback segment and take a long time.
14. How can I view the database engine error?
Answer: alert log.
15. Given the structure of the two methods checklist.
Answer: 1.DESCRIBE command 2.DBMS_METADATA.GET_DDL package.
16. Explain the data block, extent, and the difference between segment.
Answer: data block is the smallest logical database storage unit. When the database objects need more physical storage space, a continuous data block to form the extent. A database object is called with all the extents of the object segment.
17. How to convert init.ora to spfile?
Answer: Use the create spfile from pfile command.
18. If you must use a database backup and recovery, but you do not have control file, how to solve the problem?
Answer: rebuilding the control file, with a backup control file clause of the recover command to recover the database.
19. Cold backup and hot backup and the respective merits of different points
Answer: hot backup mode for the archive database is still in the database backup is in working condition. The cold backup means the database is closed, the backup, the database for all modes. The advantages of hot backup when the backup, the database can still be used and can restore the database to any point in time. Cold backup is that it's quite simple backup and recovery operations, and because of the cold backup of the database can work in non archive mode, the database performance was slightly better than the archive mode. (Because do not have to write the archive log disk ).
20. ORA-01555 to address the challenges?
Answer: The exact error message is a snapshot too old within rollback seg, usually by increasing the rollback seg to solve the problem. Of course, should check the SQL error text of the specific cause.
8. How the premise does not affect the child table, the reconstruction of a parent table.
Answer: the child table foreign key force effectiveness, re-master table, activate the foreign key.
9. Give two related constraints?
Answer: primary keys and foreign keys.
10. In the STAR SCHEMA given in two tables and the data they contain, respectively.
Answer: Fact tables and dimension tables. Fact table contains a number of major information and dimension tables on the fact table storing the information described some of the properties.
11. FACT Table on the need to establish what kind of index?
Answer: Bitmap indexes (bitmap index).
12. Use reason for the index.
Answer: quick access to table data block.
13. Comparison of truncate and delete command.
Answer: Both can be used to delete all records in the table. The difference is: truncate is a DDL operation, it moves HWK, do not need to rollback segment. And Delete is a DML operation, you need to rollback segment and take a long time.
14. How can I view the database engine error?
Answer: alert log.
15. Given the structure of the two methods checklist.
Answer: 1.DESCRIBE command 2.DBMS_METADATA.GET_DDL package.
16. Explain the data block, extent, and the difference between segment.
Answer: data block is the smallest logical database storage unit. When the database objects need more physical storage space, a continuous data block to form the extent. A database object is called with all the extents of the object segment.
17. How to convert init.ora to spfile?
Answer: Use the create spfile from pfile command.
18. If you must use a database backup and recovery, but you do not have control file, how to solve the problem?
Answer: rebuilding the control file, with a backup control file clause of the recover command to recover the database.
19. Cold backup and hot backup and the respective merits of different points
Answer: hot backup mode for the archive database is still in the database backup is in working condition. The cold backup means the database is closed, the backup, the database for all modes. The advantages of hot backup when the backup, the database can still be used and can restore the database to any point in time. Cold backup is that it's quite simple backup and recovery operations, and because of the cold backup of the database can work in non archive mode, the database performance was slightly better than the archive mode. (Because do not have to write the archive log disk ).
20. ORA-01555 to address the challenges?
Answer: The exact error message is a snapshot too old within rollback seg, usually by increasing the rollback seg to solve the problem. Of course, should check the SQL error text of the specific cause.
Comments
Post a Comment