DataBase] Oracle 테이블에 대한 정보 조회 쿼리

-- 테이블에 대한 컬럼명, 컬럼 타입, 컬럼 길이, 코멘트 보는 쿼리.
SELECT T.TABLE_NAME, T.COLUMN_NAME, T.DATA_TYPE, T.DATA_LENGTH, C.COMMENTS
FROM ALL_TAB_COLUMNS T JOIN ALL_COL_COMMENTS C
    ON T.TABLE_NAME = C.TABLE_NAME
        AND T.COLUMN_NAME = C.COLUMN_NAME
WHERE T.TABLE_NAME = 'TABLE_NAME'
;

Select 에서 여러 행을 하나의 행으로 합쳐서 뽑고자 하는 경우

Select 쿼리를 사용하여 나온 결과가 여러행인 경우 키값이 같은 경우 하나의 행으로 보여주고자 하는 경우, 아래의 쿼리를 사용하여 간단하게 원하는 결과값을 얻을 수가 있다.

아래 쿼리는 DB2에서 재현을 해본 결과 해당 쿼리가 무난하게 돌아가는것을 확인하였다.


실행 쿼리 :
=================================
with tb(docno, seqno, prot, empno) as
(
select '1111' docno, 1 seqno, 1 prot, 'AAAA' empno from dual union all
select '1111' , 1 , 2 , 'BBBB' from dual union all
select '5555' , 3 , 1 , 'KKKK' from dual union all
select '5555' , 3 , 2 , 'MMMM' from dual union all
select '5555' , 3 , 3 , 'PPPP' from dual
)
select docno
, seqno
, listagg(empno,' ') WITHIN GROUP (ORDER BY seqNO, prot) as empno
from tb
group by docno
, seqno
;


실행 결과 :
=================================
docno seqno   empno
1111 1     AAAA BBBB
5555 3     KKKK MMMM PPPP


내용 출처 : http://www.oracleclub.com/article/51669

아래의 링크도 참조하기 바랍니다.
 http://blog.naver.com/PostView.nhn?blogId=jauroy&logNo=50044746370

위에 사용된 listagg() 란 함수에 대해 찾아보다가 발견한 아래의 페이지도 참고 바랍니다.
http://blog.naver.com/PostView.nhn?blogId=joonaha&logNo=70108717698

SQL] DB2 date 를 milliseconds 로 변환하기.

DB2
=======
select to_number(sysdate - to_date('1970-01-01','YYYY-MM-DD')) * (24 * 60 * 60 * 1000)
FROM sysibm.sysdummy1 ;


ORACLE
========

select to_number(sysdate - to_date('1970-01-01','YYYY-MM-DD')) * (24 * 60 * 60 * 1000)
FROM dual;

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;

오라클] 프로시저 내용 확인하는 sql

select * from all_source where name = upper('프로시저명');

select * from user_source where name = upper('프로시저명');

오라클] 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
;

DB / MSSQL ] 인덱스를 이용한 조건검색

인덱스의 유무에 따라서 데이터를 찾는 성능의 차이가 천차만별이 될 수도 있으나 또한 너무 남용을 하게 되면 데이터의 크기보다 인덱스의 크기가 커지는 배보다 배꼽이 더 큰경우가 발생하는 경우도 있으니 유의해서 사용을 해야된다.

인덱스는 테이블에서 어느 하나의 필드, 혹은 필드들의 조합으로 생성을 할 수 있다.
이 생성된 인덱스를 사용하게 하는 것이 쿼리 작성자의 능력이다.

이제 조건 검색에서 주의할 사항을 알아보자.

1. 조건검색시 ''='' 은 모든 인덱스검색중 최우선 순위를 가진다.

2. 조건 검색시 좌변인덱스는 가공하지 말고, 우변을 가공해서 조건을 생성할 것.
    단, 의도적인 인덱스 사용을 막고자 하는 경우는 예외.

3. 조건 검색시 부정의 표현을 사용하지 말고 긍정표현 및 범위 조건을 사용할것.
     다시말해,  ''not'', ''<>'', ''or'' 이런 조건들은 될 수 있으면 긍정적인 표현으로 바꾸어서 사용할 것.
     ''<>'' => exists로 대체,  ''not'' A => not (A) 대체 
4. 인덱스가 있는 곳과 없는곳의 테이블을 조인하여 결과 검색시, 인덱스가 없는곳의 데이터가 먼저 읽혀지고 다음에 인덱스가 있는 테이블의 데이터가 읽혀진다.

5. 가급적 조건에 ''Null'', ''Not Null'' 을 없애기 위해 테이블 설계시, 결합인덱스의 구성컬럼이 된다면 Not Null로, 또한 입력조건값으로 자주 사용되는 것이면 Not Null로 설계를 하여서 조건검색시 Not Null, Null이란 조건을 가급적 없앤다.


위의 사항만을 유념해서 검색을 하더라도 select 쿼리 수행 시간을 줄일 여지가 많이 있다.

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

쿼리문의 속도 향상 방법.

부분범위처리를 사용한다.

부분범위처리란

 - 조건을 만족하는 전체집합이 아닌 일부분만을 Access 하게 하는것
 - Data량이 많아도 퍼포먼스에는 지장이 없고, 오히려 향상을 가져올 수도 있다.
 - 인덱스나 클러스트를 적절히 활용하여 sort를 대체가능하다.
 - table은 액세스하지 않고 index만 사용하도록 유도
 - exists를 활용한다.
 - query를 이원화 하여 일부분씩 scan하도록 유도

위의 것들을 조합하여 활용하면 쿼리문을 작성하여 대량의 데이터를 처리할 경우에도 무리없이 원하는 속도를 얻을 수 있을 것이다.

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/오라클] 오라클_테이블및_오브젝트_생성일자확인쿼리

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/오라클]오라클공통 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';

DBMS/오라클]Linux Oracle 10g 자동 시작 하기

Linux Oracle 10g 자동 시작 하기


자신이 설치한 DB 타겟으로 걸면 된다.

수동으로 하는게 일단 좋을듯 합니다. 다만 급해서 리부팅 후에 자동으로 하는게 필요하다면 이방법을 사용하시면 될겁니다.







1. 그냥 하는 방법

step1. root 계정에서 /etc/rc.d 로 이동하여 rc.local 파일을 vi 편집기로 열어 줍니다.

          (rc.local 파일은 리눅스의 초기부팅설정화일로서 DOS의 Autoexec.bat 같은 것이라 보시면 됩니다)

step2. 파일의 마지막 부분에 실행하고 싶은 명령어(경로포함) 를 기록합니다


/etc/rc.d/rc.local


rdate -s time.bora.net


su - oracle -c '/home/oracle/product/10.2.0/db_1/bin/lsnrctl start'

sleep 10
su - oracle -c '/home/oracle/product/10.2.0/db_1/bin/dbstart'

sleep 30
su - oracle -c '/home/oracle/product/10.2.0/db_1/bin/emctl start dbconsole'
==========================================================================

리눅스에서 재부팅시 오라클을 자동으로 시작하고 종료하는 방법

/etc/oratab의 내용 중 다음을 변경



/etc/oratab


ORCL:/home/oracle/product/10.2.0/db_1:Y
==================================================================
 







2. 스크립트 실행 방법



(1) 스크립트 하나 만든다.


 #!/bin/bash

source /etc/profile


# oracle start
su - oracle -c '/home/oracle/product/10.2.0/db_1/bin/lsnrctl start'

sleep 5

su - oracle -c 'sqlplus "/as sysdba"' << EOF
startup
exit
EOF

sleep 60

su - oracle -c '/home/oracle/product/10.2.0/db_1/bin/emctl start dbconsole'


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


(2) 리눅스 start 하면 스크립트 돌아게 만든다.


/etc/rc.d/rc.local


#oracle start
/root/bin/oracle_start.sh
==============================================================

DBMS/오라클] RemoteOperationException_에러_해결방법_리눅스

RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)
Hi All,

I was trying using Oracle Enterprise Manager to start the database/Listener and when I tried to start the database/listener through OEM i got the following error

RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)

Later on I discovered that the error was since the root.sh script was not run properly.

Then i tried to rerun the root.sh which is located in $ORACLE_HOME/root.sh the

above error disapperad. It was because the improper permissions on oracle binaries in particular nmo,nmhs,nmb binaries.

Later on after running the root.sh the following permission were giving the nmo,nmhs and nmb binaries

-rwsr-s—   1 root   oinstall     23744 Oct 15 16:17 nmo
-rwsr-x—   1 root   oinstall     46480 Oct 15 16:17 nmhs
-rwsr-s—   1 root   oinstall     17256 Oct 15 16:17 nmb

Here ’s’ stands for that SUID bit is set for this binaries



결론 root 로 로그인

 $ORACLE_HOME/root.sh 실행



 

DBMS/오라클]sqlplus 에서 vi 편집기 사용하기

sqlplus 에서 vi 편집기 사용하기 

단계1 오라클 사용자로 터미날 열어서 cd



단계2  ls -a

-- .bash_porfile 파일이 보일것이다. 이것을 조작한다.



단계3  geidt .bash_profile



--> export EDITOR=vi  추가 후 저장



 단계 4 로그 아웃 후 다시 접속