Oracle classic Interview Questions and answers

Typical questions and answers from Oracle 1. Explanations of differences between cold backup and hot backup and their respective advantages answer: Hot Backup is applicable to databases in archive mode when the database is still working. 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 logs are not necessarily written to the hard disk) www.2cto.com 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? Answer: Use the create spfile from pfile command. 4. Explain the differences between data block, extent, and segment. (We recommend that you use an English term here.) Answer: 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. solution: 1. DESCRIBE command 2. DBMS_METADATA.GET_DDL Package 6. alert log. 7. comparison of 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 that moves HWK without rollback segment. delete is a DML operation that requires rollback segment and takes a long time. www.2cto.com 8. how to use indexes: quick access to data block 9 in a table. the two types of tables in star schema and their respective data solutions are provided: Fact tables and dimension tables. fact table contains a large amount of main information, while dimension tables stores information about certain attribute descriptions of fact table. 10. what indexes should be created on the FACT Table? Q: bitmap index 11. Are there two constraints? Answer: primary key and foreign key 12. how to Create a parent table without affecting the sub-table? Answer: The foreign key of the sub-table is forced to be effective, the parent table is rebuilt, and the foreign key is activated 13. explanation of differences between archive and non-archive modes and their respective advantages and disadvantages answer: Archive Mode means that you can back up all the databases transactions and restore them to any point in time. Non-archive mode, on the contrary, cannot be recovered to any point in time. However, the non-archive mode can slightly improve the database performance. 14. How to Create a backup control file? Q: Alter database backup control file to trace. 15. How many statuses have the database experienced during normal start? Answer: startup nomount-start startup mount of the database instance-database load startup open-OPEN Database 16. Which column can be used to distinguish between V $ view and GV $ view? A: INST_ID indicates a specific instance in the cluster environment. 17. How to generate an explain plan? A: Run utlxplan. SQL. create a plan table for specific SQL statements and run utlxplp using the explain plan set statement_id = 'tst1' into plan_table. SQL or utlxpls. check the SQL statement in the explain plan 18. how to increase the buffer cache hit rate? Q: When the database is busy, the buffer cache advisory tool is used to query v $ db_cache_advice. If necessary, you can use the alter system set db_cache_size command www.2cto.com 19. How does the ORA-01555 respond? A: The specific error message is snapshot too old within rollback seg. Generally, you can increase the rollback seg to solve the problem. Of course, you also need to check the specific SQL text that causes the error 20. What is the difference between $ ORACLE_HOME and $ ORACLE_BASE? Answer: ORACLE_BASE is the root directory of oracle, and ORACLE_HOME is the directory of oracle products 21. How to determine the time zone of the database? Answer: select dbtimezone from dual; 22. explains how to set GLOBAL_NAMES to TRUE. Answer: GLOBAL_NAMES indicates how to connect to the database. If this parameter is set to TRUE, you must use the same name to connect to the remote database 23 when creating a database connection. How to encrypt PL/SQL programs? Answer: WRAP 24. explain the difference between FUNCTION, PROCEDURE and PACKAGE: function and procedure are a collection of PL/SQL code, usually to complete a task. Procedure does not need to return any value, and function will return a value. On the other hand, Package is a set of functions and proceudh for a commercial function. 25. explanation: TABLE Function returns a set of records through PL/SQL logic for common tables/views. They are also used in pipeline and ETL processes. 26. three methods can be used to collect three advisory statistics. Answer: Buffer Cache Advice, Segment Level Statistics, Timed Statistics 27. Which oracle directory structure does Audit trace store? Answer: unix $ ORACLE_HOME/rdbms/audit Windows the event viewer 28. Explains the role of materialized views www.2cto.com. Answer: Materialized views is used to reduce the number of sets of summary, sets, and groups. They are generally suitable for data warehouses and DSS systems. 29. Which background process is responsible for clearing a user's process when an error occurs? A: PMON 30. Which background process refreshes materialized views? Q: The Job Queue Processes. 31. How do I determine which session is being linked and The resources they are waiting? Q: V $ SESSION/V $ SESSION_WAIT 32. What is redo logs? Answer: Redo Logs is the physical and logical structure used to store database data changes. Can be used to repair the database. 33. How to force log switch? Q: alter system switch logfile; 34. are two methods used to judge DDL changes? Q: What does Logminer or Streams 35. Coalescing do? Q: What is the difference between Coalescing and the PERMANENT tablespace in terms of table space in dictionary management by combining the neighboring small extents into a single large extent. 36. TEMPORARY tablespace? Answer: A temporary tablespace is used for temporary objects, such as sorting structures, and permanent tablespaces is used to store objects with 'true' (such as tables and rollback segments) 37. what is the name of the tablespace automatically created when a database is created? A: SYSTEM tablespace. www.2cto.com 38. When creating a user, you must grant the new user the permissions to connect the user to the database. Answer: CONNECT 39. How to add data files in tablespace? Q: alter tablespace <tablespace_name> add datafile <datafile_name> SIZE <size> 40. How can I change the SIZE of a data file? Answer: alter database datafile <datafile_name> RESIZE <new_size>; 41. Which VIEW is used to check the size of the data file? Answer: DBA_DATA_FILES 42. Which VIEW is used to determine the remaining space of tablespace? Answer: DBA_FREE_SPACE 43. How can I determine who has added a record to the table? Q: auditing 44. How to reconstruct the index? Answer: alter index <index_name> REBUILD; 45. Explain what is Partitioning and its advantages. A: Partition splits large tables and indexes into smaller partitions that are easy to manage. 46. You just compiled a PL/SQL Package but reported an error. How can I display the error message? Q: show errors 47. How do I collect various table status data? A: ANALYZE www.2cto.com The ANALYZE command. 48. how to start the TRACE at the SESSION level: DBMS_SESSION.SET_ SQL _TRACE alter session set SQL _TRACE = TRUE; 49. differences between IMPORT and SQL * LOADER: These two ORACLE tools are used to IMPORT data to the database. The difference is that the IMPORT tool can only process data generated by another ORACLE tool EXPORT. SQL * LOADER can import 50 data sources in different ASCII Formats. 2 files used for network connection? Answer: chow _ zh by TNSNAMES. ORA and SQLNET. ORA