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_clob
  alter 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';
  --被锁的sessionid
  select /*+ 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';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!