oracle开发
1.查看事务id
select xid from v$transaction;
2.查看当前scn值
select current_scn from v$database;
select dbms_flashback.get_system_change_number from dual;
3.查看锁
select sid,type,lmode,request,ctime,block from v$lock;
表锁、行锁
悲观锁---探查
select * from emp1 where empno=7788 for update nowait;
select * from emp1 where empno=7788 for update skip locked;
4.查看会话
show parameter session;
5.查询阻塞
select sid ,serial#,blocking_session,username,event from v$session where blocking_session_status='VALID';
6.杀死会话
alter system kill session 'sid,serial#';
7.死锁
抱住了，自动探查的。
8.查看执行计划
set autotrace on;
set autotrace off;
9.查询索引
select index_name,index_type,table_name,uniqueness,status from user_indexes ;
select index_name,table_name,column_name,column_position from user_ind_colunms;
10.反向建索引
create index index_name on emp1(mgr) reverse;反向索引不支持范围扫描。
11.压缩索引
create index index_name on emp1(mgr) compress;
12.索引重构
alter index index_name rebulid online;
13.收集统计信息
analyze table emp1 compute statistics;
14.查询约束
select constraint_name,table_name from user_constraints;
15.查询sql异常报错详细内容
!oeer ORA 01400
16.删除约束和新增约束
alter table t1 drop constraint constraint_name;
alter table t1 modify id constraint constraint_name not null;
alter table t1 add constraint constraint_name unique(id);
17.外键级联删除
alter table emp1 add constraint fk_emp1 foreign key (deptno) references dept1(deltno) on delete cascade;
18.非级联删除，设置空值
alter table emp1 add constraint fk_emp1 foreign key (deptno) references dept1(deltno) on delete set null;
19.主键失效，外键级联失效
alter table dept1 disable constraint pk_dept1 cascade;
20.重新将主键生效，外键不会级联生效,需要单独将外键生效
alter table dept1 enable constraint pk_dept1 ;
alter table dept1 enable constraint fk_emp1;
21.有外键的时候删除表
drop table dept1 cascade constraint purge;
22.约束的四种状态
alter table b modify ename constraint enamenotnull not null;
alter table b modify constraint enamenotnull disable novalidate;
alter table b modify constraint enamenotnull enable novalidate;
alter table b modify constraint enamenotnull enable validate;
23.延迟约束
24.查询会话的权限
select * from session_privs;
grant create view to scott;
grant select on scott.v1 to tim;
25.闪回命令
flashback table emp1 to before drop;
26.先建视图，后建基表
create force view v1 as select * from emp2;
27.查询视图
select view_name,text from user_views;
28.视图约束
create force view v1 as select * from emp2 where deptno=10 with check option;
