FirstTime

2014年10月29日星期三

Excel VBA RegExp Function

Function reg(str As String, pattern As String)
Set regEx = CreateObject("VBScript.RegExp")
regEx.pattern = pattern
regEx.Global = True
regEx.IgnoreCase = True
Set Matches = regEx.Execute(str)

If Matches.Count = 0 Then
reg = "Not Found"
Else
reg = Matches(0).Value
End If

End Function

=CONCATENATE("CLM-",reg(B2, "\d\d\d"))

2014年10月15日星期三

Excel Macro

Sub delete4857to5236()
'
' delete4857to5236 巨集
'
' 快速鍵: Ctrl+a
'
Application.Goto Reference:="R4857:R5236"
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Rows("1:10000").Select
Selection.RowHeight = 18.00
Range("A1").Select
Selection.End(xlDown).Select
End Sub

2014年3月18日星期二

drop user & role

drop user gs38ga cascade;
drop user gs38ga_app cascade;
drop user gs38ga_read cascade;
drop role GS38GA_APP_ROLE cascade;
drop role GS38GA_READ_ROLE casacde;

2013年12月19日星期四

Oracle system01.dbf空間過大, ORA-00604, ORA-01653, ORA-02002

1. 先看看sys.aud$的空間是否過大, 或是關閉oracle audit機制
alter system set audit_trail=none scope=spfile;
shutdown immediate;
truncate table sys.aud$;
startup;

2. 調整 system01.dbf的檔案大小
alter database datafile '/usr/local/oracle/oradata/ora11g2/system01.dbf' resize 2048M;
alter database datafile '/usr/local/oracle/oradata/ora11g2/system01.dbf' auto extend on next 10M maxsize unlimited;

ORA-28002 密碼過期

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

將預設180天改成無限制。
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

若不知密碼, 下面查出密碼, 再重新設定一次即可。
select password from $user where name='GS36_MSIG_POC_APP';
alter user GS36_MSIG_POC_APP identified by values '35EE9CA093003211';

2013年12月10日星期二

Linux Boot Auto Start Oracle Oracle database & emctl

/etc/rc.d/rc.local 檔案內容

su - oracle -c "source /home/oracle/.bash_profile"
su - oracle -c "lsnrctl start"
sleep 5
su - oracle -c "echo startup |sqlplus \"/as sysdba\" "
su - oracle -c "lsnrctl set log_status off"
su - oracle -c " emctl start dbconsole"