Oracle DBA interview Some common face questions collection

While Oracle DBAs are high-end jobs in the IT industry and demand is strong, it is essential to enter the industry to do a DBA interview. Oracle Training Center CUUG the most important Oracle DBA interview questions and answers for the Oracle DBA for reference after a trainee interview:

Problem

1. Explain the different points of cold and hot backup and their advantages

Answer: The hot backup is for the archive mode database and is backed up when the database is still in working state. A cold backup is a database that is backed up when the database is shut down and applies to all schemas. The advantage of hot backup is that when backed up, the database can still be used and the database can be recovered to any point in time. The advantage of a cold backup is that its backup and recovery operations are fairly straightforward, and that database performance is slightly better than the archive mode because the cold backed database can work in non-archival mode. (Because the archive log will not be written to the hard disk)

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 the database with the recover command with the backup control clause.

3. How do I 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: The data block is the smallest logical storage unit in the database. When the database object requires more physical storage space, the contiguous data block is composed of extent. All extents owned by a database object are called segment of that object.

5. A method for two checklist structures is given

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 records in the table. The difference is that truncate is a DDL operation, it moves HWK and does not require 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 the table

9. Give the two types of tables in the star schema and the data they contain separately

Solution: Fact Tables and dimension tables. The fact table contains a large amount of primary information and dimension tables stores some of the properties described for the fact table.

Information

What index do I need to build on the FACT table?

Answer: Bitmap index (bitmap index)

11. Give two kinds of related constraints?

Answer: Primary key and foreign key

12. How to reconstruct a parent table without affecting the sub-table

Solution: Foreign key forced invalidation of child table, rebuilding master table, activating foreign key

13. Explain the differences between archived and non-archived models and their respective strengths and weaknesses

Answer: The archive mode means that you can back up all the database transactions and restore to any point in time. The non-archive mode, in contrast, cannot be restored to any point in time.

But non-archival mode can lead to a little improvement in database performance

14. How do I create a backup control file?

Answer: Alter database backup control file to trace.

15. What are some of the states that have experienced the normal startup of a database?

Answer:

Startup nomount– DB Instance started

STARTUP mount-Database loading

STARTUP open– Database Open

16. Which column can be used to distinguish between the v$ view and the gv$ view?

Answer: INST_ID indicates a specific instance in the cluster environment.

17. How do I generate explain plan?

Answer:

Run Utlxplan.sql. Build plan table for specific SQL statements, using explain plan set statement_id = ' tst1 ' into plan_table run

Utlxplp.sql or utlxpls.sql See explain plan

18. How do I increase the hit rate of buffer cache?

Solution: When the database is busy, the buffer cache advisory tool is used to query the V$db_cache_advice. If changes are necessary, you can use the ALTER system set

Db_cache_size command

How is ORA-01555 coping?

Answer: The specific error message is the snapshot too old within rollback SEG, which can usually be solved by increasing the rollback SEG. Of course, we need to look at

SQL text that caused the error in the body

20. Explain the difference between $oracle_home and $oracle_base?

Answer: Oracle_base is the root of Oracle, Oracle_home is the catalog of Oracle Products

21. How do I determine the time zone of a database?

Solution: 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 parameter is set to TRUE, the remote database must be linked with the same name when establishing the database link

23. How do I encrypt a PL/SQL program?

Solution: WRAP

24. Explain function,procedure and package differences

Solution: Function and Procedure are a collection of PL/SQL code, usually to complete a task. Procedure does not need to return any values and the function returns a value in another

aspect, the package is a set of functions and Proceudre to complete a business function

25. Explaining the purpose of the table function

Answer: Table function returns a set of records through PL/SQL logic for normal tables/views. 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 does the. Audit trace reside in?

Solution: Unix $ORACLE _home/rdbms/audit Windows the Event Viewer

28. Explaining the role of materialized views

Answer: Materialized views is used to reduce the number of aggregated, set, and grouped information sets. They are typically suitable for data warehousing and DSS systems.

29. When the user process fails, which background process is responsible for cleaning it

Answer: Pmon

30. Which background process refreshes materialized views?

Answer: The Job Queue Processes.

31. How can I tell 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 the physical and logical structure used to store database data changes. can be used to repair the database.

33. How do I force log SWITCH?

Answer: ALTER SYSTEM SWITCH LOGFILE;

34. Give two ways to judge DDL changes?

Answer: You can use Logminer or Streams

What did coalescing do?

Solution: Coalescing for the dictionary-managed tablespace, merging the adjacent small extents into a single large extent.

What is the difference between temporary tablespace and permanent tablespace?

Solution: A temporary tablespace for temporary objects such as the sort structure and permanent tablespaces to store those ' real ' objects (such as tables, rollback segments, etc.)

37. The tablespace name that is automatically created when you create a database?

Answer: SYSTEM tablespace.

38. When creating a user, you need to give the new user what permissions to make it linked to the database.

Answer: CONNECT

39. How do I add data files to my tablespace?

Answer: ALTER tablespace ADD datafile SIZE

40. How do I change the size of my data file?

Answer: ALTER DATABASE datafile RESIZE;

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 the tablespace

Answer: Dba_free_space

43. How can I tell who has added a record to the table?

Answer: Auditing

44. How do I refactor an index?

Answer: ALTER INDEX REBUILD;

45. Explain what is partitioning (partition) and its merits.

Answer: Partition partition large tables and indexes into smaller, easier-to-manage partitions.

46. You have just compiled a PL/SQL package with error reporting, how do I display an error message?

Answer: SHOW ERRORS

47. How do I collect various status data for a 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;

The different points of the 2 tools, import and Sql*loader

Answer: These two Oracle tools are used to import data into a database.

The difference is that the import tool can only be processed by another Oracle tool export generation

The data. Instead, Sql*loader can import data sources in different ASCII formats

50. 2 files for a network connection?

Answer: TNSNames. ORA and Sqlnet. ORA

Oracle DBA interview Some common face questions collection