Version info : 19c on RHEL 7.9
As shown below, I am trying to grant 1GB quota on USERS tablespace to common user C##ABC_USER.
But, neither cdb_ts_quotas nor dba_ts_quotas views reflect what I just granted. i.e. The bytes column of cdb_ts_quotas/dba_ts_quotas keep showing zero.
As, shown below, I even logged in to an individual container and granted 1GB quota on USERS to C##ABC_USER. It did not work either.
What am i doing wrong ?
SQL> SELECT CON_ID, TABLESPACE_NAME FROM CDB_TABLESPACES WHERE TABLESPACE_NAME = 'USERS' ORDER BY CON_ID ASC;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 USERS
3 USERS
4 USERS
5 USERS
6 USERS
7 USERS
8 USERS
11 USERS
8 rows selected.
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_WMS READ WRITE NO
4 BRMSPC_WE8ISO8859P15_AL16UTF16 READ ONLY NO
5 BRMSPC_AL32UTF8_AL16UTF16 READ ONLY NO
6 BRMSPC_AL32UTF8_UTF8 READ ONLY NO
7 DBFS READ WRITE NO
8 PDB_MCT READ WRITE NO
11 PDB_CLF READ WRITE NO
SQL>
SQL> alter user C##ABC_USER quota 1g on users container=all;
User altered.
SQL> select con_id, TABLESPACE_NAME, bytes/1024/1024 sizemb from cdb_ts_quotas where username = 'C##ABC_USER';
CON_ID TABLESPACE_NAME SIZEMB
---------- ------------------------------ ----------
7 USERS 0
1 USERS 0
11 USERS 0
3 USERS 0
8 USERS 0
--- I even logged in to an individual container and granted 1GB quota on USERS to C##ABC_USER.
Still doesn't work.
SQL> alter session set container = PDB_MCT;
Session altered.
SQL> select TABLESPACE_NAME, bytes/1024/1024 sizemb from dba_ts_quotas where username = 'C##ABC_USER';
TABLESPACE_NAME SIZEMB
------------------------------ ----------
USERS 0
SQL> alter user C##ABC_USER quota 1g on users;
User altered.
SQL> select TABLESPACE_NAME, bytes/1024/1024 sizemb from dba_ts_quotas where username = 'C##ABC_USER';
TABLESPACE_NAME SIZEMB
------------------------------ ----------
USERS 0
Edit: I added a new user in a PDB and granted 1GB quota on USERS tablespace. The user (TEST_USER1) can create tables in USERS tablespace but still not reflected in dba_ts_quotas. And the value shown in user_ts_quotas.bytes is not accurate
I added a new user in a PDB and granted 1GB quota on USERS tablespace. The user (TEST_USER1) can create tables in USERS tablespace but still not reflected in dba_ts_quotas. And the value shown in user_ts_quotas.bytes is inaccurate.
SQL> show con_name
CON_NAME
------------------------------
PDB_MCT
SQL> create user test_user1 identified by Tiger#283;
User created.
SQL> select TABLESPACE_NAME, bytes/1024/1024 sizemb from dba_ts_quotas where username = 'TEST_USER1';
no rows selected
SQL> ALTER USER TEST_USER1 quota 1g on users;
User altered.
SQL> Select TABLESPACE_NAME, bytes/1024/1024 sizemb from dba_ts_quotas where username = 'TEST_USER1';
TABLESPACE_NAME SIZEMB
------------------------------ ----------
USERS 0
SQL> ALTER USER TEST_USER1 DEFAULT TABLESPACE USERS;
User altered.
SQL> Select TABLESPACE_NAME, bytes/1024/1024 sizemb from dba_ts_quotas where username = 'TEST_USER1';
TABLESPACE_NAME SIZEMB
------------------------------ ----------
USERS 0
SQL> SELECT sum(bytes/power(1024,2)) sizemb, SUM(MAXBYTES/power(1024,2)) MAXBYTES_SIZEMB from dba_Data_Files where tablespace_name = 'USERS';
SIZEMB MAXBYTES_SIZEMB
---------- ---------------
2048 32767.9844
But the user can use this tablespace and user_ts_quotas show some value in
bytes column which is not accurate.
$ sqlplus TEST_USER1/Tiger#283@PDB_MCT
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 26 10:29:27 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> create table abc (name varchar2(30));
Table created.
SQL> insert into abc values ('DKDKDKDKDKD');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> select TABLESPACE_NAME, bytes/1024/1024 sizemb from user_ts_quotas;
TABLESPACE_NAME SIZEMB
------------------------------ ----------
USERS .0625