【oracle 学习笔记 10】 约束延期，decode，锁事务

SQL> select table_name from user_tables 2 / TABLE_NAME ------------------------------ EMPEMP TESTCHAR TESTSEQUENCE DUPEMP TESTCHARBYTE TESTCHARCHAR T TESTPRI TESTINDEX T2 TEST TABLE_NAME ------------------------------ TESTDATE T1 IMPEXT DUPTESTCASE TESTCASE TESTEXT TB 18 rows selected. SQL> ed Wrote file afiedt.buf 1 CREATE TABLE tempTable( 2 id NUMBER, 3 CONSTRAINT primary_key PRIMARY KEY(id) 4 deferrable initially immediate 5* ) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 BEGIN 2 INSERT 3 INTO tempTable 4 VALUES(1); 5 INSERT 6 INTO tempTable 7 VALUES(1); 8* END; SQL> / BEGIN * ERROR at line 1: ORA-00001: unique constraint (YMC.PRIMARY_KEY) violated ORA-06512: at line 5 SQL> ed Wrote file afiedt.buf 1* SET CONSTRAINT primary_key deferred SQL> / Constraint set. SQL> ed Wrote file afiedt.buf 1 BEGIN 2 INSERT 3 INTO tempTable 4 VALUES(1); 5 INSERT 6 INTO tempTable 7 VALUES(1); 8* END; 9 / PL/SQL procedure successfully completed. SQL> commit 2 / commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (YMC.PRIMARY_KEY) violated SQL> ed Wrote file afiedt.buf 1 SELECT * 2* FROM tempTable SQL> / no rows selected SQL> ed Wrote file afiedt.buf 1* lock TABLE tempTable IN share mode SQL> / Table(s) Locked. SQL> conn sys as sysdba Connected. SQL> ed Wrote file afiedt.buf 1 SELECT t2.username, t1.sid, t1.type, 2 decode(t1.LMODE,0,'NONE' 3 ,1,'NULL' 4 ,2,'RS' 5 ,3,'RX' 6 ,4,'S' 7 ,5,'SRX' 8 ,6,'X' 9 ) LMODE, 10 decode(t1.REQUEST,0,'NONE' 11 ,1,'NULL' 12 ,2,'RS' 13 ,3,'RX' 14 ,4,'S' 15 ,5,'SRX' 16 ,6,'X' 17 ) REQUEST 18 FROM v$lock t1, v$session t2 19* WHERE t1.sid=t2.sid AND t2.username=upper('ymc') SQL> / USERNAME SID TY LMOD REQU ------------------------------ ---------- -- ---- ---- YMC 152 TX X NONE YMC 152 TM RX NONE SQL> conn ymc Connected. SQL> ed Wrote file afiedt.buf 1 SELECT username,privilege,admin_option 2* FROM user_sys_privs SQL> / USERNAME PRIVILEGE ADM ------------------------------ ---------------------------------------- --- YMC CREATE VIEW NO YMC CREATE SESSION NO YMC UNLIMITED TABLESPACE NO SQL> ed Wrote file afiedt.buf 1 CREATE TABLE tb( 2 id NUMBER 3* ) SQL> / CREATE TABLE tb( * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> ed Wrote file afiedt.buf 1 CREATE TABLE t( 2 id NUMBER 3* ) SQL> / CREATE TABLE t( * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER D DATE SQL> select * from t 2 / no rows selected SQL> drop table t 2 / Table dropped. SQL> ed Wrote file afiedt.buf 1 CREATE TABLE t( 2 id NUMBER 3* ) SQL> / Table created. SQL> spool off