Category Archives: oracle

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.

LinuxでOracleのポート番号1521を変更する

1.リスナーの停止
$ lsnrctl status
$ lsnrctl stop
2.ポートを変更
$ vi $ORACLE_HOME/network/admin/listener.ora
$ sqlplus / as sysdba
> show parameter local_listener
> alter system set local_listener=”(address = (protocol = tcp)(host = 172.17.100.101)(port = 9998))”;

> show parameter local_listener

3.リスナーの起動
$ lsnrctl start
$ netstat -an|grep 9998

4.ファイアウォールを開放
$ su – root
# /sbin/iptables -I INPUT -p tcp –dport 9998 -j ACCEPT
# /etc/rc.d/init.d/iptables save

oracle 10gにユーザとテーブルスペースを作成する

1.データベースに接続
# su – oracle
$ conn / as sysdba

2.テーブルスペースを作成
SQL> create tablespace lijiastartnews datafile ‘/lijiastartnews/oracle/oradata/lijiastartnews.dbf’ size 1024M autoextend on next 1024M maxsize 10240M extent management local;
3.ユーザを作成
SQL> create user lijiastartnewsname identified by lijiastartnewspasswd default tablespace lijiastartnews;

4.権限を設定
SQL> grant connect, resource to lijiastartnews;

5.テーブルスペース空間を設定
SQL> grant unlimited tablespace to lijiastartnews;

6.ユーザをスイッチ
SQL> conn lijiastartnews/lijiastartnews;

「Oracle学習」文字列をbase64でエンコード/デコードする方法

1. base64のdecode関数
select utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(‘dGVzdA==’))) from dual

2. base64のencode関数
select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(‘arkgame.com’))) from dual

MyBatis insert操作に主キーの戻り値を取得する方法

1.SqlServer2005の定義
create table Dic_City (
ID int identity,
City_Code varchar(10) not null,
Provinces_Code varchar(20) not null,
State_Code varchar(10) not null,
City_Name varchar(50) null,
PY_Code varchar(50) null,
PY_Code_Short varchar(10) null,
Ext1 varchar(20) null,
Ext2 varchar(20) null,
Ext3 varchar(20) null,
Ext4 varchar(20) null,
Ext5 varchar(20) null,
constraint PK_DIC_CITY primary key (ID)
)

2.MyBatisXMLの配置

配置方法1
<insert id=”insert” parameterType=”ja.softsea.model.DicCity” >
<selectKey resultType=”java.lang.Integer” keyProperty=”id” order=”AFTER” >
SELECT @@IDENTITY
</selectKey>
insert into Dic_City (City_Code, Provinces_Code,
State_Code, City_Name, PY_Code,
PY_Code_Short, Ext1, Ext2,
Ext3, Ext4, Ext5)
values (#{cityCode,jdbcType=VARCHAR}, #{provincesCode,jdbcType=VARCHAR},
#{stateCode,jdbcType=VARCHAR}, #{cityName,jdbcType=VARCHAR}, #{pyCode,jdbcType=VARCHAR},
#{pyCodeShort,jdbcType=VARCHAR}, #{ext1,jdbcType=VARCHAR}, #{ext2,jdbcType=VARCHAR},
#{ext3,jdbcType=VARCHAR}, #{ext4,jdbcType=VARCHAR}, #{ext5,jdbcType=VARCHAR})
</insert>
配置方法2
<insert id=”insert” parameterType=”cn.softsea.model.DicCity” useGeneratedKeys=”true” keyProperty=”id”>
insert into Dic_City (City_Code, Provinces_Code,
State_Code, City_Name, PY_Code,
PY_Code_Short, Ext1, Ext2,
Ext3, Ext4, Ext5)
values (#{cityCode,jdbcType=VARCHAR}, #{provincesCode,jdbcType=VARCHAR},
#{stateCode,jdbcType=VARCHAR}, #{cityName,jdbcType=VARCHAR}, #{pyCode,jdbcType=VARCHAR},
#{pyCodeShort,jdbcType=VARCHAR}, #{ext1,jdbcType=VARCHAR}, #{ext2,jdbcType=VARCHAR},
#{ext3,jdbcType=VARCHAR}, #{ext4,jdbcType=VARCHAR}, #{ext5,jdbcType=VARCHAR})
</insert>
3.Mapperを呼び出して、主キーを戻る
//挿入オブジェクトを作成
DicCity city = new DicCity();
city.setCityCode(“104003”);
city.setCityName(“東京都品川区”);
//mapperオブジェクトを取得
DicCityMapper cityMapper = (DicCityMapper) SpringContextUtil.getBean(“dicCityMapper”);
int row = cityMapper.insert(city);
System.out.println(“応答行目数:”+row);
//自動採番のID値
System.out.println(“新たに挿入されたID:”+city.getId());

ubuntu12.04でjdk7をインストールする

1.インストールじたopenjdk6をアンインストール
sudo apt-get purge openjdk*
sudo apt-get purge openjdksudo apt-get purge openjdk

2.Oracle JDK7のダウンロード
http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html
tar -xvf jdk-7u60-linux-x64.tar.gz

3.インストールディレクトリ
/ejbca/jdk1.7.0_60
vim /etc/profile
sudo gedit /etc/profile

ファイルの最後に次の内容を追加
export JAVA_HOME=/ejbca/jdk1.7.0_60
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export CLASSPATH=$CLASSPATH:.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

4.3の内容を保存終了、次のコマンドを実行してデフォルトJDKを設定
sudo update-alternatives –install /usr/bin/java java /ejbca/jdk1.7.0_60/bin/java 300
sudo update-alternatives –install /usr/bin/javac javac /ejbca/jdk1.7.0_60/bin/javac 300
source /etc/profile

ターミナルには、次のように入力する
java -version

内容の表示:
java version “1.7.0_60”
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) 64-Bit Server VM (build 24.60-b09, mixed mode)

CentOS 6でOracle 11gをインストール、設定

1.VNC Serverをインストール

# yum install tigervnc-server
# yum groupinstall “General Purpose Desktop” “Desktop Platform”
# yum install xorg-x11-server-utils

2.VNC serverを設定
# vim /etc/sysconfig/vncservers
VNCSERVERS=”2:root”
VNCSERVERARGS[2]=”-geometry 1440×900″
# vncpasswd  vncユーザーrootのパスワードを設定
# vim ~/.vnc/xstartup
#!/bin/sh

[ -r /etc/sysconfig/i18n ] && . /etc/sysconfig/i18n
export LANG
export SYSFONT
vncconfig -iconic &
unset SESSION_MANAGER
unset DBUS_SESSION_BUS_ADDRESS
OS=`uname -s`
if [ $OS = ‘Linux’ ]; then
case “$WINDOWMANAGER” in
*gnome*)
if [ -e /etc/SuSE-release ]; then
PATH=$PATH:/opt/gnome/bin
export PATH
fi
;;
esac
fi
if [ -x /etc/X11/xinit/xinitrc ]; then
exec /etc/X11/xinit/xinitrc
fi
if [ -f /etc/X11/xinit/xinitrc ]; then
exec sh /etc/X11/xinit/xinitrc
fi
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
#xterm -geometry 80×24+10+10 -ls -title “$VNCDESKTOP Desktop” &
#twm & //内容をコメントアウトする
exec gnome-session &
# /etc/init.d/vncserver restart //vncserverが再起動
3 Oracleをインストール
依存パッケージ

RHEL/CentOs 6.x
# cd /etc/yum.repos.d
# wget https://public-yum.oracle.com/public-yum-ol6.repo
# wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

RHEL/CentOs 5.x
# cd /etc/yum.repos.d
# wget https://public-yum.oracle.com/public-yum-el5.repo
# wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

RHEL/CentOs 4.x
# cd /etc/yum.repos.d
# wget https://public-yum.oracle.com/public-yum-el4.repo
# wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-el4 -O /usr/share/rhn/RPM-GPG-KEY-oracle

 

必要なパッケージをインストール
# yum install oracle-rdbms-server-11gR2-preinstall
環境変数を設定
# vim /etc/hosts
10.0.121.232 startnews24_db1

# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle
# vim /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240

# su – oracle
# vim .bash_profile
export ORACLE_BASE=/data/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_OWNER=oracle
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

# mkdir /data/app
# chown -R oracle:oinstall /data/app

 
# wget ftp://ftp.pbone.net/mirror/www.whiteboxlinux.org/whitebox/4/en/os/x86_64/WhiteBox/RPMS/pdksh-5.2.14-30.x86_64.rpm
# rpm -ivh pdksh-5.2.14-30.x86_64.rpm

「SinkRunner-PollingRunner-DefaultSinkProcessor」の対策

エラーメッセージ:
Exception in thread “SinkRunner-PollingRunner-DefaultSinkProcessor” java.lang.OutOfMemoryError

対策:
修正前:maxOpenFile  5000
修正後:maxOpenFile  500

参考URL:

https://issues.apache.org/jira/browse/FLUME-1850

3 / 41234