After Revoked Tablespace Quota From User in Oracle

In topic, I will descript how Oracle handle once we revoke tablespace quota from user. Once blocks assigned to a segment, even after revoked the Tablespace quota and deleted records from table, all allocated block will remain mapping with segment until we released block by using Shrink.
This is the one good reason to release the space once we delete lots of records from a segment.

Create Tablespace and User:

SQL> create tablespace TEST datafile '/u01/app/oracle/oradata/SQR/datafile/test01.dbf' size 1G;

Tablespace created.

SQL> create user samad identified by samad;

User created.

SQL> grant resource, create table, create session to samad;

Grant succeeded.

SQL> grant resource, create table, create session to samad;

Grant succeeded.

SQL> alter user samad default tablespace users;

User altered.

SQL> alter user samad quota 20M on TEST;

User altered.

Create a table and insert records until have allocated space.

SQL> col username for A20
SQL> set linesize 1000

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
TEST                           SAMAD                         0   20971520          0       2560 NO
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO


SQL> create table samad.test tablespace test as (select * from dba_objects);

Table created.

SQL> Select TABLESPACE_NAME, TABLESPACE_SIZE*8/1024 as "Size", USED_SPACE*8/1024 as "Used"
From dba_tablespace_usage_metrics
where tablespace_name in ('TEST');  

TABLESPACE_NAME                      Size       Used
------------------------------ ---------- ----------
TEST                           18958.8672         21

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
TEST                           SAMAD                  20971520   20971520       2560       2560 NO
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO


SQL> insert into samad.test (select * from dba_objects where rownum < 65000);

64999 rows created.

insert into samad.test (select * from dba_objects where rownum <100)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'


SQL> insert into samad.test (select * from dba_objects where rownum <20);

19 rows created.

# Insert records until TBS getting filled. 

SQL> /
insert into samad.test (select * from dba_objects where rownum <20)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'

SQL> Select TABLESPACE_NAME, TABLESPACE_SIZE*8/1024 as "Size", USED_SPACE*8/1024 as "Used"
From dba_tablespace_usage_metrics
where tablespace_name in ('TEST');  

TABLESPACE_NAME                      Size       Used
------------------------------ ---------- ----------
TEST                           18958.8594         21

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
TEST                           SAMAD                  20971520   20971520       2560       2560 NO
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO

After revoked tablespace quota for a user, till will able to insert records until all blocks have available PCT USED space.

Example 1:

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
TEST                           SAMAD                 100663296  104857600      12288      12800 NO
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO


SQL> alter user samad quota 0 on test;

User altered.

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO


SQL> insert into samad.test (select * from samad.test where rownum < 10);

9 rows created.

Example 2:

SQL> alter user samad quota 30M on TEST;

User altered.

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
TEST                           SAMAD                  20971520   31457280       2560       3840 NO
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO


SQL> insert into samad.test (select * from dba_objects where rownum <22000);

21999 rows created.

SQL> Select TABLESPACE_NAME, TABLESPACE_SIZE*8/1024 as "Size", USED_SPACE*8/1024 as "Used"
From dba_tablespace_usage_metrics
where tablespace_name in ('TEST');

TABLESPACE_NAME                      Size       Used
------------------------------ ---------- ----------
TEST                           18958.9063         24

SQL> alter user samad quota 0 on TEST;

User altered.

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO


SQL> insert into samad.test (select * from dba_objects where rownum <2000);
insert into samad.test (select * from dba_objects where rownum <2000)
                  *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'

SQL> insert into samad.test (select * from dba_objects where rownum <200);

199 rows created.

SQL> /
insert into samad.test (select * from dba_objects where rownum <200)
                  *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'

All allocated blocks for tablespace mapping will remain with segment even though tablespace has no quota for user and block has no data.

User has no quota on TEST Tablespace.
Delete all records from table and commit.
Will able to insert same amount of data (if records size is same then same number of records) even has no quota on the tablespace.

SQL> select * from dba_ts_quotas where username='SAMAD';

TABLESPACE_NAME                USERNAME                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
USERS                          SAMAD                  25165824 1073741824       3072     131072 NO

SQL> delete from samad.test;

179503 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into samad.test (select * from samad.test_bck);

179503 rows created.

SQL> commit;

Commit complete.

SQL> insert into samad.test (select * from samad.test where rownum < 1000);
insert into samad.test (select * from samad.test where rownum < 1000)
                  *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'

After revoked tablespace quota, able to insert records for free PCT of available block by deleted records. (with Shrink)

User has no quota on TEST Tablespace.
Delete all records from table and commit.
Shrink the Table.
Will able to insert few data only for allocate couple of blocks which were used for Table Structure.

SQL> alter table samad.test enable row movement;

Table altered.

SQL> delete from samad.test;

179503 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table samad.test shrink space;

Table altered.

SQL> insert into samad.test (select * from samad.test_bck);
insert into samad.test (select * from samad.test_bck)
                  *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'

SQL> insert into samad.test (select * from samad.test_bck where rownum < 100);

99 rows created.

SQL> insert into samad.test (select * from samad.test_bck where rownum < 100);

99 rows created.

SQL> /
insert into samad.test (select * from samad.test_bck where rownum < 100)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST'

SQL> select owner, segment_name, tablespace_name, file_id, block_id from dba_extents where segment_name = 'TEST';

OWNER      SEGMENT_NAME         TABLESPACE_NAME         FILE_ID   BLOCK_ID
---------- -------------------- -------------------- ---------- ----------
SAMAD      TEST                 TEST                          5        128

SQL> Select num_rows, blocks From dba_tables Where Table_name='TEST';

  NUM_ROWS     BLOCKS
---------- ----------
         0          5

SQL> EXEC DBMS_STATS.gather_table_stats('SAMAD','TEST');

PL/SQL procedure successfully completed.

SQL> Select num_rows, blocks From dba_tables Where Table_name='TEST';

  NUM_ROWS     BLOCKS
---------- ----------
       198          5

Till occupied couple of blocks for segment even all data have been deleted from table.

SQL> delete from samad.test;

198 rows deleted.

SQL> commit;

Commit complete.

SQL> Select num_rows, blocks From dba_tables Where Table_name='TEST';

  NUM_ROWS     BLOCKS
---------- ----------
       198          5

SQL> EXEC DBMS_STATS.gather_table_stats('SAMAD','TEST');

PL/SQL procedure successfully completed.

SQL> Select num_rows, blocks From dba_tables Where Table_name='TEST';

  NUM_ROWS     BLOCKS
---------- ----------
         0          5

After Revoked Tablespace Quota From User in Oracle

About Me

Categories

Archives

VISITORS