"Interview" Oracle FAQ "01" ____oracle

"Summary catalogue of common interview questions >>>" 001 Database Paging Query

SELECT * FROM (
Select T.*,rownum from table1 t where condition)
where Rownum> (pangeNow-1) *pagesize and rownum<= (pagenow) *pagesize 002 Transaction Concepts

Answer: A transaction is a mechanism that ensures that multiple SQL statements are handled as a single unit of work. Transactions have the following effects:
　　consistency : Simultaneous queries and updates do not conflict with each other, and other users do not see data that has changed but has not yet been committed.
　　 recoverability: Once the system fails, the database automatically recovers the unfinished transaction completely. 003 The role of triggers, when to use triggers, to create the steps of a trigger, whether the trigger can have a commit, why.

Answer: A trigger is a procedure that can be started by an event and that exists in the database server. His role: to achieve more complex integrity requirements by implementing complex constraints that cannot be accomplished by general constraints. There is no strict qualification for the use of triggers, so long as the user wants to complete the database integrity without human involvement in the general definition of constraints that cannot be completed, then triggers can be used.
Since triggers are primarily used to guarantee the integrity of the database, to create a trigger, you first need to be clear about which one the trigger should belong (Dml,instead Of,system) because they each have a purpose, and secondly, to determine what data is designed after the trigger is triggered. Commit cannot be used in triggers. 004 difference between relational database system and file database system where is the relational database system generally applicable to those aspects?

Answer: The difference between a relational database system file system is:
　　first , the overall data of the relational database is structured, and the relational data model is used to describe it, which is fundamentally different from the file system. (The data model includes: data structure, operation and integrity constraints)
　　secondly , the relational database system has high sharing and low redundancy, which can be applied to the whole system, while file system has the limitation of application scope and is not easy to expand.
　　third , the relational database system uses the two-level mapping mechanism to ensure the high independence of the data, thus making the program and data have a high degree of independence. This is the file system can not be achieved, it can only be targeted at a specific application. (Level Two mapping: External mode/pattern mapping to ensure logical independence and internal mode/pattern mapping to ensure physical independence.) External mode: User mode, which is the description of the logical structure feature of the local data of the database user. Pattern: A description of the logical structure characteristics of the data of the whole database. Internal mode: The description of the final physical storage structure of the data. ）
　　Fourth , the relational database system is managed by a unified DBMS to provide data such as security protection, concurrency control, integrity checking, and database recovery services. 005 The concept of triggers, the concept of stored procedures.

Answer: Trigger: Is stored in the database process, when the table is modified (add, delete, change) when it is implicitly excited. Stored procedure: is a collection of database language SQL, also stored in a database, but he is started by other applications or can also run directly. 006 What is transactional consistency? Select a familiar database to implement a transaction, such as credit card withdrawals. Answer:

Transactional Consistency : Is the embodiment of transactional atomicity, the database operation of the transaction either succeeds or fails without a third. A transaction cannot affect the consistency status of the database data, whether it is committed successfully or not.
Transaction : A user-defined sequence of database operations, either complete successfully or all not, is an indivisible whole. The SQL statement that defines the transaction is: BEGIN transaction,commit,rollback.
Transactional atomicity : The database operations that the transaction contains are either done or do not.
The isolation of a transaction : The transaction's operation on the data cannot be affected by other transactions.
The persistence of a transaction : That is, the impact of a transaction on the data is permanent.
for credit card withdrawals this is to ensure that the ' extract to cash ' and ' Card account balances ' changes are successful or fail at the same time. 　　　　　　
BEGIN TRANSACTION
Reads an account balance BALANCE
Balance=balance-amount transfer amount
IF (balance<0)
THEN ROLLBACK
ELSE BEGIN
Writes the new balance of a to the
Read account balance BALANCEB
Balanceb=balanceb+amount transfer Amount
Write a New Balance of B back to
COMMIT
End IF
End 007 to implement the index? The principle of the index? The type of index.

Answer: There are two ways to implement an index : to create a specific index on some fields of a table, such as a table name on the Oracle:create index index (a field name), or a PRIMARY KEY constraint or a unique constraint for a field when the table is created, and the system will automatically index it.
　　the principle of indexing : based on the indexed field to establish the index table, the value of the field and the corresponding physical address of the record, so that the search in accordance with the value of the field search the index table to the physical address direct access to records.
Although the introduction of index has improved the speed of query, but it occupies a certain amount of system storage capacity and system processing time, it is necessary to carry out specific analysis according to the actual situation.
The types of (Oracle) indexes are:B-Tree index , bitmap index , function index , and so on. What is the concept of 008 view? When to apply?

Answer: View is a multi-angle of table-level data perspective, applicable to query security, flexibility has certain requirements of the environment. 009 access to a database using stored procedures is more advantageous than accessing directly with SQL statements.

Answer: Stored procedures are precompiled, execute without compiling, execute faster; Stored procedures encapsulate a batch of SQL statements to facilitate the maintenance of data integrity and consistency; You can implement code reuse. 010 The concept of rollback in Oracle. What is the effect of the rollback segment.

　　rollback is the state of the database data before the transaction commits the database data to the transaction modification.
The rollback segment is to provide a basis for rollback, recording data before the transaction operations database or operations that correspond to previous operations, depending on the previous operation. For example, if the previous transaction operation is update then the rollback segment stores the previous data for update, and if the transaction is a delete operation, then the corresponding insert operation statement is stored, and the corresponding delete operation is recorded if the transaction operation is insert. 011 describes Oracle's architecture.

　　logical Architecture: blocks, extents, segments, table spaces
　　Physical Architecture: Table space, three large files
　　Software Architecture: SGA, background process. 012 Briefly describe the components of the SGA in Oracle.

A: The system global area includes: shared pool , redo log buffer , data cache area , large pool ,Java pool . 013 Briefly describe how many steps Oracle has to start and close.

Start: Start the instance, load the database data, open the database. OFF: Close the database, unload the database data, and close the instance. 014 What types of files are available in an Oracle database.

Data files, control files, log files, parameter file 015 in Oracle, the table space information you create is placed where.

stored in the data dictionary, the contents of the data dictionary correspond to the system table space. What are the basic data types for Oracle 016?

Char () stores a fixed-length character, it can be defined without specifying a length for him but an error occurs if you insert a value into it; varchar2 () store variable-length character definitions must specify length, date store time date, number () numeric type, including integer, floating-point, etc. clob () Large-capacity string; blob () Several physical files of the binary object 017 database.

1) Data files
2) Control file
3 log file 018 control files contain what information.

Control files contain instance information (instance name creation time, etc.), data files and log file information, System change codes (SCN) for System Run-time records, checkpoint information, and current status information for the archive. When the database loads the database, it first reads the control file to obtain the physical structure information related to the database before it can properly load the data file and log file and open the database. the difference between 019 truncate and delete.

1. Truncate are very fast on various tables, large or small. If a rollback command delete is revoked, the truncate is not revoked.
2. Truncate is a DDL language and delete is a DML statement, and, like all other DDL languages, he will be implicitly committed and cannot use the rollback command against truncate.
3. Truncate will reset the high level line and all indexes. When you fully browse the entire table and index, the table after the truncate operation is much faster than the table after the delete operation.
4. Truncate cannot trigger the trigger, and delete triggers the trigger.
5. You cannot grant anyone permission to empty another's table.
6. When the table is emptied, the table and table indexes are reset to the initial size, and delete is not.
7. Cannot empty the parent table. 020 How the table space is expanded. and write with the statement.

Two ways to extend:
1. Add the data file alter tablespace tablespace_name add datafile ' XXMB
2. Extended data file size ALTER DATABASE datafile ' resize newmb 021 table space management mode. Which method is now recommended for use.

1. Dictionary Management way Extent management dictionary; default method
2. Local management style extent management Local[autoallocate/uniform xxmb]< recommendation >; 022 application of partitioned tables.

1. One partition table has one or more partitions, each partition by using a range partition, a hash partition, or a combination of partition's rows
2. Each partition in the partition table is a segment, and can be located in different table spaces
3. It is very useful for large table partitions that can query or manipulate several processes at the same time 023 talk about the use and principle of indexing.

An index is a list of keywords for several data rows that, when queried for data, can be quickly positioned through the keywords in the index to the block of data to which the record is to be accessed, greatly reducing the number of I/O to the data block, and thus significantly improving performance. 024 The application of stored procedures, how to both input and output.

Create procedure pro_name (xxxx in/out type; yyyy in/out/inout type;)
Is/as zzzz type;
Begin
Sqlpro;
Exception exceptionxxxxx;
Commit
End 025 What classes of exceptions are in Oracle.

There are three kinds of exceptions in Oracle. Predefined exceptions non-predefined exception user-defined exceptions the second, predefined exception is associated with a specific Oracle error. 026 Oracle's optimization strategy (also applies to MySQL)

Optimization strategies generally include: memory optimization, operating system optimization, data storage optimization, network optimization and other methods
Specific to different databases involves adjusting different database configuration files, different operating system parameters, network parameters, and so on, different databases. 027 use of index query can improve the performance of the query. Why.

In general, querying data through an index is faster than full table scans. But we must also pay attention to its cost. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that the insert,delete,update of each record will pay 4, 5 more disk I/O. Because indexes require additional storage and processing, unnecessary indexing slows down query response times. Using an indexed query does not necessarily improve query performance, index range queries ( The INDEX RANGE SCAN applies to two scenarios:
Based on a range of retrieval, the general query returns the result set less than 30% of the records in the table should be adopted;
Based on the retrieval of the non-uniqueness index, the index is to improve the performance of the query, if the index in the query does not improve performance, can only be said to be using the wrong index, or the occasion of different 028 binding variable is what. What are the advantages and disadvantages of binding variables.

　　A binding variable is the use of a variable in an SQL statement, changing the value of a variable to change the execution result of the SQL statement.
　　advantages : Using binding variables can reduce the parsing of SQL statements and reduce the resources that the database engine consumes on SQL statement resolution. Improved programming efficiency and reliability. Reducing the number of accesses to the database can actually reduce Oracle's workload.
　　Disadvantages : Often need to use dynamic SQL, because of the different parameters, may be the implementation of SQL different efficiency;
The binding variable is relative to the text variable, the so-called text variable refers to the SQL directly write query conditions, such SQL in different conditions need to be repeatedly resolved, the binding variable refers to the use of variables to replace the direct writing conditions, query bind value at run time passed, and then binding execution.
　　The Advantage is to reduce hard parsing, reduce CPU contention, save Shared_pool
　　The disadvantage is that histogram,sql optimization is difficult to use 029 simple to describe the relationship between Tablespace/segment/extent/block

tablespace : A database is divided into one or more logical units, the logical unit becomes a tablespace, and each tablespace may contain one or more Segment;
Segments : segment refers to the space allocated for a particular logical storage structure in tablespace. Each segment is made up of one or more extent. Includes data segments, index segments, rollback segments, and temporary segments.
Extents : A extent consists of a series of consecutive Oracle blocks. Oracle allocates space for segment through extent.
Data Blocks : The smallest I/O storage unit of the Oracle database, one Data block corresponding to one or more operating system blocks assigned to Data file. When
table is created, a data segment is created by default, and each data segment contains the number of extents specified by min extents, each extent a certain number of blocks according to the stored parameters of the table space What is the function of the strong>030 rollback segment.

The

Rollback segment is used to hold images of data modifications that are used to generate read-consistent database information, for database recovery, and for rollback. Only one rollback segment can be used for a transaction.
Transaction rollback : When the transaction modifies the data in the table, the value before the data modification (that is, the front image) is stored in the rollback segment, and when the user rolls back the transaction (ROLLBACK), Oracle will use the pre-data image in the rollback segment to restore the modified data to its original value.
Transaction Recovery : When a transaction is being processed, the routine fails, the rollback segment information is saved in the Undo table space, and Oracle will use the rollback to recover the uncommitted data the next time the database is opened.
Read Consistency : When a session is modifying data, other sessions will not see the uncommitted changes to the session. When a statement is executing, the statement does not see uncommitted modifications (statement-level read consistency) from the beginning of the statement when Oracle executes a SELECT statement, Oracle follows the current system change number (the systems changes NUMBER-SCN) To ensure that any uncommitted changes that were prior to the current SCN are not handled by the statement. As you can imagine: When a long-running query is executing, if another session changes a block of data that the query is querying, Oracle constructs a read consistency view using the data front image of the rollback segment What is the role of the 031 log?

　　log files record all modifications to the database data, primarily to protect the database against failures, and to recover data. Its characteristics are as follows:
1. Each database contains at least two log file groups. Each log file group contains at least two log file members.
2. The log file group writes in a circular fashion.
3. Each log file member corresponds to a physical file.
Record database transactions to maximize data consistency and security
　　Redo log Files : Contains changes to the database records, so that in the event of a failure to enable data recovery, a database requires at least two redo log files
　　Archive log files : An offline copy of the redo log files that may be necessary for recovery from media failure. 031 SGA Mainly has those parts, the main function is what

　　system Global Area (SGA): A set of shared buffer storage areas allocated by Oracle for instance to store database data and control information for the management and operation of database data. The SGA mainly includes:
1. Shared pool : Data used to store recently executed SQL statements and recently used data dictionaries.
2. Data buffer (database buffer cache): used to store data that was recently read and written from a data file.
3. Redo Log buffers (redo log buffer): Used to record the operation of a service or background process on a database. In addition, there are two optional memory structures in the SGA:
4. java pool: used to store Java code.
5. Large pool: Used to store large memory structures that are not directly related to SQL. Backup, restore use. what are the main functions of the 033 Oracle system process?

Data Write process (DBWR) : Responsible for writing changed data from the database buffer cache to the data file
log write process (LGWR) : Writes changes in redo log buffers to the online redo log file
system monitoring (Smon) : Checking the consistency of the database if necessary, you will also start the recovery of the database when the database is opened
process monitoring (Pmon) : Responsible for cleaning up resources when an Oracle process fails
checkpoint process (CKPT) : is responsible for updating database state information in control files and data files whenever changes in the buffer cache are permanently recorded in the database.
Archive Process (ARCH) : Back up or archive a full log group for each log switch
recovery process (RECO) : Ensure consistency of distributed transactions, in distributed transactions, Either commit at the same time or rollback the 034 backup How to categorize

Logical Backup : Exp/imp logical backup of a specified table physical backup:
hot backup : Alter tablespace begin/end backup;
Cold backup : Offline backup (database shutdown)
rman backup
Full backup/incremental backup (tired Product/variance) Physical backup
Physical backup is the primary backup method. Used to ensure that the database is recovered with minimal database loss or no data loss. The
Cold physical
Cold physical Backup provides the simplest and most straightforward way to protect your database from physical damage. It is recommended to use in the following situations.
for a database that already has a large maximum amount of data, the database can be closed at night, and a cold physical backup is applied at this time. For upgrades to the database server, such as replacing the hard drive, you need to back up the database information and restore the data information on the new hard disk and recommend a cold physical backup.
Thermal Physical
primarily refers to a backup process that occurs when the database is open and users can use it. The case for hot-physical backups is that the
requires uninterrupted work because of the nature of the database, so only hot physical backups can be used at this time. Because the time required for backup is too long, and the database can only be closed for a short time. The
logical Backup (EXP/IMP)
Logical backup is used to implement recovery of database objects. But not based on a point-in-time, fully recoverable backup strategy. Only as a supplement to online and offline backups.
Full Logical backup
A full logical backup is a format file that exports the entire database to a database that can be ported between different database versions, operating systems, and hardware platforms.
Specify logical backups of tables
by using the Backup tool, you can back up the specified database tables, which avoids the time and financial waste of a full logical backup. 035 What is things?

A

transaction is a series of operations performed as a logical unit, and a logical work unit must have four properties called the ACID (atomicity, consistency, isolation, and durability) property. This is the only way to become a transaction:
atomicity : A transaction must be an atomic unit of work, or all of its data modifications, or none of the executions.
Consistency : When a transaction is complete, all data must be kept in the same state. In the related database, all rules must be applied to the modification of the transaction to preserve the integrity of all data. When a transaction ends, all internal data structures, such as a B-tree index or a two-way list, must be correct.
Isolation : Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state that the data is in when the transaction views the data, either when another concurrent transaction modifies its previous state, or when another transaction modifies its state, the transaction does not view the data in the middle state. This is called serializable because it can reload the starting data and replay a series of transactions so that the state of the data at the end is the same as that of the original transaction.
Persistence : After a transaction completes, its effect on the system is permanent. This modification is maintained even if a system failure occurs. 036 Database deadlock prevention and cancellation

　　condition of deadlock occurrence :
1. Resources cannot be shared and need to be used only by one process or thread
2. Request and maintain, the resources that have been locked are not released by themselves
3, do not deprive, self-sufficiency of the requested resources can not be deprived of others
4. Cycle Waiting
If you want to prevent deadlocks, destroy one of the above four conditions.
　　The way to prevent deadlocks is to avoid situations where deadlock conditions are met, and users need to follow the following guidelines.
(1) Try to avoid concurrent execution of statements involving the modification of data.
(2) Each transaction is required to lock all data to be used at once, otherwise it will not be executed.
(3) A sequence of closures is specified beforehand, and all transactions must be blocked by the data in this order. The order in which the objects are updated in the transaction should be kept as consistent as possible within a different process.
(4) The execution time of each transaction must not be too long, and it is possible to consider splitting the transaction into several small transactions if the business allows. "For example, to spread complex multiple-table queries into multiple single table queries"
(5) Data storage space discretization method. The data storage space discretization method is to take various means to scatter the data logically in one table to some discrete space, in order to improve the access performance of the table. Mainly by decomposing the large table by rows or columns into several small tables, or by different user groups to decompose two ways to achieve. This approach is similar to dispersing "data hotspots", but it is true that deadlocks do not occur too often if the data is not too often accessed.
(6) or similar (1), for example, with an UPDATE statement that modifies hundreds of records, we can modify each of the 10 update statements, or simply record an UPDATE statement for each one.
(7) Separate the frequently updated database and query database to 037 when the control files in Oracle are read.

When the Oracle server starts, the individual files that start the instance and then read the database are also included in the control file, of course. That is, read at the second step of the database server startup. 038 Oracle indexes are grouped into categories that tell the concept of a unique index and a bitmap index.

Oracle Index has B-Tree index, bitmap index, function index, cluster index , etc.
　　A unique index is also one of the B-tree indexes, which requires that the value of the field being indexed cannot be duplicated. Created using the B-tree algorithm when it was created.
　　Rather than using a unique index to store (indexed field values, record rowID) to create an index segment, a bitmap index creates a bitmap for each unique field value, using a bit in the bitmap to correspond to the rowid of a record. The bits to the ROWID are mapped to. 039 describe the relationship between Tablespace and DataFile

A table space can contain one or more data files. Table space expands the table space by increasing or extending the data file, and the size of the table space is the size of the data file that makes up the table space. A datafile can only belong to a table space; A tablespace can have one or more datafile, each datafile can only be within one tablespace, and the data in the table is distributed through the tablespace in datafile by the hash algorithm, Tablespace is a logical concept, and datafile physically stores various objects of the database.