OracleXE скрипты

Также все скрипты продублированы на SVN

\DB_ER\maintenance\OracleXE

Использование ОЗУ

--  Параметр space Allocated
SELECT to_char(round(space_used/1024/1024), '999G999G990') as "space_used"
--,space_used
  FROM (SELECT SUM (bytes) space_used
          FROM sys.dba_data_files, sys.ts$
         WHERE tablespace_name = name AND contents$ = 0 AND flags != 17) s
ORDER BY 1

Место по каждому tablespace

-- Oracle XE запрос, который считает место по Table_space
SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN'))
           tablespace_name,
       kbytes_alloc kbytes,
       kbytes_alloc - NVL (kbytes_free, 0) size_alloc_bytes,
       ROUND ( ( (kbytes_alloc - NVL (kbytes_free, 0)) / kbytes_alloc) * 200)
           used_chart,
       TO_CHAR (
           ( (kbytes_alloc - NVL (kbytes_free, 0)) / kbytes_alloc) * 100,
           '999G999G999G999G999G999G990D00')
       || '%'
           used,
       data_files
  FROM (SELECT SUM (bytes) / 1024 / 1024 kbytes_free,
               MAX (bytes) / 1024 / 1024 largest,
               tablespace_name
          FROM sys.dba_free_space
        GROUP BY tablespace_name) a,
       (SELECT SUM (bytes) / 1024 / 1024 kbytes_alloc,
               tablespace_name,
               COUNT (*) data_files
          FROM sys.dba_data_files
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name(+) = b.tablespace_name
ORDER BY 1

Занято места всего

--  Параметр space Allocated
SELECT to_char(round(space_used/1024/1024), '999G999G990') as "space_used"
--,space_used
  FROM (SELECT SUM (bytes) space_used
          FROM sys.dba_data_files, sys.ts$
         WHERE tablespace_name = name AND contents$ = 0 AND flags != 17) s
ORDER BY 1