Script:收集数据库中用户的角色和表空间等信息

以下脚本可以用于收集数据库中用户的角色和表空间等信息(user_role_tbs.sql):

 

SET pagesize 50  linesize 115
REM
COLUMN username                 format a10 heading User
COLUMN default_tablespace       format a12 heading Default
COLUMN temporary_tablespace     format a12 heading Temporary
COLUMN granted_role             format a25 heading Roles
COLUMN default_role             format a10 heading Default?
COLUMN admin_option             format a7  heading Admin?
COLUMN profile                  format a12 heading Profile
REM
BREAK on username skip 1 on account_status on default_tablespace on temporary_tablespace on profile
REM
SELECT username,
       default_tablespace,
       temporary_tablespace,
       profile,
       granted_role,
       admin_option,
       default_role
  FROM sys.dba_users a, sys.dba_role_privs b
 WHERE a.username = b.grantee
 ORDER BY username,
          default_tablespace,
          temporary_tablespace,
          profile,
          granted_role;
REM
SET termout on flush on feedback on verify on
CLEAR columns
CLEAR breaks

 

Sample Output:

 

User       Default      Temporary    Profile      Roles                     Admin?  Default?
---------- ------------ ------------ ------------ ------------------------- ------- ----------
SCOTT      USERS        TEMP         DEFAULT      CONNECT                   NO      YES
                                                  RESOURCE                  NO      YES
SYS        SYSTEM       TEMP         DEFAULT      AQ_ADMINISTRATOR_ROLE     YES     YES
                                                  AQ_USER_ROLE              YES     YES
                                                  CONNECT                   YES     YES
                                                  DBA                       YES     YES
                                                  DELETE_CATALOG_ROLE       YES     YES
                                                  EXECUTE_CATALOG_ROLE      YES     YES
                                                  EXP_FULL_DATABASE         YES     YES
                                                  HS_ADMIN_ROLE             YES     YES
                                                  IMP_FULL_DATABASE         YES     YES
                                                  OEM_MONITOR               YES     YES
                                                  RECOVERY_CATALOG_OWNER    YES     YES
                                                  RESOURCE                  YES     YES
                                                  SELECT_CATALOG_ROLE       YES     YES

SYSTEM     SYSTEM       TEMP         DEFAULT      AQ_ADMINISTRATOR_ROLE     YES     YES
                                                  DBA                       YES     YES

Script:列出用户表空间的定额

以下脚本用于列出用户表空间的使用量和定额:


PROMPT Print the details of the Users Tablespace Quotas
TTITLE left _date center ' Database Users Space Quotas by Tablespace' skip 2 Quotas by Tablespace"
COL un          format a25              heading 'User Name'
COL ta          format a25              heading 'Tablespace'
COL usd         format 9,999,999        heading 'K Used'
COL maxb        format 9,999,999        heading 'Max K '

SELECT   tablespace_name ta, username un, bytes / 1024 usd,
         max_bytes / 1024 maxb
    FROM dba_ts_quotas
   WHERE MAX_BYTES!=-1
ORDER BY tablespace_name, username;

set linesize 140 pagesize 1400

SELECT
  username,
  tablespace_name,
  privilege
FROM (
  SELECT
    grantee username, 'Any Tablespace' tablespace_name, privilege
  FROM (
    -- first get the users with direct grants
    SELECT
      p1.grantee grantee, privilege
    FROM
      dba_sys_privs p1
    WHERE
      p1.privilege='UNLIMITED TABLESPACE'
    UNION ALL
    -- and then the ones with UNLIMITED TABLESPACE through a role...
    SELECT
      r3.grantee, granted_role privilege
    FROM
      dba_role_privs r3
      START WITH r3.granted_role IN (
          SELECT
            DISTINCT p4.grantee
          FROM
            dba_role_privs r4, dba_sys_privs p4
          WHERE
            r4.granted_role=p4.grantee
            AND p4.privilege = 'UNLIMITED TABLESPACE')
    CONNECT BY PRIOR grantee = granted_role)
    -- we just whant to see the users not the roles
  WHERE grantee IN (SELECT username FROM dba_users) OR grantee = 'PUBLIC'
  UNION ALL
  -- list the user with unimited quota on a dedicated tablespace
  SELECT
    username,tablespace_name,'DBA_TS_QUOTA' privilege
  FROM
    dba_ts_quotas
  WHERE
    max_bytes = -1 )
WHERE tablespace_name LIKE UPPER('SYSTEM')
    OR tablespace_name = 'Any Tablespace';

Too many fragmentation in LMT?

这周和同事讨论技术问题时,他告诉我客户的一套11.1.0.6的数据库中某个本地管理表空间上存在大量的Extents Fragment区间碎片,这些连续的Extents没有正常合并为一个大的Extent,他怀疑这是由于11.1.0.6上的bug造成了LMT上存在大量碎片。

同事判断该表空间上有碎片的依据是从dba_free_space视图中查询到大量连续的Free Extents:

SQL> select tablespace_name,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where tablespace_name='FRAGMENT';

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
FRAGMENT                       LOCAL      SYSTEM

SQL> select block_id,blocks from dba_free_space where tablespace_name='FRAGMENT' and rownum<10;
BLOCK_ID     BLOCKS
---------- ----------
40009     222136
25          8
9           8
17          8
33          8
41          8
49          8
57          8
65          8
.............. 

SQL> select count(*),blocks from dba_free_space where tablespace_name='FRAGMENT' and blocks=8 group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
      5000          8

以上可以看到FRAGMENT表空间使用autoallocate的Local Extent Management,的确存在大量的连续Extents没有合并。在DMT即字典管理表空间模式下需要SMON进程定期维护FET$基表将tablespace上的连续空闲Extents合并为更大的一个Extents。而在LMT模式下因为采用数据文件头上(datafile header 3-8 blocks in 10g)的位图管理区间,所以无需某个后台进程特意去合并区间。

为什么LMT下连续空闲Extents没有合并而造成碎片呢?因为这套库采用11gr1较不稳定的11.1.0.6版本,所以把问题归咎为某个bug似乎可以讲得通。一开始我较为认同同事的bug论,且和同事一起查询了Metalink上11gr1上一些已知的bug,但并没有发现症状匹配的bug note。

这让我反思这个问题,过早的将cause定位到bug过于主观了,并不是所有我们预期外的情况(unexpected)都属于bug。

实际上dba_free_space所显示的信息可能并不”真实”,这种幻象往往由10g以后出现的flashback table特性引起:

SQL> select text from dba_views where view_name='DBA_FREE_SPACE';

TEXT
--------------------------------------------------------------------------------
======DMT REAL FREE EXTENTS=============

select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0

union all

======LMT REAL FREE EXTENTS=============

select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

======LMT RECYCLEBIN FREE EXTENTS=============

select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

======DMT RECYCLEBIN FREE EXTENTS=============

select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0

以上我们通过解析10g中的dba_free_space视图可以了解到该视图所显示的Free Extents由以下四个部分组成:

  1. LMT表空间上真正空闲的Extents
  2. DMT表空间上真正空闲的Extents
  3. LMT表空间上被RECYCLEBIN中对象占用的Extents
  4. DMT表空间上被RECYCLEBIN中对象占用的Extents

而在10g以前的版本中因为没有recyclebin特性的”干扰”,所以dba_free_space所显示的Free Extents由前2个部分组成,因此我们可以在10g中创建一个兼容视图以实现对真正空闲空间的查询:

create view dba_free_space_pre10g as
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       f.block# BLOCK_ID,
       f.length * ts.blocksize BYTES,
       f.length BLOCKS,
       f.file# RELATIVE_FNO
  from sys.ts$ ts, sys.fet$ f, sys.file$ fi
 where ts.ts# = f.ts#
   and f.ts# = fi.ts#
   and f.file# = fi.relfile#
   and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 f.ktfbfebno BLOCK_ID,
 f.ktfbfeblks * ts.blocksize BYTES,
 f.ktfbfeblks BLOCKS,
 f.ktfbfefno RELATIVE_FNO
  from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
 where ts.ts# = f.ktfbfetsn
   and f.ktfbfetsn = fi.ts#
   and f.ktfbfefno = fi.relfile#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
 /

create view dba_free_space_recyclebin as
select /*+ ordered use_nl(u) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 u.ktfbuebno BLOCK_ID,
 u.ktfbueblks * ts.blocksize BYTES,
 u.ktfbueblks BLOCKS,
 u.ktfbuefno RELATIVE_FNO
  from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
 where ts.ts# = rb.ts#
   and rb.ts# = fi.ts#
   and u.ktfbuefno = fi.relfile#
   and u.ktfbuesegtsn = rb.ts#
   and u.ktfbuesegfno = rb.file#
   and u.ktfbuesegbno = rb.block#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
union all
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       u.block# BLOCK_ID,
       u.length * ts.blocksize BYTES,
       u.length BLOCKS,
       u.file# RELATIVE_FNO
  from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
 where ts.ts# = u.ts#
   and u.ts# = fi.ts#
   and u.segfile# = fi.relfile#
   and u.ts# = rb.ts#
   and u.segfile# = rb.file#
   and u.segblock# = rb.block#
   and ts.bitmapped = 0
/

通过以上创建的dba_free_space_pre10g和dba_free_space_recyclebin视图,我们可以很明确地区分表空间上空闲Extents。

针对本例中的LMT上存在大量连续的空闲Extent碎片,可以直接从上述视图中得到答案:

SQL> select * from dba_free_space_pre10g where tablespace_name='FRAGMENT';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
FRAGMENT                               13      40009 1819738112     222136           13

SQL> select count(*),blocks from dba_free_space_recyclebin where tablespace_name='FRAGMENT' group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
      5000          8

显然是RECYCLEBIN中存在大量的小"对象"从而造成了LMT上出现大量碎片的假象

SQL> select space,count(*) from dba_recyclebin where ts_name='FRAGMENT' group by space;

     SPACE   COUNT(*)
---------- ----------
         8       5000

我们可以通过purge recyclebin来"合并"这些Extents碎片

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL>  select count(*),blocks from dba_free_space where tablespace_name='FRAGMENT' group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
         1     262136

如果应用程序创建大量的小型堆(heap)表来存放临时数据,在不再需要这些数据时将这些堆表drop掉,那么就可能造成上述LMT”碎片”问题。我们在实际处理10g以后的这类空间问题时一定搞清楚,哪些是真正的Free Extents,而哪些是来自RECYCLEBIN的Extents。

另一方面这个case还告诉我们不要一遇到预料外的行为方式(unexpected behavior)就将问题定位到bug,这样会过早僵化我们的诊断预期。为了尽可能地发散思维,我们有必要如围棋中所提倡的”保留变化”那样来安排诊断步骤。

11g Release 2 enhanced Tablespace Point In Time Recovery

11g release 2中引入了针对被dropped掉的表空间的表空间时间点恢复,这是一种十分有用的新特性。TSPITR(TablesSpace Point In Time Recovery)在10g中就能做到自动创建辅助实例以恢复表空间到某个时间点,但在10g中是无法恢复一个已经被drop掉的表空间的。如同10g中一样11g仍旧可以利用全自动的TSPITR恢复被drop的表空间;Oracle会自动创建并启动辅助实例,且仅仅还原那些恢复所需的控制文件,system,sysaux,undo表空间及目标表空间,这些工作都将在用户指定的辅助目的地’Auxiliary Destination’中完成;之后Oracle将进一步使用辅助实例recover目标表空间到指定的时间点,并将其中的数据以Data Pump传输表空间的形式倒回到原数据库当中。
接下来我们要具体测试这一新特性,我们会创建一个示例表空间并在该表空间上产生少量数据,之后我们将对数据库进行备份,drop目标示例表空间,并在RMAN中使用TSPITR的方式将已经被drop掉的表空间恢复回来。在正式drop表空间前我们当然需要留意时间点或者当时的scn号,以保证正常恢复,同时在测试时使用recovery catalog恢复目录,虽然我们同样可以不用。
[Read more…]

Script:Tablespace Report

该脚本用以汇总表空间使用情况报告:

REM tablespace report
set linesize 200
select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes / 1048576) Max
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
1048576) megs_free,
round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 -
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(f.maxbytes) / 1048576) max
from sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool  p,
dba_temp_files           f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/
SELECT d.tablespace_name "Name", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" 
FROM sys.dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = t.tablespace_name(+) 
AND d.extent_management like 'LOCAL' 
AND d.contents like 'TEMPORARY'
/
ttitle - 
center  'Database Freespace Summary'  skip 2 
comp sum of nfrags totsiz avasiz on report 
break on report 
set pages 999
col tsname  format     a16 justify c heading 'Tablespace' 
col nfrags  format 999,990 justify c heading 'Free|Frags' 
col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)' 
col totsiz  format 999,999 justify c heading 'Total|(MB)' 
col avasiz  format 999,999 justify c heading 'Available|(MB)' 
col pctusd  format     990 justify c heading 'Pct|Used' 
select total.TABLESPACE_NAME tsname,
D nfrags,
C/1024/1024 mxfrag,
A/1024/1024 totsiz,
B/1024/1024 avasiz,
(1-nvl(B,0)/A)*100 pctusd
from
(select sum(bytes) A,
tablespace_name
from dba_data_files
group by tablespace_name) TOTAL,
(select sum(bytes) B,
max(bytes) C,
count(bytes) D, 
tablespace_name
from dba_free_space
group by tablespace_name) FREE
where 
total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
/
SELECT t.tablespace_name,
CASE
WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
u.bytes
ELSE
df.user_bytes - NVL(fs.bytes, 0)
END / 1024 / 1024 used_mb,
CASE
WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
df.user_bytes - NVL(u.bytes, 0)
ELSE
NVL(fs.bytes, 0)
END / 1024 / 1024 free_mb,
fs.min_fragment / 1024 / 1024 min_fragment_mb,
fs.max_fragment / 1024 / 1024 max_fragment_mb,
(fs.bytes / 1024 / 1024) / fs.fragments avg_fragment_mb,
fs.fragments,
t.status,
t.contents,
t.logging,
t.extent_management,
t.allocation_type,
t.force_logging,
t.segment_space_management,
t.def_tab_compression,
t.retention,
t.bigfile
FROM dba_tablespaces t,
(SELECT tablespace_name,
SUM(bytes) bytes,
MIN(min_fragment) min_fragment,
MAX(max_fragment) max_fragment,
SUM(fragments) fragments
FROM (SELECT tablespace_name,
SUM(bytes) bytes,
MIN(bytes) min_fragment,
MAX(bytes) max_fragment,
COUNT(*) fragments
FROM dba_free_space
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
SUM(bytes) bytes,
MIN(bytes) min_fragment,
MAX(bytes) max_fragment,
COUNT(*) fragments
FROM dba_undo_extents
WHERE status = 'EXPIRED'
GROUP BY tablespace_name)
GROUP BY tablespace_name) fs,
(SELECT tablespace_name, SUM(user_bytes) user_bytes
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(user_bytes) user_bytes
FROM dba_temp_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes_used) bytes
FROM gv$temp_extent_pool
GROUP BY tablespace_name) u
WHERE t.tablespace_name = df.tablespace_name(+)
AND t.tablespace_name = fs.tablespace_name(+)
AND t.tablespace_name = u.tablespace_name(+)
/

沪ICP备14014813号

沪公网安备 31010802001379号