Oracle Interview Questions - Technical Articles
This may be the article you have been looking forward to, while paying attention to this technical problem, please be sure to read questions and answers about individuals in the interview. The answer here is not very comprehensive, these issues can be explained by multiple angles. Maybe you don't have to give a completely detailed answer during the interview process, just need to make the interview examinations to understand your familiarity of Oracle concept. . 1. Explain the different points of the cold backup and hot spare and their own advantages
Solution: The hot backup for the archive mode, the database is still backup when the database is still working. The cold backup refers to the backup after the database is closed, and it is suitable for databases of all modes. The advantage of the thermal spare is that the database can still be used when backup and can restore the database to any point in time. The advantage of the cold backup is that its backup and recovery operation is quite simple, and the database performance is slightly better than the archive mode due to the database of the cold backup. (Because you don't have to write Archive Log to your hard drive)
2. You must use the backup to recover the database, but you don't control the file, how to solve the problem?
Solution: Reconstruction Control File, recover the database with the Recover command with the Backup Control File clause.
3. How to convert init.ora to SPFILE?
Solution: Use the Create SPFile from Pfile command.
4. Explain the difference between Data Block, Extent and Segment (herein, English terms)
Solution: Data Block is the smallest logical storage unit in the database. When the object of the database requires more physical storage space, consecutive Data Block constitutes an extent. A database object has all ExtensS called the object's segment.
5. Give two methods of checking table structure
Answer: 1.Describe command
2.DBMS_METADATA.GET_DDL package
6. How to view the error of the database engine
Solution: Alert log.
7. Compare Truncate and Delete Commands
Solution: Both can be used to delete all records in the table. The difference is that Truncate is DDL operation, it moves HWK, does not require rollback segment. And DELETE is DML operation, which requires Rollback Segment and spends longer.
8. Reasons for using indexes
Answer: Quick access to Data Block
9. Given the two tables in Star Schema and their respective data
Answer: Fact Tables and Dimension Tables. Fact Table contains a large amount of information and Dimension Tables store information for some attributes of Fact Table.
10. What index is needed on Fact Table?
Answer: Bitmap Index)
11. Give two related constraints?
Answer: Primary key and foreign key
12. How to rebuild a master table without affecting the child table
Solution: The foreign key of the sub-table is enforced, reconstructed the master table, and the foreign key is activated.
13. Interpret the difference between archiving and non-archive mode and their respective advantages and disadvantages
Answer: Archiving mode means you can back up all database transactions and return to any point in time. In contrast, the non-invasive mode cannot be recovered to any time point. However, non-archiving patterns can bring a little increase in database performance.
14. How to build a backup control file?
Answer: Alter Database Backup Control File to TRACE.
15. How many states have been introduced by the database normal startup?
Solution: Startup Nomount - Database instance start
STARTUP MOUNT - Database Database
STARTUP OPEN - Database Open
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 an Explain Plan?
Solution: Run UTLXPLAN.SQL. Create a Plan table
Using Explain Plan Set Statement_ID = 'TST1' INTO PLAN_TABLE for a specific SQL statement
Run UTLXPLP.SQL or UTLXPLS.SQL to view Explain Plan
18. How to increase the hit rate of buffer cache?
Solution: When the database is busy, the Buffer Cache Advisory tool is applied, query V $ db_cache_advice. If necessary, you can use the ALTER System Set DB_Cache_SIZE command.
19. What is the right method of ORA-01555?
Solution: The specific error message is Snapshot Too Old Within Rollback SEG, you can usually solve the problem by increasing Rollback Seg. Of course, you need to look at the specific SQL text that causes the error.
20. Interpret $ Oracle_Home and $ ORACLE_BASE?
Solution: Oracle_base is the root of Oracle, Oracle_Home is the directory of Oracle products.
