Oracle 笔记

• 根据主键找到表

  select *  from user_constraints a, USER_CONS_COLUMNS b where a.CONSTRAINT_TYPE = 'P'   and a. constraint_name = b.constraint_name   and a.constraint_name = 'SYS_C0011150';

  
  
• 当前用户表空间的所有表

  select * from all_tables where TABLESPACE_NAME='USERS' and owner='SHANGYIGU';--与上面方法类似select * from user_tables;--找到表字段select * from user_tab_columns where Table_Name='T_ANEMIA';

• 搜索所有表的某个类型字段

  select *  from user_tab_columns where table_name in (select Table_Name from user_tables)   and data_type = 'VARCHAR2'   and data_length = '4000'   and column_name not in ('CHKPIC');

  
  
• 搜索拥有某个字段的所有表

  select table_name  from user_tab_columns where column_name = 'CHKPIC';

  
  
• 新增表字段

  alter table t_XIFE add (tid varchar2(255) );

  
  
• 修改表字段类型

  --varchar2_clobalter table T_MESSAGEINFO rename column MRESULT to MRESULT1;alter table T_MESSAGEINFO add MRESULT clob;update T_MESSAGEINFO set MRESULT = MRESULT1;alter table T_MESSAGEINFO drop column MRESULT1;

  
  
• 查询表索引

  select t.*, i.index_type  from user_ind_columns t, user_indexes i where t.index_name = i.index_name   and t.table_name = 'T_T_ANA';

  
  
• 主键操作

  select cu.table_name, cu.constraint_name  from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name   and au.constraint_type = 'P'   and au.table_name = 'T_BIFE'   and cu.owner = 'SHANGYIGU';--查询主键select cu.*  from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name   and au.constraint_type = 'P'   and au.table_name = 'T_URINE_ROUTINE'   and cu.owner = 'SHANGYIGU';--删除主键alter table students drop constraint yy;--添加主键alter table student add constraint pk_student primary key(studentid);

  
  
• 块操作

  DECLARE   cursor c is      select t.* from t_user_post t;   c_row        c%rowtype;   c_rank_value number;BEGIN   for c_row in c   loop      c_rank_value := get_user_post_rank_value(c_row.STICK_FLAG, c_row.HIGHLIGHT_FLAG, c_row.COMMENT_TIMES);      update t_user_post t         set t.rank_value = c_rank_value       where t.tid = c_row.tid;   end loop;END;

  
  
• 死锁解决办法

  --查询死锁SELECT *  FROM V$DB_OBJECT_CACHE WHERE name = 'GETDICINFOPATIENT'   AND LOCKS != '0';--被锁的sessionidselect /*+ rule*/ SID  from V$ACCESS WHERE object = 'GETDICINFOPATIENT';--查到sid之后还要查到serial＃SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE SID = '147';--kill掉session就可以了alter system kill session '152,11973';--把KidneyWebPool全都干掉select *  from v$session s, v$process p where s.PADDR = p.ADDR   and s.USERNAME is not null   and osuser = 'KidneyWebPool'   and status <> 'KILLED';alter system kill session '144,121';