AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2012-10-25 884
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 原文转自：http://www.askmaclean.com/archives/know-more-about-oracle-user-space-quota.html Tablespace Quota 表空间限额是Oracle数据库中限制User使用空...

原文转自：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> 
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中的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 ?
文章标签：
高速通道
关系型数据库
SQL
Oracle
Perl
Linux
数据库
缓存
关键词：
Oracle数据库一键上云表空间
oracle数据库一键上云user
Oracle数据库一键上云用户
Oracle数据库一键上云quota
长烟慢慢
目录
相关文章
_长银_
|
7月前
|
存储 Oracle NoSQL
Oracle 表空间、数据文件、schema的关系
Oracle 表空间、数据文件、schema的关系
_长银_
198 2
冷色系456
|
SQL 监控 Oracle
Oracle创建和管理表空间
Oracle创建和管理表空间
冷色系456
101 1
度假的小鱼
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
度假的小鱼
126 0
花酒锄作田
|
4月前
|
Oracle 关系型数据库 数据库
[oracle]拆分多用户的公共表空间
[oracle]拆分多用户的公共表空间
花酒锄作田
49 1
熊猫Jay字节之旅
|
7月前
|
SQL Oracle 关系型数据库
Oracle 数据泵导出导入(映射表空间、Schema)
Oracle 数据泵导出导入(映射表空间、Schema)
熊猫Jay字节之旅
112 0
李振良_阿良
|
7月前
|
Oracle 关系型数据库 数据库
Oracle 11gR2学习之三（创建用户及表空间、修改字符集和Oracle开机启动）
Oracle 11gR2学习之三（创建用户及表空间、修改字符集和Oracle开机启动）
李振良_阿良
246 0
1941623231718325
|
7月前
|
存储 Oracle 关系型数据库
Oracle表空间：数据王国的疆域规划
【4月更文挑战第19天】Oracle中的表空间是逻辑存储结构，用于存放数据库对象的物理数据，是数据库性能优化和备份恢复的基础。表空间类型多样，如永久和临时表空间，需根据业务需求进行规划和管理。通过监控使用情况、利用自动扩展功能，可有效管理表空间，提高数据访问速度和可靠性。深入理解表空间有助于优化数据库存储和管理。
1941623231718325
57 0
一个黄金爆头
|
7月前
|
Oracle 关系型数据库
oracle 修改表空间文件路径方法
oracle 修改表空间文件路径方法
一个黄金爆头
396 0
机器学习Zero
|
7月前
|
Oracle 关系型数据库 数据库
Oracle系列之五：Oracle表空间
Oracle系列之五：Oracle表空间
机器学习Zero
86 0
javaNice
|
7月前
|
SQL Oracle 关系型数据库
Oracle查看表空间 及表空间是否需要扩展
Oracle查看表空间 及表空间是否需要扩展
javaNice
65 0