Oracle Database Development interview questions, I did not do the test at that time, now with the original questions and answers, oracle Test

Oracle Database Development interview questions, I did not do the test at that time, now with the original questions and answers, oracle Test

1,

ID
1
2
3
5
6
7
8
10
11
12
15

Table Name tt. use SQL to find the non-consecutive IDs in the ID column, for example, 4 which does not exist:

--Create tables and data
CREATE TABLE tt (ID INTEGER);

INSERT INTO tt
SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual
UNION ALL
SELECT 3 FROM dual
UNION ALL
SELECT 5 FROM dual
UNION ALL
SELECT 6 FROM dual
UNION ALL
SELECT 7 FROM dual
UNION ALL
SELECT 8 FROM dual
UNION ALL
SELECT 10 FROM dual
UNION ALL
SELECT 11 FROM dual
UNION ALL
SELECT 12 FROM dual
UNION ALL
SELECT 15 FROM dual;
COMMIT;
 

-Use connect by level to create data
WITH IT AS
  (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL <= (SELECT MAX (ID) FROM TT))
SELECT A.ID
   FROM IT A
  WHERE NOT EXISTS (SELECT 1 FROM TT B WHERE A.ID = B.ID)

 

2,

Organize nonstandard room information into a standard format

Non-standard table (multiple rooms are separated by commas)

ID	ROOM
1	101,102
2	201,202,203
3	301
.......

 

 

 

 

 

Specification table

ID	ROOM
1	101
1	102
2	201
2	202
2	203
3	301
......

 

 

 

 

 

 

 

 

--Single row and single column to multiple rows
--Create tables and data
create table ttt (id integer, room varchar2 (200));

insert into ttt
select 1, '101,102' from dual
union all
select 2, '201,202,203' from dual
union all
select 3, '301' from dual;
commit;
SELECT DISTINCT ID, REGEXP_SUBSTR (room, '[^,] +', 1, LEVEL, 'i') AS STR
   FROM ttt
CONNECT BY LEVEL <= LENGTH (room)-LENGTH (REGEXP_REPLACE (room, ',', '')) + 1; 

 

The following is a question about the oracle database: I only knew a few questions at the time of the interview. How can I write this question?

I. Database deadlock
When the program is executed, click OK or save. The program does not respond or reports an error.
Ii. deadlock Principle
If you update or delete a column in a table in the database, this statement is not mentioned after the execution.
The statement that updates this column of data is in the waiting state during execution,
In this case, this statement has been executed, but it has not been successfully executed and no error has been reported.
Iii. deadlock locating
By checking the database table, you can check which statement is deadlocked and which machine is causing the deadlock.
1) Use the dba user to execute the following statements
Select username, lockwait, status, machine, program from v $ session where sid in
(Select session_id from v $ locked_object)
If there are output results, it indicates a deadlock exists and you can see which machine is deadlocked. Field description:
Username: the database user used by the deadlock statement;
Lockwait: the status of the deadlock. If there is content, it indicates the deadlock.
Status: Status. active indicates a deadlock.
Machine: The Machine where the deadlock statement is located.
Program: the application that generates the deadlock statement.
2) You can run the following statements with dba to view the statements that have been deadlocked.
Select SQL _text from v $ SQL where hash_value in
(Select SQL _hash_value from v $ session where sid in
(Select session_id from v $ locked_object ))
Iv. Solution to deadlock
Generally, you only need to submit statements that generate deadlocks, but in the actual execution process. Users can
I don't know which statement is used to generate a deadlock. You can close the program and restart it.
This problem is often encountered during the use of Oracle, so a few solutions are also summarized.
1) Find the deadlock process:
Sqlplus "/as sysdba" (sys/change_on_install)
SELECT s. username, l. OBJECT_ID, l. SESSION_ID, s. SERIAL #,
L. ORACLE_USERNAME, l. OS _USER_NAME, l. PROCESS
From v $ LOCKED_OBJECT l, V $ session s where l. SESSION_ID = S. SID;
2) kill the deadlock process:
Alter system kill session 'sid, serial # '; (sid = l. session_id)
3) if the problem persists:
Select pro. spid from v $ session ses, v $ process pro where ses. sid = XX and ses. paddr = pro. addr;
The sid is replaced by the sid of the deadlock: exit
Ps-ef | grep spid
Where spid is the process Number of the Process, kill the Oracle Process
From: southking.javaeye.com/blog/550832

Select A. SQL _TEXT, B. USERNAME, C. OBJECT_ID, C. SESSION_ID,
B. SERIAL #... the remaining full text>

Oracle Database written test!

Management requires a lot of knowledge, at least backup and recovery...
If it is convenient, let me know what the company is ..