DB] oracle 에 있는 sign() 의 기능

오라클에 있는  SIGN이라는 함수의 기능은 원하는 결과값(?!)이 양수 / 음수 / 0 이냐에 따라서 값을 반환해주는 함수.

예)
SELECT SIGN(1+1) a, SIGN(1-1)  b, SIGN(1-5) c
FROM DUAL;

결과
---------------------------

a | b | c
==========
1 | 0 | -1


추가적인 예)
필드의 값이 100 이상인 것의 합과 미만인 것의 합을 구하는 쿼리.
출처 : http://ssiso.net/cafe/club/club1/board1/content.php?board_code=oracle%7Coratip&idx=31597&club=oracle

SELECT SUM(CASE SIGN(A-100)

                         WHEN 0 THEN A

                         WHEN 1 THEN A

                         ELSE 0

                     END)                      AS OVER100,

             SUM(CASE SIGN(A-100)

                         WHEN -1 THEN A

                         ELSE 0

                     END)                      AS DOWN100

    FROM DUAL;

DB2] locking 정보 확인

-- db2 locking 정보 확인.  

SELECT lock_name,
  hld_member,
  lock_status,
  hld_application_handle
FROM  TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2))

오라클] DB User 별 권한 할당 관계 확인하는 쿼리

/* --------------------------------
    용도        : DB User 별 권한 할당 관계 확인하는 쿼리
    작업순서    : SYS(DBA권한)으로 로그인 해서 작업
    update Date : 2009.04.29
    update By   : 정봉수
-------------------------------- */
select  OWNER         --// 소유자
, grantee          --// 권한을 부여 받은 사용자
, MAX(decode(privilege, 'INSERT', 'O','')) PRIV_INS  --// INSERT 권한
, MAX(decode(privilege, 'UPDATE', 'O','')) PRIV_UPD  --// UPDATE 권한
, MAX(decode(privilege, 'DELETE', 'O','')) PRIV_DEL  --// DELETE 권한
, MAX(decode(privilege, 'SELECT', 'O','')) PRIV_SEL  --// SELECT 권한 
, MAX(decode(privilege, 'EXECUTE', 'O','')) PRIV_EXE  --// EXECUTE 권한
from dba_tab_privs
where owner NOT LIKE '%SYS%' --// SYS 사용자는 제외하기 위한 조건
GROUP BY OWNER, GRANTEE
ORDER BY OWNER
;

FreeBSD/MySQL]FreeBSD5.3.1R 에서 mysql4.x 설치하기

이글은 FreeBSD 5.3R 에서의 포트시스템으로의 설치를 기본으로 합니다.
1. 디렉토리 이동
# cd /usr/ports/databases/mysql41-server

--> mysql 버전이 5.x 가 있지만 proftpd포트 설치시 mysql4.x 클라이언트

를 설치하므로 버전을 맞추기 위해 4.x 를 설치하기로 합니다.

# make install clean WITH_CHARSET=euc_kr BUILD_OPTIMIZED=yes

(

mysql41-server 의 경우 WITH_CHARSET옵션의 값이 euckr로 변경됨.

mysql 4.1버전의 경우

# make install clean WITH_CHARSET=euckr BUILD_OPTIMIZED=yes

)

# /usr/local/bin/mysql_install_db

==> 처음으로 mysql을 사용하기 전에 시스템 DB를 만들기 위한 스크립트를

실행합니다.

위 명령은 mysql을 설치한 후 반드시 한번만 하시기 바랍니다.



2. 설치 확인 작업

# /usr/local/bin : mysql바이너리 파일들이 있나 확인

# /usr/local/etc/rc.d/mysql-server.sh : mysql 기동 스크립트

# /var/db/mysql : mysql 데이터베이스 파일



3. 데몬 시작및 중지하기

3.1. 시작하기

# /usr/local/etc/rc.d/mysql-server.sh start 혹은

/usr/local/bin/mysqld_safe -user=mysql &



3.2 중지하기

# /usr/local/etc/rc.d/mysql-server.sh stop 혹은

/usr/local/bin/mysqladmin -u root shutdown



4. mysql 데이터베이스를 사용하기 전에

반드시 루트 암호를 생성하고 사용하시기 바랍니다.

#/usr/local/bin/mysqladmin -u root password '새로운 비밀번호'

#/usr/local/bin/mysqladmin -u root -h 호스트명 password '새로운 비밀번호'



5. 데이터베이스 사용하기

#/usr/local/bin/mysql -u root -p

password :




** 만약 위와같이 작업시 에러발생하는 경우


4번 작업시 에러발생시에는

1. 우선 mysql서버 데몬을 죽인다.

# kill mysql_pid 혹은

/usr/local/etc/rc.d/mysql-server.sh stop



2. /usr/local/etc/rc.d/mysql-server.sh 파일을 편집해서

========================

편 집내용.

/usr/local/bin/mysqld_safe --user=mysql --datadir=${DB_DIR}
--pid-file=${PIDFILE} --language=korean > /dev/null &

를 다음과 같이 수정합니다 .

/usr/local/bin/mysqld_safe --user=mysql --datadir=${DB_DIR}
--pid-file=${PIDFILE} --language=korean --skip-grant > /dev/null &



========================



3. mysql서버 데몬을 다시 시작한다.

# /usr/local/etc/rc.d/mysql-server.sh start



4. 위와같이 하면 권한 테이블을 사용하고 데몬을 띄우게 됩니다.

5. mysql에 접속하여

# /usr/local/bin/mysql -u root -p

6. root의 암호를 변경합니다.

mysql> use mysql;

mysql> select * from user where user = 'root';

mysql> update user SET password = PASSWORD('newpassword');

7. 권한 테이블을 다시 읽는다.

mysql> flush privileges;

8. mysql을 종료

mysql> \q



9. mysql 서버 데몬 죽입니다.

#/usr/local/etc/rc.d/mysql-server.sh stop



10./usr/local/etc/rc.d/mysql-server.sh 파일을 편집해서

========================

편집내용.

/usr/local/bin/mysqld_safe --user=mysql --datadir=${DB_DIR}
--pid-file=${PIDFILE} -language=korean --skip-grant > /dev/null &

를 다음과 같이 수정합니다 .

/usr/local/bin/mysqld_safe --user=mysql --datadir=${DB_DIR}
--pid-file=${PIDFILE} -language=korean > /dev/null &



========================

다 시 원상복귀 합니다.



11. mysql 서버를 다시 시작합니다.

#/usr/local/etc/rc.d/mysql-server.sh start



12. 정상적으로 mysql을 사용가능합니다.

DB/MySQL] mysql 유지보수 MYD,MYI,FRM

[[[ SQL 자료의 이전과 dump ]]]
table dump (특정 테이블을 sql 문으로 저장하기)
./mysqldump -u ID -p DBname tablename > tablename.sql
-------------------------------------------------------------------------
특정 table 설치
./mysql -u ID -p DBname < table.sql

--------------------------------------------------------------------------
특정 테이블 지우기
./mysql -u DBname -p
./use DB
./drop table tablename;

---------------------------------------------------------------------------
DB 을 모두 sql Dump 백업
./mysqldump -u ID -p DBname > ***.sql
./mysql -u ID -p DB < ***.sql

***********************************************************
 [[[[[[[[[[[ 테이블의 유지 보수 ]]]]]]]]]]]]]]]]]]]]]]
전원중단,비정상종료,frm화일 삭제수정등에 의한 DB 이상
mysql 의 테이블 검사 및 오류를 수정하는 유틸리티
* myisamchk 를 사용시 mysql를 종료한다.
* 테이블은 3개 화일 : frm, MYI, MYD 파일을 검사한다.
   frm(테이블구조기록), MYI(테이블인덱스화일), MYD (데이터화일)
------------------------------------------------------------------------
myisamchk table명 (table 조회 검색)
myisamchk --recover --quick table명 (빠른복구)
myisamchk --recover table명 (복구)
myisamchk --safe-recover table명 (재오류시)

-------------------------------------------------------------------------
복 구되지 않는 경우
-테이블구조가 기록된 frm 파일이 삭제 또는 손상
-테이블 인덱스 파일은 MYI 삭제 손상된 경우
-MYD 는 데이터 파일

---------------------------------------------------------------------------
myisamchk -r table명
(테이블 파일의 빈공간등을 없에 최적화 시킨다.)
-a : 흩어진 인덱스를 모아 속도 향상
-S : 인덱스트리 소팅 검색 속도 향상
-R : 인덱스 기준 레코드 정렬 속도 향상
      myisamchk -R1 tablename
      * 1번 인덱스 기준 레코드 정렬

-----------------------------------------------------------------
[[[[[[ EXCEL 에 있는 자료 mysql로 이전하기 ]]]]]]]
1. 엑셀의 화일을 파일형식-텍스트(탭으로분리)로 선택 저장
    * sample.txt
  ex) 허정수  A  경기도
        이효진  B  구리시
2. FTP 로 전송후 LOAD DATA INFILE 명령으로 테이블 입력
    * 미리 테이블이 생성되어 있어야 한다.
    * 해당 txt 에 맞게 테이블을 만들어 주세요!
3.LOAD DATA INFILE 'sample.txt'  INTO TABLE sample
   LINES  TERMINATED BY '\r\n' ;
   * '\r\n' 줄구분차이 도스 윈도우
4. select * from sample;


----
복구하는 방법은 ...
mysql data 디렉토리에 그냥 같다가 놓는겁니다 ^^

.frm 파일은 테이블 구조가 저장되어 있는 파일입니다
.MYD 파일은 실제 데이터가 들어있는 파일입니다
.MYI 파일은 Index 정보가 들어가 있는 파일입니다

위 3개의 파일을 손상되지 않은 상태에서 가지고 계신다면
그냥 갖다가 놓는것 만으로 복구하실 수 있습니다
단, 퍼미션 조절은 해주셔야겠지요. 퍼미션 조절 안하시면 나중에 DB 파일을 읽어오지 못합니다

그리고 저 파일들을 .sql 형태로 변환하는 방법은 없습니다
적 어도 제가 알기론요..
다만
백업받으실때 .sql 형태로 백업받을 수는 있습니다

mysqldump -A -u root -p > mysql_bakup_all.sql

이렇게 하시면 모든 데이터베이스를 .sql 형태로 백업받게 됩니다
특 정 DB 만, 혹은 특정 Table 만 백업받는 방법은
Mysql 메뉴얼을 참조하시구요..
더 자세한 답변은 여기 계신 고수분들께서 해주실겁니다
그리고
database.sarang.net 사이트로 한번 들려보세요

DB] DB2에서 merge into 사용

merge 문장의 문법 :

merge into tableA  a
using (
select * from tableB
) b
  on (   a.aaa = b.bbb  --// 조회 조건.
     )
 when matched then
                      update 문장. <== tableA에 업데이트할 항목 나열
when not matched then
                      insert 문장.   <== tableA에 insert 할 항목 나열.
else ignore;


설명 :
 merge into 문장은 insert / update 작업을 한번에 할 수 있는 장점이 있는 sql 구문이다.
그러나, 사용상 주의점은 b 로 묶인 select 문장의 조회 결과가 없는 경우, when 조건절에 걸리지 않는 상황이 발생하는걸 경험했다.


현재까지의 결론.
 1. merge into 는 tableA 에 대해 insert / update 작업을 한번에 수행한다. 
    단, a.aaa = b.bbb 가 일치하는 항목에 대해.
 2. merge into 는 "select * from tableB" 에서 조회 결과가 없으면 when ... 을 무시한다. 
    결과적으로 else ignore 가 실행되는 듯 하다. 

DBMS/오라클] 스키마 정보 확인

 
  select * --column_name, data_type, data_length, data_precision, nullable, default_length, data_default
  from user_tab_columns@db_name
  where table_name='NPORDERMST'
  order by column_id
  ;
 
  /
 
  select table_name,column_name from user_tab_columns
  where table_name='EMP';  

DBMS/오라클] job 수행 시간 및 간격, 실행 건수 확인

-- sys user에서 작업
-- job 수행 시간 및 간격, 실행 건수 확인
--
select schema_user db_user
    , what as job_proc
    , last_date
    , last_sec
    , next_date
    , next_sec
    , total_time pros_time
    , broken
    , interval
    , failures
from dba_jobs
order by schema_user, what
;

DBMS/오라클] copy_t 테이블 및 데이터 생성

--// 오라클에서 copy_t 테이블 및 데이터 입력 쿼리문
create table copy_t(no number, no2 varchar2(2)) nologging;

insert into copy_t values (1 ,'01');
insert into copy_t values (2 ,'02');
insert into copy_t values (3 ,'03');
insert into copy_t values (4 ,'04');
insert into copy_t values (5 ,'05');
insert into copy_t values (6 ,'06');
insert into copy_t values (7 ,'07');
insert into copy_t values (8 ,'08');
insert into copy_t values (9 ,'09');
insert into copy_t values (10 ,'10');
insert into copy_t values (11 ,'11');
insert into copy_t values (12 ,'12');
insert into copy_t values (13 ,'13');
insert into copy_t values (14 ,'14');
insert into copy_t values (15 ,'15');
insert into copy_t values (16 ,'16');
insert into copy_t values (17 ,'17');
insert into copy_t values (18 ,'18');
insert into copy_t values (19 ,'19');
insert into copy_t values (20 ,'20');
insert into copy_t values (21 ,'21');
insert into copy_t values (22 ,'22');
insert into copy_t values (23 ,'23');
insert into copy_t values (24 ,'24');
insert into copy_t values (25 ,'25');
insert into copy_t values (26 ,'26');
insert into copy_t values (27 ,'27');
insert into copy_t values (28 ,'28');
insert into copy_t values (29 ,'29');
insert into copy_t values (30 ,'30');
insert into copy_t values (31 ,'31');
insert into copy_t values (32 ,'32');
insert into copy_t values (33 ,'33');
insert into copy_t values (34 ,'34');
insert into copy_t values (35 ,'35');
insert into copy_t values (36 ,'36');
insert into copy_t values (37 ,'37');
insert into copy_t values (38 ,'38');
insert into copy_t values (39 ,'39');
insert into copy_t values (40 ,'40');
insert into copy_t values (41 ,'41');
insert into copy_t values (42 ,'42');
insert into copy_t values (43 ,'43');
insert into copy_t values (44 ,'44');
insert into copy_t values (45 ,'45');
insert into copy_t values (46 ,'46');
insert into copy_t values (47 ,'47');
insert into copy_t values (48 ,'48');
insert into copy_t values (49 ,'49');
insert into copy_t values (50 ,'50');
insert into copy_t values (51 ,'51');
insert into copy_t values (52 ,'52');
insert into copy_t values (53 ,'53');
insert into copy_t values (54 ,'54');
insert into copy_t values (55 ,'55');
insert into copy_t values (56 ,'56');
insert into copy_t values (57 ,'57');
insert into copy_t values (58 ,'58');
insert into copy_t values (59 ,'59');
insert into copy_t values (60 ,'60');
insert into copy_t values (61 ,'61');
insert into copy_t values (62 ,'62');
insert into copy_t values (63 ,'63');
insert into copy_t values (64 ,'64');
insert into copy_t values (65 ,'65');
insert into copy_t values (66 ,'66');
insert into copy_t values (67 ,'67');
insert into copy_t values (68 ,'68');
insert into copy_t values (69 ,'69');
insert into copy_t values (70 ,'70');
insert into copy_t values (71 ,'71');
insert into copy_t values (72 ,'72');
insert into copy_t values (73 ,'73');
insert into copy_t values (74 ,'74');
insert into copy_t values (75 ,'75');
insert into copy_t values (76 ,'76');
insert into copy_t values (77 ,'77');
insert into copy_t values (78 ,'78');
insert into copy_t values (79 ,'79');
insert into copy_t values (80 ,'80');
insert into copy_t values (81 ,'81');
insert into copy_t values (82 ,'82');
insert into copy_t values (83 ,'83');
insert into copy_t values (84 ,'84');
insert into copy_t values (85 ,'85');
insert into copy_t values (86 ,'86');
insert into copy_t values (87 ,'87');
insert into copy_t values (88 ,'88');
insert into copy_t values (89 ,'89');
insert into copy_t values (90 ,'90');
insert into copy_t values (91 ,'91');
insert into copy_t values (92 ,'92');
insert into copy_t values (93 ,'93');
insert into copy_t values (94 ,'94');
insert into copy_t values (95 ,'95');
insert into copy_t values (96 ,'96');
insert into copy_t values (97 ,'97');
insert into copy_t values (98 ,'98');
insert into copy_t values (99 ,'99');

commit;


=================================================================
copy_ymd 테이블 생성 및 데이터 입력 스크립트 ( 오라클용)

create table copy_ymd nologging
as
select to_char(sysdate+rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate+rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from tab a, tab b, tab c
where rownum < 100000
union all
select to_char(sysdate+rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate+rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from dual
union all
select to_char(sysdate-rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate-rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from tab a, tab b, tab c
where rownum < 100000
union all
select '00010101' ymd, to_date('00010101') ymd_date from dual
union all
select '99991231' ymd, to_date('99991231') ymd_date from dual;

COMMIT;
=================================================================
copy_yy 테이블 생성 및 데이터 입력 스크립트 ( 오라클용)

create table copy_yy nologging
as
select to_char(1900+rownum) yy from all_objects where rownum < 200


commit;



=================================================================
잘못된 날짜 데이터 찾기

SELECT A.EMPNO, A.ENAME, A.HIREDATE, B.YMD -- TO_DATE(HIREDATE, 'YYYY-MM-DD')
FROM TEST20 A, COPY_YMD B
WHERE A.HIREDATE = B.YMD(+)
AND B.YMD IS NULL;

혹은

SELECT * FROM (
SELECT A.EMPNO, A.ENAME, A.HIREDATE,
(SELECT B.YMD FROM COPY_YMD B WHERE A.HIREDATE = B.YMD ) YMD
FROM TEST20 A
)
WHERE YMD IS NULL;

( TEST20 테이블의 HIREDATE는 VARCHAR2(8) 로 구성되어 있으며, 중간중간 잘못된 데이터가 입력되어 있음)

DBMS/오라클] 오라클 트리거 작성 예

CREATE SEQUENCE 이름
[ INCREMENT BY n ]
[ START WITH n ]
[ {MAXVALUE n | NOMAXVALUE} ]
[ {MINVALUE n | NOMINVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
-----------------------------------------------------


SQL> CREATE [ PUBLIC ] SYNONYM 시노님 명
FOR 오브젝트 명 ;
-----------------------------------------------------


CREATE [OR REPLACE] TRIGGER trigger명
timing event1 [OR event2 ...] OF 칼럼명 ON 테이블명
[FOR EACH ROW]
PL/SQL 블록
-----------------------------------------------------

실예:]

CREATE OR REPLACE TRIGGER time_emp
BEFORE insert OR update OR delete ON s_emp
BEGIN
IF to_char(sysdate,'hh24') NOT BETWEEN '09' AND '12' THEN
raise_application_error(-20201,'시간이 끝났음');
END IF;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER up_emptemp
AFTER UPDATE OF SALARY ON s_emp
FOR EACH ROW
BEGIN
UPDATE EMPTEMP
SET salary =:NEW.salary
WHERE id = :OLD.id ;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER ord_total
AFTER INSERT OR DELETE OR
UPDATE OF price, quantity ON s_item
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE s_ord
SET total = NVL(total,0)+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
ELSIF DELETING THEN
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
WHERE id = :OLD.ord_id;
ELSE
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
END IF;
END;
/

DBMS/오라클] 테이블 리스트 보기

select *
from dictionary



select *
from all_all_tables

select *
from user_tables;

--//현재 사용자 정보
select *
from user_users


--// 사용자 테이블의 이름과 설명.
select *
from user_tab_comments


select *
from user_tab_privs

--// 사용자테이블리스트와 해당 테이블의 컬럼명 및 속성.
select *
from user_tab_cols

select *
from user_tab_columns

select *
from user_tab_col_statics




--// 오라클에서 데이터딕셔너리에서 사용자 테이블의 이름 가져오는 쿼리
select rownum, aat.*
from all_all_tables aat
where aat.owner ='LASADM'  


select table_name, column_name, data_type, data_length, nullable,  data_default
from cols  --// user_tab_columns  ==> cols  와 동일
where table_name = 'BL_TB_LAW_CONTRACT_MF'


--// 사용자 테이블의 이름과 설명.
select *
from user_tab_comments


--// 오라클에서 데이터딕셔너리에서 사용자 테이블의 정보 가져오는 쿼리

select rownum, utc.table_name, utc.column_name, utc.data_type, utc.data_length, utc.nullable,  utc.data_default, acc.comments
from cols utc --// user_tab_columns utc ==> cols utc 와 동일
join all_col_comments acc
on utc.table_name = acc.table_name
and utc.column_name = acc.column_name
--where utc.table_name = 'BL_TB_LAW_CONTRACT_MF'

DBMS/오라클] 주차 구하기

--// 오라클 주차 구하는 쿼리
SELECT TO_CHAR(SYSDATE,'WW') AS WEEK FROM DUAL;

DBMS/오라클] 오라클_테이블및_오브젝트_생성일자확인쿼리

DBA 계정으로 접속
다음의 명령을 실행 하면 생성한 오브젝트(테이블, 프로시저, 트리거 등) 의 
생성일자(CREATED), 최종 수정일자(LAST_DDL_TIME)를 확인 가능합니다. 

SELECT * 
FROM SYS.DBA_OBJECTS
WHERE OWNER LIKE DBUSER_NAME

DBMS/오라클]3489347_오라클_LOCK_확인_프로시저

select *
from v$session s, v$lock l, dba_objects o
where s.sid= l.sid and o.object_id = l.id1 and s.username is not null;



select a.sid, a.serial#, a.username, a.process, b.object_name,
decode(c.lmode, 2, 'RS', 3, 'RX',
4, 'S', 5, 'SRX', 8, 'X', 'NO') TABLE_LOCK,
decode(a.command, 2, 'INSERT',3, 'SELECT',6, 'UPDATE',
7, 'DELETE', 12, 'DROP',6, 'LOCK', 'unknown') SQL,
decode(a.lockwait, NULL, 'No Wait', 'Wait') STATUS
from V$SESSION a, DBA_OBJECTS b, V$LOCK c
where a.sid = c.sid and b.object_id = c.id1
and c.type = 'TM'
and a.username = 'NIOTALKUSER'
;



select substr(c.object_name,1,20),a.sid,a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
and b.id1 = c.object_id
and b.type='TM';



SELECT SUBSTR(S.USERNAME, 1,11) "ORACLE USER", P.PID "PROCESS ID", S.SID "SESSION ID", S.SERIAL#, OSUSER "OS USER", P.SPID "PROC SPID",
    S.PROCESS "SESS SPID", S.LOCKWAIT "LOCK WAIT"
FROM V$PROCESS P, V$SESSION S, V$ACCESS A
WHERE A.SID = S.SID
AND P.ADDR = S.PADDR
AND S.USERNAME != 'SYS'

DBMS/오라클] 오라클에서 해당 데이타베이스의 테이블 리스트 보는 명령어.

--// 오라클에서 해당 데이타베이스의 테이블 리스트 보는 명령어.
select * from dba_tables

/


// 오라클에서 해당 테이블의 컬럼 명
select * from USER_TAB_COLUMNS where table_name = '대문자테이블명';
/

select * from dba_objects WHERE OWNER='NCHANNEL' AND OBJECT_TYPE = 'INDEX';
/
select * from dba_objects WHERE OWNER='NCHANNEL' AND OBJECT_TYPE = 'TABLE';

DBMS/오라클]_권한_부여_생성_sql스크립트

--// 특정 테이블만 제외하고 select 권한주기

select 'grant select  on '||table_name||' to 유저명;'
from ALL_TABLES /* select 권한만 */ .. 혹은
--from user_tables
where owner in ('유저01','유저02','유저03')  /* 원본 유저 리스트 */
AND table_name  NOT IN ('테이블1 ','테이블2'); /* 권한 제외할 테이블 리스트 */



--// billuser 에게 현재 로그인한 사용자의 모든 테이블에 대한 select 권한 주는쿼리문 생성
select 'grant select on ' || table_name || ' to billuser;' a
from user_tables
order by a
;

DBMS/오라클] 각종 세션/커서 관련 유용한 쿼리

각종 세션/커서 관련 유용한 쿼리 | Oracle DB 2006/03/31 10:11


http://blog.naver.com/yc7497/130003056914


아래 모니터링 쿼리문은 여기 저기 기웃거리며 동냥해서 얻은 지식들을 적어 놓은 것입니다.
순서없이 뒤죽박죽이어서 보시기 불편할 지도 모르겠네요.

사용법은 오라클이 설치된 서버에 텔넷을 이용하여 root계정으로
접속후에 top 혹은 topas 명령으로 프로세스들을 감시하면서 cpu를 많이 차지하는 프로세스 아이디등을 발견하여 조치하는 것입니다.



==================================================

/*********************************************************************************************
* 각 테이블스페이스 사용용량 체크하기
*********************************************************************************************/
SELECT a.tablespace_name,
             a.total "Total(Mb)",
             a.total - b.free "Used(Mb)",
             nvl(b.free,0) "Free(Mb)",
             round((a.total - nvl(b.free,0))*100/total,0)  "Used(%)"
from    (   select     tablespace_name,
                            round((sum(bytes)/1024/1024),0) as total
               from       dba_data_files
               group by tablespace_name) a,
         (     select     tablespace_name,
                             round((sum(bytes)/1024/1024),0) as free
               from        dba_free_space
               group by  tablespace_name) b
where      a.tablespace_name = b.tablespace_name(+)
order by   a.tablespace_name;

/*********************************************************************************************

/*오라클서버의 메모리에 관한 테이블*/
select * from v$sgastat
 
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool

/*cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기*/
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '675958'
order by c.PIECE



/*cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기*/
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE

/*프로세스 아이디를 이용하여 쿼리문 알아내기*/
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE

/*세션 죽이기(SID,SERAIL#)*/
ALTER SYSTEM KILL SESSION '8,4093'

/*hash_value 가지고 SQL 문장 찾아내는 쿼리 */
select sql_text from v$sqltext where hash_value= 317853294


/*오라클 세션과 관련된 테이블*/
select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

/*인엑티브 상태의 세션들-로그온타입이 과도하게 지나도록 없어지지 않는것은 세션을 죽이도록 한다
단 machine이 머신이름 에 해당하는 것만 죽이도록 한다
prev_hash_value 로 해쉬밸류를 알아내어 쿼리문을 확인할 수 있다
*/
select sid,serial#,username,status,logon_time,prev_hash_value,machine
from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'KILLED'

SELECT COUNT(*) FROM V$SESSION WHERE MACHINE ='머신이름' AND SCHEMANAME ='스키마이름'  AND STATUS = 'ACTIVE'
   
--현재 스키마이름 스키마로 실행중인(active) 프로세스를 알아내는 쿼리문
select SID, SERIAL#, SQL_HASH_VALUE, PREV_HASH_VALUE,ROW_WAIT_OBJ#, LOGON_TIME, MACHINE
from v$session
where schemaname ='스키마이름' and status = 'ACTIVE'
 
--위에서 알아내 hash_value 값을 이용하여 해당 프로세스가 실행중인 쿼리문 알아내기
select * from v$sqltext where hash_value = 2626426688-
order by piece

select * from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'ACTIVE' and ROW_WAIT_OBJ# > 0



/******************************************************************************************/
 현재 커서 수 확인하는 Query
/******************************************************************************************/


SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = '유저이름'
GROUP BY sid
ORDER BY cursor DESC


SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC





 
 

 
   116, 15217
 
    select sql_text from v$sqltext
 where hash_value= 3252988466 --1389452958
 order by piece
 
select * from v$session_wait
 
  select sid, serial#, username, taddr, used_ublk, used_urec
 from v$transaction t, v$session s
 where t.addr = s.taddr;


 
select *  from sys.v_$open_cursor
 
 
  select user_name, sql_text, count(*) cnt from sys.v_$open_cursor
 where user_name = '유저이름' and sid = 114
  group by user_name, sql_text
  order by cnt desc

 alter system kill session '27,127'

--ALTER SYSTEM KILL SESSION '45, 3977'
--이런식으로 통계정보를 생성하신 후에

 analyze table emp compute statistics;

 alter table customers pctfree 5 ;

select * from dba_tables
where table_name = 'RP_PART_MST'




/*******************************************************************************
* LOCK 관련
*******************************************************************************/

--V$LOCK 을 사용한 잠금 경합 모니터링
SELECT s.username, s.sid, s.serial#, s.logon_time,
    DECODE(l.type, 'TM', 'TABLE LOCK',
          'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL  

--락이 걸린 세션 자세히 알아보기
select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

--락이 걸린 세션 간단히 알아보기
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
       a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
  and b.id1 = c.object_id
  and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
ALTER SYSTEM KILL SESSION '11,39061'


/**********************************************************************************************/
select *
  from v$session
  where sid in (80,100)

SELECT l.SESSION_ID,
    LPAD('  ', DECODE(l.xidusn,0,3,0))
    ||l.oracle_username "User Name",
    o.owner, o.object_name, o.object_type
    FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER by o.object_id, 1 desc


--아래 table 은 존재하지 않네...

SELECT sw.username "WAITING_USER", bu.username "LOCKING_USER",
    dw.lock_type, dw.mode_held, dw.mode_requested,
    dw.waiting_session, dw.holding_session
FROM dba_waiters dw, v$session sw, v$session bu
WHERE dw.waiting_session = sw.sid
ANd dw.holding_session = bu.sid  
   

SELECT s.username, s.sid, s.serial#
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid


SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id

SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id


Select *
FROM
  all_col_comments
WHERE
  table_name = '테이블이름'
 
 
/************************************************
 alter session으로 죽지않는 프로세스 죽이기
 1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
 2.su -오라클계정
 3.sqlplus '/as sysdba''
 4.connect system/sys
 5.ALTER SYSTEM KILL SESSION '137,1723'
**************************************************/

 
 

DBMS/오라클] 오라클 10g용db_crypto함수

--[EDIT BY SINU]

conn / as sysdba

create user sinu
identified by sinu;

grant resource, connect to sinu;
grant execute on DBMS_CRYPTO to sinu;

conn sinu/sinu

CREATE OR REPLACE PACKAGE pkg_crypto
IS
    FUNCTION encrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := '12345678'
    ) RETURN RAW;
   
    FUNCTION decrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := '12345678'
    ) RETURN VARCHAR2;

END pkg_crypto;
/


CREATE OR REPLACE PACKAGE BODY pkg_crypto
IS
    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;
   
    FUNCTION encrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
     RETURN RAW
    IS
   
        key_data_raw        RAW(64);
        converted_raw       RAW(64);
        encrypted_raw       RAW(64);

    BEGIN

        converted_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');

        encrypted_raw :=
             DBMS_CRYPTO.ENCRYPT(
                 src => converted_raw ,
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);
       
        RETURN encrypted_raw;

    END encrypt;
   
    FUNCTION decrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
     RETURN VARCHAR2
    IS
        converted_string    VARCHAR2(64);
        key_data_raw        RAW(64);
        decrypted_raw    VARCHAR2(64);

    BEGIN

        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');
       
        decrypted_raw :=
            DBMS_CRYPTO.DECRYPT(
                 src => input_string ,
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);

        converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

        RETURN converted_string;

    END decrypt ;
END pkg_crypto;
/

col card_number format a64
create table card_info ( id number, card_number varchar2(64) ) ;

insert into card_info values ( 1 , pkg_crypto.encrypt('1234567812345678')) ;
insert into card_info values ( 2 , pkg_crypto.encrypt('12345678')) ;
commit;

select * from card_info ;
select id , pkg_crypto.decrypt(card_number) card_number
from card_info;

insert into card_info values ( 3 , pkg_crypto.encrypt('1234567812345678','00000000')) ;
select id , pkg_crypto.decrypt(card_number,'00000000') card_number from card_info where id = 3  ;

select * from card_info ;

drop package body pkg_crypto;
drop package pkg_crypto;
drop table card_info;

conn / as sysdba
drop user sinu cascade;

--[EDIT BY SINU]

DBMS/오라클]오라클공통 SQL

/** 테이블명 조회 */
SELECT * FROM ALL_TAB_COMMENTS --WHERE OWNER = 'VRM' ORDER BY COMMENTS
/
SELECT * FROM ALL_INDEX_COMMENTS --WHERE OWNER = 'VRM' ORDER BY COMMENTS
/
/** 컬럼명 조회 */
SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'VRM' AND TABLE_NAME LIKE 'CMZG%' ORDER BY TABLE_NAME, COMMENTS

/** 테이블 조회*/
SELECT * FROM TAB

/** 휴지통 조회*/
SELECT * FROM RECYCLEBIN;

/** DROP TABLE 복구 */
FLASHBACK TABLE CSMG_IBUSPB01 TO BEFORE DROP;

/** 휴지통 미입력 삭제*/
DROP TABLE CSMG_IBUSPB01 PURGE;

/** DELETE 데이터 복구(15분전)*/
INSERT INTO CSMG_IBUSAD01
SELECT *
FROM  CSMG_IBUSAD01
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE);


/** 이전테이터 조회*/
SELECT *
FROM  CSMG_IBUSAD01
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE);

DBMS/오라클] ORA-01652 에러 대처

ORA-01652:128(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다.

원인1

Temp Tablespace를 구성하는 Data File의 Size가 작은 경우

해결1

TEMP 테이블스페이스의 TEMPFILE 크기 확인

SELECT *

FROM DBA_TEMP_FILES

WHERE TABLESPACE_NAME='TEMP';

테이블스페이스 공간 추가(3개중의 하나)

1) ALTER TABLESPACE TEMP ADD TEMPFILE '/ORADATA/TEMP02.DBF' SIZE 100M;

또는

2) ALTER DATABASE TEMPFILE '/ORADATA/TEMP01.DBF' AUTOEXTENT ON;

기존의 TEMP01.DBF를 AUTOEXTENT로 바꿈

또는

3) ALTER DATABASE TEMPFILE ''/ORADATA/TEMP01.DBF'' RESIZE 100M;

원인2

Temp Tablespace의 Max Extents에 도달한 경우

해결2

v$sort_segment를 확인해서 현재 할당된 크기가 얼마인지 확인한다.

할당할 수 있는 최대 EXTENT 의 크기가 얼마인지 확인한다.

SELECT MAX(blocks), MAX(bytes)

   FROM DBA_FREE_SPACE

  WHERE TABLESPACE_NAME = 'TEMP';