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';

Oracle 笔记

猜你在找的Oracle相关文章