Summary of Oracle interview questions

1. What is the difference between delete and truncate?

1) Truncate is a DDL statement and DELETE is a DML statement.

2) Truncate is much faster than DELETE;

The reason is: When performing a DELETE operation, all table data is first COPYed to the rollback table space, and the amount of data takes different amounts of time. . TRUNCATE directly deletes data without entering the rollback table space.

3) Delete data can run Rollback for data rollback. Truncate is permanently deleted and cannot be rolled back.

4) The Truncate operation will not trigger the delete trigger on the table, but delete will trigger normally.

5) The Truncate statement cannot bring the where condition, which means that all data can only be deleted, while DELETE can bring the where condition to delete data.

6) The Truncate operation will reset the high water mark (High Water Mark) of the table, but the delete operation will not.

[Topic recommendation]:2020 Oracle interview questions summary (latest)

2, set operator

Union : Does not contain duplicate values, and is sorted in ascending order by the first column of the first query by default.

Union All: The complete union contains duplicate values. Not sorted.

Minus does not contain duplicate values ​​and is not sorted.

3. What are the three major paradigms of databases?

1) First normal form: atomic parts, requiring that the value of each column cannot be split any further.

2) Second normal form: A table only describes one entity (if there is redundant data in the column, it is not satisfied)

3) Third normal form: All columns and primary key values D.

4 What are the characteristics of transactions (ACID)?

1) Atomic: All operations in a transaction must be done or not done at all. The failure of any operation will cause the entire transaction to fail.

2) Consistency: The system state is the same after the transaction ends.

3) Isolated: Transactions executed concurrently cannot see each other’s intermediate state.

4) Durable: After the transaction is completed, even if a catastrophic failure occurs, the data can be reconstructed after the failure through log and synchronization backup.

5 What is the difference between Mysql database and Oracle database?

1. In terms of application, Mysql is a database for small and medium-sized applications. Generally used by individuals and small and medium-sized enterprises. Oracle is a large database and is generally used for enterprise applications of considerable scale.

2. Auto-growing data types: MySQL has auto-growing data types. Oracle does not have auto-growing data types. An auto-increment sequence needs to be created.

3, group by usage: In Mysql, group by can be used freely in the SELECT statement, but in ORACLE, if there is a group function in the query statement, then other columns must be processed by the group function or group by clause, otherwise an error will be reported.

4. In terms of guidance: In MySQL, you can use single quotes and double quotes to wrap strings. In Oracle, you can only use single quotes to wrap strings.

6 Oracle and SQL Server The difference in 2005?

Macroscopically:
1). The biggest difference is the platform. Oracle can run on different platforms, while SQL server can only run on the Windows platform. Due to the stability and stability of the Windows platform Security affects the stability and security of sql server
2). The scripting language used by oracle is PL-SQL, while the script used by sql server is T-SQL
Microscopically: From the data type, database Structure, etc. Answer

7. How to use Oracle's cursor?

1). Cursors in Oracle are divided into display cursors and implicit cursors
2). Display cursors are cursors defined with the cursor...is command, which can return query statements (select) Multiple records are processed; implicit cursors are automatically defined by PL/SQL when executing insert, delete, update, and query (select) statements that return a single record.
3). Explicit cursor operations: open the cursor, operate the cursor, close the cursor; PL/SQL implicitly opens the SQL cursor, processes the SQL statement internally, and then closes it

8 What is the difference between function and procedure in Oracle?

1). It can be understood that a function is a type of stored procedure
2). A function can have no parameters, but it must have a return value. A stored procedure can have no parameters and does not need a return value
3). The function return value does not have a return parameter mode. The stored procedure returns the value through the out parameter. If you need to return multiple parameters, it is recommended to use a stored procedure
4). Only functions can be called in SQL data manipulation statements. The stored procedure cannot be called

9 There are several ways to import and export Oracle, what are the differences?

1). Use oracle tool exp/imp
2). Use plsql related tools
Method 1. Import/export binary data, 2. plsql import/export It is a text file of sql statement

3) sqlloader

4) dblink

10. Explain the differences between cold backup and hot backup and their respective advantages?

Cold backup is a way of copying key files to another location when the database has been shut down normally.
Hot backup is a method of backing up data in archive mode while the database is running.

Advantages and disadvantages of cold standby:
1). It is a very fast backup method (just copy the files)
2). Easy to archive (just copy)
3). Easy to restore to a certain point in time (just copy the files back)
4). It can be combined with archiving methods to restore the "latest state" of the database.
5)． Low maintenance, high security.
Insufficient cold backup:
1)． When used alone, it can only provide recovery to a "point in time".
2). During the entire process of implementing backup, the database must be backed up and no other work can be performed. In other words, during the cold backup process, the database must be closed.
3)． If the disk space is limited, you can only copy to other external storage devices such as tapes, which will be very slow.
4)． Cannot be restored by table or by user.

Advantages and Disadvantages of Hot Standby
1). It can be backed up at the table space or data file level, and the backup time is short.
2). The database is still available during the backup.
3)． Achieving second-level recovery (recovery to a certain point in time).
4)． Can restore almost all database entities.
5)． Recovery is fast and in most cases occurs while the database is still working.
The shortcomings of hot backup are:
1). You can't make mistakes, otherwise the consequences will be serious.
2). If the hot backup is unsuccessful, the results cannot be used for point-in-time recovery.​
3)． Because it is difficult to maintain, special care must be taken and "ending in failure" is not allowed.

11 Explain the difference between data block, extent and segment?

data block Data block is the smallest logical unit of Oracle. Usually, what Oracle reads and writes from disk is the block
extent area, which is composed of several adjacent blocks
segment segment , is composed of a group of areas
tablespace tablespace, where data in the database is logically stored. A tablespace can contain multiple data files

12. Explain what deadlock is and how to solve it in Oracle deadlock?

In short, there is a lock but not unlocking. It may be that the lock is not committed or rolled back. If it is a table-level lock, the table cannot be operated. The client is in a waiting state. If it is a row-level lock, The lock cannot operate on the locked row

Solution:
1). Find the locked table
select b.owner,b.object_name ,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;

select b.username,b.sid,b.serial# ,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

2). Kill the session in the process

alter system kill session "sid,serial#";

13 Briefly describe the use of dml, ddl, and dcl in oracle

Dml data manipulation language, such as select, update, delete, insert
Ddl data definition language, such as create table, drop table, etc.
Dcl data control language, such as commit, rollback, grant, invoke, etc.

14 How to create an index, the principles of using indexes, what are the advantages and disadvantages

##Create a standard index: CREATE INDEX index name ON Table name (column name) TABLESPACE table space name;

Create unique index: CREATE unique INDEX index name ON table name (column name) TABLESPACE table space name;

Create a combined index: CREATE INDEX index name ON table name (column name 1, column name 2) TABLESPACE table space name;

Create a reverse key index: CREATE INDEX index name ON table name (column name) reverse TABLESPACE table space name;

Index usage principles: It is recommended to establish NOT NULL constraints for index fields
Tables that are often connected to other tables , indexes should be established on the connection fields;
Fields that often appear in the Where clause and are highly filterable, especially fields in large tables, should be indexed;
Keywords with high selectivity, Indexes should be established;
Keywords with low selectivity can be used, but when the value distribution of the data is very different, indexes can still be used to improve efficiency when the selective data is relatively small
The establishment of composite indexes requires careful analysis; try to consider Replace with single-field index:
A. Correctly select the first field in the composite index, which is generally more selective and on the fields commonly used in the where clause;
B. Several components of the composite index If the fields often appear in the Where clause in AND mode at the same time, a composite index can be established; otherwise, a single field index;
C. If the fields included in the composite index often appear alone in the Where clause, it will be decomposed into multiple single fields. Index;
D. If the compound index contains more than 3 fields, then carefully consider the necessity and consider reducing the number of compound fields;
E. If there is both a single field index and these fields Composite indexes can generally be deleted;
For tables with frequent DML, do not create too many indexes;
Do not use frequently modified columns as index columns;

Indexed Advantages and Disadvantages:
Somewhat: 1. Create a unique index to ensure the uniqueness of each row of data in the database table
2 . Greatly speed up the retrieval of data, which is also the main reason for creating indexes
3. Accelerate the connection between tables, especially in achieving referential integrity of data.
4. When using grouping and sorting clauses for data retrieval, the time for grouping and sorting in the query can also be significantly reduced.

Disadvantages: 1. Indexes are created on tables and cannot be created on views
2. Creating and maintaining indexes takes time. This kind of time Increases as the amount of data increases
3. Indexes need to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you want to build a clustered index, then the space required It will be bigger
4. When adding, deleting and modifying data in the table, the index must be dynamically maintained, which reduces the data maintenance speed

15 There are several types of Oracle locks, and what are their definitions;

1. Row Shared Lock (ROW SHARE)
2. Row Exclusive Lock (ROW EXCLUSIVE)
3. Shared lock (SHARE)
4. Shared row exclusive lock (SHARE ROW EXCLUSIVE)
5. Exclusive lock (EXCLUSIVE)
How to use ：
SELECT * FROM order_master WHERE vencode="V002"
FOR UPDATE WAIT 5;
LOCK TABLE order_master IN SHARE MODE;
LOCK TABLE itemfile IN EXCLUSIVE MODE NOWAIT;
ORACLE locks are specifically divided into the following categories:
1. According to users and systems, they can be divided into automatic locks and display locks
Automatic locks : When a database operation is performed, by default, the system automatically obtains all necessary locks for this database operation.
Display lock: In some cases, the user needs to display the data used to lock the database operation so that the database operation can be performed better. The display lock is set by the user for the database object.
2. According to the lock level, it can be divided into shared locks and exclusive locks
Shared locks: Shared locks enable a transaction to share access to specific database resources— —Another transaction can also access this resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but poor transaction design can easily cause deadlocks or loss of data updates.
Exclusive lock: After a transaction sets an exclusive lock, the transaction obtains this resource alone. Another transaction cannot obtain a shared lock or exclusive lock on the same object before this transaction is committed.
3. According to operation, it can be divided into DML lock and DDL lock
DML lock can be divided into row lock, table lock and deadlock
Row lock: When a transaction performs database insert, update, and delete operations, the transaction automatically obtains an exclusive lock on the operation row in the operation table.
Table-level lock: When a transaction obtains a row lock, the transaction will also automatically obtain the table lock (shared lock) of the row to prevent other transactions from making DDL statements that affect the update of record rows. A transaction can also obtain a shared lock or an exclusive lock during the process. Only when the transaction explicitly defines an exclusive lock using the LOCK TABLE statement, the transaction will obtain an exclusive lock on the table. It can also be displayed using the LOCK TABLE statement. Define a table-level shared lock (please refer to the relevant documentation for specific usage of LOCK TABLE).
Deadlock: When two transactions require a set of conflicting locks and the transaction cannot continue, a deadlock occurs.
For example, transaction 1 has an exclusive lock in record #3 of table A, and is waiting for transaction 2 to release the exclusive lock in record #4 of table A, while transaction 2 has a row in record #4 of table A. It locks and waits for transaction; 1. Record #3 in table A for the release of the exclusive lock. Transaction 1 and transaction 2 wait for each other, thus causing a deadlock. Deadlocks generally occur due to poor transaction design. Deadlock can only be solved using SQL: alter system kill session "sid,serial#"; or use the command to kill the process of the relevant operating system, such as kill -9 sid under UNIX, or use other tools to kill the deadlock process.
DDL locks can be divided into: exclusive DDL locks, shared DDL locks, analysis locks
Exclusive DDL locks: Create, modify, and delete a database object The DDL statement obtains an exclusive lock on the operation object. For example, when using the alter table statement, in order to maintain the completeness, consistency, and legality of the data, the transaction obtains an exclusive DDL lock.
Shared DDL locks: DDL statements that need to establish interdependencies between database objects usually require shared DDL locks.
If you create a package, the procedures and functions in the package reference different database tables. When the package is compiled, the transaction obtains the shared DDL lock of the reference table.
Analysis lock: ORACLE uses a shared pool to store analyzed and optimized SQL statements and PL/SQL programs, making applications that run the same statements faster. An object cached in the shared pool acquires an analysis lock on the database object it references. Analytical locks are a unique type of DDL lock that ORACLE uses to track the dependencies between shared pool objects and the database objects they reference. When a transaction modifies or deletes a database object that holds an analysis lock in the shared pool, ORACLE invalidates the object in the shared pool. The next time this SQL/PLSQL statement is referenced, ORACLE re-analyzes and compiles this statement.
4. Internal latch
Internal latch: This is a special lock in ORACLE, used for sequential access to internal system structures. When a transaction needs to write information to the buffer, in order to use this memory area, ORACLE must first obtain the latch of this memory area before it can write information to this memory area.

Related learning recommendations: oracle database learning tutorial

The above is the detailed content of Summary of Oracle interview questions. For more information, please follow other related articles on the PHP Chinese website!