用户表空间限额（Oracle User Space Quota ）

原文转自：http://www.askmaclean.com/archives/know-more-about-oracle-user-space-quota.html

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.oracledatabase12g.com

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 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, defsch 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, /* users 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 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.

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)))),

 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)

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 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 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中的0×3200对应为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 ?