The]Oracle interview questions (fundamental)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The differences between the 1 Oracle with SQL Server 2005?
The macro:
1)The platform. The biggest difference is that, Oracle can run on different platforms, SQL server can only run on the platform of windows, the stability and security of the windows platform affected the stability and safety of SQL Server
2)Oracle scripting language for PL-SQL, and the SQL server script for T-SQL
Micro: from data type, answer database structure etc.

2 how to use the Oracle cursor?
1)Oracle cursor is divided into display and implicit cursor cursor
2)The is command. Display cursor is defined using the cursor... The cursor, it can query (select) multiple records returned for processing; implicit cursor is in the implementation of insertion (insert), delete (delete), modified (update) and returns a single record query (select) by PL/SQL automatic definition statement the.
3). explicit cursor operation: open the cursor operation, close the cursor, the cursor; PL/SQL implicitly open SQL, and in its internal processing a SQL statement, then shut it down

The difference between function and procedure in Oracle 3?
1)Can understand the function is a stored procedure
2)No parameters. The function can be, but need to return a value, a stored procedure can have no parameters, return values are not needed
3)The return function returns the value of the parameters. No mode returns, the stored procedure by out parameters and return values, you need to return multiple parameters is suggested using the stored procedure
4)In the SQL data manipulation statements can only call a function instead of calling a stored procedure

Import and export of 4 Oracle in several ways, what is the difference between?
1)Using the Oracle tool exp./imp
2)The use of PLSQL tools.
Methods 1 import / export is binary data, 2.plsql import / export is a SQL statement text file
What kinds of documents in Oracle 5?
Data file (general suffix.Dbf or.Ora), the log file (.Log extension), the control file (suffix.Ctl)

6 how to optimize Oracle database, there are several ways?
Personal understanding, factors of database performance is most important to IO, because the operating memory is fast, but the disk read and write speed is very slow, the optimization problem of the database is most important to reduce disk IO, personal understanding, should optimize is divided into physical and logical, physical refers to the optimization of Oracle of the product itself, logic optimization is to optimize the application level
Some principles of physical optimization:
1)At the operating environment of Oracle (network, hardware etc.)
2)Use the appropriate optimizer.
3)The rational allocation of the instance of Oracle parameters.
4). establish a suitable index (reduced IO)
5)The index data and table data separately in different table space (reduced IO conflict)
6)Established the partition, the data were stored in a different partition (in a space for time, reduce IO)
Logic optimization:
1)Table. Be logical partitioning, such as China mobile user table, can according to the mobile phone number is divided into 10 tables, this will have a certain effect on Performance
2)The Sql statement uses placeholder. Statement, and SQL statement when must be prepared in accordance with the provisions (such as all uppercase, lowercase etc.) Oracle parse the statement will be placed in the shared pool
Such as: select * from Emp where name=? This statement will only in the shared pool there is one, and if it is a string of words, then according to the different names exist different statements, so a placeholder for better efficiency
3). database is not only a place to store the data, is also a programming places, some time-consuming operation, the stored procedure in a small number of users, thus staggered system using the peak time, improve database performance
4). try not to use * *, such as select from Emp, because of the need to translate into concrete column is to check the data dictionary, time-consuming
5)Select a valid table name.
For multi table join query, the optimizer may Oracle not optimized to this extent, Oracle multi table query is based on the FROM words from right to left of the data, it is best to the table on the right (i.e., base tables) choose less data tables, such ordering more quickly, if you have a link table (more on many middle table), then the link table to the right as the base table, by default Oracle will automatically optimize, but if the configuration of the circumstances, may be automatic optimization does not, so it is best to write SQL this way
6)Where clause rules.
Oracle Where words from right to left the table, the connection between the written in other conditions before, can filter out much of the data, in the end of the where, another! = sign compare columns will not use index, column after calculation (for example, does not use the index (uppercase) need to establish function), is null, is not null, the optimizer will not use index
7)Use Not Exits instead of In. Exits Not in
8)Rational use of affairs., reasonable set transaction isolation
The database operation is consumption database resources, as far as possible the use of batch processing, in order to reduce the number of transactions

A string of 7 Oracle using any symbolic links?
Oracle in the connection string such as the use of || symbol ‘abc’ || ‘d’
8 Oracle partition is how to optimize the database?
Oracle partition can be divided into: List partitioning, range partitioning hash partitioning composite partitioning,,.
1). enhancing availability: if one partition of the table due to system failure can not be used, the rest of the partition table can still be used.,
2). reduce closing time: if the system fault affects only a part of the partition table, then only the part of partition in need of repair, less time than the table repair flower,
3)If you need to. Easy maintenance: table building, independent management of each public area much easier than managing a single large table,
4)I/O: you can put different. Balanced partition table to a different disk to balance I/O performance improvement,
5)To improve the performance of large scale.: query, add, modify the operation can be decomposed into different partitions of the table to the parallel implementation, can run faster
6). partition is transparent to the user, the user can not feel the presence of the partitions.

9 how is Oracle paging?
Oracle uses rownum to carry on the paging, this is the best method for paging efficiency, hibernate also uses rownum to carry on the ORALCE page
select * from
( select rownum r,a from tabName where rownum <= 20 )
where r > 10
10 how the Oralce store file, which can store files?
Oracle can store CLOB, nclob, blob, bfile
Variable length character data type Clob, is also mentioned in other database text data type
Nclob variable character type data, but its storage is character data Unicode character set
Blob variable length binary data
Variable binary data stored in Bfile database.
The index of the column using the 11 Oracle, the column where query, sorting, grouping, using aggregate functions, which use the index?
Will use the index, notably composite index (index established in column A and column B as) may have different situation
How to realize the 12 database backup once every 30 minutes?
Through the timing task calls script derived database operating system

Performance comparison of where 13 Oracle in the query and ranking?
Order by used to index the condition is very strict, only meet the following conditions may only use index,
1)Order column in by must contain the same index and the index sequence and sort order
2)Cannot have a null column value.
So the sort of performance often is not high, so try to avoid order by

The 14 explanation is cold and hot backups at different points and their respective advantages?
Cold backup occurs in the database have been shut down properly, copy the key file to another suggestion position
Hot backup in the database operation, by using the method of backup data archiving mode
The advantages and disadvantages of cold standby.:
1).The backup method is very fast (just copy the file)
2).Easy (simple file copy)
3).Easy to restore to a particular point in time (only the file and then copy back)
4).Can be combined and archiving methods, as the database “ the new state ” recovery.
5).Low maintenance, high security.
Cold backup deficiencies:
1).When used alone, can only be provided to “ a point in time recovery of ”.
2).In the whole process of the implementation of the backup, the database must be the backup and can not do other work. That is to say, in the process of cold backup, the database must be closed.
3).If disk space is limited, can only be copied to tape or other external storage device, the speed will be very slow.
4).Not according to the table or according to user's recovery.

The advantages and disadvantages of hot standby
1).In the table space or data file level backup, backup time is short.
2).Using the database can backup.
3).Can be second recovery (recovery to a certain time point).
4).Can be used for recovery of almost all database entity.
5).Recovery is rapid, in most cases recovered in the database is still at work.
Hot backup is insufficient:
1).Make no mistake, otherwise the consequence is serious.
2).If the backup is not successful, the results can not be used for recovery time point.
3).Because of the difficulty of maintenance, so be very careful, do not allow the “ ended in failure.”.

The 15 interpretation of data block, the difference between extent and segment?
Data block data block is the smallest unit, the Oracle logic, usually Oracle read from disk is block
Extent District, is composed of several adjacent block
Segment segment, there is a group composed of district
The tablespace tablespace, the logic of the data stored in the database in place, a tablespace can contain multiple data files
16 comparison of truncate and delete commands ?
1)Truncate and delete data can be deleted from the truncate entity, the operation is not recorded in the rollback log, so the operation speed, But at the same time, the data could not be restored
2)The Delete operation does not make the table space. The space
3)Truncate cannot be deleted. To view
4)Truncate is a data definition language (DDL), and delete is the data manipulation language(DML)
17 explain what the deadlock, how to solve the deadlock in Oracle?
In a nutshell is added to lock and unlock the lock is not used, can not commit or rollback transaction, if a table level lock is not operating table, the client is in the state, if the row level lock is not operating locking rows
The solution:
1)Find out the lock 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 process in the session.
alter system kill session "sid,serial#";
The use of 18 Oracle DML, DDL, DCL
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.
19 about Oracle is often used to function
The length of Length, lower, upper lowercase uppercase, the conversion of to_date date, the conversion of to_char character
Ltrim to the left, to the right space space rtrim, substr string, add_month increases the transition or lose month, to_number digital
20 how to create a stored procedure, the cursor to use in the storage process, what are the benefits of?
Attached: the general format of a stored procedure, the reference problem using the cursor
1 use the cursor can perform multiple unrelated operations. If you want when producing a result set, the centralized data for a variety of unrelated data operation
2 use the cursor can provide readable scripts
3 use the cursor can establish a command string, use the cursor can transmit table name, or put the variable parameter passed to, in order to establish a command string can be performed.
But personally think that the cursor operation efficiency is not too high, and be particularly careful when using, will promptly closed after use
The advantages and disadvantages of the stored procedure:
1 storage process enhances the SQL language function and flexibility. Stored procedures can be used in flow control statements prepared, have very strong flexibility, can complete the complex judgment and complex operation.
2 can ensure the data security and integrity.
3. The stored procedure can make no permissions indirectly access under the control of the database, so as to ensure the security of data.
     The stored procedure can make the action together, so as to maintain database integrity.
3 then run the stored procedure, the database has been analyzed on the grammar and syntax, and gives the optimized execution plan. The compiler has good performance of the process can greatly improve the SQL statement. Because most of the work to execute the SQL statement has been completed, so the stored procedure can be performed with great speed.
4 can reduce network traffic, not through the network to send a lot of SQL statements to the database server to
5 to reflect the enterprise rules calculation program into the database server, so that the centralized control
      When changing the stored procedures in the server business rules change, do not need to modify any application. Characteristics of business rule is to want to often change, if the enterprise reflects the rules of the program into the application, when the business rules change, you need to modify the application workload is very large (modified, issuance and install the application). If the enterprise reflects the rules of operation in the storage process, when the business rules change, just change the stored procedure can be, the application without any changes.
The 1 poor portability
2 server end many resources, caused great pressure on the server
3 readability and maintainability is not good

Create [or replace] Name (parameter procedure process …)as
vs_ym_sn_end CHAR(6);    - period end month
CURSOR cur_1 IS cursor (simply means that one can traverse the result set)
SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn
WHERE ym >= vs_ym_sn_beg
AND ym <= vs_ym_sn_end
GROUP BY area_code,CMCODE;
With the input parameters to the variable initialization, use the Oralce SUBSTR TO_CHAR ADD_MONTHS TO_DATE is commonly used functions.
vs_ym_beg := SUBSTR(is_ym,1,6);
vs_ym_end := SUBSTR(is_ym,7,6);
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,"yyyymm"), -12),"yyyymm");
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,"yyyymm"), -12),"yyyymm");
- first delete table specific conditions data.
DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;
-- then print out rows number effect the built-in put_line method of the DBMS_OUTPUT object, which uses a system variable SQL%rowcount
DBMS_OUTPUT.put_line("Del last month record = "||SQL%rowcount||"");

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
WHERE ym >= vs_ym_beg
AND ym <= vs_ym_end
GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line("Ins month record = "||SQL%rowcount||".");
Traversing the cursor after treatment -- updates to the table. There are several ways to traverse the cursor, use the for statement which is a more intuitive.
FOR rec IN cur_1 LOOP
UPDATE xxxxxxxxxxx_T
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
  WHERE area_code = rec.area_code
  AND ym = is_ym;
-- error handling. OTHERS said that in addition to any specified error. SQLERRM is the built-in variable preservation of detailed information on the current error.
     vs_msg := "ERROR IN xxxxxxxxxxx_p("||is_ym||"):"||SUBSTR(SQLERRM,1,500);
The current record -- error log table.
  INSERT INTO LOG_INFO(proc_name,error_info,op_date)
21 how to create an index, the index using the principle, what are the advantages and disadvantages
Create index:
The CREATE INDEX index name ON table name (Lie Ming) TABLESPACE tablespace name;
Create unique index:
CREATE unique INDEX index ON table name (Lie Ming) TABLESPACE tablespace name;
Create index:
The CREATE INDEX index name ON table name (column 1, column 2) TABLESPACE tablespace name;
Create a reverse key index:
The CREATE INDEX index name ON table name (Lie Ming) reverse TABLESPACE tablespace name;
Using the principle of index:
Index field recommended the establishment of NOT NULL constraints
Regular connection table to other tables, indexes on the join columns should be established,
Often appear in the columns in the Where clause and filtering is very strong, especially field large tables, should establish the index,
High selectivity of keywords, should establish the index,
Selective low key, but the value of the data distribution are very different, still can improve the utilization efficiency of index selectivity data is small
Establishment of a composite index is the need for careful analysis; try to consider instead of single field index:
A, Select the first field in a composite index is generally correct, good selectivity and in the where clause are commonly used in field,
B, Several fields of composite index is often at the same time, by way of AND appears in the Where clause can establish a composite index; otherwise the single field index,
C, If the field contains in a composite index is often appear in the Where clause, it is decomposed into multiple single field index,
D, If the field of composite index contains more than 3, so careful consideration of its necessity, consider reducing the composite field,
E, If you have a single field index, and the composite index of these fields, the general can remove the composite index,
Frequent DML table, don't build too many index,
Don't put that frequently modified columns as the indexed column,
The advantages and disadvantages of index:
1 create unique index, ensure that each row of data in a database table only.
2 greatly accelerate the speed of data retrieval, the main reason for this is to create an index
The 3 accelerometer and the connection between tables, especially in referential integrity data of particular significance.
4 for data retrieval in the use of grouping and sorting clause, the same can significantly reduce the query grouping and sorting time.
1 create index on the table, could not be created in the view.
2 create index and maintaining the index takes time, this time increased with the increasing amount of data
3 the index needs to be accounted for the physical space, in addition to the data table of data space, every index also accounts for the physical space, to create a clustered index, so need space will be greater
4 when the data in the table to increase, delete and modify, maintain index should be dynamic, reduce the data maintenance rate

22 how to create a view, view the benefits, the view can control it?
Create view view as select column name [alias] … from [unio [all] select … ] ]
1 can easily be views as SQL query, view the biggest benefit is not occupied space
2 some high security system, not to publish the table structure of the system, may use some sensitive information filtering or rename announced after the structure view
3 simplified query
Can control authority, when in use will need access to the user view grant
23 how to create a trigger, the trigger is defined, how to define trigger cursor
CREATE [OR REPLACE] TIGGER triggers a triggering event triggering time
 The ON table name
  pl/The SQL statement
CURSOR cursor name is SELECT * FROM table name (cursor)
 Among them:
 Trigger name: name trigger objects.
 The trigger is the database automatically, so the name is just a name, no real purpose.
The trigger time: when the value specified trigger execution, desirable:
Before--- said the trigger is executing in the database before the operation;
The after--- representation of the device is performed on the database of movements.
The trigger event: specify which database triggers the trigger:                      
   insert: The database insert trigger the trigger;

Methods 24 Oracle create table; should pay attention to what
Do not know this is it right? Wrong, feel very strange
1 using the graphical tools to create table
2 using the data the DDL statement to create table
3 in PLSQL code can dynamically create table
Should pay attention to: whether to have permission to create the table, use the table space.
25 how will an old database to a new database
1. Imp/Exp to import the data in the database to a new database
2 if memory transfer directly to the storage device linked to the new machine
Several key has 26;
Character, integer type, compound type
Lock and several 27 Oracle respectively, what is the definition of;
The 1 row share (ROW SHARE)
The 2 row exclusive(ROW EXCLUSIVE)
3 shared lock(SHARE)
4 share row exclusive(SHARE ROW EXCLUSIVE)
5 rows of lock him(EXCLUSIVE)
Method of use:
SELECT * FROM order_master WHERE vencode="V002"
The ORACLE lock is divided into the following categories:
1 according to the user and system division, can be divided into automatic lock and display lock
Auto lock: when a database operation, by default, the system automatically for this database operation to obtain all necessary locks.
Display the lock: in some cases, require the user to display the lock database operations to use data, can make the operation of the database performs better, indicating a user for a database object set.
2 according to the lock level division, can be divided into shared and exclusive locks lock
Share: share locks that a transaction for a particular database resources sharing access to the — — another transaction may also have access or access to the same shared lock. Shared lock for the transaction to provide high concurrency, but if the transaction design + a shared lock easily lead to deadlocks or data lost update.
Exclusive: transaction set exclusive lock, the transaction independently acquired this resource, before another transaction cannot be on the transaction to obtain the same object sharing lock or exclusive lock.
3 according to the operating division, can be divided into DML lock, DDL lock
The DML lock can be divided into, row locks, table lock, a deadlock
Row lock: when the transaction database insert, update, delete operations, the transaction is automatically obtained operating table operation for an exclusive lock.
Table level lock: when the transaction was a row lock, this transaction will automatically get the rows of the table lock (share), to prevent other transactions for the DDL statement affects rows to update. Transactions can also acquire a shared lock or exclusive lock in the process, only when the transaction display defined using the LOCK TABLE statement shows an exclusive lock, the transaction will be the table row lock, can also be defined using the LOCK TABLE displays a table level lock (shared LOCK TABLE specific usage please reference to relevant documents).
Deadlock: when the two transaction requires a set of conflicting locks, but not the transaction continues, then a deadlock.
If the transaction is 1 in table A for recording #3 in a row lock it, and wait for the transaction of 2 in table A records #4 ranked it release of the lock, and transaction 2 in table A record for #4 in a row lock it, and wait for the transaction; 1 in table A records #3 ranked it lock release 1, transaction and transaction 2 waiting for one another, because this will result in a deadlock. Deadlock is usually caused by poor transaction design. Deadlock can only use the SQL: alter system kill session "SID, serial#"; or use operating system kill process commands, such as UNIX kill -9 SID, or use other tools to kill process Deadlock.
The DDL lock can be divided into: exclusive DDL lock, DDL lock, lock sharing
Exclusive DDL lock: the DDL statement to create, modify, delete a database object to obtain an exclusive lock object. Such as using the ALTER TABLE statement, in order to maintain data consistency, completeness, validity, the transaction to obtain a exclusive DDL lock.
Share DDL lock: the DDL statement in the database object is established between the interdependent relationships usually need to share DDL lock.
Such as creating a package, the package and function in reference to a different database tables, when compiling this package, the transaction was quoted share DDL lock table.
Analysis of lock: ORACLE uses a pooled storage analysis and optimization of SQL statements and PL/SQL code sharing, the application of identical statement faster running speed. A cache of objects in the shared pool obtain its citation database object lock. Analysis of lock is a unique DDL lock type, ORACLE uses it to track the shared pool object and its dependency relationships between database objects. When a transaction modifies or deletes the shared pool holds analysis database object locking, ORACLE allows objects in the shared pool void, the next in reference to the SQL/PLSQL statement, ORACLE re analysis compile this statement.
4 internal latch
The internal latch: This is ORACLE in a special lock for sequential access, internal system structure. When a transaction needs to write information to the buffer, in order to use this memory region, ORACLE must first obtain the memory of this region of the latch, to write information to the block of memory.

28 in the Java how to call the Oracle stored procedure;
Using CallableStatement to call stored in the java process
Create the necessary test table: create table Test(tid varchar2(10),tname varchar2(10));
The first case: no return value.
     create or replace procedure test_a(param1 in varchar2,param2 in varchar2) as
           insert into test value(param1,param2);
Call Java code:
package com.test;
import java.sql.*;
import java.sql.*;
public class TestProcA
  public TestProcA(){
  public static void main(String []args)
       ResultSet rs = null;
       Connection conn = null;
       CallableStatement proc = null;
conn = DriverManager.getConnection("jdbc:oracle:thin:@", "test", "test");
         proc = conn.prepareCall("{ call test_a(?,?) }");
         proc.setString(1, "1001");
         proc.setString(2, "TestA");
       }catch(Exception e){


          }catch(Exception ex){

Second cases: a stored procedure return value (the return value non list).
The stored procedure for:
create or replace procedure test_b(param1 in varchar2,param2 out varchar2)
   select tname into param2 from test where tid=param1;

Call Java code:
package com.test;
import java.sql.*;
import java.sql.*;

public class TestProcB
  public TestProcB(){
  public static void main(String []args)
       Connection conn = null;
       CallableStatement proc = null;
         conn = DriverManager.getConnection("jdbc:oracle:thin:@", "test", "test");
         proc = conn.prepareCall("{ call test_b(?,?) }");
         proc.setString(1, "1001");
         proc.registerOutParameter(2, Types.VARCHAR);
         System.out.println("Output is:"+proc.getString(2));
       }catch(Exception e){


          }catch(Exception ex){

Third kinds of cases: the returned list.

Because the Oracle stored procedure does not return a value, all of its return value is replaced by the out parameter, the list is not exceptional, but because it is set, so you can't use the general parameters, you must use pagkage. There are two parts to write:
create or replace package tpackage as
type t_cursor is ref cursor;
procedure test_c(c_ref out t_cursor);
end ;

create or replace package body tpackage as
procedure test_c(c_ref out t_cursor) is
     open c_ref for select * from test;
end test_c;
end tpackage;

Call Java code:
package com.test;
import java.sql.*;
import java.sql.*;

public class TestProcB
  public TestProcB(){
  public static void main(String []args)
       Connection conn = null;
       CallableStatement proc = null;
       ResultSet rs = null;
         conn = DriverManager.getConnection("jdbc:oracle:thin:@", "test", "test");
         proc = conn.prepareCall("{? = call tpackage.test_b(?) }");
         proc.registerOutParameter(1, OracleTypes.CURSOR);
             System.out.println(rs.getObject(1) + "\t" + rs.getObject(2));
       }catch(Exception e){

         }catch(Exception ex){

29. rowid, The definition of rownum
1 ROWID and rownum are virtual column
2 ROWID is a physical address, for physical storage location specific data location in Oracle
The 3 rownum is the output of SQL sorting, you can see from the examples below the difference.
The stored procedure Oracle 30, difference between the cursor and function
Cursor like a pointer, cursor can perform multiple unrelated operations. If you want when producing a result set, the result set data were not related to the data operation
Function can understand the function of a stored procedure parameter; function can not, but must return a value, a stored procedure can have no parameters, return values are not needed; both can return values through the out parameter, you need to return more than one parameter are recommending the use of stored procedure; in SQL data manipulation statements only call the function instead of calling a stored procedure
31 using Oracle pseudo columns to delete duplicate records in the table:
Delete table t where t.rowid!=(select max(t1.rowid) from table1 t1 where

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Ryan at December 03, 2013 - 5:34 PM