Statements that are often used in Oracle databases but are hard to remember

1. Associate update table operation

For example, if we need to operate on a table, it is usually OK to update, but sometimes the conditions have to be related. There are some differences between this oracle and mysql, so I'll just say oracle:

Update sys_role t

Set t.password = 'Q3qtuxGsI7B5u60nFMuqiqAqiqiao'

Where exists (select 1

From tempstaff s

Where t.userid = s.userid

And t.operatorid = s.operatorid)

In this example, I made a mistake, and then found thousands of pieces of data that were wrongly modified by taking a snapshot for too long, and then linked back to change the password back, and then operated uniformly.

2. Backup table data

This is very simple and easy to remember. I'll just write it down. The backup table is testTable1.

Create table testTable1 as select * from baseTable

3. Add the fields of the table

I added a collectdate field to the downsheet_flag_record table as follows:

Alter table downsheet_flag_record add (collectdate varchar2 (30))

4. Check whether a certain person in a certain field is in Chinese, because sometimes, for example, the employee form does not have a real name system. If you write it casually, we will find it for inspection and choose a name called Baoba, Wang Egg, × ×, Niu er, Zhang Wei and so on.

Select t.useridrecovert.operatorname, ascII (substrb (trim (t.operatorname), 1meme 1)) from sys_user t where ascII (substrb (trim (t.operatorname), 1meme 1)) 32

This is judged by the asc code. 32 represents Chinese.

5. Delete a column

ALTER TABLE ipms_parameters DROP COLUMN endtime

6. Unlock the table:

Select object_name,machine,s.sid,s.serial#

From v$locked_object l,dba_objects o, v$session s

Where l.object_id = o.object_id and l.session_id=s.sid

Alter system kill session '2684 and 53431'

7. Modify the field type

Alter table alarmmsg modify (sm_text varchar)

8. Modify the field name

Alter DOWNSHEET_FLAG rename column loid to busi_nbr