数据库常用的小技巧

1.解决PLSQL无法正确显示中文

  1）查询oracle server端的字符集。打开PLSQL的查询窗口中输入下面SQL查询语句。 

  select userenv('language') from dual;

        得到的服务器字符集为：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
  2）我的电脑中环境变量界面，在系统变量中新加：
        变量名：NLS_LANG
        变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
        变量值跟服务器查询到的值一致即可

2.查询被锁的表及解锁

---------------------------sqlserver--------------------------------------

  1) 查找锁

select request_session_id,OBJECT_NAME(resource_associated_entity_id) tableName from  sys.dm_tran_locks  
where resource_type='OBJECT'  

   2)解锁    

kill $pid

--------------------oracle--------------------------------------------------

1) 查找锁

    SQLSELECT object_name, machine, s.sid, s.serial#  FROM gv$locked_object l, dba_objects o, gv$session s
    WHERE l.object_id　= o.object_id  AND l.session_id = s.sid;

  2)解锁

    alter system kill session 'sid, serial#';

3.查看表字段注释

 ----------------------mysql---------------------

 show  full columns from xxx;

 --------------------sqlserver------------------

</pre><pre name="code" class="sql">SELECT  A.name AS table_name, B.name AS column_name, C.value AS column_description FROM sys.tables A  
INNER JOIN sys.columns B ON B.object_id = A.object_id  
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id 
WHERE A.name = 'wl_cgdd_detail' ;

--------------------oracle------------------------

select * from user_tab_columns where Table_Name='用户表';
select * from user_col_comments;