Know more about Oracle User Space Quota

Tablespace Quota 表空间限额是Oracle数据库中限制User使用空间的重要手段，我们来深入浅出地了解一下Space Quota在内部的实现：

SQL> select  * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select  * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

SQL> create user maclean_space identified by oracle;

User created.

SQL> oradebug setmypid;       
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> 
SQL> alter user maclean_space quota 10M on users;

User altered.

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_25686.trc

[oracle@vrh8 ~]$ egrep -i "insert|update|delete" /s01/admin/G10R25/udump/g10r25_ora_25686.trc
insert into tsq$ (ts#,user#,blocks,maxblocks,grantor#,priv1,priv2,priv3) values (:1,:2,:3,:4,:5,:6,:7,:8)

update user$ set name=:2,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'), 
'0000-00-00', to_date(NULL), :9),exptime=DECODE(to_char(:10, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :10),
ltime=DECODE(to_char(:11, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :11),astatus=:12, lcount=:13, defschclass=:14, spare1=:15 where user#=:1
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  USER$ (cr=4 pr=0 pw=0 time=296 us)'

以上可以看到登录用户quota限额信息到数据字典的dictionary recursive SQL 数据字典递归SQL是”insert into tsq$” 向字典TSQ$中插入一条记录, TSQ$是重要的数据字典基表，在创建数据字典时被create，在11g以前可以从$ORACLE_HOME/rdbms/admin/sql.bsq中找到该表的定义:

create table tsq$                                  /* tablespace quota table */
( ts#           number not null,                        /* tablespace number */
  user#         number not null,                              /* user number */
  grantor#      number not null,                               /* grantor id */
  blocks        number not null,         /* number of blocks charged to user */
  maxblocks     number,     /* user's maximum number of blocks, NULL if none */
  priv1         number not null,            /* reserved for future privilege */
  priv2         number not null,            /* reserved for future privilege */
  priv3         number not null)            /* reserved for future privilege */
cluster c_user# (user#)
/

USER_TS_QUOTAS和DBA_TS_QUOTAS这些字典视图直接依赖于tsq$和seg$这2个字典基表，它们的定义在11g之前可以在$ORACLE_HOME/rdbms/admin/catspace.sql中找到：

remark  FAMILY "TS_QUOTAS"
remark  Tablespace quotas for users.
remark  This family has no ALL member.
remark
Rem
Rem  Performance improvement:
Rem    Get segments number of blocks from seg$.blocks. This column was
Rem    introduced in 10g. For databases that were upgraded from older
Rem    releases, dbms_space_admin.segment_number_blocks() is called to
Rem    gather the information.
Rem    View USER_TS is now useless. It is still left here just to avoid
Rem    any potential upgrade issue.
Rem
create or replace view USER_TS(uname, tsname, tsn)
as select user$.name, ts$.name, ts$.ts# from user$, ts$
/
create or replace view TBS_SPACE_USAGE(tsn, user#, blocks, maxblocks)
as select tsq$.ts#, tsq$.user#,
          NVL(sum(decode(bitand(seg$.spare1, 131072), 131072, seg$.blocks,
                         (decode(bitand(seg$.spare1, 1), 1,
                            dbms_space_admin.segment_number_blocks(tsq$.ts#,
                                   seg$.file#, seg$.block#, seg$.type#,
                                   seg$.cachehint, seg$.spare1,
                                   seg$.hwmincr, seg$.blocks), seg$.blocks)))),
              0),
          tsq$.maxblocks
from seg$, tsq$
where tsq$.ts# = seg$.ts# (+)
and   tsq$.user# = seg$.user# (+)
group by tsq$.ts#, tsq$.user#, tsq$.maxblocks
/
create or replace view USER_TS_QUOTAS
    (TABLESPACE_NAME, BYTES, MAX_BYTES, BLOCKS, MAX_BLOCKS, DROPPED)
as
select ts.name, spc.blocks * ts.blocksize,
       decode(spc.maxblocks, -1, -1, spc.maxblocks * ts.blocksize),
       spc.blocks, spc.maxblocks, decode(ts.online$, 3, 'YES', 'NO')
from sys.ts$ ts, sys.tbs_space_usage spc
where spc.tsn = ts.ts#
  and spc.user# = userenv('SCHEMAID')
/

需要注意的是UNLIMITED TABLESPACE这个无限表空间限额的系统权限并不依赖于TSQ$的份额基表，所以也不会产生USER_TS_QUOTAS/DBA_TS_QUOTAS中的记录：

SQL> create user maclean_space1 identified by oracle;

User created.

SQL> oradebug setmypid;       
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> 
SQL> grant UNLIMITED TABLESPACE to maclean_space1;

Grant succeeded.

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_25820.trc

[oracle@vrh8 ~]$ egrep -i "insert|update|delete" /s01/admin/G10R25/udump/g10r25_ora_25820.trc
insert into sysauth$ (grantee#,privilege#,option$,sequence#) values (:1,:2,decode(:3,0,null,:3),system_grant.nextval)

此外Oracle并不会通过dictionary recursive SQL字典递归SQL了解表空间份额的信息，而是直接将这部分信息缓存在row cache字典缓存的dc_tablespace_quotas中：

SQL> ALTER SESSION SET EVENTS 'immediate trace name row_cache level 10';

Session altered.

SQL> oradebug setmypid;
Statement processed.
SQL> 
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_25854.trc

[oracle@vrh8 ~]$ grep dc_tablespace_quotas /s01/admin/G10R25/udump/g10r25_ora_25854.trc
  row cache parent object: address=0x876732e0 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8a4bb778 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8aa09478 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8a91c958 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8749f648 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8a8aafd8 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8749f418 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8d358fc0 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8aa69e88 cid=5(dc_tablespace_quotas)

当我们添加一个定额用户(quota user)时，相应的要多产生一个dc_tablespace_quotas row cache parent object：

SQL> create user maclean_space2 identified by oracle;

User created.

SQL> alter user maclean_space2 quota 100M on users;

User altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name row_cache level 10';

Session altered.

[oracle@vrh8 ~]$ grep dc_tablespace_quotas /s01/admin/G10R25/udump/g10r25_ora_25891.trc
 row cache parent object: address=0x872d3d08 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x876732e0 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8a4bb778 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8aa09478 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8a91c958 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8749f648 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8a8aafd8 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8749f418 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8d358fc0 cid=5(dc_tablespace_quotas)
  row cache parent object: address=0x8aa69e88 cid=5(dc_tablespace_quotas)

BUCKET 23:
  row cache parent object: address=0x872d3d08 cid=5(dc_tablespace_quotas)
  hash=36109d16 typ=9 transaction=(nil) flags=00000002
  own=0x872d3dd8[0x872d3dd8,0x872d3dd8] wat=0x872d3de8[0x872d3de8,0x872d3de8] mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  00000004 0000004a 00000000 00003200 00000000 00000000
  BUCKET 23 total object count=1

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS                                   0  104857600          0      12800 NO

可以看到以上address=0x872d3d08对象的dc_tablespace_quotas记录是create user/alter user quota后产生的，该row cache的data stack中的0x3200对应为12800 个block。

修改该用户的quota信息，会引发stack data的变化：

  SQL> alter user maclean_space2 quota 101M on users;

User altered.

  BUCKET 23:
  row cache parent object: address=0x872d3d08 cid=5(dc_tablespace_quotas)
  hash=36109d16 typ=9 transaction=(nil) flags=00000002
  own=0x872d3dd8[0x872d3dd8,0x872d3dd8] wat=0x872d3de8[0x872d3de8,0x872d3de8] mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  00000004 0000004a 00000000 00003280 00000000 00000000
  BUCKET 23 total object count=1

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS                                   0  105906176          0      12928 NO  

 12928 block = 0x3280

Oracle内部使用KTS模块的函数实现Tablespace Quota的管理，以下为ORA-01950错误的errostack stack call:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 1950 trace name errorstack level 4:10046 trace name context forever,level 12;
Statement processed.
SQL> create table maclean_space.space_test tablespace system as select * from dba_tables;
create table maclean_space.space_test tablespace system as select * from dba_tables
                                                                         *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_25758.trc

Current SQL statement for this session:
create table maclean_space.space_test tablespace system as select * from dba_tables
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ssd_unwind_bp: unhandled instruction at 0x76a02d instr=f
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
ksddoa()+1766        call     ksedmp()             000000004 ? 000000001 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
ksdpcg()+646         call     ksddoa()             7FAACAD703F8 ? 7FAACAD56980 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
ksdpec()+247         call     ksdpcg()             00000079E ? 7FAACAD703F8 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
ksfpec()+171         call     ksdpec()             00000079E ? 7FAACAD703F8 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
kgesev()+686         call     ksfpec()             00000079E ? 7FFF9451AF10 ?
                                                   7FFF9451AF10 ? 7FFF9451AF70 ?
                                                   7FFF9451AEB0 ? 000000000 ?
ksesec1()+189        call     kgesev()             006AF5CE0 ? 00805C028 ?
                                                   00000079E ? 000000001 ?
                                                   7FFF9451C100 ? 000000000 ?
kttgsq()+425         call     ksesec1()            006AF5CE0 ? 000000001 ?
                                                   000000006 ? 7FFF9451C1F2 ?
                                                   0000000CA ? 08FF3BF28 ?
ktfbtgex1()+420      call     kttgsq()             000000000 ? 000000048 ?
                                                   000000006 ? 7FFF9451C1F2 ?
                                                   0000000CA ? 08FF3BF28 ?
ktsscrseg()+1072     call     ktfbtgex1()          7FFF9451CA88 ? 000000048 ?
                                                   7FFF9451D370 ? 000000008 ?
                                                   7FAA00000000 ? 7FFF00000001 ?
ktssctr_segment1()+  call     ktsscrseg()          7FFF9451D368 ? 7FFF9451D04C ?
939                                                7FFF9451CC40 ? 7FFF9451CFF8 ?
                                                   300000000 ? 7FFF00000001 ?
ktssctr_segment()+2  call     ktssctr_segment1()   7FFF9451DDC8 ? 7FFF9451D368 ?
26                                                 7FFF9451DC20 ? 7FFF9451CFF8 ?
                                                   7FAA00000000 ? 7FFF00000001 ?
ktrsexec()+437       call     ktssctr_segment()    7FFF9451DC08 ? 7FFF9451D368 ?
                                                   7FFF9451DC20 ? 006AF5E60 ?
                                                   7FAA00000000 ? 7FFF00000001 ?
ktsscf_segment()+67  call     ktrsexec()           7FFF9451DC08 ? 7FFF9451D368 ?
7                                                  7FFF9451DC20 ? 006AF5E60 ?
                                                   7FAA00000000 ? 7FFF00000001 ?
qerlt_lsa()+1695     call     ktsscf_segment()     7FFF9451DDC8 ? 000000005 ?
                                                   7FFF9451DC20 ? 006AF5E60 ?
                                                   7FAA00000000 ? 7FFF00000001 ?
klclil1r()+483       call     qerlt_lsa()          000002000 ? 08731BA70 ?
                                                   7FAACACCC2F8 ? 000000001 ?
                                                   7FFF9451DE94 ? 7FFF00000001 ?
qerltRop()+928       call     klclil1r()           7FAACACCC008 ? 08731BA70 ?
                                                   7FAACACCC2F8 ? 000000001 ?
                                                   7FFF9451DE94 ? 7FFF00000001 ?
qerstRowP()+388      call     qerltRop()           08731BA70 ? 000007FFF ?

Comments

2 responses to “Know more about Oracle User Space Quota”

Mahir M. Quluzade

June 20, 2012

Hi Maclean !

How to find this article in English?
I didn’t understand China.

Thanks
Mahir M. Quluzade

1. Maclean Liu
  
  June 20, 2012
  
  I am afraid of that you have to google some topic in English.

Know more about Oracle User Space Quota

Comments

2 responses to “Know more about Oracle User Space Quota”

Leave a Reply Cancel reply