Oracle database frequently used but poorly remembered statements

1. association Update table Operations
For example, we need to work on a table, generally update it, but sometimes the condition must be related, this oracle and MySQL have some differences, I directly say Oracle:
Update Sys_role T
Set T.password = ' q3qtuxgsi7b5u60nfmu+qa== '
where exists (select 1
From Tempstaff S
where T.userid = S.userid
and T.operatorid = S.operatorid)
This example is my error operation, and then through the snapshot too long to find the wrong modification of the thousands of data, associated back to change the password back, then unified operation.

2. Backup table Data
This is very simple, but also very good to remember, I will write. Backup table is TestTable1
CREATE TABLE TestTable1 as SELECT * from BaseTable;

3. Add a table field
I add a collectdate field to the Downsheet_flag_record table as follows:
ALTER TABLE Downsheet_flag_record Add (collectdate varchar2 (30));

4, to see if one of the fields is Chinese, because sometimes for example, employee table it is not real-name system, casually write, to deal with the inspection we will find out, take a name, called the King Eight, King eggs, cattle, cattle two, Zhang Wei and so on
Select T.userid,t.operatorname, ASCII (SUBSTRB (Trim (t.operatorname), up)) from Sys_user t where AscII (SUBSTRB (Trim ( T.operatorname) <>32;
This is the ASC code to judge, 32 is the Chinese language.

5. Delete a column
ALTER TABLE ipms_parameters DROP COLUMN endtime;

6, Ching:
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,53431 ';

7. Modify the field type
ALTER TABLE ALARMMSG Modify (sm_text varchar (100));

8. Modify field names
Alter downsheet_flag rename column loid to BUSI_NBR;

Oracle database frequently used but poorly remembered statements