MS SQL Server 如何取sysdate

若建立table，
在oracle 寫法
-取日期yymmdd則使用 to_char (sysdate,'YYYYMMDD')
-取時間hh24miss則使用to_char(sysdate,'HH24MISS')

MS SQL Server中文版的預設日期datetime格式是yyyy-mm-dd hh:mm:ss.mmm
在sql server寫法上非常不同
-取日期yymmdd，使用 CONVERT(VARCHAR(8), GETDATE(), 112)
-取時間yymmdd，使用REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
取時間的時候需要多作一次轉換，將":"的地方去除。

另外~convert參數中的112、108，sql server在轉換日期格式時，所代表的編號。
附上Online book上描述的convert 格式編號:

url= http://msdn.microsoft.com/zh-tw/library/ms174450(SQL.90).aspx

一般來說，沒有標示世紀的話~就以yy為主，例如yymmdd的格式編號就是12，
但若要標示世紀的話，就要將12+100，要使用112的編號才能顯示 yyyymmdd。

Oracle HTTP Service 會自動安裝apache元件，開啟80port

最近在客戶那裡安裝oracle Server時，由於
Oracle HTTP Service 會自動安裝Apache元件，開啟80port，
而客戶在執行弱點掃描時，認為80port是安全性漏洞，所以要求要移除http service。

在移除http service後，發現無法完全移除http service，原因是因為Oracle OEM 工具也是需要http service的元件，所以一開始在安裝時，選擇自訂安裝，要disable 下列兩項安裝選項:
A. Disable --Oracle Enterprise Manager Products 9.2.0.1
B．Disable --Oracle HTTP Service 9.2.0.1
這樣才能完全不裝Apache的元件，並通過弱點掃描。

Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

metalink doc. about ORA-01000 error

Subject:
Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

Doc ID:
76684.1
Type:
BULLETIN

Modified Date:
13-JUN-2008
Status:
PUBLISHED
"Checked for relevance on 08-Oct-2007"
Overview
--------
This article contains information on how different cursors are managed and
monitored in PL/SQL. It addresses issues with the open_cursors parameter,
as well as the v$open_cursor view in the context of implicit, declared, and
dynamic cursors.
Monitoring and Managing Open Cursors
------------------------------------
The number of open cursors can limit operation in PL/SQL procedures and
SQL*Plus sessions. While the parameter open_cursors sets the limit,
programming issues can cause the following error:
ORA-1000 maximum open cursors exceeded
Three important values are the following:
o init.ora parameter open_cursors
o v$open_cursor view
o v$sysstat view
These values are similar, but differ in their accounting of Dynamic Cursors.
Note: Dynamic cursors are those opened using dbms_sql.open_cursor().
The means to compute those values are as follows:
>>> View v$open_cursor
'select count(*) from v$open_cursor' =>
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors PARSED and NOT CLOSED.
- Accumulates dynamic cursors PARSED and NOT CLOSED over a session
- Available to system/manager
- Includes the text of open cursors - helpful for debugging
- Since this view does not track unparsed (but opened) dynamic cursors,
the count(*) may not show all cursors that count against open_cursors.
>>> View v$sysstat
'select value from v$sysstat where statistic# = 3' =>
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors OPENED.
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session
- Available to system/manager
- Since this view does track unparsed (but opened) dynamic cursors,
the statistic#3 shows all cursors that count against open_cursors.
>>> init.ora parameter open_cursors =
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors OPENED.
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session

ORA-1000
--------
The following are several items to check when encountering ORA-1000 in
PL/SQL:

1. Be sure that all dbms_sql cursors opened at DECLARE time are closed.
Every unclosed OPEN counts against open_cursors. The number of open
cursors can be determined as follows in SQL*Plus:
select value from v$sysstat where statistic# = 3

2. Be aware that v$open_cursor only tracks the CUMULATIVE number of
implicit + distinct explicit cursors in the procedure PLUS unclosed
dynamic cursors that have been PARSED in the session.
Note: It does not include any dynamic cursors that were opened but
not parsed.
The text of the parsed, open cursors can be determined as follows
in SQL*Plus:
select sql_text from v$open_cursor

3. Dynamic cursors persist from run-to-run in a session, but are not
closeable after a procedure has completed. This can accumulate and
error-out with open_cursors after a number of runs. They will not
appear in v$open_cursors after a session.
The following are two code snippets that can help diagnose ORA-1000. Text
lines are shown for each cursor.
-- snippet 1
declare
cursor opencur is select * from v$open_cursor;
ccount number;
begin
select count(*) into ccount from v$open_cursor;
dbms_output.put_line(' Num cursors open is 'ccount);
ccount := 0;
-- get text of open/parsed cursors
for vcur in opencur loop
ccount := ccount + 1;
dbms_output.put_line(' Cursor #'ccount);
dbms_output.put_line(' text: ' vcur.sql_text);
end loop;
end;
-- snippet 2
select value, name from v$sysstat where statistic# in (2,3);
Related Information
-------------------
"Oracle 8i Reference [Data Dictionary]", (A67790-01) page 3-70, page 3-119
Note 1012266.6 OVERVIEW OF ORA-01000 MAXIMUM NUMBER OF CURSORS
EXCEEDED
.

Toad 支援的Windows OS 及versions for Oracle

目前在windows 2008 64bit Standard Edition (With Oracle Client for 64bit 10.2.0.4)安裝toad 9.7後，在執行toad connection欲連線資料庫時，出現一個警示視窗，說未安裝oracle clients。

google了一下~在toad官網找不到相關的os supported及versions說明~但在wiki 上找到一段說明:

Supported Operating Systems and Versions
TOAD for Oracle 9.7 is available for Windows 32-bit platform 2000/XP/2003/Vista and for Windows 64-bit platform 2003/XP/Vista with 32-bit Oracle Client. For good performance it is enough to have 512 MB RAM whilst for 1GB workstation it presents platform for very comfortable work. Concerning the disk space, it depends on the choice of your distribution: for example, for Expert edition it will take between 75MB and 100MB. When it comes to supported versions of Oracle databases almost all the versions are supported, from 7.3.4 to the last 11g database. To connect you can use clients for Oracle SQL Net, Net 8 (32-bit only) Net 9, Net 10, Net 11 or Instant Client.

不確定這是否就是toad無法連接資料庫的原因？

[Oracle]IMP-00058&ORA-12541 error.

After create a new database ... I met an error when I tried to import data into the new database.



------------------------------------------

OS: Windows 2003 Enterprise Edition

CPU:2.80GHz

RAM:3.75GB

------------------------------------------



The error code as following:



imp bbvsvc/bbvsvc@testw
Import: Release 9.2.0.3.0 - Production on 星期三 2月 4 16:23:31 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
IMP-00058: ORACLE 錯誤 12541

發生ORA-12541: TNS:沒有監聽器

IMP-00000: 匯入作業異常終止



I checked the tnsname.ora and listener.ora, them seems okay...

So, I don't know why it happened...







following shows the part of tnsnames.ora contents:



testw =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.32)(PORT = 1521))

)

(CONNECT_DATA = (SERVICE_NAME = testw)

)

)



and here is the part of Listener.ora contents:



LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))

)

(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)



SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = O:\oracle\ora92)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = TSCSVCT)

(ORACLE_HOME = O:\oracle\ora92)

(SID_NAME = TSCSVCT)

)

)

Already dealed with...

There are a client and a server on the machine. So ...

The TNS_ADMIN is not O:\oracle\ora92client\network\admin, the correct path is "O:\oracle\ora92\network\admin\".

And the connection information is no correctly, especially "Port" number on the tnsnames.ora.

Changed the Port number from "1526" to "1521", then the Export data process can run smoothly.

小信豬的原始部落: [Oracle] 多國語系的處理

朋友分享的~非常有用！

小信豬的原始部落: [Oracle] 多國語系的處理