'Oracle'에 해당되는 글 8건

개인서버를 운영하는데, 오라클은 유로라서 혹시 모를 상황에 대비해 MariaDB로 변경했다. 그 과정을 여기에 나열하겠다. MariaDB에 오라클과 동일하게 테이블을 생성할 수 있는 방법이며 not null, pk 지정은 따로 해주셔야합니다.

http://www.sqlines.com/oracle-to-mysql (좌) /  http://www.sqlines.com/online (우)

왼쪽의 사이트를 들어가면, sqlLines의 메인페이지를 볼 수 있다. 데스크탑 버전과 온라인 버전이 있다고 하는데, 간편하게 온라인 버전을 사용하겠습니다. 오른쪽의 링크를 클릭하면 바로 들어갈 수 있습니다.

온라인 버전을 들어오면 왼쪽이 변환전 오른쪽이 변환후 언어입니다. 왼쪽은 Sources를 Oracle로 지정, 오른쪽 Target을 MariaDB로 설정합니다.

오라클 테이블 및 데이터 익스포트 하는 방법

왼쪽에 sql파일 내용을 넣고, convert버튼을 눌러 변환합니다. 이를 이용하여 Maria DB에 옮길 수 있습니다. 변환 시에 누락사항이 있어 직접 수정한 부분이 있었는데 그 점에 대해 안내드립니다.

SQL문 수정사항

날짜 Default값 수정

날짜의 Default값을 지정할 때, 오라클은 systimestamp를 사용합니다. MariaDB에서는 CURRENT_TIMESTAMP를 사용하므로 테이블 생성 SQL문에서 모두 바꿔주어야 합니다.

<ORACLE>
CREATE TABLE "LOGGER" ("LOG" VARCHAR2(1000), "TIME" VARCHAR2(30) DEFAULT SYSDATE)

<MariaDB>
CREATE TABLE "LOGGER" ("LOG" VARCHAR2(1000), "TIME" VARCHAR2(30) DEFAULT CURRENT_TIMESTAMP)

날짜 입력 형식 수정

오라클에 systimestamp로 저장하게 되면 '19/11/20 13:56:04'와 같은 형태로 저장됩니다. 오라클에서는 이러한 날짜 형식을 'RR/MM/DD HH24:MI:SS'로 표현하지만, MariaDB에서는 '%y/%m/%d %H:%i:%s'와 같이 표현됩니다. 따라서 이러한 형태로 들어가는 데이터들을 모두 바꿔주어야 합니다.

<ORACLE>
Insert into MEMBER (ID,PW,DATE_REGISTER,NICKNAME)
	values ('asdf','1234',to_date('19/11/20 13:56:04','RR/MM/DD HH24:MI:SS'),'asdf');

<MariaDB>
Insert into MEMBER (ID,PW,DATE_REGISTER,NICKNAME)
	values ('asdf','1234',str_to_date('19/11/20 13:56:04','%y/%m/%d %H:%i:%s'),'asdf');

SQL 변경 후 새로운 쿼리 파일을 생성해줍니다.

생성된 파일에 변환된 SQL문을 옮겨주고, 원하는 부분을 선택 한 뒤 SQL문을 실행해 줍니다. 잘 적용이 되는지 확인하면서 SQL문을 실행해주면 됩니다.

SQL문이 모두 정상적으로 실행되었다면, 기존 DB에서 NM조건과 PK를 참고하시어 테이블 설정을 해주시면 됩니다.

오라클 서버 설치후 시스템 계정의 비밀번호를 까먹는 경우가 종종 있습니다.

이럴때 간단하게 시스템 계정의 비밀번호를 찾는 방법이 있어 포스팅 해보겠습니다.

또한, 일반 사용자에 대한 비밀번호 변경 하는 방법도 포스팅 하겠습니다.

1. 시스템 계정 비밀번호 찾기

  - 기본 시스템 계정은 sys , system 계정이 있습니다.

1-1. "Run SQL Command" 사용시

  가. 오라클 11g를 설치 할 경우 "Run SQL Command Line" 을 실행 합니다.

  나. conn 입력 후 엔터치면 사용자 명을 입력 하라고 합니다.

  다.  사용자명에 "sys as sysdba" 입력 합니다.

  라. 비밀번호는 없이 엔터를 입력 합니다.

  마. 그럼 Connected. 라고 하면서 접속이 됩니다.

  바. 접속 후 alter user sys identified by 1234; 로 비밀번호를 변경 합니다.

       (sys 는 계정명 입니다. 1234 는 변경할 비밀번호 입니다.)

  사. 또한, system 계정의 비밀번호도 변경 합니다.

   - Run SQL Command Line 실행 캡쳐 화면

1-2 cmd 사용시

  가. 윈도우에 보조프로그램에 명령프롬프트 실행

       또는 윈도우+r 실행 후 cmd 입력후 실행

  나. sqlplus 입력 합니다.

  다.  사용자명에 "sys as sysdba" 입력 합니다.

  라. 비밀번호는 없이 엔터를 입력 합니다.

  마. 그럼 Connected. 라고 하면서 접속이 됩니다.

  바. 접속 후 alter user sys identified by 1234; 로 비밀번호를 변경 합니다.

       (sys 는 계정명 입니다. 1234 는 변경할 비밀번호 입니다.)

  사. 또한, system 계정의 비밀번호도 변경 합니다.

   cmd 창으로 비밀번호 변경 실행 화면 캡쳐

2. 일반 사용자 비밀번호 변경

  - 일반 사용자의 비밀번호는 DBA권한이 있는 sys 계정으로 접속해야 합니다.

  - 접속후 alter user 계정 identified by 비밀번호; 명령으로 비밀번호 변경이 가능합니다.

  - 변경 후 conn 명령으로 계정 접속을 해봅니다. (저는 TUSER 계정의 비밀번호 변경 후 접속 해 보았습니다.)

일반사용자 및 시스템계정의 비밀번호 변경에 대해서 알아보았습니다.

비밀번호는 단순하게 1234로 변경 하였지만, 실제로 사용하시는 비밀번호는 특수문자 및 대소문자가 포함된 비밀번호를 사용하시기 바랍니다.

begin 
         FOR cur IN (SELECT OBJECT_NAME, OBJECT_TYPE
           FROM sys.USER_objects 
           WHERE  status = 'INVALID' ) LOOP 
          BEGIN
         if cur.OBJECT_TYPE = 'PACKAGE BODY' then 
          EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' ||  cur.OBJECT_NAME || '" compile body'; 
         else 
          EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.OBJECT_NAME || '" compile'; 
         end if; 
EXCEPTION
   WHEN OTHERS THEN NULL; 
END;
         end loop; 
         end;
         

SELECT
  TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24') "NEXT_TIME"
, COUNT(DISTINCT NAME) "TOTAL_ARC_COUNT"
, ROUND(SUM(DISTINCT BLOCKS*BLOCK_SIZE)/1024/1024/1024,2) "TOTAL_SIZE_GB"
, SUM(CASE WHEN INST_ID = 1 AND THREAD# = 1 THEN 1 ELSE 0 END) "RAC1"
, SUM(CASE WHEN INST_ID = 2 AND THREAD# = 2 THEN 1 ELSE 0 END) "RAC2"
, SUM(CASE WHEN INST_ID = 3 AND THREAD# = 3 THEN 1 ELSE 0 END) "RAC3"
, SUM(CASE WHEN INST_ID = 4 AND THREAD# = 4 THEN 1 ELSE 0 END) "RAC4"
FROM GV$ARCHIVED_LOG
WHERE NEXT_TIME >= TRUNC(SYSDATE) -10
GROUP BY TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24')
ORDER BY 1;



select * 
  from GV$ARCHIVED_LOG;
  
  
  select * from v$recovery_file_dest;
  
  select * from V$ARCHIVED_LOG;
  
  select * from v$archive;
  
  select * from v$log;
  
  select * from v$archive_dest;
  
  select * from v$archive_processes;
 
 select dest_name, name, sequence#, b.status
  from v$archive_dest a, v$archived_log b
  where a.dest_id = b.dest_id;

오라클(oracle) 락(lock) 확인 및 제거(kill)
============================================================================

-- 락걸린 테이블 확인
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 A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
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' AND C.OBJECT_NAME IN ('테이블명');

/* 락발생 사용자와 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
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 , '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;

/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '26,6044';

/* 현재 접속자의 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;

BEGIN
 -- 시노님 삭제 
    BEGIN 
    FOR C IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'NWISDBA'  
    ) LOOP 
        EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '|| C.TABLE_NAME || '; 
    END LOOP; 
    END; 

    -- 테이블삭제 
    BEGIN 
    FOR C IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'NWISDBA'  
    ) LOOP 
        EXECUTE IMMEDIATE 'DROP TABLE '|| C.OWNER || '.' || C.TABLE_NAME || ' CASCADE CONSTRAINTS  PURGE'; 
    END LOOP; 
    END; 

    -- 시퀀스 삭제 
    BEGIN 
    FOR C IN (SELECT SEQUENCE_OWNER,SEQUENCE_NAME FROM ALL_SEQUENCES  
    WHERE SEQUENCE_OWNER = 'NWISDBA'  
    ) LOOP 
        EXECUTE IMMEDIATE 'DROP  SEQUENCE '|| C.SEQUENCE_OWNER || '.' || C.SEQUENCE_NAME; 
    END LOOP; 
    END; 

    -- 뷰 삭제 
    BEGIN  
    FOR C IN ( 
    SELECT OWNER,VIEW_NAME FROM ALL_VIEWS 
    WHERE OWNER = 'NWISDBA'  
    ) LOOP 
        EXECUTE IMMEDIATE 'DROP  VIEW '|| C.OWNER || '.' || C.VIEW_NAME; 
    END LOOP; 
    END;
     
    -- 트리거 삭제 
    BEGIN
    FOR C IN (SELECT OWNER, TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER = 'NWISDBA' ) 
    LOOP
        EXECUTE IMMEDIATE 'DROP  TRIGGER '|| C.OWNER || '.' || C.TRIGGER_NAME;
    END LOOP;
    END;

    --인덱스 삭제
    BEGIN
    FOR C IN (SELECT OWNER, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = 'NWISDBA' AND UNIQUENESS <> 'UNIQUE') 
    LOOP
        EXECUTE IMMEDIATE 'DROP  INDEX '|| C.OWNER || '.' || C.INDEX_NAME;
    END LOOP;
    END;

    --프로시저 삭제
    BEGIN
    FOR C IN (SELECT OWNER, OBJECT_NAME FROM ALL_PROCEDURES WHERE OWNER = 'NWISDBA' AND OBJECT_TYPE = 'PROCEDURE') 
    LOOP
        EXECUTE IMMEDIATE 'DROP  PROCEDURE '|| C.OWNER || '.' || C.OBJECT_NAME;
    END LOOP;
    END; 

    --펑션 삭제
    BEGIN
    FOR C IN (SELECT OWNER, OBJECT_NAME FROM ALL_PROCEDURES WHERE OWNER = 'NWISDBA' AND OBJECT_TYPE = 'FUNCTION') 
    LOOP
        EXECUTE IMMEDIATE 'DROP  FUNCTION '|| C.OWNER || '.' || C.OBJECT_NAME;
    END LOOP;
    END;
    
 END;

--뷰 상태 리스트
SELECT OBJECT_NAME
     , STATUS
     , B.COMMENTS
  FROM (SELECT A.*, 'NWISDBA' AS OWNER
          FROM USER_OBJECTS A
         WHERE OBJECT_TYPE = 'VIEW'
           AND STATUS = 'INVALID'
        ) A
     , ALL_TAB_COMMENTS B  
 WHERE A.OWNER = B.OWNER(+)  
   AND A.OBJECT_NAME = B.TABLE_NAME(+)  
 ORDER BY OBJECT_NAME;
-- WHERE OBJECT_NAME LIKE 'TB_ATTEND%' AND OBJECT_TYPE='TABLE'; 


oracle 각종 조회 쿼리
1. 사용자와 롤에 부여된 시스템 권한을 조회
SELECT * FROM USER_SYS_PRIVS; 

2. 현재 세션에서 사용자와 롤에 부여된 시스템 권한을 조회.
SELECT * FROM SESSION_PRIVS; 

3.. 내가 다른 사용자에게 부여한 객체 권한을 조회.
SELECT * FROM USER_TAB_PRIVS_MADE; 

4. 나에게 부여된 객체 권한, 객체 이름을 조회.
SELECT * FROM USER_TAB_PRIVS_RECD; 

5. 내가 다른 사용자에게 부여한 칼럼에 대한 객체 권한과 칼럼 이름을 조회.
SELECT * FROM USER_COL_PRIVS_MADE; 

6. 나에게 부여된 칼럼에 대한 객체 권한과 칼럼 이름을 조회.
SELECT * FROM USER_COL_PRIVS_RECD;

7. 롤에 부여한 시스템 권한 조회
SELECT * FORM ROLE_SYS_PRIVS; 

8. 롤에 부여한 객체 권한 조회
SELECT * FROM ROLE_TAB_PRIVS; 

9. 사용자가 부여받은 롤 조회
SELECT * FROM USER_ROLE_PRIVS; 

10. USER_데이터 딕셔너리 뷰 조회
SELECT TABLE_NAME FROM USER_TABLES; 

11. ALL_데이터 딕셔너리 뷰의 조회
SELECT OWNER, TABLE_NAME FROM ALL_TABLES; 

12. DBA_딕셔너리 뷰의 조회
SELECT OWNER, TABLE_NAME FROM DBA_TABLES; 

13. USER_TABLE 조회
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;
SELECT TABLE_NAME, TABLE_SPACE, MIN_EXTENTS, MAX_EXTENTS 
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'ADD%'; //이런식을 테이블 이름을 넣고

 

14.USER_OBJECTS 조회
SELECT OBJECT_NAEM, OBJECT_TYPE, CREATED
FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE 'TB_ATTEND%' AND OBJECT_TYPE='TABLE'; 

15. USER_CATALOG 조회
SELECT * FROM USER_CATALOG; 

16. USER_CONSTRAINTS 조회
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('TB_CLASS', 'TB_SUGANG');   

17. 인덱스 정보 조회
SELECT INDEX_NAME, UNIQUENESS 
FROM USER_INDEXES
WHERE TABLE_NAME='TBSTUDENT';   

18. 뷰의 확인
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS; 

19. redolog 조회
select group#,members, bytes/10241024||’M’ from v$log;      

20. redolog 위치 조회
 select member from v$logfile;    

21. 컨트롤 파일 파라미터 조회
show parameter control_files;
select * from v$controlfile; 

22. 동적으로 변경 가능한 파라미터 조회
SELECT
v.name,
v.value,
DECODE(ISSES_MODIFIABLE, 'IMMEDIATE', 'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') AS SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO', 'DEFERRED', 'NO','YES') AS SYS_MODIFIABLE,
v.description
FROM V$PARAMETER v WHERE name not like 'nls%'
ORDER BY 1; 

23. 딕셔너리 뷰의 조회
SELECT * FROM DICT_COLUMNS WHERE TABLE_NAME LIKE '%INDEX%';
SELECT * FROM dict WHERE table_name LIKE UPPER('%&데이타사전%');

24. 세그먼트 조회
select segment_name, tablespace_name, segment_ID, FILE_ID from dba_rollback_segs; 

25. 블럭 조회
SHOW PARAMETER BLOCK; 

26. shared_pool_size 조회
SHOW PARAMETER SHARED 

27. 인스턴스 조회
select instance_name, host_name from v$instance; 

28. 버퍼 조회
show parameter buffer; 

29. 데이타 파일 조회
SELECT NAME,BYTES FROM V$DATAFILE;

30. DBA 데이타 파일 조회
SELECT FILE_NAME, TABLESPACE_NAME  FROM DBA_DATA_FILES; 

31. SORT 영역 크기 조회
SHOW PARAMETER SORT_AREA_SIZE; 

32. 아카이브 확인
archive log list;
SELECT * FROM V$DATABASE; (시스템이 아카이브인지 확인할 수 있다) 

33. 인덱스 블럭에 delete 된 entry 조회
 > analyze index 'index_name' validate structure 로 분석 후에
 > select  LF_ROWS,
            DEL_LF_ROWS,
            DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED, 
    from index_stats 
    where NAME= :p_index; 

34. 딕셔너리캐시 히트율 조회
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" 
from v$rowcache;  

35. 버퍼캐시 히트율 조회
select round(((1-(sum(decode(name, 'physical reads', value,0))/ 
(sum(decode(name, 'db block gets', value,0))+ 
(sum(decode(name, 'consistent gets', value, 0))))))*100),2) 
|| '%' "Buffer Cache Hit Ratio" 
from v$sysstat;

 36. 라이브러리캐시히트율 조회
select namespace, sum(gets) gets, sum(gethits) gethits,
       round(sum(gethits)/sum(decode(gets,0,0.0001,gets)),3)
       "Get Hit Ratio",
       sum(pins) pins, sum(pinhits) pinhits, 
       round(sum(pinhits)/sum(decode(pins,0,0.0001,pins)),3)
       "Pin Hit Ratio",
       sum(invalidations) invalidations
  from v$librarycache
group by namespace;    

37. 설치된 옵션 조회
SELECT * FROM V$OPTION;

38. Tablespace 장애 확인
select h.tablespace_name, d.name, h.error
from v$datafile d, v$datafile_header h

where d.file#=h.file#;

참조**********

구분 테이블명 설명 
오브젝트 USER_OBJECTS (OBJ) 모든 오브젝트에 대한 정보를 지원 오즈젝트 유형, 작성시간, 오브젝트에 사용된 최종 DDL 명령, alter, grant 및 revoke 등 
테이블 USER_TABLES (TABS) 테이블에 대한 정보 
열 USER_TAB_COLUMNS (COLS) 컬럼에 대한 정보 
뷰 USER_VIEWS 뷰에 대한 정보 
동의어 USER_SYNONYMS (SYN) 
시퀀스 USER_SEQUENCES (SEQ) 
제약조건 USER_CONSTARINTS   
제약조건열 USER_CONS_COLUMNS 제약 조건을 가진 열에 대한 정보 
제약조건의 예외사항 EXCEPTIONS 제약조건을 활성화시 에러사항에 대한 정보 
테이블 주석 USER_TAB_COMMENTS 테이블/뷰에 대한 주석 
열 주석 USER_COL_COMMENTS 열에 대한 주석 
인덱스 USER_INDEXES (IND) ( 인덱스에 관한 정보) 
인덱스 열 USER_IND_COLUMNS 인덱스열에 대한 정보 
클러스터 USER_CLUSTERS (CLU) 클러스터와 관련된 정보 
데이터베이스 링크 USER_DB_LINKS 링크에 관련된 정보 
스냅샷 USER_SNAPSHOTS   
스냅샷 로그 USER_SNAPSHOT_LOGS   
트리거 USER_TRIGGERS   
프로시저, 함수 및 패키지 USER_SOURCE   
코드 오류 USER_ERRORS   
테이블스페이스 USER_TABLESPACES   
영역 할당량 USER_TS_QUOTAS 테이블스레이스 단위로 사용자가 이용할 수 있는 영역의 최대크기와 할당된 영역의 크기 파악에 대한 정보 
세그먼트와 익스텐트 USER_SEGMENTS 
USER_EXTENTS   
여유 영역 USER_FREE_SPACE 현재 여유로 표시된 영역이 얼마인지에 대한 정보 
사용자 USER_USERS   
자원 제한량 USER_RESOURCE_LIMITS   
테이블 권한 USER_TAB_PRIVS   
열 권한 USER_COL_PRIVS   
시스템 권한  USER_SYS_PRIVS 

/*테이블 ANALYZE*/
SELECT 'ANALYZE TABLE '|| TABLE_NAME ||' COMPUTE STATISTICS;' 
  FROM USER_TABLES 
-- WHERE SUBSTR(TABLE_NAME,4,1) = '_'
   
; 

/*인덱스 ANALYZE*/
SELECT 'ANALYZE INDEX NWISDBA.'||INDEX_NAME||' COMPUTE SYSTEM STATISTICS;'
  FROM USER_INDEXES --WHERE INDEX_TYPE <> 'LOB'
-- ORDER BY INDEX_NAME
;