Category Archives: oracle

DebianでOracle instant Clientクライアントをインストールする

1.ダウンロード
http://www.oracle.com/technetwork/topics/linuxsoft-082809.html

oracle-instantclient-basic-10.2.0.5-1.i386.rpm
oracle-instantclient-devel-10.2.0.5-1.i386.rpm
oracle-instantclient-jdbc-10.2.0.5-1.i386.rpm
oracle-instantclient-sqlplus-10.2.0.5-1.i386.rpm

2.alienのインストール
# apt-get install alien

3.rpmをインストール
#alien -i xxx.rpm

ORA_HOMEは「/usr/lib/oracle/10.2.0.5/client/」です。

4. /etc/ld.so.conf の末尾下記追加
/usr/lib/oracle/10.2.0.5/client/lib

実行コマンド
ldconfig
sqlplus

5. /etc/profileの末尾下記追加:
export ORACLE_HOME=/usr/lib/oracle/10.2.0.5/client/
export LD_LIBRARY_PATH=:$ORACLE_HOME/lib:/usr/local/lib:$LD_LIBRARY_PATH:.
export TNS_ADMIN=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin:.

実行コマンド
source /etc/profile
echo $ORACLE_HOME

Oracle入門–select構文で不等号(<>)とNullの検索結果

SQLコマンド
select * from scott.user t;  —-All data

select * from scott.user t where t.ctm != 800;–Not equal 800(Not contain null)

select * from scott.user t where t.ctm != 800 or t.ctm is null;–Not equal 800(Does contain null)

OracleでSIGN(n)とDEDECODE(VALUE, if1,then1,xx)の使い方

1.SIGN(n)
形式
n < 0 — -1
n = 0 — 0
n > 0 — 1

SQL構文
select sign(-8),sign(0),sign(8) from dual
—————————–
-1 0 1

2.DECODE(VALUE, if1,then1, if2,then2, if3,then3, DEFAULT)
SQL構文
select
level
, decode(
level
, 2
, ‘two’
, 5
, ‘five’
, 8
, ‘eight’
, 11
, ‘huhu’
, ‘student’
)
from
dual
CONNECT BY
LEVEL < 9
実行結果
———- ——-
LEVEL DECODE(LEVEL,2,’TWO’,5,’FIVE’,8,’EIGHT’,11,’HUHU’,’STUDENT’)
1 student
2 two
3 student
4 student
5 five
6 student
7 student
8 eight

3.SIGN()とDECODE()連合
LEVEL<6 –不合格
LEVEL<8 –普通
ELSE –優秀

SQL構文
SELECT
LEVEL
, DECODE(
– 1
, SIGN(LEVEL – 6)
, ‘不合格’
, SIGN(LEVEL – 8)
, ‘普通’
, ‘優秀’
)
FROM
DUAL
CONNECT BY
LEVEL < 10

実行結果
———————————————–
LEVEL DECODE(-1,SIGN(LEVEL-6),’不合格’,SIGN(LEVEL-8),’普通’,’優秀’)
1 不合格
2 不合格
3 不合格
4 不合格
5 不合格
6 普通
7 普通
8 優秀
9 優秀

Oracleで日付関数sysdateの使い方

SQL構文

select to_char(sysdate,’YYYY/MM/DD’) FROM DUAL; — 2016/09/02
select to_char(sysdate,’YYYY’) FROM DUAL; — 2016
select to_char(sysdate,’YYY’) FROM DUAL; — 016
select to_char(sysdate,’YY’) FROM DUAL; — 16
select to_char(sysdate,’MM’) FROM DUAL; — 09
select to_char(sysdate,’DD’) FROM DUAL; — 02
select to_char(sysdate,’D’) FROM DUAL; — 6
select to_char(sysdate,’DDD’) FROM DUAL; — 246
select to_char(sysdate,’WW’) FROM DUAL; — 36
select to_char(sysdate,’W’) FROM DUAL; — 1
select to_char(sysdate,’YYYY/MM/DD HH24:MI:SS’) FROM DUAL; — 2016/09/02 13:51:09
select to_char(sysdate,’YYYY/MM/DD HH:MI:SS’) FROM DUAL; — 2016/09/02 01:51:37
select to_char(sysdate,’J’) FROM DUAL; — 2457634
select to_char(sysdate,’RR/MM/DD’) FROM DUAL; –16/09/02

Oracleでround関数(四捨五入)とtrunc関数(切り捨て) の使い方

1.数字操作
sql>select trunc(6.32), trunc(6.32,1), trunc(6.32,-1) from dual
—————-
trunc(6.32), trunc(6.32,1), trunc(6.32,-1)
6 6.3 0

sql>select round(6.32), round(6.32,1), round(6.32,-1) from dual
—————-
round(6.32), round(6.32,1), round(6.32,-1)
6 6.3 10

2.日付操作
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) now_Date,
to_char(round(sysdate),’yyyy-mm-dd hh24:mi:ss’) rdnow_Date,
to_char(trunc(sysdate),’yyyy-mm-dd hh24:mi:ss’) truncnow_Date from dual
—————-
now_Date        rdnow_Date         truncnow_Date
2016-09-02 11:00:09 2016-09-02 00:00:00 2016-09-02 00:00:00

3.trun関数のサンプル
①SELECT TRUNC(TO_DATE(‘2016-09-02 12:00:01′,’YYYY-MM-DD hh24:mi:ss’)) from DUAL
—————-
2016-09-02

②SELECT TRUNC(TO_DATE(‘2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’D’)
FROM DUAL;
—————-
2016-08-28

③SELECT TRUNC(TO_DATE(‘2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’D’)+1 FROM DUAL;
—————-
2016-08-29

④SELECT TRUNC(TO_DATE(‘2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’MM’) FROM DUAL;
—————-
2016-09-01

⑤SELECT TRUNC(TO_DATE(‘2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’MM’)-1 FROM DUAL;
—————-
2016-08-31

⑥SELECT TRUNC(TO_DATE(‘2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’Y’) FROM DUAL;
—————-
16-01-01

⑧SELECT TRUNC(TO_DATE(‘2016-09-02 12:59:59′,’YYYY-MM-DD hh24:mi:ss’),’Y’)-1 FROM DUAL;
—————————————————————–
15-12-31
4.round関数のサンプル
select round(770.456, 0) from dual; –770
select round(770.456, 1) from dual; –770.5
select round(770.456, 2) from dual; –770.46
select round(770.456, 3) from dual; –770.456
select round(-770.456, 2) from dual; — -770.46

Oracleでスキーマ(schema)の使い方

操作コマンド
SQL> Gruant dba to scott
SQL> create tablekoyolist(name char(15));
Table created.

SQL> create table system.user_infotbl(name char(15));
Table created.

SQL> insert intokoyolist values(‘scott’);
1 row created.

SQL> insert into system.user_infotbl values(‘system’);
1 row created.

SQL> commit;
Commit complete.

SQL> conn system/manager
Connected.

SQL> select * fromkoyolist;
NAME
———-
system

SQL> ALTER SESSION SET CURRENT_SCHEMA = scott;
Session altered.

SQL> select * fromkoyolist;
NAME
———-
scott

SQL> select owner ,table_name from dba_tables where table_name=upper(‘user_infotbl’);
OWNER TABLE_NAME
—————————— ——————————
SCOTTkoyolist
SYSTEMkoyolist

Oracleでユーザのアカウントのロックを解除、temp表領域を作成する方法

1.sysユーザとしてログイン
SQL> alter user mpcuser account unlock;

2.temp表領域を作成
create temporary tablespace kanhu_temp11 tempfile ‘/home/oracle/oradata/mpcsuat/kan_temp.dbf’ size 50M autoextend on next 150M

Oracleでテーブルロック(table lock)とsessionプロセス(process)を殺す(kill)

1.ロックされたテーブルを探す
select count(*) from v$locked_object;
select * from v$locked_object;

2.ロックされたテーブルの詳細情報を探す
select n.owner,n.object_name,m.session_id,m.locked_mode
from v$locked_object m,dba_objects n
where n.object_id = m.object_id;

3.sessionを探す
select n.username,n.sid,n.serial#,logon_time
from v$locked_object m,v$session n
where m.session_id = n.sid order by n.logon_time;

4.プロセスを殺す
alter system kill session’3020,34′;
sid->3030 serial#->34

Oracle SQL でdecode関数のorder byの使い方

SQL構文
SELECT kotost.adv_cd
FROM mg_ kotost kotost
ORDER BY decode(kotost.adv_cd,
‘COST’,
1,
‘INCOME’,
2,
3) ASC;

実行結果
17 COST
18 COST
19 COST
20 INCOME
21 INCOME
22 INCOME

OracleでDUAL表の使い方

1.現在ユーザを取得
select user from dual
—————————————– ——– —————————-
USER
STARTNEWS24USER88

2.システム時刻を取得
select to_char(SYSDATE,’yyyy-mm-dd hh24:mi:ss’) from dual
—————————————– ——– —————————-
TO_CHAR(SYSDATE,’YYYY-MM-DDHH24:MI:SS’)
2016-08-25 10:31:14

select sysdate from dual
—————————————– ——– —————————-
SYSDATE
16-08-25

3.ロケール(locale)を取得
select SYS_CONTEXT(‘USERENV’,’language’) from dual;
—————————————– ——– —————————-
SYS_CONTEXT(‘USERENV’,’LANGUAGE’)
JAPANESE_JAPAN.JA16SJIS

4.ランダム数字を取得
select SYS.DBMS_RANDOM.RANDOM from dual
—————————————– ——– —————————-
RANDOM
167899999

5.電卓として利用
select 2*3 from dual
—————————————– ——– —————————-
2*3
6.dualのオブジェクト名とオブジェクトタイプを取得
select owner, object_name , object_type from dba_objects where object_name like ‘%DUAL%’; OWNER OBJECT_NAME OBJECT_TYPE
—————————————– ——– —————————-
OWNER OBJECT_NAME OBJECT_TYPE
SYS DUAL TABLE
PUBLIC DUAL SYNONYM

select dummy from dual
—————————————– ——– —————————-
DUMMY
X

7.DUALの英語ドキュメント
There is internalized code that makes this happen. Code checks that ensure that a table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrect but this is now an obsolete product. The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1). This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other prebuilt or application functions. If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception. So DUAL should ALWAYS have 1 and only 1 row.