CE-00046: Monitoring (Oracle free space tablespace)

[RU]

Источник ошибки: Ядро мониторинга работы СУБД Oracle

Текст сообщения: Monitoring (Oracle free space tablespace)

Информация

• Получение данных о потенциальном свободном месте в табличных пространствах, количестве дата файлов в них

Запрос

select a."name",
       a."count",
       nvl(round(a."all"/1024),0) "all",
       nvl(round(a."alloc"/1024),0) "alloc",
       nvl(round(a."used"/1024),0) "used",
       nvl(100-round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100,2),0) "%", nvl(100-round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100),0) "%1"
 from (
select tablespace_name "name",
       count(file_name) "count",
       round(sum(DECODE(autoextensible,'YES',maxbytes,'NO',bytes))/1048576) "all",
       round(sum(bytes)/1048576) "alloc",
       round(sum(user_bytes)/1048576) "used"
  from DBA_DATA_FILES
 group by tablespace_name
union all
  SELECT h.tablespace_name "name",
         count(f.FILE_NAME ) "count",
         ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) /  1048576) "all",
         ROUND (SUM (h.bytes_free + h.bytes_used) /  1048576) "alloc",
         ROUND (SUM (NVL (p.bytes_used, 0)) /  1048576) "used"
    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) a
 order by 6 desc

Решение

Предоставить пользователю права на чтение данных системного каталога, например

grant select any dictionary to <UserName>; 
или   
grant select on DBA_DATA_FILES to <UserName>;
grant select on sys.v_$TEMP_SPACE_HEADER to <UserName>;
grant select on sys.v_$Temp_extent_pool to <UserName>;
grant select on dba_temp_files to <UserName>;

[EN]

Error Source: Oracle DBMS Operation Monitoring Kernel

Message text: Monitoring (Oracle free space tablespace)

Information

• Obtaining data on potential free space in tablespaces, the number of date files in them

Query

select a."name",
       a."count",
       nvl(round(a."all"/1024),0) "all",
       nvl(round(a."alloc"/1024),0) "alloc",
       nvl(round(a."used"/1024),0) "used",
       nvl(100-round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100,2),0) "%", nvl(100-round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100),0) "%1"
 from (
select tablespace_name "name",
       count(file_name) "count",
       round(sum(DECODE(autoextensible,'YES',maxbytes,'NO',bytes))/1048576) "all",
       round(sum(bytes)/1048576) "alloc",
       round(sum(user_bytes)/1048576) "used"
  from DBA_DATA_FILES
 group by tablespace_name
union all
  SELECT h.tablespace_name "name",
         count(f.FILE_NAME ) "count",
         ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) /  1048576) "all",
         ROUND (SUM (h.bytes_free + h.bytes_used) /  1048576) "alloc",
         ROUND (SUM (NVL (p.bytes_used, 0)) /  1048576) "used"
    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) a
 order by 6 desc

Solution

Grant the user rights to read system directory data, for example

grant select any dictionary to <UserName>; 
или   
grant select on DBA_DATA_FILES to <UserName>;
grant select on sys.v_$TEMP_SPACE_HEADER to <UserName>;
grant select on sys.v_$Temp_extent_pool to <UserName>;
grant select on dba_temp_files to <UserName>;