Oracle interview questions

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

1 for string manipulation functions?
Answer: ASCII (&ndash); function returns the leftmost character of a character expression value of ASCII code
CHR() –Function for the ASCII code will be converted to a character
–If there is no input 0 ~ 255 between the ASCII code value CHR the function will return a NULL value
–Should be to Chr () Fu digital value
concat(str,str)Connect the two string
LOWER() –The string is converted to lowercase all function
UPPER() –Function to convert a string converted to uppercase
LTRIM() –Function spaces removed the string head
RTRIM() –Function returns the string trailing spaces removed
TRIM() –At the same time, remove all the ends of the box
In fact, LTRIM (), RTRIM (), TRIM () is to specify the characters from a string cut
The LTRIM (), RTRIM (XXXX) format (string is cut the string, to cut off), but TRIM (TRIM) format (string one character to truncate the from truncated)
SUBSTR() –The function returns a partial string
INSTR(String,substring) –Function to return a string of a specified substring occurring start position, if there is no return 0
REPLACE (the original string, to be replaced by a string, the string should be replaced)
SOUNDEX() –The function returns a four bit character code
–The SOUNDEX function can be used to find the sound of string similarity but SOUNDEX function of digital and Chinese characters are just returns the value of NULL

2, The transaction concept
Answer: the transaction is such a mechanism, it ensures that multiple SQL statements is treated as a single unit of work to deal with. The transaction has the following effect:
* Consistency: at the same time query and update conflicts with each other does not, other users will not see changes but uncommitted data.
* Recovery: once the system failure, the database will automatically complete recovery of unfinished business.
3, Query system time in Oracle
Answer: select sysdate from dual
4, The trigger function, when the trigger is used, create trigger steps, the trigger can be commit, why?
Answer: the trigger is from the event to start running, a process exists in the database server.
His role: complex constraints can achieve a general constraint can not be completed, in order to achieve a more complex integrity requirements.
Using the trigger does not have strict limit, as long as the user to define constraints in general without artificial participation under the condition of not complete the constraints, to ensure the integrity of the database, you can use triggers.
The trigger is mainly used to ensure the integrity of the database, so to create a trigger, we must first clarify the trigger should belong to that kind of (DML, INSTEAD, OF, SYSTEM) because they all have a purpose; second is to determine the trigger is triggered after the design of the data.
The use of COMMIT can not be starting device.
5.Digital function
abs()The absolute value of exp (value) e value power ceil (floor) is greater than or equal to the minimum integer the value () is less than or equal to the maximum integer TRUNC values (value, precision) retain precision fractional interception of valueround (value, precision) retain precision decimal value for four to five homes in sign (according to value) plus or minus zero return 1, -1, 0mod()The modulo operation power (value, exponent) value exponent power sqrt (square root)
The 1 primary
4, Relational database system and file database system differences in there? Generally applicable to those aspects of a relational database system?
Answer:
The difference between the file system is a relational database system:
First of all, the whole data relational database is structured, uses the relational data model to describe, and it is the fundamental difference between file system. (data model includes: data structure, data operation and integrity constraints)
Secondly, a relational database system high sharing, redundant low for the whole system, and the limitation of the file system is not easy to expand the scope of application,.
Third, ensuring high data independence by two levels of mapping relational database system, which makes the program and data are independence is very high. This is the file system can not be achieved, it is only for a specific application. (two level mapping: guarantee mode / logical independence model mapping and guarantees the mapping mode / pattern of physical independence. External modes: user mode, is the logical structure of local characteristics of the description of the database user data. Model: the logic structure feature database of all data description. In the model: is the physical storage structure of data the final description. )
Fourth, the relation database system managed by the unified DBMS, so as to provide data provided as security, concurrency control, integrity and database recovery service.

5, The trigger concept, concept of the stored procedure.
Answer:
Trigger: is stored in the database, when the table is altered (add, delete, change) it implicitly be excited.
The stored procedure: is a collection of database language SQL, is also stored in the database, but he is by other applications to run or can be run directly.
6, What are the basic SQL statements.
Answer:
select, insert, update, delete, create, drop, truncate
The 1 Intermediate
8, What is the transactional consistency? Select familiar with database to realize a transaction, such as credit card withdrawals.
Answer:
Transactional consistency: is the embodiment of transaction atomicity, firm the corresponding database operation either success or failure no third cases. The transaction either submit success can not affect the database data consistency state.
Transaction: a user-defined database operation sequence, these operations either all completed successfully or not do it all, is an inseparable whole. The SQL statement to define the affairs of: BEGIN TRANSACTION, COMMIT, ROLLBACK.
Transaction atomicity is firm: contains the database operation or do, or don't do.
Transaction isolation: the effect of operation about the data can not be influenced by other transactions.
Persistent affairs: namely the transaction data is permanent.
The ’ &rsquo credit card cash transaction; this is to ensure that the ’ extraction to cash ’ and ’ card account balance ’ modification to success or failure.
BEGIN TRANSACTION
A read the account balance BALANCE,
BALANCE=BALANCE-AMOUNT transfer amount,
IF(BALANCE<0) THEN
ROLLBACK,
ELSE
BEGIN
The new balance A write back,
B read the account balance BALANCEB,
BALANCEB=BALANCEB+AMOUNT transfer amount,
The new balance B write back,
COMMIT,
END IF,
END,

9, The actual writing and debugging a stored procedure or trigger.
Answer:
10, The index of the principle? Index? Index price index types??
Answer: the implementation of index in two ways: to create the index table in some fields, such as the oracle: create index index name on table name (Zi Duanming); a primary key constraint or unique constraint for the field when you create a table, the system will automatically to index.
Principle of index: according to field indexing indexing table, storage field values and physical addresses corresponding record, which at the time of search search index table according to the value of the field to the physical address directly access records.
The index can improve the query speed, but itself take up storage capacity of the system and some processing time, need to carry out specific analysis according to the actual situation.
The type of index: B tree index, bitmap index, function index.
11, The concept of view when the application??
Answer: view is a multi angle perspective on the table level data, suitable for query safety, flexibility has certain requirements of the environment
12, The SQL statement illustrates exception handling??
Answer: when the SQL statement surprises in the implementation process, such as query, query result not to change; no records of the measures to be taken.
In the case of Oracle: to test their understanding of exception.
The select field into variable from table ;
If the query into a plurality of recording, should increase the exception exception handling.
Oracle treatment of abnormal three:
1.Exception handling predefined, EXCEPTION WHEN exception_name THEN
2.EXCEPTION_INIT caused exception
The first declare statement to an exception; then the exception name and error code pragma exception_init (error, error code); finally, in the EXCEPTION when error then
3.User defined Exceptions, not EXCEPTION, when the judge when conditions are ripe with raise_application_error (-20000~~~-20999, message).
13, The following paper is correct. (1) a transaction in a SQL command submission, other SQL command can not submit. (2) in a relational table, the primary key can be uniquely identifies a row. (3) a relationship table foreign key must be a primary key in a table. (4) the rollback in transaction failure, back to the state before the transaction data.
Answer: (1) the error (2) to (3) to (4) the right
14, Which of the following methods can ensure the integrity and consistency of the data in the database: (1) the custom data types (2) trigger (3) primary key and foreign key (4) affairs
Answer: (1) (2) (3) (4) (why 1)
15, Use stored procedures to access database access than what advantages directly using the SQL statement?
Answer: the stored procedure is precompiled, execution shall not be compile, run faster; stored procedure encapsulates a batch of SQL statements, easy to maintain data integrity and consistency; reuse can achieve code.
1 senior
16, For the description of the architecture of database system in, including the storage mechanism, the rollback mechanism, operation mechanism and so on.
Answer: on Oracle systems, structural description of SGA; CKPT, LGWR, PMON, SMON and other functional process; allocation table space; structure rollback segment
Oracle SGA (system global area) the main areas include: database buffer cache, redo log buffer, the shared pool (data dictionary cache and the library cache), large pool etc. Database buffer cache is used to store recently used data block and a background process database writer process (DBWR) relationship and data files; redo log buffer is used to store the database operation data generated redo log information, Cooperate with a redo log write process (LGWR) and redo log files shared pool; main cache SQL/PLSQL, Resource lock, Control information, The library cache cache which mainly be parsed out SQL/PLSQL the library cache can be divided into shared SQL and private SQL district two, Share the SQL for syntax analysis results for the SQL statement and the execution plan, Private SQL is used to store and specific SQL statement execution bound variables related to, Session parameters etc.
Another important part of the ORACLE instance is the background process, the main processes are: database writer process (DBWR), redo log write process (LGWR), system monitor (SMON), process monitor (PMON), checkpoint process(CKPT). DBWR is mainly to write data on the file operation dirty cold data in the database buffer cache; LGWR is mainly the redo log information for database manipulation have written to the redo log files; SMON complete because the recovery of the whole database of non normal shutdown database in case of heavy database; PMON is used to process user recovery failed and the service process, and the release of the system resources occupied; CKPT can be said here in the state of complete database.
Logical storage structure: data blocks BLOCK, EXTENT, SEGMENT, TABLESPACE tablespace
Physical storage space: space, data files, control files, log files, data dictionary
Software architecture is the description above on SGA and background process.
17, For the guarantee mechanism, the database system in describing the consistency of data including lock, transactional consistency.
Answer: in the complicated environment, using a variety of mechanisms to maintain the consistency of data, such as consistency, transaction level Oracle system provides the row level lock, a table level lock and so on.
18, The recovery mechanism to describe the online backup mechanism, database system master, check their understanding of the log.
Answer: real-time online backup strategy description of related databases, such as real-time data backup of the database system through which way in operation, when problems occur, should adopt what kind of measures to restore from online backup.
On the Oracle, the archive online backup should be how to set, modify what parameters, how to arrange backup space and so on.
19, Describes the performance optimization method of database system master, including optimization, parameter, test methods.
Answer:
20, Truncate; With the delete; the difference? (this can be derived from many problems such as: want to quickly remove a lot of data in the table should use what operation? The answer is: should use truncate. Also, in two of the same record number are table using truncate, commit and delete; after count of the two tables (*) statistical records of operation, the speed will be faster? The answer is: the use of the truncate will be faster. )
21, Write a ‘ a ’ and use it. (the problem should pay attention to is: use the cursor must remember to close the cursor).
Declare
Cursor cursor_name is
Select * from tablename;
Begin
Open cursor_name;
Loop
Fetch cursor_name into bianliang;
Exit when cursor_name%notfound;
Sql……..
End loop;
Close cursor_name;
End;
22, Set when creating the table
pctfree 10
pctused 40
When the create table index did not
pctused 40
This is why?
(the main idea of this question is not on the pctused 40 index and what benefits)
23, The largest surface used how many rows (records? If this problem is not to see the candidate technology does not pass, but examination of large database management operation experience)
Not on the indexed column function operation, because this system cannot use the index, so that the query becomes very slow, but after ora8i began to support the function index, to make up for this deficiency.

24, Step ORACLE database startup and shutdown
Start: start the instance of — - load database data — – open database
Close: close the database — unloading database — - shutting down the instance
25, The difference between Delete and truncate
Delete is generally used to remove a small amount of records of the operation, and it is necessary to use rollback segment and to display the commit transaction. While truncate is used to delete data, but also implicitly commit the transaction, the speed is much faster than using delete.
26, DDL and DML represent what??
DDL said the data definition language, in the ORACLE including CREATE, ALTER, DROP
DML said the data manipulation language, major DML SELECT, INSERT, UPDATE, DELETE
28, The function call Javascript dynamic effect in.
29, A plurality of the same record database, delete to only one record. How to do, please use the SQL statement by ROW_ID to write the implementation process.
Delete table_name where ziduan=’’ and rowed<(select max(rowed) from table_name where ziduan=’’);
30, Oracle database table stored in the local disk what (what the physical space — — probably mean)?
Oracle database table is stored in the data file.
31, Oracle's statement to the user to assign permissions?
The GRANT TO statement
32, You are in the project site, the user requires you to add a field to the running table, what should you do?
The first method: close the database, and then use the constrained mode to open, from sys/sysdba to
Second kinds of methods: do not close the database, the database quiesced state by ALTER SYSTEM QUISCE RESTRICTED in SYS/SYSDBA mode, you can operate on the database is only SYS/SYSDBA of that state, modified quit again after silence state ALTER SYSTEM UNQUISCE
Here to review two special state database: silence state (QUISCE) and suspended state
Silence is the only state special permissions SYS/SYSDBA can operate on the database, using the ALTER SYSTEM QUISCE RESTRICTED system will wait for the activities of the active end of session, and prevent the establishment of a new session, the system to hang all SQL statements, recovery after re activation in session execution pending SQL.
The suspended state is the system database for physical files (all data files, control files, log files) I/O operations are suspended, but does not prohibit non DBA users to the database operation. It is mainly used for database backup.

33, The concept of Oracle ROLLBACK SEGMENT rollback? What is the role of.
Rollback is before the transaction commits the database data back to modify the database state before the transaction.
Rollback segment is to provide the basis for a rollback, the record is before the transaction database data or corresponding to the previous operation, the content according to the previous operating set. For example before operation if UPDATE then rollback segment is stored in UPDATE of previous data, if the transaction is the DELETE operating the storage is the corresponding INSERT statement, if the transaction operation is INSERT then the corresponding record is the DELETE operation.
34, Oracle what is the difference between 8I and 9I
35, A table with 100000 records, how to remove any one of the 20 records? Please operate using the SQL statement
36, How does the client access server oracle? If the client can not access the server ORALCE, may be what reason?
Connect the client through the network or the process approach to identity to get and server ORACLE. If clients cannot access the server ORACLE may arise is: the user does not have access to the database is not open; the server (third step start database is not complete); if the server is on a shared communication mode may not correspond to the use of the customer's scheduling process Dnnn.
37, Where to find the error message execution errors in Oracle?
Select * from USER_ERRORS
38, The select statement is required to submit? Insert and update statements.?
39, The insert statement is executed and submit, the submitted data stored somewhere??
Are stored into a data file
40, What types of files in Oracle?
Data files, control files, log files and data dictionary
41, Introduce the system structure of Oracle?
The logic system structure: block, region, section, table space
The physics system structure: table space, three file
Software architecture: SGA, the background process
42, Whether or not to talk about Oracle row_ ID understand? Please describe?
ORACLE row_id is a pseudo columns, which is 18 bytes to 18 bytes in the 6363 division, respectively, said code, data file number, block number and record number.
Row_id is a record of the physical memory address.
43, How to delete the user Oracle?
Oracle in the use of DROP USER to delete the user, if you use the DROP USER CASCADE so the user object also deleted. In order to use the object to the user to delete the user does not affect the effect and can use the alter user username account lock lock user.
44, What is the client to the server ORALCE flow?
Special mode: the user through the application process to get connected  client of a ORACLE database server and transmitted through the network connection request, The ORACLE server then use the monitoring process of monitoring user request, And to verify user identity, Through the verification for the user to assign a dedicated server process  user submitted SQL special service statement  process is first in the SGA area of the shared pool check whether there is similar to the SQL statement has been parsed and caching SQL statement, If there is the analytical results and its execution plan to execute a SQL statement, If not, the SQL statement parsing an execution plan is generated by analyzing the execution  to fetch the data  will perform the results back to the client.
Shared mode: different and special mode is legitimacy when monitoring procedures to validate user later not to allocate it a dedicated server process, but the request and the response of the scheduling process is linked, and will play into a request queue, and ultimately by the response of the Dnnn from the scheduling queue access a request and service process to allocate a free of service process, then the request service operation and special way, after processing is complete by the service process will result in a return to the queue and finally a scheduling process (Dnnn) will be returned to the queue and return results to the corresponding user.
45, Exits and in in the ORALCE database, the implementation of more efficient?
Exits performs higher efficiency than in. Because:
46, How to judge the cursor to the last line?
Cursor_name%notfound
47, The clustered index and average index in different SQL statements in which more efficient?? (the difference between the t test, the original title can't recall, is the theme of the clustered index and average index)
Concept class:
The aggregation function definition and usage of Session?? the stored procedure Oracle? What is the constructor?

48, Part of the ORACLE SGA.
Answer: including the system global area: the shared pool, redo log buffer, data buffer cache, large pool, the JAVA pool.
49, The ORACLE startup and shutdown of the number of steps?
Start: start the instance, loading the database data, open the database.
Close: close database, unloading, shutting down the instance database.
50, In the classification and function of Oracle table space, such as sorting data will be placed in what table space?
Role is to break through the limit of storage capacity, is a logical concept. Sort the data in the temporary table space.
51, The COMMIT command is executed, the database will change what happened, ROLLBACK.?
Answer: commit submission is firstly and transaction corresponding redo log information will be written to the physical database files in the redo log files, as to whether or not the transaction operation content to reflect the data file. DBWR is launched. In the insert complete database, delete and modify operations, only when the transaction is committed to the database to complete, have submitted before the operation of the database I can only see, others only completed in the final submission can see.
ROLLBACK rollback the uncommitted transaction, restore the database to the state before the transaction operation.
52, Create user command, and user authorization.
Create user user_name
identified by password /
identified externally/
identified blobally as ‘CN=user’
default tablespace tablespace_name
temporary tablespace tablespace_name;
grant role/privilege to user_name;

53, Write a stored procedure, the employee (name, age, emp_no, salary) in the table of salary values between 0-1000 staff wages rose 20%, (hint: use the cursor request)
Cteate or replace procedure emp_sal
V_name employee.name%type;
V_emp_no employee.emp_no%type;
V_salary employee.salary%type;
Cursor cursor_sal is
Select name,emp_no,salary from employee where salary between 0 and 1000;
Begin
Open cursor_sal;
Loop
Fetch cursor_sal into v_name,v_emp_no,v_salary;
Exit when cursor_sal%notfound;
Update employee set salary=salary*1.2 where name=v_name and emp_no=v_emp_no;
End loop;
Close cursor_sal;
Commit;
End;
54, The ORACLE database are what type of file?
Data files, control files, log files, parameter file
55, Create table space, the user command, and authorized, to recover the permissions for user.
Create tablespace tabllespacename
Datafile ‘f:\orcl\user001.dbf size 20m
Default storage(
Initial 512k
Next 512k
minextents 2
pctincrease 50%
maxExitnts 2048)
minimum extent 512k
logging
online
permanent
extent management dictionary;
Recovery authority: revoke privilege from user,
create tablespace tablespace_name
datafile ‘’
default storage(
initial xxxkb
next yykb
minextents 2
pctincrease nnnn
maxextents mmm)
logging
online
extent management dictionary/local[autoallocate/uniform size xxxmb];
create user user_name
identified by passwore/
identified externally/
identified globally as ‘CN=user’
default tablespace tablespace_name
temporary tablespace tablespace_name
[account lock /unlock]
grant connect to user_name;
grant create table to suer_name;
grant update on table_name to user_name;
revoke create table from user_name;
revoke update on table_name from user_name;
56, How to change the user name in Oracle?
57, How to query a user has all built in the Oracle table?
Answer: select * from cat;
57, Execute the TRUNCATE command, the storage space is there, whether can be the other table occupied?
After TRUNCATE, table for the distribution of the district space will be recycled HWM will return, if when using TRUNCATE does not specify a REUSE STORAGE operation is performed after leaving only then all space specified by the MINEXTENTS area or table will be recycled for redistribution.
59, Where in the Oracle view the current user, usually have what way? (hint: show user and select * from user_users show user);/select username from user_users;
60, In Oracle, create the grammatical structure of table space, and the parameter description.
Create tablespace tablespacename
Datafile ‘’
Logging/nologging
Default storage(initial kb
Next kb
Minextents x
Maxextents y
Pctincrease z
)
online/not online
extent management dictionary/local[autoallocate/uniform size kb];
61, How to judge whether the cursor to the end? (hint: use%notfound)
cursor_name%notfound
62, In Oracle, how to view all table spaces under the current user?
Select tablespace_name from user_tablespaces;
63, In Oracle, you create tablespace information where?
Stored in the data dictionary, the corresponding data dictionary content space SYSTEM table space in the system tables.
64, When the control in the ORACLE file read?
The ORACLE server is started, to start an instance of each file and then read the database also includes the control file. That is read in the database server to start the second step.
65, The Oracle form of information storage in which place?
Data dictionary file SYSTEM table space.
66, According to the dictionary to create a table space, and that the parameters?
66, ORACLE when a user is operating a process, the administrator then canceled his authority, what will happen?

68, Talk about your understanding of the role, what are the common role?
A role is a set of permissions database entity, it does not belong to any mode or user but can be granted to any user. Common role has CONNECT, DBA, RESOURCE, SELECT_CATALOG_ROLE (query all the table view right), DELETE_CATALOG_ROLE (delete) etc.
Create and authorization role: and create a user for user authorization almost. Create role role_name identified …
Grant permissions for the to role_name.
69, Archiving Oracle brief and no archive mode, respectively.
The Oracle archive mode refers to the ARCHIVELOG parameter specified in the creation of a database, this kind of mode, when the redo log file write full time will save the redo log file contents to the specified location (by the parameter ARCHIVE_LOG_DEST_n initialization file to determine). Time is not the database in archivelog mode can automatically complete filing operation, in archivelog mode can have two types: automatic archiving filing mode (ARCHIVE_LOG_START parameter in the initialization file is set to TRUE) and manual filing. If the archive mode automatic archiving is not started, but did not implement the manual filing so when the LGWR process will have filled online redo log files when the database will be suspended until the archived redo log information. The archive is a kind of protection to the redo log file information.
Oracle non archive mode when the redo log file is full when the write operation of LGWR redo log information, redo log information will be saved in the redo log files are covered.
70, The Oracle index is divided into which a few kinds, conceptual name only index and bitmap index.
The Oracle index with B tree index, bitmap index, function index, cluster index etc.
A unique index is B tree index, it requires the indexed field value can not be repeated. Using the B tree at the time of creation algorithm to create.
Bitmap indexes are not used as storage as a unique index (indexed field value, ROWID) to create the index segment, but for each unique field values create a bitmap, bitmap using bit corresponding to a recording of ROWID. Bit to ROWID through the mapping to.
71, What are the basic data type of ORACLE?
Char()Storage of fixed length character, the definition of time can not he specified length but if to insert values will make mistakes; (VARCHAR2) to store variable length character definition must specify the length, date storage time and date; (Number) the numeric types, including integer, floating-point type; CLOB (string BLOB) large capacity; (binary large object)
72, SQL, four to five homes in which function was?
Round(value, Retention of decimal digits) and the corresponding and a similar function TRUNC (value, the number of decimal places reserved) which is according to the retention of decimal digits to intercept the original number.
73, Oracle database table stored in the local disk? Data file
ORACLE database table stored in the physical file data file.
74, When executing insert statements and submit, the submitted data stored somewhere??
Stored in the database in the data file.
75, Exits and in in the ORALCE database, the implementation of more efficient?
EXITS execution efficiency is faster than using IN.
76, The function ORACLE to provide their own, want to know the specific location, how to operate?
77, Several physical file database?
1)Data files control files 2) 3) log file
78, A control file contains information about the?
Control files are examples of information (instance name creation time, etc.), the data and log files and the system information, recording system runtime change code (SCN), check point information and archive the status information. First time database in the database loading to read control file is obtained and the physical structure of the database related information to the correct loading of data file and log file and open the database.
79, The use of the Decode function?
The syntax of DECODE: DECODE (value, IF1, then1, IF2, then2, IF3, then3, &hellip, else;), says that if value equals IF1, the DECODE function returns the results of then1, &hellip, if any; not equal to a if value, it returns else. At first glance, DECODE can only be equal to the test, but saw just now, we through some function or calculation instead of value, can make the DECODE function with greater than, less than or equal to the function.
2 how to use decode to greater than the?
Using sign () function and DECODE and together with
Select decode (sign (1- variable, -1 2), variable 1 variable, 2) from dual;

Decode (condition, 1, translation 1, value 2, translation 2, … N, the value n, the default value)
This function is defined as follows:
IF = 1 THEN
RETURN (translation, 1)
ELSIF = 2 THEN
RETURN (translation, 2)
……
ELSIF = n THEN
RETURN (translation of n value)

ELSE
RETURN (default)
END IF
• Method of use:
1, Compare the size of
Select decode (sign (1- variable, -1 2), variable 1 variable, 2) from dual; – smaller
sign()Function according to a value of 0, positive or negative, respectively return 0, 1, -1

For example:
The variables 1=10, 2=20 variable
The sign (1- variable 2) return -1, decode decoding results for “ the variable 1”, reached a smaller purpose.

2, Conversion table, view structure
The existing a sales table sale, table structure:
month char(6) –Month
sell number(10,2) –The amount of monthly sales

Existing data:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300

Want into the structure of data:
year char(4) –Particular year
month1 number(10,2) –In January the amount of sales
month2 number(10,2) –In February the amount of sales
month3 number(10,2) –In March the amount of sales
month4 number(10,2) –In April the amount of sales
month5 number(10,2) –In May the amount of sales
month6 number(10,2) –In June the amount of sales
month7 number(10,2) –In July the amount of sales
month8 number(10,2) –In August the amount of sales
month9 number(10,2) –In September the amount of sales
month10 number(10,2) –In October the amount of sales
month11 number(10,2) –In November the amount of sales
month12 number(10,2) –In December the amount of sales

The SQL statement for the structural transformation:
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),’01′,sell,0)),
sum(decode(substrb(month,5,2),’02′,sell,0)),
sum(decode(substrb(month,5,2),’03′,sell,0)),
sum(decode(substrb(month,5,2),’04′,sell,0)),
sum(decode(substrb(month,5,2),’05′,sell,0)),
sum(decode(substrb(month,5,2),’06′,sell,0)),
sum(decode(substrb(month,5,2),’07′,sell,0)),
sum(decode(substrb(month,5,2),’08′,sell,0)),
sum(decode(substrb(month,5,2),’09′,sell,0)),
sum(decode(substrb(month,5,2),’10′,sell,0)),
sum(decode(substrb(month,5,2),’11′,sell,0)),
sum(decode(substrb(month,5,2),’12′,sell,0))
from sale
group by substrb(month,1,4);

79, The CASE statement.?
Oracle usage is very simple:
SELECT last_name, job_id, salary
CASE job_id
WHEN ‘IT_PROG’ THEN 1.10*salary
WHEN ‘ST_CLERK’ THEN 1.15*salary
WHEN ‘SA_REP’ THEN 1.20*salary
ELSE salary END “REVISED_SALARY”
FROM employees

80, The difference between truncate and delete?
1, TRUNCATE in various table whether big or small are all very soon. If the ROLLBACK command DELETE will be revoked, and the TRUNCATE will not be revoked.
2, TRUNCATE is a DDL language and DELETE is the DML statement, like all other DDL language, he will be implicitly commit, not on the TRUNCATE using the ROLLBACK command.
3, TRUNCATE will reset the high level and all the index. On the table and index completely browsing, after TRUNCATE operation table than DELETE operating table after the much faster.
4, TRUNCATE cannot trigger, DELETE trigger.
5, Do not grant any empty others table permissions.
6, When the table is empty tables and table index about reset to the initial size, while delete is not.
7, Don't empty the parent table.
81, Table space how to extend and use statements to write??
Two types of extensions:
a)Increased data file
alter tablespace tablespace_name add datafile ‘’ xxMB
b)Extended data file size
alter database datafile ‘’ resize newMB
82, Table space area management? Which way it is recommended to use?
a)Dictionary management mode
extent management dictionary;The default mode
b)Local management
extent management local[autoallocate/uniform xxmb];
83, Get a date with what function? And the date of the month, day, year
to_char(sysdate,’year’):tow thsound six to_char(sysdate,’yyyy’) :2006
to_char(sysdate,’month’):In August to_char(sysdate,’mm’):08
to_char(sysdate,’day’):Week 4 to_char(sysdate,’dd’):22
84, Application of the partition table?

a)A partitioned table with one or multiple partitions, each partition by range partitioning, hash partitioning, or a combination of partition line
b)Each partition in the partition table for a while, can each located in a different table space
c)At the same time can use several process queries or operation of a large table partitioning is very useful

85, Usage and principle about index?
A plurality of rows of data index is a list of keywords, data query, the index of keywords can block fast positioning to access within a record, thus greatly reducing the read data block I/O times, so it can significantly improve the performance.
86, The application of the stored procedure, 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;
87, What are often abnormal?
Commonly used predefined exception
CURSOR_ALREADY_OPEN — ORA-06511 SQLCODE = -6511 cursor has been opened
DUP_VAL_ON_INDEX — ORA-00001 SQLCODE = -1 violates the uniqueness constraint
INVALID_CURSOR — ORA-01001 SQLCODE = -1001 illegal cursor operation
INVALID_NUMBER — ORA-01722 SQLCODE = -1722 characters to digital conversion failed
LOGIN_DENIED — ORA-01017 SQLCODE = -1017
NO_DATA_FOUND — ORA-01403 SQLCODE = +100 data not found
NOT_LOGGED_ON — ORA-01012 SQLCODE = -1012 is not connected to the database
PROGRAM_ERROR — ORA-06501 SQLCODE = -6501 internal error
STORAGE_ERROR — ORA-06500 SQLCODE = -6500
TIMEOUT_ON_RESOURCE — ORA-00051 SQLCODE = -51
TOO_MANY_ROWS — ORA-01422 SQLCODE = -1422 return multiple rows
TRANSACTION_BACKED_OUT — ORA-00061 SQLCODE = -61
VALUE_ERROR — ORA-06502 SQLCODE = -6502 numerical conversion error
ACCESS_INTO_NULL attempts to attribute assignment as a NULL object
ZERO_DIVIDE — ORA-01476 SQLCODE = -1476 divide by zero
OTHERS — Any other error handling

88, How to use the exception?
In Oracle there are three types of exceptions. Abnormal predefined abnormal abnormal user defined predefined exception second non predefined is associated with a particular Oracle error. And with PRAGM EXCEPTION_INIT (EXCEPTION_NAME, ERROR_NUMBER) associated with. But what's the use ah? For example: declare dup_primary_key exception; pragma exception_init(dup_primary_key,-1); begin insert into itemfile values(‘i201′,’washer’,'spares’,100,50,250,12,30); exception when dup_primary_key then dbms_output.put_line(‘Repeat the item number-Primary key conflict’); end
Using the method of the first kind: exception
Abnormal when name then
Exception handling code;
Third kinds of usage: If then
raise_application_error(-20000“““`-20999,Prompt information),
end if;
89, Optimization strategies generally include:
•Memory optimization
•Operating system optimization
•The optimization of data storage
•Network optimization method
Specific to the different database involves adjusting the database configuration file, different operating system parameters, network parameters and so on, different database.

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

Posted by Enoch at December 12, 2013 - 1:47 PM