ORACLE

Product detail

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

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

Backup and Restore

• Cumulate differential backup and restore(jp)
  • https://www.atmarkit.co.jp/ait/articles/0611/02/news126_2.html
  • https://www.oracle.com/technetwork/jp/database/articles/kusakabe/kusakabe-8-5172663-ja.html
• Oracle stream(replication) official documentation
  • https://docs.oracle.com/cd/E16338_01/server.112/b61351/toc.htm
• Oracle stream (jp)
  • https://blogs.oracle.com/supportjp/oracle-database-oracle-streams

Useful SQL

• SQL on dbms memory

SELECT
  s.sid, s.serial#, s.status, s.machine, s.osuser, s.module, s.username, s.process, p.program,
  a.sql_text
FROM v$session s,
     v$sqlarea a,
     v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
  AND s.PREV_SQL_ADDR = a.address
  AND s.paddr = p.addr
order by SID
;

* SQL on dbms memory sorting executed count
```sql
select count(1) as CntSQL
    , SQL_TEXT
from v$sqlarea a
group by SQL_TEXT
order by count(1) desc
;

• all table size
  • https://www.zanmai.net/blog/data/252.html

SELECT 
      a.table_name
      ,a.num_rows
      , b.MB
FROM all_tables a
      , (Select Segment_Name,Sum(bytes)/1024/1024 MB From all_segments Group By Segment_Name) b
WHERE a.table_name = b.Segment_Name
ORDER BY a.num_rows desc, b.MB desc;

• Update Join

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

• Tablespace management

  • official tablespace management
    • https://docs.oracle.com/cd/E57425_01/121/ADMIN/tspaces.htm#GUID-FB734C25-BA3A-45E8-BDCE-77C668140B67
  • https://doracle.tistory.com/48
    • tablespace auto resize, manual resize
  • http://kaludin.egloos.com/v/2919398

• 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;
/

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/