'Oracle'에 해당되는 글 42건

  1. 2011/01/24 boyo 오라클 락테이블 해제
  2. 2011/01/17 boyo flashback query [9i]
  3. 2011/01/03 boyo 오라클 락테이블 찾아서 죽이기
  4. 2010/12/27 boyo 동적, 정적 parameter
  5. 2010/12/22 boyo oracle 세션 초과시 처리방법
  6. 2010/12/17 boyo ORACLE DROP TABLE, TABLE 휴지통 관리
  7. 2010/12/17 boyo Oracle(오라클) 버전 확인
  8. 2010/12/16 boyo 오라클 비밀번호 바꾸기
  9. 2010/12/15 boyo 권한, 프로파일
  10. 2010/12/13 boyo 오라클 시스템 뷰

-- 테이블에 lock을 발생시킨 user를 찾을 경우가 많습니다.
-- 특히 OPS(RAC on 9i) system에서는 더더욱 그런 경우가 많구요.
-- 제가 관리하던 시스템에서 자주 썼던 sql입니다.
-- 기타 많은 유사한 sql로 있습니다만 한번 써 보시길 바랍니다.


/* 락발생 사용자와 sql, object 조회 */

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$locked_object x, v$session a, v$sqltext b, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
and a.sql_address = b.address
--and a.terminal = ''
order by b.address,b.piece;



/* 락 발생 사용자확인 */

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time
--select  'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'
from gv$locked_object x, gv$session a, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
order by logon_time;
;



/* 현재 접속자의 sql 분석 */

select distinct a.sid,a.serial#,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;


/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '51,1111;
 

2011/01/24 13:32 2011/01/24 13:32

SQL> DELETE FROM EMP WHERE EMPNO = 7934 ;

1 행이 삭제되었습니다.


SQL> COMMIT;

커밋이 완료되었습니다.


SQL> SELECT * FROM EMP WHERE EMPNO = 7934;

선택된 레코드가 없습니다.


SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
09/02/18 15:05:22.203000 +09:00


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

여기서 플래쉬백 쿼리를 날려서 2009년 2월 18일 15시 3분버전(약2분전)으로 되돌린다.

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

SQL> EXEC DBMS_FLASH.ENABLE_AT_TIME('09/02/18 15:03:00')
BEGIN DBMS_FLASH.ENABLE_AT_TIME('09/02/18 15:03:00'); END;

      *
1행에 오류:
ORA-06550: 줄 1, 열7:PLS-00201: 'DBMS_FLASH.ENABLE_AT_TIME' 식별자가 정의되어야 합니다
ORA-06550: 줄 1, 열7:PL/SQL: Statement ignored


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

그런데 여기서 문제가 발생한다.ORA-06550이라면서 DBMS_FLASH을 SCOTT이 사용할수 없다.

이럴경우 DBA권한으로 들어가서 FLASHBACK에 대한 실행 권한을 SCOTT에게주면 된다.

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

SQL> conn system/manager as sysdba
연결되었습니다.
SQL>  grant execute on sys.dbms_flashback to scott;

권한이 부여되었습니다.

SQL> conn scott/tiger
연결되었습니다.
SQL> exec dbms_flashback.enable_at_time('09/02/18 15:02:00')

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from emp where empno = 7934;
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM   DEPTNO
---------- ---------- --------- ---------- -------- ---------- ----------- ---------
      7934 MILLER     CLERK           7782 82/01/23       1300                                10

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

죽었던 7934사원을 살렸다..^^

하지만 이걸로 해결되는가 아니다.

잠시 살아난것처럼 보이지만 이건 좀비스러운 데이터다.

잠시 나의 세션에서만 살아났을뿐 다른곳에서는 죽은 데이터다.

완전히 살리기 위해서는  프로시저로 생성해주거나, 아래와 같은 방법으로 해야한다.

우선 삭제된건이 7934인지 확인한후 EMP테이블에 인서트를 해주면 된다.

(단,트리거가 걸려있을경우는 안된다.)

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

SQL> SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP

         ('20090218 16:19:00' ,'YYYYMMDD HH24:MI:SS')  MINUS

         SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM  DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ---------
      7934     MILLER     CLERK           7782 82/01/23         1300                         10

 

SQL>INSERT INTO EMP
       (
        SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP

        ('20090218 16:19:00','YYYYMMDD HH24:MI:SS') MINUS

        SELECT * FROM EMP
       );

1 개의 행이 만들어졌습니다.

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

#참고: DBMS_FLASHBACK 패키지를 이용한 복구방법은 아래와 같습니다.

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

SQL>DECLARE 
   
     CURSOR emp_cursor is
       SELECT * FROM emp;
     
      v_emp emp%ROWTYPE;
     
    BEGIN
   
     DBMS_FLASHBACK.ENABLE_AT_TIME

    (TO_TIMESTAMP('20090218 16:19:00' ,'YYYYMMDD HH24:MI:SS'));
     
     OPEN emp_cursor;
     
   -- Flashback을 Disable했지만 커서(test_cursor)는 여전히 과거시점의 데이터를 가지고 있습니다.
     DBMS_FLASHBACK.DISABLE;
                             
     LOOP
        FETCH emp_cursor INTO v_emp;
            EXIT WHEN emp_cursor%NOTFOUND;
            INSERT INTO emp VALUES (v_emp.empno, v_emp.ename, v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
     END LOOP;
     CLOSE emp_cursor;
     COMMIT;
    END;
    /
 

PL/SQL 처리가 정상적으로 완료되었습니다.

출처 : http://blog.naver.com/PostView.nhn?blogId=ambion&logNo=50042754527&redirect=Dlog&widgetTypeCall=true

2011/01/17 13:13 2011/01/17 13:13
lock 찾음 

SELECT  T1.object_name, DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode
FROM  dba_objects T1, v$locked_object T2 
WHERE T1.object_id = T2.object_id; 


select * 
from  
(select * from v$locked_object where oracle_username = 'ESMCONSOLE') aa, 
(select object_id,object_name from dba_objects) bb 
where aa.object_id = bb.object_id(+) 


kill 방법 

kill시키고자 하는 시리얼 번호를 알아야 하거든여.. 
select  
a.session_id as SESSION_ID, 
b.serial# as SERIAL_NO, 
a.os_user_name as OS_USER_NAME, 
a.oracle_username as ORACLE_USERNAME, 
b.status as STATUS 
from v$locked_object a, v$session b 
where a.session_id = b.sid; 

세션 ID값으로 세션 검색하여 락거는 세션 죽이면 끝
2011/01/03 19:24 2011/01/03 19:24

정적 파라미더 v$parameter
동적 파라미더 v$session

정적 : 오라클 재시작시 적용
동적 : 재시작 없이 적용

control 파일을 두개이상 두어 사용할 것
장애 발생시 쉽게 복구 할 수 있음.

init.ora 에 지정하여 2개로 만들어 사용할 수 있음

show parameter control #control파일 위치를 확인할 수 있다.

column column_name format a10 #colmun size 지정

2010/12/27 19:49 2010/12/27 19:49
1. 사용자 프로파일러 확인
select username, profile
from dba_users
where username=upper('USERNAME');

2. 사용자 세션 확인
SELECT nvl(S.OSUSER,S.type) OS_User, S.USERNAME Ora_User, S.sid SID, S.serial# Serial#, P.spid OS_Pid, S.program Program FROM V$SESSION S, V$PROCESS P WHERE  s.paddr  = p.addr and S.USERNAME like upper('%USERNAME%');

3. 세션이 초과하였다면 세션 죽이기
alter system kill session 'SID, SERIAL#';

4. 세션 테이블만 확인시 status가 KILLED 일때 immediate 옵션 추가하여 죽이기
    -- status가 KILLED이면 트랜잭션 때문에 세션을 죽지 않고 트랜잭션이 완료 될때까지 기다리는 경우
    select username, sid, serial#, status
    from v$session
    where username = upper('USERNAME');

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
UIP80                                2578      32375 KILLED
UIP80                                2605      35016 KILLED
UIP80                                2620      24609 KILLED

    -- immediate 옵션은 진행중이던 트랜잭션을 롤백하고 모든 세션의 잠금을 해제
        alter system disconnect session 'SID, SERIAL#' immediate;
2010/12/22 18:42 2010/12/22 18:42

▶ 10g이상부터는 테이블삭제 시 휴지통기능이 있는데 휴지통 기능없이 바로 삭제시 사용

DROP TABLE 테이블명 CASCADE CONSTRAINTS PURGE


▶ 오라클 10g 부터 추가된 테이블 삭제시 휴지통기능 사용시엔 기존 DROP TABLE 명령어를 사용

DROP TABLE 테이블명 CASCADE CONSTRAINTS;

- 테이블 삭제시 BIN$로 시작하는 테이블이 저절로 생긴다.
- 휴지통에 있는 것이므로 drop table 명령어로 절대 안지워진다.

▶ 휴지통 비우기 명령어로 삭제한다.

PURGE RECYCLEBIN;


▶ 테이블삭제 후 휴지통 보는 쿼리
SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME,DROPSCN FROM RECYCLEBIN;

 

▶ 휴지통에 있는 테이블 복원하는 명령어
FLASHBACK TABLE 테이블명 TO BEFORE DROP


http://blog.naver.com/jamesblue/20048086004   내용

테이블명 변경    8i 이상

RENAME 변경전 테이블명 TO 변경후 테이블 명;


컬럼명 변경   9i 이상

ALTER TABLE 테이블명 RENAME COLUMN 변경전 컬럼명 TO 변경후 컬럼명;


컬럼 생성

ALTER TABLE 테이블명 ADD(컬럼명 VARCHAR2(10));

 

컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

컬럼 수정

ALTER TABLE 테이블명 MODIFY(컬럼명 VARCHAR2(10));

 

Primary key로 추가하는 방법

1. 변경 하고자 하는 컬럼을 NOT NULL로 변경

ALTER TABLE 테이블명 MODIFY(컬럼명 VARCHAR2(10) NOT NULL);

 

2. Primary key로 지정 돼 있는 것들을 삭제한다.

ALTER TABLE 테이블명 DROP PRIMARY KEY;

 

3. Primary key로 추가합니다.

ALTER TABLE 테이블명 ADD PRIMARY KEY("필드명1","필드명2","필드명3");  

2010/12/17 13:20 2010/12/17 13:20
1. V$VERSION : 오라클 버전에 대한 정보를 볼수 있습니다.
 
SQL>COL banner  FORMAT A70 HEADING "Oracle Versions"

SQL>SELECT banner FROM  V$VERSION;
 
Oracle Versions
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE    8.1.6.0.0       Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
  
2. PRODUCT_COMPONENT_VERSION : 제품 인스톨에 대한 정보를 볼 수 있습니다.
 
 SQL>COL PRODUCT FORMAT A30 HEADING "Products Installed"
 SQL>COL VERSION FORMAT A15
 SQL>COL STATUS  FORMAT A15
 
 SQL>SELECT product, version, status FROM  PRODUCT_COMPONENT_VERSION;

Products Installed                    VERSION           STATUS
------------------------------ --------------- ---------------
NLSRTL                                      3.4.1.0.0         Production
Oracle8i Enterprise Edition            8.1.6.0.0         Production
PL/SQL                                      8.1.6.0.0         Production
TNS for 32-bit Windows:               8.1.6.0.0         Production

 3. V$OPTION : 옵션 인스톨에 대한 정보를 볼 수 있습니다.

SQL>COL PARAMETER   FORMAT A50 HEADING "Options Installed"    
SQL>COL VALUE       FORMAT A10 HEADING "True/False"           

SQL>SELECT parameter, value FROM   V$OPTION;


출처 : http://blog.naver.com/idearman/120007487130

2010/12/17 11:04 2010/12/17 11:04

sqlplus / as sysdba
alter user aaa identified by 바꾸려는패스워드;

2010/12/16 13:48 2010/12/16 13:48
grant privilege
on object
to [user,role,PUBLC]
[with grant option] - 권한 부여 권한을 줄것인지 여부 설정

사용자 생성
create user testuser
identified by ora;

세션생성권한부여 - 권한을 줘야 접속이 가능해진다. 아직 테이블을 생성할 권한이 없다.
conn system;
grant create session to testuser;

권한 박탈
revoke create session from testuser;

롤 : 권한의 집합
create role select_table; 롤 생성
grant create session, select any table to select_table; 롤에게 권한지정
grant select_table to testuser;  사용자에게 롤 부여

quota 설정
alter user testuser
quata 10M on users;

저장영역 추가
grant create any table to select_table;select_table라는 롤에 테이블 생성권한 부여
grant select_table to testuser; testuser에 테이블 생성권한 부여


프로파일 설정

프로파일 정보 확인
select profile, resource_name, limit
from dba_profiles
where profile='profile_name';

show parameter resource_limit 로 자원제한사항을 볼수 있다.
alter system set resource_limit = ture --system계정이 자원에 대한 제약을 할 수 있도록 변경

제한 걸기
create profile app_profile limit
sessions_per_users 2
connect time 60
idle_time 1

현재 사용자의 프로파일 확인
select user_name, profile
from dba_users
where username='testuser';

프로파일 사용자에게 적용하기
alter user testuser
profile app_profile;



2010/12/15 10:50 2010/12/15 10:50
DBA_ #해당 데이터베이스에 존재하는 모든 내용 조회 가능
ALL_ # 해당 유저에게 권한이 존재하는 모든 내용 조회 가능
USER_ #해당 유저가 소유자로 되어 있는 모든 내용 조회 가능

1. 오브젝트 관련 데이터 딕셔너리 뷰
DBA_OBJECTS
2010/12/13 20:08 2010/12/13 20:08