[Oracle] ORA-01950: no privileges on tablespace 'USERS' -> quota 권한으로 해결

 

ORA-01950: no privileges on tablespace 'USERS'

02:26:04 SUA@ORCL> 
insert into emp
values(
 7934, 'MILLER', 'CLERK', 7782,
 to_date('23-1-1982','dd-mm-yyyy'),
 1300, null, 10
);


02:26:05   2  02:26:05   3  02:26:05   4  02:26:05   5  02:26:05   6  insert into emp
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

 

 

전체 tablespace 확인 (dba_tablespaces)

02:28:43 SUA@ORCL> select TABLESPACE_NAME ,  BLOCK_SIZE , INITIAL_EXTENT  from dba_tablespaces;

TABLESPACE_NAME 	       BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
SYSTEM				     8192	   65536
SYSAUX				     8192	   65536
UNDOTBS1			     8192	   65536
TEMP				     8192	 1048576
USERS				     8192	   65536
SOE_10G 			     8192	 1048576
MEM_TBS 			     8192	   65536

7 rows selected.

Elapsed: 00:00:00.01

 

user의 기본 tablespace 확인(dba_users)

02:35:01 SYS@ORCL> select DEFAULT_TABLESPACE , TEMPORARY_TABLESPACE , PASSWORD_VERSIONS from dba_users where username = 'SUA'
02:35:41   2  ;

DEFAULT_TABLESPACE	       TEMPORARY_TABLESPACE	      PASSWORD_VERSIONS
------------------------------ ------------------------------ -----------------
USERS			       TEMP			      11G 12C

 

 

 

USER 용 tablespace 생성하기(dba권한이 필) 

[oracle@sua-oracle sample]$ sqlplus sua/sua as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 02:32:30 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0



02:32:30 SYS@ORCL> create tablespace SUA_DAT datafile 'sua_dat_01.dbf' size 5G;


Tablespace created.






02:35:43 SYS@ORCL> select TABLESPACE_NAME ,  BLOCK_SIZE , INITIAL_EXTENT  from dba_tablespaces;

TABLESPACE_NAME 	       BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
SYSTEM				     8192	   65536
SYSAUX				     8192	   65536
UNDOTBS1			     8192	   65536
TEMP				     8192	 1048576
USERS				     8192	   65536
SOE_10G 			     8192	 1048576
MEM_TBS 			     8192	   65536
SUA_DAT 			     8192	   65536

 

 

USER의 default Tablespace 변경하기

Elapsed: 00:00:00.03
02:37:44 SYS@ORCL> alter user SUA default tablespace SUA_DAT ;

User altered.

Elapsed: 00:00:00.10


02:37:53 SYS@ORCL>  select DEFAULT_TABLESPACE , TEMPORARY_TABLESPACE , PASSWORD_VERSIONS from dba_users where username = 'SUA'
02:38:18   2  ;

DEFAULT_TABLESPACE	       TEMPORARY_TABLESPACE	      PASSWORD_VERSIONS
------------------------------ ------------------------------ -----------------
SUA_DAT 		       TEMP			      11G 12C

 

 

 

tablesapce 에 대한 USER 권한 부여하기

02:57:57 SYS@ORCL> ALTER USER SUA QUOTA UNLIMITED ON SUA_DAT;

User altered.




02:58:34 SYS@ORCL>  SELECT TABLESPACE_NAME, MAX_BYTES FROM DBA_TS_QUOTAS;


TABLESPACE_NAME 		MAX_BYTES
------------------------------ ----------
SYSAUX				       -1
SYSAUX				       -1
SYSAUX				       -1
SYSAUX				       -1
SYSTEM				       -1
SYSTEM				       -1
SYSAUX				       -1
SYSAUX				       -1
SOE_10G 			       -1
SYSAUX				       -1
SUA_DAT 			       -1

TABLESPACE_NAME 		MAX_BYTES
------------------------------ ----------
SYSTEM				       -1