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 thermal spare and their own advantage: The hot backup for the archive mode, the database is still in the working state. 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 restore 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: Using the Create SPFile from Pfile command. 4. Explain the Data Block, Extent and Segment Differences (Recommended English Terminology) Answer: Data Block is the smallest logical storage unit in the database. When the database's object requires more physical storage space, continuous Data Block constitutes an extent. A database object has all extents called the object's segment. 5. Method of answering the table structure: 1.Describe command 2.DBMS_Metadata.get_ddl package 6. How to view the database engine error answer: Alert log.7. Compare the truncate and delete commands: Both can be used to delete all records in the table. The difference is that Truncate is a DDL operation, it moves hwk, does not require Rollback Segment. The delete is a DML operation, which requires Rollback Segment and takes a long time. 8. Reason to use the index: Quick access to the Data Block 9. Data from both tables in Star Schema and their separate data: Fact Tables and Dimension Tables. Fact Table contains a large amount of main information and Dimension Tables stores information on the Fact Table some attributes 10.Fact Table What kind of index is built? Answer: BitMap Index 11. Give two related constraints? Solution: Primary key and foreign key 12. How to rebuild a masterometer answer without affecting the child table: The foreign key for the sub-table is enforced, Reconstruction of the master table, activate the foreign key 13. Explain the difference between archiving and non-invasive modes and their respective advantages and disadvantages: Archive mode means that you can back up all database Transactions and return to any time point. 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. Several status experienced by the database normal boot? Solution: Startup Nomount - Database instance launched Startup Mount - Database installed Startup Open - Database Open 16. Which Column can be used V $ view and GV $ view? Solution: inst_id indicates a specific INSTANCE in the cluster environment. 17. How to generate an Explain Plan? Answer: Run UTLXPLAN.SQL. Create a Plan table For a specific SQL statement, use the Explain Plan_Table run UTLXPLP.SQL or UTLXPLS.SQL to view the Explain Plan 18. How to add Buffer Cache Hit rate? Answer: When the database is busy, the Buffer Cache Advisory tool is applied to query V $ db_cache_advice. If necessary, you can use the ALTER SYSTEM SET DB_CACHE_SIZE command 19. ORA-01555 should be the right way? Solution: The specific error message is Snapshot Too Old Within Rollback SEG, you can usually solve the problem by increasing Rollback Seg. Of course, I need to check the specific SQL text 20. explanates the difference between $ ORACLE_HOME and $ ORACLE_BASE? Solution: Oracle_base is the root of Oracle, Oracle_Home is the directory of Oracle products. 21. How to determine the time zone of the database? Solution: Select DBTIMEZONE from Dual; 22. Explaining Global_Names Use to True Use: Global_Names specifies the way the join database. If this parameter is set to true, the remote database 23 must be connected with the same name when establishing the database link. How to encrypt the PL / SQL program? Answer: Wrap24. Explaining Function, Procedure, and Package Differences: Function and Procedure are the collection of PL / SQL code, usually in order to complete a task. Procedure does not need to return any values and Function will return a value in another, and Package is a collection of Function and Proceudre for completing a business function. 25. Explanation of Table Function: Table Function is returned by PL / SQL logic A set of records for ordinary table / views. They also used for the Pipeline and ETL processes. 26. cited three kinds of answers can collect three advisory statistics:. Buffer Cache Advice, Segment Level Statistics, Timed Statistics27 Audit trace which is stored in the directory structure oracle answered:?. Unix $ ORACLE_HOME / rdbms / auditWindows the event viewer28 explain materialized views The effect of the answer: Materialized Views is used to reduce the collection of information for those summaries, collections, and grouping. They are usually suitable for data warehouses and DSS systems.
29. When the user's process is wrong, which background process is responsible for cleaning it: PMON30. Which background process refreshes materialIzed Views? Answer: The Job Queue Processes.31. How to determine which session is connecting and the resources they wait? Answer: V $ session / v $ session_wait32. Description What is Redo Logs: Redo logs is a physical and logical structure for storing database data changes. Can be used to repair the database .33. How to force log switch? Solution: ALTER system switch logfile; 34. Have two methods for judging DDL changes? Answer: What do you have to use Logminer or streams35. Coalescing? Answer: Coalescing For the tableSpace for the dictionary, Coalescing is fragmented, and the adjacent small extents merges into a single large extent.36. Temporary TableSpace and Permanent TableSpace are the difference? Solution: A Temporary TableSpace is used for temporary objects such as sorting structure and Permanent TableSpaces is used to store those objects (such as tables, rollback segments, etc.) 37. Create a database automatically established TableSpace name? Answer: System TableSpace.38. When you create a user, you need to give new users what permissions can make it on the database. Answer: CREATE SESSION39. How do I add data files in TableSpace? Answer: ALTER TABLESPACE
