OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 유저별 테이블스페이스 quota 확인 및 변경
오라클에서 quota는 테이블스페이스에 객체를 생성할 권한과 할당량 (공간 허용 또는 제한)을 부여하는 기능임
만약 a라는 유저의 default tablespace가 users 테이블스페이스일지라도 quota를 0으로 두면 a유저는 users 테이블스페이스에 extent가 포함된 테이블을 생성할 수 없음
본문에서는 유저에 테이블스페이스 quota를 부여하거나 제한했을때 어떻게 동작하는지 확인해봄
테스트
유저 생성
|
1
2
3
|
SQL>
create user test identified by test account unlock default tablespace users quota unlimited on users;
grant resource, connect to test;
|
추가 테이블스페이스 생성 및 권한 부여
|
1
2
3
4
|
SQL>
drop tablespace testts including contents and datafiles;
create tablespace testts datafile '/oradata1/ORACLE19/testts01.dbf' size 2g autoextend off;
alter user test default tablespace testts quota unlimited on testts;
|
dba_ts_quotas 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col username for a20
select * from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------------- -------------------- ---------- ---------- ---------- ---------- ---
SYSAUX AUDSYS 0 -1 0 -1 NO
SYSTEM OUTLN 589824 -1 72 -1 NO
SYSAUX GSMADMIN_INTERNAL 917504 -1 112 -1 NO
SYSAUX DBSFWUSER 0 -1 0 -1 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SYSAUX GGSYS 0 -1 0 -1 NO
SYSAUX MDSYS 210960384 -1 25752 -1 NO
SYSTEM MDSYS 0 -1 0 -1 NO
SYSAUX OLAPSYS 0 -1 0 -1 NO
SYSTEM LBACSYS 327680 -1 40 -1 NO
TESTTS TEST 0 -1 0 -1 NO
USERS TEST 0 -1 0 -1 NO
12 rows selected.
|
현재 test 유저는 users 테이블스페이스와 testts 테이블스페이스에 대한 권한과 할당량이 모두 부여되어 있음
MAX_BLOCKS, MAX_BYTES가 "-1"이면 unlimited 무제한으로 데이터를 삽입할수 있다는 뜻임
만약 testts 테이블스페이스에 대해 quota를 1로 설정하면 1bytes가 아닌 오라클의 최소 할당 단위인 1개의 block크기인 8192bytes 만큼만 사용 가능하게됨
|
1
2
3
|
SQL> alter user test default tablespace testts quota 1 on testts;
User altered.
|
dba_ts_quotas 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col username for a20
select * from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------------- -------------------- ---------- ---------- ---------- ---------- ---
SYSAUX AUDSYS 0 -1 0 -1 NO
SYSTEM OUTLN 589824 -1 72 -1 NO
SYSAUX GSMADMIN_INTERNAL 917504 -1 112 -1 NO
SYSAUX DBSFWUSER 0 -1 0 -1 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SYSAUX GGSYS 0 -1 0 -1 NO
SYSAUX MDSYS 210960384 -1 25752 -1 NO
SYSTEM MDSYS 0 -1 0 -1 NO
SYSAUX OLAPSYS 0 -1 0 -1 NO
SYSTEM LBACSYS 327680 -1 40 -1 NO
TESTTS TEST 0 8192 0 1 NO
USERS TEST 0 -1 0 -1 NO
12 rows selected.
|
MAX_BYTES가 8192로, MAX_BLOCKS가 1로 변경됨
test 유저로 접속해 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> conn test/test
Connected.
SQL> create table testtbl (col1 number);
Table created.
SQL> insert into testtbl values (1);
insert into testtbl values (1)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TESTTS'
|
quota를 설정해서 1block 만큼은 데이터를 삽입할수 있어야하는데 실제로는 quota 제한때문에 삽입되지 않음
quota를 다시 unlimited로 변경 후 데이터 삽입 후 dba_ts_quotas 확인
quota 변경 및 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> alter user test default tablespace testts quota unlimited on testts;
User altered.
SQL> conn test/test
Connected.
SQL> insert into testtbl values (1);
1 row created.
SQL> commit;
Commit complete.
|
unlimited로 변경시 정상적으로 삽입됨
dba_ts_quotas 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col username for a20
select * from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
-------------------- -------------------- ---------- ---------- ---------- ---------- ---
SYSAUX AUDSYS 0 -1 0 -1 NO
SYSTEM OUTLN 589824 -1 72 -1 NO
SYSAUX GSMADMIN_INTERNAL 917504 -1 112 -1 NO
SYSAUX DBSFWUSER 0 -1 0 -1 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SYSAUX GGSYS 0 -1 0 -1 NO
SYSAUX MDSYS 210960384 -1 25752 -1 NO
SYSTEM MDSYS 0 -1 0 -1 NO
SYSAUX OLAPSYS 0 -1 0 -1 NO
SYSTEM LBACSYS 327680 -1 40 -1 NO
TESTTS TEST 65536 -1 8 -1 NO
USERS TEST 0 -1 0 -1 NO
12 rows selected.
|
1건을 insert 했지만 초기 할당 블록이 8블록에 65536bytes를 사용함
이는 testts 테이블스페이스를 생성시 별다른 옵션 없이 기본값으로 생성했기 때문에
initial_extent가 65536bytes로 지정되어 있어 그런것 현재 8개의 8k 블록으로 이루어져있음
testts 테이블스페이스 정보 확인
|
1
2
3
4
5
6
7
8
|
SQL>
select tablespace_name, block_size, initial_extent
from dba_tablespaces
where tablespace_name = 'TESTTS';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
TESTTS 8192 65536
|
결론 :
이렇게 dba_ts_quotas 뷰를 이용해 유저별 테이블스페이스 quota 설정을 확인할수 있음
quota를 unlimited로 설정하면 해당 테이블스페이스에 max값까지 무제한으로 삽입 가능함, 그리고 이때는 dba_ts_quotas의 max_bytes나 max_block에 "-1"로 표시됨
quota를 1로 설정하면 1바이트가 아니라 최소 할당 단위(1블록, 8KB)가 적용됨
하지만 테이블스페이스의 initial_extent 기본값이 64KB로 설정되어 있기 때문에 1블록(8KB)로는 테이블을 사용할 수 없어서 데이터 삽입이 불가능함
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_TS_QUOTAS.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html#GUID-83B788D2-B621-4FCF-9A26-7AF7557A6E3A.
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_TABLESPACES.html
https://positivemh.tistory.com/1017
https://msutic.blogspot.com/2010/07/dbatsquotas-ora-00959ora-01536.html
1615183.1
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 파티션 인덱스의 테이블스페이스 변경 (0) | 2025.03.31 |
|---|---|
| 오라클 19c alert log DDE flood control 기능(에러 홍수 제어) (0) | 2025.03.24 |
| 오라클 19c dbca 기본 템플릿과 컴포넌트(옵션) (1) | 2025.03.17 |
| 오라클 19c 언두 테이블스페이스에 일반 테이블 생성 (0) | 2025.03.10 |
| 오라클 19c bigfile과 smallfile 테이블스페이스의 extent 할당 단위 확인 (0) | 2025.03.03 |