오라클 블록 사이즈 : 2k, 4k, 8k(기본값), 16k, 32k

SELECT * FROM dba_tablespaces; TABLESPACE_NAME BLOCK_SIZE ALLOCATIO
--------------- ---------- ---------
SYSTEM                8192 SYSTEM
SYSAUX                8192 SYSTEM
USERS                 8192 SYSTEM
EXAMPLE               8192 SYSTEM
UNDO1                 8192 SYSTEM
TEMP                  8192 UNIFORM



# SGA영역 상태정보
SELECT * FROM v$sgastat;


# SGA 메모리 free 확인

SELECT * FROM v$sgastat 
WHERE name = 'free memory';		 POOL         NAME              BYTES
------------ ------------ ----------
shared pool  free memory    13726036
large pool   free memory     7897088
java pool    free memory     4194304
SELECT round(sum(bytes)/1024/1024,2) free_mb 
FROM v$sgastat
WHERE name = 'free memory';		 FREE_MB
26.25



SELECT * FROMm v$memory_dynamic_components;

기본 블록 크기는 데이터베이스 생성시 결정한다.
dbca를 통해서 데이터베이스 생성시에는 8k로 결정된다. (기본값)
다른 block 크기를 기본 블록으로 설정하려면 수동으로 데이터베이스를 생성하면된다.

SQL> show parameter db_block_size NAME                TYPE        VALUE
------------------- ----------- -------
db_block_size       integer     8192
SQL> show parameter db_cache_size

db_cache_size
: db_block_size 로 설정한 값의 data buffer cache
  기본값으로 설정된 8k 블록은 전부 여기로 모인다.		 NAME                TYPE        VALUE
------------------- ----------- -------
db_cache_size       big integer 0
SQL> show parameter %k_cache_size NAME                TYPE        VALUE
------------------- ----------- -------
db_8k_cache_size    big integer 0
 └> Standard Block Size

db_2k_cache_size    big integer 0
db_4k_cache_size    big integer 0
db_16k_cache_size   big integer 0
db_32k_cache_size   big integer 0
 └> non standard block



1. 4k block 메모리 공간 확보

SQL> show parameter db_4k_cache_size		 NAME                TYPE        VALUE
------------------- ----------- -------
db_4k_cache_size    big integer 0
alter system set db_4k_cache_size = 10M;  
SQL> show parameter db_4k_cache_size

- 4k 배수단위로 등록 된다.		 NAME                TYPE        VALUE
------------------- ----------- -------
db_4k_cache_size    big integer 12M
2. 4k block size tablespace 생성

CREATE TABLESPACE oltp_tbs
DATAFILE '/home/oracle/userdata/oltp_tbs01.dbf'
SIZE 5M AUTOEXTEND ON
BLOCKSIZE 4K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

oltp_tbs  tablespase 생성
데이터 파일 위치
5M 빈파일 생성, 자동으로 크기 증
4k block size 설정
extent를 bitmap으로 관리, extent 크기를 1M 단위로 증가
ASSM 자동 세그먼트 공간 관리
SELECT * FROM dba_tablespaces
WHERE tablespace_name = 'OLTP_TBS';		 TABLESPACE_NAME : OLTP_TBS
BLOCK_SIZE : 4096
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : UNIFORM
SEGMENT_SPACE_MANAGEMENT : AUTO
...
3. TABLE 생성

CREATE TABLE hr.oltp_emp
TABLESPACE oltp_tbs
AS SELECT * FROM hr.employees;		  
SELECT * FROM dba_data_files 
WHERE tablespace_name = 'OLTP_TBS';		 FILE_NAME : /home/oracle/userdata/oltp_tbs01.dbf
FILE_ID : 3
TABLESPACE_NAME : OLTP_TBS
...
SELECT * FROM dba_extents
WHERE owner = 'HR'
AND segment_name IN ('OLTP_EMP','EMPLOYEES');


- EMPLOYEES의 BLOCK_ID가 200번부터 시작해서 BLOCKS 8개 = 207번까지 사용

 COLUMNS              EMPLOYEES  OLTP_EMP
-------------------- ---------- ----------
EXTENT_ID            0          0
BYTES                65536      1048576
BLOCKS               8          256
FILE_ID              5          3
BLOCK_ID             200        256
SEGMENT_TYPE         TABLE      TABLE
TABLESPACE_NAME      EXAMPLE    OLTP_TBS
...
SELECT * FROM dba_segments
WHERE owner = 'HR'
AND segment_name IN ('OLTP_EMP','EMPLOYEES');


- EMPLOYEES의 HEADER_BLOCK은 202번부터 시작한다		 COLUMNS              EMPLOYEES  OLTP_EMP
-------------------- ---------- ----------
BLOCKS               8          256
HEADER_BLOCK         202        261
SEGMENT_SUBTYPE      ASSM       ASSM
BUFFER_POOL          DEFAULT    DEFAULT
TABLESPACE_NAME      EXAMPLE    OLTP_TBS
EXTENTS              1          1
...
SELECT * FROM hr.oltp_emp 
WHERE department_id = 20;		 1. parse
2. bind
3. execute
4. fetch


책 = table
페이지 = block
문장 = row
색인(본문외 공간) = index
블럭 안에서 단어 위치 = IO


 

<< SYS SESSION >>
  SELECT * FROM dba_data_files;

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO1


# UNDO TABLESPACE 생성
CREATE UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL;

SELECT segment_id, segment_name, owner, tablespace_name, status
FROM dba_rollback_segs;
TABLESPACE_NAME
- SYSTEM : 딕셔너리 테이블 갱신조작 시 사용(INSERT, DELETE, UPDATE)
- PUBLIC : USER 사용

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
<< HR SESSION >>
  UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = 200;
<< SYS SESSION >>
  << sqldeveloper >>
SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn;
NAME                   EXTENTS  RSSIZE  XACTS STATUS
---------------------- ------- -------- ----- ---------------
_SYSSMU10_4227184396$        2   122880     1 ONLINE
...

ALTER SYSTEM SET undo_tablespace = undotbs;

show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS


SELECT segment_id, segment_name, owner, tablespace_name, status
FROM dba_rollback_segs;

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn;
NAME                   EXTENTS  RSSIZE  XACTS STATUS
---------------------- ------- -------- ----- ---------------
_SYSSMU20_1305247116$        4   253952     1 PENDING OFFLINE
...
- PENDING OFFLINE : 새로운 트랜젝션 안받음. undo_retention 보장으로 기본값 900(15분) 유지
<< HR SESSION >>
  DELECT FROM hr.employees WHERE employee_id = 202;
<< SYS SESSION >>
  SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn;
NAME                   EXTENTS  RSSIZE  XACTS STATUS
---------------------- ------- -------- ----- ---------------
_SYSSMU10_4227184396$        2   122880     1 ONLINE
...
ㄴ 동일한 세션에서 작업 시 사용된 기존 언두 세그먼트 그대로 사용

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t WHERE s.saddr = t.ses_addr;
USERNAME       XIDUSN     UBAFIL     UBABLK  USED_UBLK
---------- ---------- ---------- ---------- ----------
HR                 10          6        188          1
XIDUSN      : UNDO SEGMENT ID
UBAFIL      : UNDO DATA FILE ID
UBABLK      : UNDO 블럭번호
USED_UBLK   : 사용하고있는 UNDO BLOCK 수 : 누가 과도하게 DML 작업하고 있는가


# 10분 단위 undo 발생량 정보 확인
SELECT * FROM v$undostat;

SELECT TO_CHAR(BEGIN_TIME, 'yyyy-mm-dd hh24:mi:ss') BEGIN_TIME,     -- 트랜잭션 발생시간
        TO_CHAR (END_TIME, 'yyyy-mm-dd hh24: mi:ss') END_TIME,      -- 트랜잭션 종료시간
        UNDOBLKS                                                    -- 이 구간에 발생한 undo block 수
FROM v$undostat;

SELECT TO_CHAR (BEGIN_TIME, 'yyyy-mm-dd') BEGIN_TIME, SUM(UNDOBLKS)
FROM v$undostat
GROUP BY TO_CHAR(BEGIN_TIME, 'yyyy-mm-dd');
ㄴ 일자별 체크





<< SYS SESSION >>
  ■ USER 관리

CREATE USER  ora1 IDENTIFIED BY oracle;
> CREATE USER  유저명 IDENTIFIED BY  비밀번호;    -- 기본 생성 방식

예) CREATE TABLE test (id number); 생성 시 segment의 기본 tablespace는 ?
SELECT * FROM dba_users WHERE username = 'ORA1';
DEFAULT_TABLESPACE   : USERS
TEMPORARY_TABLESPACE : TEMP

유저 생성시 default tablespace가 지정되어 있지 않으면 system tablespace가 되는 문제점때문에
10g버전부터 데이터베이스 생성시에 데이터베이스 레벨에서 default tablespace가 생성되어 있다.


# 데이터베이스 레벨에 기본값으로 설정된 정보 확인
SELECT * FROM database_properties;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS
...


# USER 생성 시 TABLESPACE의 DEFAULT 값을 설정하는 습관을 갖자.
CREATE USER ora2 IDENTIFIED BY oracle
DEFUALT TABLESPACE example  -- 수동으로 DEFUALT TABLESPACE 지정
TEMPORARY TABLESPACE temp   -- 수동으로 DEFUALT TEMPORARY TABLESPACE 지정
QUOTA 1M ON example;        -- example 공간을 최대 1M 사용할 수 있는 권한 부여

SELECT * FROM dba_users WHERE username = 'ORA2';
DEFAULT_TABLESPACE   : EXAMPLE
TEMPORARY_TABLESPACE : TEMP

CREATE TABLESPACE user_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/user_tbs01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE user_temp
TEMPFILE '/u01/app/oracle/oradata/ora11g/user_temp01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;
SELECT * FROM dba_temp_files;
SELECT * FROM dba_users WHERE username IN ('ORA1', 'ORA2');

SELECT * FROM database_properties;
>> DEFAULT_TEMP_TABLESPACE, DEFAULT_PERMANENT_TABLESPACE 기본값 확인


ALTER DATABASE DEFAULT TABLESPACE user_tbs;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE user_temp;

SELECT * FROM database_properties;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE        USER_TEMP
DEFAULT_PERMANENT_TABLESPACE   USER_TBS

SELECT * FROM dba_users WHERE username IN  ('ORA1', 'ORA2');
USERNAME   DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
---------- ------------------- ---------------------
ORA1       USER_TBS            USER_TEMP
ORA2       EXAMPLE             USER_TEMP
ㄴ DEFAULT 값으로 사용중이던 TEMP -> USER_TEMP 로 따라 바뀐다.



CREATE TEMPORARY TABLESPACE temp10
TEMPFILE '/u01/app/oracle/oradata/ora11g/temp10.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

SELECT * FROM database_properties;
SELECT * FROM dba_tablespaces;
SELECT * FROM dba_temp_files;
SELECT * FROM dba_data_files;

CREATE USER ora3 IDENTIFIED BY oracle
DEFAULT TABLESPACE example      -- example로 기본지정한다.
TEMPORARY TABLESPACE temp10     -- temp10으로 기본지정한다.
QUOTA 1M ON example;            -- example이라는 tablespace의 공간을 사용할 수 있는 권한 부여 최대 1M

SELECT * FROM dba_users WHERE username IN ('ORA1', 'ORA2', 'ORA3');
USERNAME   DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
---------- ------------------- ---------------------
ORA1       USER_TBS            USER_TEMP
ORA2       EXAMPLE             USER_TEMP
ORA3       EXAMPLE             TEMP10

SELECT * FROM database_properties;
DEFAULT_TEMP_TABLESPACE         USER_TEMP
DEFAULT_PERMANENT_TABLESPACE USER_TBS


ALTER DATABASE DEFAULT TABLESPACE users;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SELECT * FROM database_properties;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS

SELECT * FROM dba_users WHERE username IN ('ORA1', 'ORA2', 'ORA3');
USERNAME   DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
---------- ------------------- ---------------------
ORA1       USERS               TEMP
ORA2       EXAMPLE             TEMP
ORA3       EXAMPLE             TEMP10


ALTER DATABASE DEFAULT TABLESPACE user_tbs;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE user_temp;

SELECT * FROM database_properties;
DEFAULT_TEMP_TABLESPACE         USER_TEMP
DEFAULT_PERMANENT_TABLESPACE USER_TBS

SELECT * FROM dba_users WHERE username IN ('ORA1', 'ORA2', 'ORA3');
USERNAME   DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
---------- ------------------- ---------------------
ORA1       USER_TBS            USER_TEMP
ORA2       EXAMPLE             USER_TEMP
ORA3       EXAMPLE             TEMP10



GRANT CREATE SESSION, CREATE TABLE TO  ora1, ora2, ora3;

SELECT * FROM dba_sys_privs WHERE grantee IN ('ORA1', 'ORA2', 'ORA3');
GRANTEE    PRIVILEGE            ADMIN_OPTION
---------- -------------------- -------------
ORA2       CREATE SESSION       NO
ORA1       CREATE SESSION       NO
ORA1       CREATE TABLE         NO
ORA3       CREATE TABLE         NO
ORA3       CREATE SESSION       NO
ORA2       CREATE TABLE         NO
<< ORA1 SESSION >>
  SELECT * FROM session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE

CREATE TABLE test (id number, name varchar2(30), day date);

SELECT table_name, tablespace_name FROM user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USER_TBS

SELECT * FROM user_users;

INSERT INTO test(id, name, day) VALUES (1, 'james', sysdate);
ORA-01950: no privileges on tablespace 'USER_TBS'
>> USER_TBS tablespace에 대한 quota 값을 받지 않아서 발생
    - table은 딕셔너리에 설계정보만 저장된 상태이다.
    - 인서트 시 quota 값 확인해야하는데 없이 테이블을 만들었다.

SELECT * FROM user_ts_quotas;
no rows selected
>> quota값을 받은적이 없다.
<< SYS SESSION >>
  ALTER USER ora1 QUOTA 1M ON user_tbs;
ALTER USER ora1 QUOTA 1M IN example;

SELECT * FROM dba_ts_quotas WHERE username = 'ORA1';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
USER_TBS     ORA1     0     1048576     0     128         NO
EXAMPLE         ORA1     0     1048576     0     128         NO
<< ORA1 SESSION >>
  SELECT * FROM user_ts_quotas;
TABLESPACE_NAME      USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPED
-------------------- ---------- ---------- ---------- ---------- ---------- --------
USER_TBS             ORA1                0    1048576          0        128 NO
EXAMPLE              ORA1                0    1048576          0        128 NO

INSERT INTO test(id, name, day) VALUES (1, 'james', sysdate);

COMMIT;

SELECT * FROM test;
        ID NAME                           DAY
---------- ------------------------------ ---------
         1 james                          19-DEC-23

CREATE TABLE emp(id number, name varchar2(30), day date) TABLESPACE example;

INSERT INTO emp (id, name, day) VALUES (1, 'james', sysdate);

COMMIT;

SELECT * FROM emp ;
        ID NAME                           DAY
---------- ------------------------------ ---------
         1 james                          19-DEC-23
<< SYS SESSION >>
  SELECT * FROM ora1.test;
        ID NAME                           DAY
---------- ------------------------------ ---------
         1 james                          19-DEC-23

SELECT * FROM ora1.emp;
        ID NAME                           DAY
---------- ------------------------------ ---------
         1 james                          19-DEC-23

 

저작자표시

'Oracle 11g > Oracle DB' 카테고리의 다른 글

52 ORACLE Role, Execute, Profile  (1) 2023.12.21
51 ORACLE Tablespace, Privilege, Role  (1) 2023.12.20
49 ORACLE Tablespace, VM Backup, Control File, Redo log file  (0) 2023.12.18
48 ORACLE ASSM, Tablespace, Undo  (0) 2023.12.14
47 ORACLE Redo Log File, Tablespace, FLM  (0) 2023.12.13
'Oracle 11g/Oracle DB'의 다른글
  • 현재글50 ORACLE SGA, Undo, User

티스토리툴바