Размер табличных пространств Oracle

Другой запрос для вычисления размера табличных пространств Oracle:
http://www.dba-oracle.com/t_tablespace_script.htm

set lines 132
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00

select
a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from
dba_data_files
group by
tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from
dba_segments
group by
tablespace_name ) b
where
a.tablespace_name = b.tablespace_name (+)
and
a.tablespace_name not in
(select distinct
tablespace_name
from
dba_temp_files)
and
a.tablespace_name not like 'UNDO%'
order by 1;

ousers.sh

cat ousers.sh
#!/bin/bash
# 17.03.2015
# Selecting usernames and their statuses from dba_users (ORACLE)
daScr=/tmp/osu_120629.sql
echo SET PAGESIZE 0\;>$daScr

if [ -z $1 ]; then # Выполнять этот блок, если скрипт запущен без ключей.

echo select username \|\| case when account_status=\'OPEN\' then \'\' else \' \(\' \|\| account_status \|\| \'\)\' end case from dba_users>>$daScr
echo where default_tablespace=\'USERS\'>>$daScr
echo and username not in \(\'APEX_PUBLIC_USER\',\'ORACLE_OCM\'\,\'SPATIAL_CSW_ADMIN_USR\',\'SPATIAL_WFS_ADMIN_USR\'\) --Исключаем из выборки пользователей, которые создаются по-умолчанию в процессе установки и конфигурирования Oracle>>$daScr
echo and username like \'%=_%\' escape \'=\' -- Включаем в выборку только тех пользователей, у которых в имени есть символ подчеркивания \(мы только так схемы и называем\)>>$daScr
echo order by account_status desc, username asc\;>>$daScr
echo exit\;>>$daScr
sqlplus -S / as sysdba @$daScr

if [[ -n $DISPLAY ]]; then read -p 'Press ENTER key to exit.'; fi

elif [ $1 == "-s" ]; then # Скрипт выводит список схем с указанием размера каждой из них

echo select owner, to_char\(sum\(bytes\)/1048576,\'999G999G999G999D999\'\) \"SIZE \(MB\)\" from dba_segments group by owner order by sum\(\bytes\) desc\;>>$daScr
echo exit\;>>$daScr
sqlplus -S / as sysdba @$daScr

else
  echo Usage:
  echo $0 [-s]
fi
rm $daScr

Размер схем Oracle

--Для DBA (c примером форматирования числа: группировка цифр по разрядам с помощью TO_CHAR)

select owner, to_char(sum(bytes)/1048576,'999G999G999G999D999') "SIZE (MB)" from dba_segments group by owner order by sum(bytes) desc;

Размер таблиц схемы Oracle

--Для SYSDBA

select t.TABLE_NAME, sum(s.bytes)/1048576 "SIZE (MB)"
from dba_segments s join dba_tables t on t.owner=s.owner and t.TABLE_NAME=s.SEGMENT_NAME
where t.owner=upper('<OWNER_NAME>')
group by t.TABLE_NAME
order by sum(s.bytes) desc;

--Для пользователя схемы:

select t.TABLE_NAME, sum(s.bytes)/1048576 "SIZE (MB)"
from user_segments s join user_tables t on t.TABLE_NAME=s.SEGMENT_NAME
group by t.TABLE_NAME
order by sum(s.bytes) desc;

Расчет размера табличных пространств Oracle (еще один подход к снаряду)

--DBA_DATA_FILES
SELECT /* + RULE */
df.tablespace_name,
round(sum(df.maxbytes)/1048576) "Size (MAX) (MB)",
round(sum(df.bytes)/1048576) "Size (CRNT) (MB)",
round((sum(df.maxbytes) - sum(df.bytes))/1048576) "Free (MB)",
round((sum(df.bytes)*100)/sum(df.maxbytes)) "Free (MAX) (%)"
FROM dba_data_files df
GROUP BY df.tablespace_name
UNION ALL
--DBA_TEMP_FILES
SELECT /* + RULE */
df.tablespace_name,
round(sum(df.maxbytes)/1048576),
round(sum(df.bytes)/1048576),
round((sum(df.maxbytes) - sum(df.bytes))/1048576),
round((sum(df.bytes)*100)/sum(df.maxbytes))
FROM dba_temp_files df
GROUP BY df.tablespace_name
order by 1 desc;

/* bash-вариант */
cat osize.sh
#!/bin/sh
daScr=/tmp/ots_150306.sql
echo --DBA_DATA_FILES>$daScr
echo SELECT /\* + RULE \*/>>$daScr
echo df.tablespace_name,>>$daScr
echo round\(sum\(df.maxbytes\)/1048576\) \"SizeMAX\(MB\)\",>>$daScr
echo round\(sum\(df.bytes\)/1048576\) \"SizeCUR\(MB\)\",>>$daScr
echo round\(\(sum\(df.maxbytes\) - sum\(df.bytes\)\)/1048576\) \"Free\(MB\)\",>>$daScr
echo round\(\(sum\(df.bytes\)\*100\)/sum\(df.maxbytes\)\) \"Free\(MAX\)\(%\)\">>$daScr
echo FROM dba_data_files df>>$daScr
echo GROUP BY df.tablespace_name>>$daScr
echo UNION ALL>>$daScr
echo --DBA_TEMP_FILES>>$daScr
echo SELECT /\* + RULE \*/>>$daScr
echo df.tablespace_name,>>$daScr
echo round\(sum\(df.maxbytes\)/1048576\),>>$daScr
echo round\(sum\(df.bytes\)/1048576\),>>$daScr
echo round\(\(sum\(df.maxbytes\) - sum\(df.bytes\)\)/1048576\),>>$daScr
echo round\(\(sum\(df.bytes\)\*100\)/sum\(df.maxbytes\)\)>>$daScr
echo FROM dba_temp_files df>>$daScr
echo GROUP BY df.tablespace_name>>$daScr
echo order by 1 desc\;>>$daScr
echo exit\;>>$daScr
if [ $USER == root ]
then
su - oracle -c "sqlplus / as sysdba @$daScr"
else
sqlplus / as sysdba @$daScr
fi
rm $daScr
if [[ -n $DISPLAY ]]; then read -p 'Press ENTER key to exit.'; fi

Размер БД Oracle с разбивкой по сегментам

Расчет размера БД Oracle:

select
    sum(bytes)
from
    DBA_EXTENTS
where
    owner=<OWNER_NAME>;

Размер БД с разбивкой по именам сегментов и их типов (таблицы, индексы и т.д.):
Примечание: в академических целях расписано подробно.

select
    SEGMENT_NAME,
    SEGMENT_TYPE,
    sum(bytes)
from
    DBA_SEGMENTS
where
    owner=<OWNER_NAME>
group by
    SEGMENT_NAME, SEGMENT_TYPE;

Размер БД по таблицам (с учетом индексов и LOB-сегментов):

select TABLE_NAME, sum(bytes)/1048576 as SIZE_MB from
(
select t.TABLE_NAME, s.bytes
from user_segments s join user_tables t on t.TABLE_NAME=s.SEGMENT_NAME
where s.SEGMENT_TYPE = 'TABLE'
union all
select t.TABLE_NAME, s.bytes
from user_segments s join user_indexes t on t.INDEX_NAME=s.SEGMENT_NAME
where s.SEGMENT_TYPE = 'INDEX'
union all
select t.TABLE_NAME, s.bytes
from user_segments s join user_indexes t on t.INDEX_NAME=s.SEGMENT_NAME
where s.SEGMENT_TYPE = 'LOBINDEX'
union all
select t.TABLE_NAME, s.bytes
from user_segments s join user_lobs t on t.SEGMENT_NAME=s.SEGMENT_NAME
where s.SEGMENT_TYPE = 'LOBSEGMENT'
)
group by TABLE_NAME
order by sum(bytes) desc
;


По мотивам.