ORACLE interview Questions-Technical articles
1. Explain the different points of cold and hot backup and their advantages
Answer: Hot-backed databases for archive mode, backed up while the database is still working. A cold backup is a database that is backed up after the database is closed and applies to all schemas. The advantage of hot backup is that when the backup is in place, the database can still be used and the database can be restored to any point in time. The advantage of a cold backup is that its backup and recovery operations are fairly simple, and because a cold backed up database can work in a non-archive mode, the database performance is slightly better than the archive mode. (Because the archive log will not be written to the hard drive)
2. You have to use Backup to restore the database, but you do not have control files, how to solve the problem.
Answer: Rebuild the control file and restore with the recover command with the backup controls file clause
Database.
3. How to convert Init.ora to SPFile?
Answer: Use the Create SPFile from Pfile command
.
4. Explain the difference between data block, extent and segment (English terminology is recommended here)
Answer: Data block is the smallest logical storage unit in a database. When the object of the database needs more physical storage space, the continuous data block is composed of extent. A Database object
All extents owned are referred to as the segment of the object.
5. A method for the construction of two inspection tables
Answer: 1. Describe command
2. Dbms_metadata. GET_DDL Bag
6. How to view the database engine error
Answer: Alert log.
7. Compare truncate and DELETE commands
Answer: Both can be used to delete all the records in the table. The difference is that truncate is a DDL operation, it moves HWK and does not need rollback segment. And delete is a DML operation that requires rollback segment and takes a long time.
8. Reasons for using the index
Answer: Quick access to data block in a table
9. Given the two tables in the star schema and the data they contain separately
Answer: Fact tables and dimension tables. Fact table contains a large amount of primary information and dimension tables store information about some of the properties of fact table
What indexes need to be established on FACT table.
Answer: Bitmap index (bitmap index)
11. Give two kinds of related constraints?
Answer: Primary key and foreign key
12. How to rebuild a master table without affecting the child table
Answer: The foreign key of the child table enforces the effect, rebuilds the master table, activates the foreign key
13. Explain the differences between archive and non-archive patterns and their respective pros and cons
Answer: Archiving mode means that you can back up all the database transactions and restore to any point in time. Non-archive mode, on the contrary, cannot be restored to any point in time. However, non-archive mode can bring a little improvement in database performance
.
14. How to create a backup control file.
Answer: Alter database backup control file to trace.
15. Give a few states that the database goes through normally?
Answer:
Startup nomount– Database instance started
STARTUP Mount-Database mount
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 explain plan?
Answer:
Run Utlxplan.sql. Build the Plan table
For specific SQL statements, use the explain plan set statement_id = ' tst1 ' into plan_table
Run Utlxplp.sql or Utlxpls.sql view explain plan
18. How to increase the buffer cache hit rate.
Answer: When the database is busy, apply buffer cache advisory tools, query V$db_cache_advice. If changes are necessary, you can use the ALTER system set DB_CACHE_SIZE command
ORA-01555 's coping methods.
Answer: The specific error message is snapshot too old within rollback SEG, which can usually be
Increase the rollback SEG to solve the problem. Of course, you need to look at the SQL text that caused the error.
20. Explain the difference between $oracle_home and $oracle_base.
Answer: Oracle_base is Oracle's root directory, Oracle_home is Oracle product
The directory.
21. How to determine the time zone of the database.
Answer: SELECT dbtimezone from DUAL;
22. Explain the purpose of global_names set to True
Answer: Global_names indicates how the database is joined. If this argument is set to True,
You must connect the remote database with the same name when you create the database link
23. How to encrypt the Pl/sql program.
Answer: WRAP
24. Interpretation of differences between function,procedure and package
Answer: Function and procedure are collections of Pl/sql code, usually in order to complete
a task. Procedure does not need to return any value and the function returns a value
On the other hand, package is a set of functions and Proceudre to complete a commercial function
The collection
25. Explain the purpose of the table function
Answer: The TABLE function returns a set of records by Pl/sql logic for
Normal table/view. They are also used for pipeline and ETL processes.
26.3 types of three advisory can be collected statistics
Solution: Buffer Cache Advice, Segment level Statistics, Timed Statistics
Which Oracle directory structure is Audit trace stored in?
Solution: Unix $ORACLE _home/rdbms/audit
Windows the Event Viewer
28. Explain the role of materialized views
Answer: Materialized views are used to reduce those totals, collections, and groupings of
The number of collections of information. They are typically suitable for data warehousing and DSS systems.
29. When the user process fails, which background process is responsible for cleaning it up
Answer: Pmon
30. Which background process refreshes materialized views?
Answer: The Job Queue processes.
31. How to determine which session is being linked and what resources they are waiting for.
Answer: v$session/v$session_wait
32. Describe what is redo logs
Answer: Redo Logs is a physical and logical structure for storing database data changes.
can be used to repair the database.
33. How do I force log SWITCH?
Answer: ALTER SYSTEM SWITCH LOGFILE;
34. Cite two methods for determining DDL changes.
Answer: You can use Logminer or Streams
What coalescing did.
Answer: coalescing for the tablespace of the dictionary management, the
The adjacent small extents are merged into a single large extent.
The difference between temporary tablespace and permanent tablespace is.
Answer: A temporary tablespace is used for temporary objects such as sorting structures and permanent tablespaces
Used to store those ' real ' objects (such as tables, rollback segments, etc.)
37. The tablespace name that is automatically established when the database is created.
Answer: SYSTEM tablespace.
38. When you create a user, you need to give the new user what permissions to enable it to be linked to the database.
Answer: CONNECT
39. How to add data files to the tablespace.
Answer: ALTER tablespace <tablespace_name> ADD datafile <datafile_name> SIZE <size>
40. How to change the size of the 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 judge the remaining space of tablespace
Answer: Dba_free_space
43. How to judge who adds a record to the table.
Answer: Auditing
44. How to refactor the index.
Answer: ALTER INDEX <index_name> REBUILD;
45. Explain what is partitioning (partition) and its advantages.
Answer: partition divides large tables and indexes into smaller, manageable partitions.
46. You have just compiled a pl/sql package but there are error reports, how to display error messages.
Answer: Show ERRORS
47. How to collect various state data of the table.
Answer: ANALYZE
The ANALYZE command.
48. How to start the session level trace
Answer: Dbms_session. Set_sql_trace
ALTER session SET sql_trace = TRUE;
Differences between the 2 tools of import and Sql*loader
Answer: These two Oracle tools are used to import data into the database.
The difference is that the import tool can only be processed by another Oracle tool export build
of data. and Sql*loader can import data sources in different ASCII formats.
50. 2 files for network connections.
Answer: TNSNAMES. ORA and Sqlnet. ORA
