Database interview questions Summary

 

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 is not necessarily 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?
Q: run 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. A database object
All the extents are called the segment of this object.
Segments exist in the tablespace. The CIDR block consists of a set of zones. A zone is a collection of data blocks. Data blocks are mapped to disk blocks.
 

5. Two Methods of checking table structure are provided.
Answer: 1. Describe command 2. dbms_metadata.get_ddl package: statements used to obtain the table structure

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.
(1) truncate is a DDL operation, and the deleted data is not included in the redo log, which is highly efficient; Delete DML operations, the deleted information is written into the redo log, with low efficiency
(2) truncate reduces hwm; Delete does not decrease hwm

8. Reasons for using Indexes
A: quick access to the data block in a table

9. are two related constraints provided?

Answer: primary key and foreign key

10. 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.

11. How to Create a backup control file?
Answer: Alter database backup control file to trace.

12. How many statuses does the database normally start?
Answer:
Startup nomount-start a database instance
Startup Mount-Database loading
Startup open-Open Database

 
13. 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.

14

Fact table and dimension tableThis is two concepts of a data warehouse and two types of tables of a data warehouse. From the perspective of data storage, there is essentially no difference between tables.
The difference is that fact tables are used to store fact data, that is, some measurable data and addition data, data quantity, amount, and so on.
Dimension table is used to store descriptive data and describe fact data, such as region, sales representative, and product.

15

Oracle indexes mainly include btree and bitmap indexes.
By default, most btree indexes are used. This index is usually the unique index seen and clustered index. btree is used in OLTP to speed up the query. Bitmap indexes are quite attractive in Oracle. They are mainly used in OLAP (online data analysis), that is, data warehouse, to speed up queries, saves storage space. Generally, indexes consume a large amount of storage space. Bitmap uses compression technology to reduce disk space. Btree uses a high base (that is, a column with a large data similarity), and a bitmap is used in a low base series.
The basic principle of Bitmap indexes is to use bitmaps instead of column values in indexes. Usually there is a very low set potential (cardinality) between the keys of a fact table and a dimension table. Bitmap indexes are used to make the storage more effective. Compared with B * tree indexes, it only requires less storage space, so that more records can be read each time. In addition, compared with B * tree indexes, bitmap indexes convert Bitmap indexes into bitwise arithmetic operations for connections and aggregation, this greatly reduces the running time and greatly improves the performance.

16. How to Increase the buffer cache hit rate?
A: When the database is busy, use the buffer cacheadvisory tool to query v $ db_cache_advice. If necessary, use the alter system set db_cache_size command.
Memory Guide
 

17. How should ORA-01555 respond?
Configure the appropriate restoration retention period

Correctly adjust the size of the restored tablespace

Consider ensuring the restoration retention period

 

18. 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.
Oracle_base isthe root directory for Oracle. ORACLE_HOME located beneath oracle_base is wherethe Oracle products reside

19. How to determine the time zone of the database?
Answer: Select dbtimezone from dual;

20. Explain 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,
When creating a database connection, you must use the same name to connect to the remote database.

21. How to encrypt PL/SQL programs?
Answer: Wrap

22. Differences between function, procedure, and Package
A: function and procedure are a collection of PL/SQL code.
A task. Procedure does not need to return any value, and function will return a value.
On the other hand, package is a group of functions and proceudrs to complete a commercial function.
.

23. Explain the purpose of Table Function
A: Table function returns a set of records through PL/SQL logic
Common tables/views. They are also used in pipeline and ETL processes.

3 types of three advisory statistics can be collected
Answer: buffer cache advice, segment level statistics, timed statistics

25 which Oracle directory structure does audit trace store?
Answer: Unix $ ORACLE_HOME/rdbms/Audit
Windows The Event Viewer

26. Explain the role of materialized views
Answer: materialized views is used to reduce those summary, sets, and groups.
Number of sets of information. They are generally suitable for data warehouses and DSS systems.

27. Which background process is responsible for clearing the user process when an error occurs?
Answer: pmon

28. Which background process refreshes materializedviews?
A: The Job Queue processes.

29. How can I determine which session is being linked and the resources they are waiting?
Q: V $ session/V $ session_wait

30. Describe 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.

31. How to force log switch?
Answer: Alter system switch logfile;

32. How can I determine DDL changes?
A: You can use logminer or streams.

33 what does coalescing do?
Answer: coalescing fragment the dictionary-managed tablespace and
Merge neighboring small extents into a single large extent.

34 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
Used to store the 'true' objects (such as tables and rollback segments)

35. What is the name of the tablespace automatically created when a database is created?
Answer: System tablespace.

36. In Oracle databases, there are three types of checkpoints Based on the execution time.

First, the database checkpoint. When the redo log of the database is switched, the database checkpoint event is triggered. Write data in the databaseProcessAll data in the cache is written to the data file. The switching of log reorganization is mainly related to the size of the redo log file. When the database update operations are the same, when the redo log files are relatively small, the time interval between the checkpoints is relatively small. When the redo log file is large, it may take a long time to trigger this checkpoint. Therefore, for database checkpoints, you can adjust the size of redo log files to set the time interval for executing checkpoints. However, because the data updated by the database in different time periods is different, the content written to the redo log file is also different. Therefore, the execution interval of the checkpoint is often different. When the size of the redo log file is the same, if the database is updated frequently, a checkpoint may be executed every several minutes or even several seconds. When there are few database update operations, it may take several hours to trigger a checkpoint. Therefore, this feature also leads to another suggestion to improve database performance. That is, when the database involves frequent update operations, you can set a larger number of redo log files to prolong the checkpoint execution time and improve the efficiency of update operations.

The second is the tablespace checkpoint. In Oracle databases, tablespaces are a logical unit of storage for data files. Table space makes it easier to manage Oracle databases. For example, you can set a tablespace of an Oracle database to read-only or offline and maintain it. This does not affect the use of other tablespaces. The tablespace checkpoint I want to talk about now is related to this feature. When the database administrator sets a tablespace offline from the Internet, the database system immediately executes a checkpoint. In this case, the database write process writes the dirty cache related to the tablespace in the database cache to the data file. Note that all the dirty caches will not be written to the database, but the dirty caches related to the tablespace. Because the tablespace checkpoint is triggered only when the tablespace is offline, the execution interval is not necessarily. I don't think any database administrator can take the tablespace offline for a while.

The third is the time checkpoint, that is, the time at which the checkpoint is executed. After a time checkpoint is set, the checkpoint is executed every time. However, this setting does not affect the preceding two checkpoints. That is, even if the set time checkpoint has not been triggered, as long as the redo log is switched or the administrator sets the tablespace to offline, the checkpoint operation is performed. By setting a reasonable time checkpoint, you can perform the checkpoint operation multiple times during a redo log switching cycle.

37. Set checkpoints reasonably

Database system crash. Because all changes made to the database have been saved to the data file, you only need to restore the database to the execution time of the previous checkpoint during data recovery. Complex recovery operations are not required. Therefore, if the checkpoint is set to a shorter one, that is, the time for the two checkpoints to occur is relatively short, it can reduce the time required for database recovery.

However, this checkpoint is not set as short as possible. If the checkpoint is 0, any changes to the database are saved to the data file in time. The performance of the database system is compromised. This is because many I/O operations are generated. During database optimization, I/O operations areBottleneck. Therefore, the checkpoint setting is not as short as possible. If the checkpoint execution interval is too short, it will cause I/O contention and affect the database performance. If the time interval is set too long, it will affect the recovery time when the database encounters a fault. Therefore, database management requires a balance between database performance and database recovery time.

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> adddatafile <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. Run the logical block check.

The analyze command can be used to verify the structure of a table or table partition, as well as the structure of an index or index partition.

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 the data generated by another oracle tool export.
. SQL * loader can import data sources in different ASCII Formats.

50. 2 files used for network connection?
Answer: tnsnames. ora and sqlnet. ora

 

Why check points must occur when the database switches logs? What is the significance of this checkpoint?
A: The execution of dbwr is triggered. dbwr writes all dirty queues related to this log to the data file to shorten the time required for instance recovery.

The checkpoinnt action is triggered in the following cases:

1. When a log group is switched

2. When the log_checkpoint_timeout, log_checkpoint_interval, fast_start_io_target, fast_start_mttr_target parameters are set

3. When altersystem switch logfile is run

4. When altersystem checkpoint is run

5. When altertablespace XXX begin backup and endbackup are run

6. When altertablespace and datafile offline are run;

 

---------------------------------------------------------------

Which of the following table space management methods are available.
A: dictionary management and local management. Local Management uses bitmap management extent to reduce competition between dictionaries and avoid fragmentation.

1 Local tablespace management has the following advantages over dictionary tablespace management:
1). Reduced recursive space management;
2) the system automatically manages the extents size or uses the unified extents size;
3). Reduced competition between data dictionaries;
4). No rollback information is generated;
5). the adjacent remaining space does not need to be merged;
6). Space fragments are reduced;
7). provides better management of temporary tablespace.

2. Differences and applicability between local indexes and global indexes.
A: For a local index, each table partition corresponds to an index partition. When the table partition changes, the index maintenance is automatically performed by Oracle. For a global index, you can select whether to partition, and the index partition does not correspond to the table partition. During partition maintenance, the global index is usually invalded and must be rebuilt after the operation is completed. Oracle9i provides the update global indexes statement, which allows you to re-create a global index while maintaining partitions.

3. A table A varchar2 (1), bnumber (1), and C char (2) have 100000 records. Create a B-tree index on field, WHO is the largest table and index? Why?
A: considering the number of bytes occupied by rowid, assume that char always occupies 2 bytes and compare rowid. In addition, table and index are managed differently in segment free block.

4. What are the differences between the data guard modes of Oracle9i.
Answer: There are three modes:
Maximize performance: This is the default protection mode of data guard. You do not need to receive feedback from standby before the transaction commit on primay. This mode may cause data loss when the primary fails, but standby has the least impact on the performance of the primary.
Maximize availability: under normal circumstances, the maximum available mode is the same as the maximum protection mode. When standby is unavailable, the maximum available mode is the maximum automatic performance mode, therefore, the Standby fault will not cause primay to be unavailable. If there is at least one standby available, even if the primarydown machine does not lose data.
Maximize protection: the highest protection mode. Transactions on primay must confirm that the redo has been passed to at least one standby before commit. If all standby is unavailable, primary will be suspended. This mode ensures zero data loss.

5. List 5 new 10 Gb features.
A:
1). Automatic shared memory (SGA) Management
2). Automatic Storage Management (ASM)
3). ADDM and Query Optimizer
4). flashbacktable)
5). Data Pump (expdp, impdp)

 

Logical and physical structures

 

 

 

 

 

 

Logical and physical structures