ORACLE

Product detail

• Product by edition and additional products(jp)
  • https://docs.oracle.com/cd/E16338_01/license.112/b56284/editions.htm#CJACGHEB

Management

Start and stop

• Starting Oracle service
  • Switching Oracle Account
    • root@localhost#>su - oracle
  • execute sqlplus
    • oracle@localhost$>sqlplus / as sysdba
  • start oracle instance
    • SQL>startup
  • exit from sqlplus
    • SQL>quit
  • startup listener
    • oracle@localhost$>lsnrctl start
  • referral docs
    • kr : https://mkil.tistory.com/336
• Stop oracle service
  • su - oracle
    • change oracle account
  • emctl stop dbconsole
    • Stop oracle enterprise manager
  • lsnrctl stop
    • Stop listener
  • sqlplus / as sysdba
    • Login sqlplus
  • shutdown immediate
    • shutdown oracle process when waiting exist connection
  • shutdown abort
    • shutdown oracle process aborting all connection
    • use when take too long time on shutdown immediate command

Delete log files

• Listner log delete

$ adrci
adrci> show homepath
adrci> show control
adrci> purge -age 50000 -type alert

• -aget 50000 : 50000min.
• when purge, deleted xml log files.

$> lsnrctl
LSNRCTL> show log_status
LSNRCTL> show log_directory
LSNRCTL> show log_file
LSNRCTL> set log_status off
$> mv <log_file> <bak_file>
LSNRCTL> set log_status on

• Select Log

select 
originating_timestamp,
module_id,
message_text
from v$diag_alert_ext

• http://ichannel.tokyo/technoracle/%E3%82%A2%E3%83%A9%E3%83%BC%E3%83%88%E3%83%AD%E3%82%B0%E3%81%AE%E6%A4%9C%E7%B4%A2%E6%96%B9%E6%B3%95/2752/

Backup and Restore

• Oracle Backup and Restore

Tuning

• https://github.com/LowyShin/KnowledgeBase/wiki/ORACLETuning

Initialize

• NLS_LANG

●UTF8
NLS_LANG=Japanese_Japan.AL32UTF8

●SJIS
NLS_LANG=Japanese_Japan.JA16SJIS

●EUC
NLS_LANG=Japanese_Japan.JA16EUC

※DatabaseをUS7ASCIIに設定して日本語を入れてもDBには正常に記録されるが、読み取りの際にUS7ASCIIにしてもORACLEの仕様上勝手にUTF8に変更される。これはOCI経由ではNLS_LANGパラメータを利用してクライアントのCharactersetを強制に指定できるが、Direct ConnectまたはODBC、JDBCを経由するとUnicodeに変換され文字化けになってしまう。なので文字化けした場合には必ず次の内容をチェックする

• OCIがサポートするクライアントツール
• NLS_LANG = US7ASCIIになっている(ClientまたはOS Parameter)
• クライアントツール側に自動変換をOFFにしている

Useful SQL

• Japanese Traditional datetime
  • https://www.shift-the-oracle.com/sql/functions/to_date.html

SQL> select to_char(SYSDATE, 'EEDL', 'NLS_CALENDAR=''JAPANESE IMPERIAL''')
  2  from dual;
 
TO_CHAR(DATE'1900-01-01','EEDL','NLS_CALEN
------------------------------------------
明治33年1月1日 月曜日

• DateDiff

select dtA - dtB as sDatediff from TableA;

• https://www.shift-the-oracle.com/sql/functions/function-base-reference-2.html

• Create DDL script

select dbms_metadata.get_ddl('TABLE','TableName','Owner') from dual;
select dbms_metadata.get_ddl('Procedure','ProcedureName','Owner') from dual;

• Find locked SQL

select * from v$lock where block = 1;

• Update Join
  • http://www.mitene.or.jp/~rnk/TIPS_ORCL_UPDATE.htm
  • https://codeday.me/jp/qa/20190309/382305.html
    • 11g Versionでは使えなかった。

Table/View/Procedure/Function structure

• https://devlights.hatenablog.com/entry/20091005/p1
  • user_tables -> all_tables のようにuserをallに変えたら異なるユーザーが作成したリソースが確認できる。

Ranking

• https://sql-oracle.com/?p=1246

SELECT stu_no,activity,score,
 RANK() OVER (ORDER BY score) score_rank
FROM student
ORDER BY score_rank;

• split field by delimeter character
  • https://www.shift-the-oracle.com/sql/functions/strtoken.html

CREATE OR REPLACE FUNCTION STRTOKEN(
	P_STRING VARCHAR2, P_DELIMIT VARCHAR2,
	P_POS POSITIVEN := 1, P_NTH POSITIVEN := 1,
	P_EOD VARCHAR2 := NULL)
RETURN VARCHAR2
IS
	vStartPos	PLS_INTEGER;
	vEndPos		PLS_INTEGER;
BEGIN
	IF (P_POS = 1) THEN
		vStartPos := 1;
	ELSE
		vStartPos := INSTR(P_STRING, P_DELIMIT, 1, P_POS - 1);
		IF (vStartPos = 0) THEN
			RETURN P_EOD;
		END IF;
		vStartPos := vStartPos + 1;
	END IF;
	vEndPos := INSTR(P_STRING, P_DELIMIT, vStartPos, P_NTH);
	IF (vEndPos = 0) THEN
		RETURN SUBSTR(P_STRING, vStartPos);
	END IF;
	RETURN SUBSTR(P_STRING, vStartPos, vEndPos - vStartPos);
END;
/

Functions

• REGEXP_SUBSTR
  • https://www.shift-the-oracle.com/sql/functions/regexp_substr.html

DB Link

• MySQL DB Link
  • https://qiita.com/mossi/items/d2a8c8c113738bee9f2d

Client

Freeware

• Oracle Instant Client
  • https://www.oracle.com/technetwork/jp/database/database-technologies/instant-client/overview/index.html

Shareware

• Orange Client
  • http://www.warevalley.com/solutions/orange.asp
• Toad
  • https://www.quest.com/products/toad-for-oracle/