Oracle-L: Question: system tablespace fragmentation.

From: Godlewski, Melissa <Melissa.Godlewski_at_hq.doe.gov>
Date: Thu, 18 Apr 2002 14:29:44 -0800
Message-ID: <F001.00448DA5.20020418142944@fatcity.com>

I granted select on table to a user. This permission already existed in the database. The sql statement returned a Grant succeeded.

I've read the fine manual and couldn't find any information about regranting a permission that already existed, so I ran a trace and the results are below: It appears to me Oracle just updates the objauth$ without checking if a permission already exists. This shouldn't (should it) cause fragmentation in the system tablespace since it updates an existing record with the same information.

grant select on contract

update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11, dataobj#=:13,flags=:14,oid$=:15
where
owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is

null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) and(subname=:12 or subname is null and :12 is null)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 2 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 2 0.01 0.01 0 2 1 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  UPDATE OBJ$ 
      2   INDEX RANGE SCAN (object id 34)
 
****************************************************************************

update objauth$ set
option$=decode(option$,null,decode(:1,0,null,:1),option$) where
grantor#=:2 and obj#=:3 and privilege#=:4 and grantee#=:5 and nvl(col#,0)=:6

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 2 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 2 0.01 0.01 0 2 2 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  UPDATE OBJAUTH$ 
      2   INDEX RANGE SCAN (object id 100)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Godlewski, Melissa
  INET: Melissa.Godlewski_at_hq.doe.gov

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Apr 18 2002 - 17:29:44 CDT