2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
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.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> 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 = 0x3280Oracle内部使用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 ?
posted on 2013-03-19 00:51 Oracle和MySQL 阅读(429) 评论(0) 编辑 收藏 举报
