Explain the Oracle unlocking process

Here we will introduce the steps for unlocking Oracle, including the specific code and operations. I hope this article will help you in Oracle database management.

 
 

  
  
from v$locked_object t1,v$session t2  
  
  
where t1.session_id=t2.sid order by t2.logon_time; 
 
 

Unlock

 
 

  
  
--alter system kill session 'sid,serial'   
  
  
 alter system kill session '146,21177'; 
 
 

Lock table -- lock table tb_name in Mode

Null

 
 

  
  
Null and false ->false 
  
  
Null and true-> null 
  
  
Null or false ->null 
  
  
Null or true->true 
 
 

Group functions ignore null values

NULL values are sorted by any value and cannot be indexed.

 
 

  
  
Merge into 
  
  
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]  
  
  
{ table | view | subquery } [t_alias] ON ( condition )  
  
  
WHEN MATCHED THEN merge_update_clause  
  
  
WHEN NOT MATCHED THEN merge_insert_clause; 
 
 

Example:

 
 

  
  
merge into acct a  
  
  
using subs b  
  
  
on (a.msid = b.msid)  
  
  
when MATCHED then 
  
  
 update set a.areacode = b.areacode  
  
  
when NOT MATCHED then 
  
  
insert (msid, bill_month, areacode) values (b.msid, '200702', b.areacode) 
 
 

10g increase 1: Conditional operation where

When matched then... where...

Enhancement 2 in 10g: delete operation

 
 

  
  
An optional delete where clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted  
  
  
 merge into acct a   
  
  
 using subs b on (a.msid=b.msid)  
  
  
 when MATCHED then 
  
  
update set a.areacode=b.areacode  
  
  
 delete where (b.ms_type!=0); 
 
 

This parameter must meet the requirements (B. ms_type! = 0) will be deleted

With statement

The with statement can only be used in select statements. The update and delete statements are not supported.

 
 

  
  
 with summary as(  
  
  
select dname, sum(sal) as dept_total  
  
  
from ct_emp, ct_dept  
  
  
 where ct_emp.deptno = ct_dept.deptno  
  
  
 group by dname)  
  
  
select dname, dept_total  
  
  
from summary  
  
  
 where dept_total > (select sum(dept_total) * 1 / 3 from summary);  
 
 

Temporary table

1. Create a temporary table first. We do not recommend that you use DDL statements to create a temporary table at runtime.

2. A temporary table can be seen as a normal physical table, but its data is session-isolated.

Differences:

L inserting data into the table only exists during sessions or transactions

L The data in the table is only visible to sessions that insert data.

L use the on commit command to determine whether the data is session-specific or transaction-specific.

 
 

  
  
Create Global TemporaryTablename (ColumnList)
  
  
 On CommitPreserveRows;-- Submit a temporary table for retaining data sessions 
  
  
On Commit Delete Rows;-- Submit and delete the temporary table of Data transactions 
 
 

The temporary table in oracle is different from that in SQL server. after use, the records in the temporary table in oracle can be defined as the automatic delete session and transaction modes. The table structure is not automatically deleted; temporary tables in SQL server are completely deleted after they are used.

Suggestion: use temporary tables only when complex data processing is required. Otherwise, use subqueries instead of cursors.

Differences between NVL and NVL2 AND USE OF NULLIF

| NVL (expr1, expr2): If expr1 is NULL, expr2 is returned. If not NULL, expr1 is returned.

| NVL2 (expr1, expr2, expr3): If xpr1 is not NULL, expr2 is returned. If it is NULL, expr3 is returned. If the expr2 and expr3 types are different, expr3 will be converted to the expr2 type.

| NULLIF (expr1, expr2): returns NULL if equal, and returns expr1 if not.

1. How to unlock users in Oracle
2. Five-minute master of Oracle tablespace
3. Authorization and management for five Oracle users
4. Oracle administrator Manual: database management tools
5. Step 4: Change the Oracle user name