Oracle NLS_DATE_FORMAT Tips
Setting NLS_DATE_FORMAT in the shell environment variable using setenv or export command is not effective. When querying dates from SQL*Plus, it does not reflect the NLS_DATE_FORMAT.
% setenv NLS_DATE_FORMAT 'YYYYMMDD'
% sqlplus / as sysdba
SQL> select sysdate from dual;
SYSDATE
---------
17-SEP-18
Oracle 23c Free rpm install Tips
MySQL 8.0 Study 015 Tips
学习系列
MySQL 8.0 Study 014 Tips
学习系列
Oracle drop user trace Tips
SYS@cdb1> drop user C##_U1;
drop user C##_U1
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'C##_U1'
SYS@cdb1>
MySQL 8.0 Study 000 Tips
常用sql
Oracle kill sql lock Tips
--Oracle查询被锁对象数目 / Check lock objects
select count(1) from v$locked_object;
--查询被锁对象 / Check which objects
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id=a.object_id;
--查询被锁对象的连接 / Check connect infor for lock objects
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
--关闭被锁对象连接 / kill session which have lock.
alter system kill session '26,45052';
-- RAC环境下kill 对应的session
COL "SID" FOR 999
COL "SERIAL#" FOR 9999999
COL "INST_ID" FOR 9999999
COL "KILL_SESSION_CMD_TEXT" FOR A41
COL "SQL_ID" FOR A13
COL "PROCESS" FOR A7
COL "USERNAME" FOR A8
COL "SQL_TEXT" FOR A100
select s1.sid,s1.serial#,s1.inst_id,'alter system kill session '''||s1.sid||','||s1.serial#||',@'||s1.inst_id||''';' as kill_session_cmd_text,s1.sql_id,s1.process,s1.username,s2.sql_text from gv$session s1, gv$sql s2 where s1.sql_id=s2.sql_id and s1.inst_id=s2.inst_id and s1.username='SYS';
SID SERIAL# INST_ID KILL_SESSION_CMD_TEXT SQL_ID PROCESS USERNAME SQL_TEXT
---- -------- -------- ----------------------------------------- ------------- ------- -------- ----------------------------------------------------------------------------------------------------
74 21111 1 alter system kill session '74,21111,@1'; g4pkmrqrgxg3b 1790 SYS select count(*) from dba_objects
MySQL 8.0 Study 013 Tips
学习系列
MySQL 8.0 Study 012 Tips
学习系列
Oracle Migration Methods Advisor Tips
Oracle Linux Kernel Tips
MySQL 8.0 Study 011 Tips
学习系列