DBA classic interview questions (3)

Source: Internet
Author: User
Tags types of tables

1. Explain the differences between cold backup and hot backup and their respective advantages
Answer: Hot Backup is applicable to databases in the archive mode. When the database is still in the working state, it is backed up. 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 log cannot be written to the hard disk ).
 
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?
A: Use the create spfile from pfile command.
 
4. Explain the differences between data block, extent, and segment (the term here is recommended)
A: 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. Two Methods of checking table structure are provided.
Answer: 1. DESCRIBE command
2. DBMS_METADATA.GET_DDL package
 
6. How to view database engine errors
Answer: alert log.
 
7. Compare the truncate and delete commands
A: both of them can be used to delete all records in the table. The difference is that truncate is a DDL operation, which moves HWK and does not require rollback segment. Delete is a DML operation, which requires rollback segment and takes a long time.
 
8. Reasons for using Indexes
A: quick access to the data block in a table
 
9. Two types of tables in star schema and their data are given respectively.
Answer: Fact tables and dimension tables.
Fact table contains a large amount of main information;
Dimension tables stores information about certain attributes of the fact table;
 
10. What indexes should be created on the FACT Table?
A: bitmap index)
 
11. are two related constraints provided?
Answer: primary key and foreign key
 
12. How to recreate a parent table without affecting the sub-table?
A: The foreign key of the sub-table is forcibly invalid. The parent table is rebuilt to activate the foreign key.
 
13. Explain the differences between archive and non-archive modes and their respective advantages and disadvantages
A: In archive mode, you can back up all database 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?
Answer: Alter database backup control file to trace.
 
15. How many statuses does the database normally start?
Answer: startup nomount database instance STARTUP
Startup mount Database loading
Startup open Database OPEN
 
16. Which column can be used to distinguish between the V $ view and the 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, use the explain plan set statement_id = 'tst1' into plan_table
Run utlxplp. SQL or utlxpls. SQL to check the explain plan
 
18. How to Increase the buffer cache hit rate?
A: When the database is busy, use the buffer cache advisory tool to query v $ db_cache_advice. If necessary, use the alter system set db_cache_size command.
 
19. How should 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?
A: 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 the purpose of setting GLOBAL_NAMES to TRUE.
A: 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 when creating a database connection.
 
23. How to encrypt PL/SQL programs?
Answer: WRAP
 
24. Differences between FUNCTION, PROCEDURE, and PACKAGE
A: 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 returns a value.
On the other hand, Package is a set of functions and proceudrs to complete a commercial function.
 
25. Explain the purpose of TABLE Function
A: The TABLE Function returns a set of records through the PL/SQL logic for common tables/views. They are also used in pipeline and ETL processes.
 
26. Three types can be used to collect advisory statistics
Answer: Buffer Cache Advice, Segment Level Statistics, Timed Statistics
 
27. Which oracle directory structure does the Audit trace store in?
Answer: unix: $ ORACLE_HOME/rdbms/audit
Windows: the event viewer
 
28. Explain the role of materialized views
Answer: Materialized views is used to reduce the number of sets of summary, set, and group information. 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?
Answer: PMON
 
30. Which background process refreshes materialized views?
A: The Job Queue Processes.
 
31. How can I determine which session is being linked and the resources they are waiting?
Q: V $ SESSION/V $ SESSION_WAIT
 
32. description of what is redo logs
A: Redo Logs is the physical and logical structure used to store database data changes. It can be used to repair databases.
 
33. How to force log switch?
Answer: alter system switch logfile;
 
34. How can I judge DDL changes?
A: You can use Logminer or Streams.
 
35. What does Coalescing do?
Answer: Coalescing fragment the tablespace for dictionary management, and merge the neighboring small extents into a single large extent.
 
36. What is the difference between TEMPORARY tablespace and PERMANENT tablespace?
A: 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?
Answer: SYSTEM tablespace.
 
38. When creating a user, what permissions should be granted to the new user before it can be connected to the database.
Answer: CONNECT
 
39. How to add data files in tablespace?
Answer: alter tablespace <tablespace_name> add datafile <datafile_name> SIZE <size>
 
40. How to change the data file size?
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?
Answer: 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?
Answer: SHOW ERRORS
 
47. How to collect various table status data?
Answer: ANALYZE
The ANALYZE command.
 
48. How to start SESSION-level TRACE
Answer: DBMS_SESSION.SET_ SQL _TRACE
Alter session set SQL _TRACE = TRUE;
 
49. Differences between IMPORT and SQL * LOADER
A: 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 data sources in different ASCII Formats.
 
50. are two files used for network connection?
Answer: TNSNAMES. ORA and SQLNET. ORA

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.