[Sql] Oracle - Self Training 오라클 정리

DB상태 확인 : select status from v$instance;

DB 단계올리기:alter database [nomount,mount,open]

DB 오픈모드확인:select open_mode from v$database;

ORA-01552:롤백 세그먼트가 없을때도 뜬다고한다.

          open_mode 가 read only 로 설정된 경우에도 이 에러가 뜬다(10g)

 

DB 제한모드:alter system [enable|disable] restricted session;

제한모드 조회:select logins from v$instance;

(restricted는 제한모드,allowed 는 일반모드)

 

DB 접속중 세션 확인:select sid,serial#,username,status from v$session;

세션종료:alter system kill session 'sid번호,serial 번호' ;

룰에 부여된 시스템 권한 조회:select * from dba_sys_privs;

(예:restricted 권한받은 사용자 찾기

 : select * from dba_sys_privs where privilege like '%RESTRICT%';)

 

파라미터 이름과 설정된 값 확인

:select name,value from v$parameter;

설정된 파라미터 값 확인  :show parameter (파라미터명)

 

-- PFILE 생성
SQL> CREATE PFILE=’D:\oracle\ora92\database\INITORACLE.ORA’
          FROM SPFILE=’D:\oracle\ora92\database\SPFILEORACLE.ORA’
파일이 생성되었습니다.

-- SPFILE 생성
SQL> CREATE SPFILE=’D:\oracle\ora92\database\SPFILEORACLE02.ORA’
         FROM PFILE=’D:\oracle\ora92\database\INITORACLE.ORA’

 

컨트롤 파일 조회 : select name from v$controlfile;

 

딕셔너리는 OPEN상태에서 조회가 가능하고

동적성능뷰는 MOUNT상태에서 조회가 가능하다.

 

 

테이블 스페이스 생성

create tablespace datafile '위치/파일명.dbf' size 1M;

9i버전은 segment space management auto 지정

8i버전은 extent management local 지정

 

테이블 스페이스 확장

alter databae datafile '위치/파일명' resize 2M;

alter tablespace <테이블스페이스이름>  add datafile '추가할 테이블스페이스 위치/파일명'
size 5;

 

UNDO 확장

alter tablespace <undo 테이블스페이스명> add datafile '추가할 테이블스페이스 위치/파일명'

size 5;

alter database datafile '위치/파일명' resize 2M;

 

temporary 생성

create temporary tablespace <테이블스페이스명>

tempfile '데이터파일명' size 크기 ;

-locally management 가 디폴트
-segment space management를 auto로 지정할수없다.(manual임)
-2번절이 datafile이 아니라 tempfile절임에 주의해야한다.

alter database default temprorary tablespace<테이블스페이스명>

-지정된 테이블스페이스를 default temporary 로 정의한다.

 

유저의 테이블스페이스 확인
(select owner,table_name,tablespace_name from dba_tables where owner='유저명';)

디폴트 테이블 스페이스 조회하기
(select * from database_properties where property_name like '%DEFAULT_PERMANENT%';)

default temporary 조회하기
(select * from database_properties where property_name like '%TEMP%';)

 

 

ROLE 이나 USER에 할당된 시스템 권한 보기

select grantee,privilege,admin_option from dba_sys_privs
where grantee in ('ROLE 이나 USER')
order by 1;

 

ROLE 이나 USER에 할당된 객체 권한

select grantee,owner,table_name,privilege,grantor from dba_tab_privs
where grantee in ('ROLE이나 USER')
order by 1;

 

ROLE 이나 USER에 할당된 ROLE을 확인

select grantee,granted_role from dba_role_privs
where grantee in ('ROLE이나 USER')
order by 1;

 

ROLE이 할당받은 권한만을 조회할경우에는

role_sys_privs,role_tab_privs,role_role_privs 에서도 조회가능하다.

 

세그먼트란?

오라클에서 세그먼트란 디스크의 공간을 차지하는 모든 오브젝트를 통칭하는 용어이다.

테이블이나 인덱스와 같이 실제로 저장되는 데이터가 있는 것들은 모드 세그먼트이고,

뷰처럼 저장되는 데이터가 없는 것들은 세그먼트라 부르지 않는다.

예외적으로 mview는 세그먼트에 속한다.

 

테이블 생성 시 고려사항

1.system 테이블스페이스에는 딕셔너리를 제외한 세그먼트를 절대 생성 하지 않는다.

2.테이블과 인덱스를 동일한 디스크(테이블스페이스)에 저장하지 않는다.

3.관계에 의해 빈번히 조인되는 테이블들을 동일한 디스크(테이블스페이스)에 저장하지 않는다.

 

 

물리적인 통계정보 갱신 방법

<analyze>-DBA가 관리 모니터링 목적으로 통계 정보를 갱신하고자 할때 사용

analyze table 테이블명 compute statistics;

analyze index 인덱스명 validate structure;

<dbms_stats>-SQL성능 최적화를 위해서 사용

exec dbms_stats.gather_schema_stats(유저명);

exec dbms_stats.gather_table_stats(유저명.테이블명);

 

 

트랜잭션이란?

트랜잭션(Transaction)은 데이터베이스에서 데이터를 처리하는 하나의 논리적인 작업단위를 의미한다.

 

DELETE 와 TRUNCATE 의 차이점

-DELETE는 많은 행을 삭제 할경우 많은 자원이 소모된다(느려진다).

왜냐하면 DELETE는 DML언어로써 삭제 이전 상태로 원상 복귀할 경우를 생각해서

ROLLBACK정보를 저장하고 있기 때문이다.(Undo segment write)

-TRUNCATE는  DDL 명령문으로 ROLLBACK될수가 없다.

 

DDL(create,rename,alter,truncate),DCL(grant,revoke)문은 자동으로 커밋된다(auto commit)

 

 

 

 

 

 

 

PL/SQL insert 반복

declare
    v_cnt number(6) := 1;
   begin
   DBMS_OUTPUT.ENABLE;
   LOOP
   insert into scott1.score values('chae'||to_char(v_cnt),'jaak'||to_char(v_cnt),v_cnt);
   v_cnt:=v_cnt+1;
   exit when v_cnt>3000;
   end loop;
  end;

 

 

 

제약조건 검색하기

select constraint_name,constraint_type,status
from dba_constraints
where owner='소유자' and table_name='테이블명';

 

제약조건 비활성 시키기

alter table 테이블명

disable constraint 제약조건이름

 

 

◈ DICTIONARY MANAGED TABLESPACE
 
 - 지금까지 사용하여 왔던 전통적인 테이블스페이스 관리방식 입니다.  
   즉, 각 Objects(테이블,인덱 스,…)의 Extents를 Data Dictionary Table들을 사용하여 관리하는 방식이죠.
 
 - 만약, 어떤 테이블에 하나의 새로운 Extent가 필요할 때에는 해당 Data Dictionary Table을 참조하기 위한 여러 개의 Recursive SQL이 내부적으로 수행되어 집니다. 
   이것은 때때로 Performance의 저하 를 초래하기도 합니다.
   하 지만, Extent의 크기 를 아주 유연하게 할 수 있는 장점이 있습니다.
   즉, 하나의 Tablespace내의 서로 다른 Object는 서로 다른 Extent의 크기를 가질 수 있습니다.
   하지만, 대부분의 경우에는 Fragment를 방지하기 위해서 동일한 크기의 Extent를 할당하여 사용하고 있습니다.

◈ LOCALLY MANAGED TABLESPACES

 - 테이블스페이스 내의 Free space, 또는 Extent를 관리하기 위해서 Data Dictionary Table을 사용하지 않습니다.

 - 대신에 해당 테이블스페이스 내에서 Bitmap을 이용하여 해당 테이블스페이스 내의 모든 Free space, 사용된 Extent의 할당 정보를 관리 합니다. 

 - Locally Managed Tablespace는, 자체 extent에 대한 관리를 각각의 데이터 파일에 비트맵 형식으로 저장하여 관리하는 테이블스페이스로, 데이터 파일을 구성하는 블럭이 비어 있는지, 사용 중인지에 대한 정보를 관리 합니다.

 - 비트맵의 각각의 비트는, 하나의 블럭 또는 블럭의 그룹에 해당하는 정보를 나타 냅니다.

 - 익스텐트가 할당되거나, 비워지거나, 재사용될 때, 오라클에서는 블럭의 새로운 상태를 나타내기 위해 비트맵의 값을 변경 합니다.

 - 이렇게 함으로써 Data Dictionary Table을 참조하기 위한 Recursive SQL을 사용하지 않아도 됩니다.

 ☞ LOCALLY MANAGED TABLESPACES의 특징

  1) 공간 정보 관리를 위한 내부 작업이 감소 합니다.

  2) 데이터 딕셔너리 테이블에 대한 경합이 감소 됩니다.

  3) 익스텐트 관리와 관련된 관련 rollback 생성이 되지 않습니다..

  4) Tablespace에 대한 주기적인 Coalesce 작업을 하지 않아도 됩니다. 이것은 해당 테이블스페이스내의 모든 Extent에 대한 정보를 Bit로 표현하기 때문에 가능합니다.

  5) 해당 테이블스페이스 내의 모든 Extent는 동일한 크기의 Extent를 할당하게 됩니다.

 ☞ LOCALLY MANAGED TABLESPACE 테이블스페이스의 공간 관리

  1) 사용되지 않는 익스텐트 정보가 비트맵에 의해 관리 됩니다. 따라서, 테이블스페이스의 일부분이 비트맵 정보를 저장하는데 사용 됩니다.

  2) 각 비트는, 블럭이나, 블럭의 그룹의 정보를 나타냅니다.

  3) 비트 정보는, 사용 중인지, 그렇지 않은지를 나타냅니다.

  4) DBA_EXTENTS 나 DBA_FREE_SPACE 등의 뷰는 동일하게 사용 합니다.

 

 

 

 

exception 사용하기

@?/rdbms/admin/utlexpt1.sql 실행

alter table 테이블명1
enable constraint 제약조건명
exceptions into exceptions;     <-제약조건에 어긋나는 행의 정보가 exceptions 테이블에 저장 된다.

select * from exceptions;

 

create table 테이블명2
as
select * from 테이블명1             <--기존에 있던 테이블1명에서 제약조건에 위배되는 사항을 테이블2로 옮긴다.
where rowid in (select row_id from exceptions); 

delete from stu
where rowid in(select row_id from exceptions);      <--제약조건 위배되는 사항 삭제하기

alter table 테이블명1
enable constraint 제약조건명     <--제약조건 활성
exceptions into exceptions;