Rozmiar baz Oracle

Dziś opowiem jak sprawdzić rozmiar naszej bazy danych Oracle.
W większości przypadków rozmiar bazy można pobieżnie sprawdzić określając rozmiar folderu ORADATA który zawiera nasze pliki bazodanowe. Załóżmy jednak że nie mamy dostępu do naszego systemu, a jedynie do znajdującej się na serwerze bazy danych. Podłączamy się konsolka SQLPLUS lub SQLCL do naszej bazy.

ROZMIAR INSTANCJI

Aby sprawdzić całkowity rozmiar bazy bez rozdrabniania się na pliki, przestrzenie tabel, tabele czy indeksy wydajemy po prostu zapytanie :

select sum(bytes)/1024/1024/1024 as byte_GB from v$datafiles;

   BYTE_GB
----------
541

Dodatkowo jeszcze dochodzą pliki TEMP

select sum(bytes)/1024/1024/1024 as byte_GB from v$tempfile;

   BYTE_GB
----------
45

Możemy sprawdzić jakie typy obietków zajmują naszą bazę:

select SEGMENT_TYPE, round(sum(bytes)/1024/1024) as byte_MB from dba_extents group by segment_type;

SEGMENT_TYPE          BYTE_MB
------------------ ----------
LOBINDEX                   46
INDEX PARTITION           662
TABLE SUBPARTITION          2
TABLE PARTITION           147
NESTED TABLE                2
ROLLBACK                    0
LOB PARTITION               5
SYSTEM STATISTICS           8
LOBSEGMENT                267
INDEX                     301
TABLE                     709
CLUSTER                    58
TYPE2 UNDO                 57

13 rows selected.

ROZMIAR PRZESTRZENI TABEL

Jak już wiemy obiekty są zapisane w przestrzeniach tabel. Aby sprawdzić rozmiar przestrzeni tabel wykonujemy:

SELECT /* + RULE */ df.tablespace_name "Tablespace",
ROUND(df.bytes / (1024 * 1024)) "Size (MB)",
ROUND(SUM(fs.bytes) / (1024 * 1024)) "Free (MB)",
Nvl(ROUND(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(ROUND((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
ROUND((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
TEMP                                   60         48         80         20
UNDOTBS1                               75         17         23         77
SYSAUX                                620         36          6         94
USERS                                 858         24          3         97
SYSTEM                                800          9          1         99

Mam jeszcze inne zapytanie sprawdzające rozmiary przestrzeni tabel

SELECT a.tablespace_name,
ROUND(a.bytes_alloc/1024/1024) mb_alloc,
ROUND(NVL(b.bytes_free,0)/1024/1024) mb_free,
ROUND((a.bytes_alloc-NVL(b.bytes_free,0))/1024/1024) mb_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,c.status,c.contents
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 ts.name tablespace_name, SUM (fs.blocks) * ts.blocksize bytes_free
FROM DBA_LMT_FREE_SPACE fs, sys.ts$ ts
WHERE ts.ts# = fs.tablespace_id
GROUP BY ts.name, ts.blocksize) b,
dba_tablespaces c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.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 (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)/1048576)) MAX,c.status,c.contents
FROM sys.v_$TEMP_SPACE_HEADER h,
(SELECT tablespace_name, file_id, SUM (bytes_used) bytes_used
FROM gv$temp_extent_pool
GROUP BY tablespace_name, file_id) p, dba_temp_files f,dba_tablespaces c
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
AND f.tablespace_name = c.tablespace_name
GROUP BY h.tablespace_name, c.status, c.contents
ORDER BY 1;

TABLESPACE_NAME      MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX STATUS    CONTENTS
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------
SYSAUX                      620         36        585          6         94      32768 ONLINE    PERMANENT
SYSTEM                      800          9        791          1         99      32768 ONLINE    PERMANENT
TEMP                         60         60          0        100          0      32768 ONLINE    TEMPORARY
UNDOTBS1                     75         17         58         23         77      32768 ONLINE    UNDO
USERS                       858         24        833          3         97      32768 ONLINE    PERMANENT

I jeszcze inne:

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSAUX                                584         36        620          6
UNDOTBS1                               57         18         75         24
USERS                                 832         26        858          3
SYSTEM                                790         10        800          1

Każda przestrzeń jest zapisana w oddzielnym pliku. Ich rozmiar można sprawdzić zapytaniem:

col file_name format a45
col tablespace_name format a20
select file_name, tablespace_name, status, ROUND(bytes/1024/1024) as BYTES_MB, autoextensible, increment_by from DBA_DATA_FILES;

FILE_NAME                                     TABLESPACE_NAME      STATUS      BYTES_MB AUT INCREMENT_BY
--------------------------------------------- -------------------- --------- ---------- --- ------------
/home/oracle/app/oracle/oradata/BAZA/datafile SYSTEM               AVAILABLE        800 YES         1280
/o1_mf_system_dfnr4h2k_.dbf

/home/oracle/app/oracle/oradata/BAZA/datafile SYSAUX               AVAILABLE        620 YES         1280
/o1_mf_sysaux_dfnr3ct0_.dbf

/home/oracle/app/oracle/oradata/BAZA/datafile USERS                AVAILABLE        858 YES          160
/o1_mf_users_dfnr5wg3_.dbf

/home/oracle/app/oracle/oradata/BAZA/datafile UNDOTBS1             AVAILABLE         75 YES          640
/o1_mf_undotbs1_dfnr5xoj_.dbf

lub podobnym zapytaniem:

select NAME,CREATION_TIME,STATUS,round(bytes/1024/1024) as bytes,blocks from v$datafile;

NAME                                               CREATION STATUS       BYTES     BLOCKS
-------------------------------------------------- -------- ------- ---------- ----------
/home/oracle/app/oracle/oradata/BAZA/datafile/o1_m 14/07/07 SYSTEM         800     102400
f_system_dfnr4h2k_.dbf

/home/oracle/app/oracle/oradata/BAZA/datafile/o1_m 14/07/07 ONLINE         620      79360
f_sysaux_dfnr3ct0_.dbf

/home/oracle/app/oracle/oradata/BAZA/datafile/o1_m 14/07/07 ONLINE          75       9600
f_undotbs1_dfnr5xoj_.dbf

/home/oracle/app/oracle/oradata/BAZA/datafile/o1_m 14/07/07 ONLINE         858     109760
f_users_dfnr5wg3_.dbf

Lub jeszcze innym

select a.tablespace_name,a.file_name,a.bytes/1024/1024 TABLESPACE_SIZE_MB,
Sum(b.bytes)/1024/1024 FREE_IN_MB from dba_free_space b,dba_data_files a
Where a.tablespace_name = b.tablespace_name
AND a.file_id = b.file_id
GROUP by a.tablespace_name, a.file_name,a.bytes/1024/1024
Order by a.tablespace_name, a.file_name;

TABLESPACE_NAME      FILE_NAME                                TABLESPACE_SIZE_MB FREE_IN_MB
-------------------- ---------------------------------------- ------------------ ----------
APLIKACJE            /oradata/AFPROD/aplikacje01.dbf                       10000     6634.5
AUDYT                /oradata/AFPROD/audyt01.dbf                           15360   178.9375
AUDYT                /oradata/AFPROD/audyt02.dbf                           15360   647.0625
AUDYT                /oradata/AFPROD/audyt03.dbf                           15360   160.6875
AUDYT                /oradata/AFPROD/audyt04.dbf                           15360    461.625
AUDYT                /oradata/AFPROD/audyt05.dbf                           15360    616.125
AUDYT                /oradata/AFPROD/audyt06.dbf                           15360     652.25
AUDYT                /oradata/AFPROD/audyt07.dbf                           15360    806.375
AUDYT                /oradata/AFPROD/audyt08.dbf                           15360    443.625
AUDYT                /oradata/AFPROD/audyt09.dbf                           10240    149.125
SYSAUX               /oradata/AFPROD/sysaux01.dbf                           1200   147.9375
SYSTEM               /oradata/AFPROD/system01.dbf                           1024   732.4375
UNDOTBS1             /oradata/AFPROD/undotbs01.dbf                         13312   2934.875
USERS                /oradata/AFPROD/users01.dbf                           10000    6494.75

14 rows selected.

ROZMIAR POJEDYNCZEJ PRZESTRZENI

W tym celu wykonujemy zapytanie:

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and df.tablespace_name = 'AUDYT';

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
AUDYT                              131092       2028     133120          2

Zapytanie od pliki pojedynczej przestrzeni tabel

col TABLESPACE_NAME format a20
col ALLOCATED clear
col FILE_ID clear
col FILE_NAME format a40

select
d.tablespace_name,
d.file_id,
d.file_name,
round(d.bytes/1024/1024,0) allocated_mb,
round(nvl(f.bytes,0)/1024/1024,0) free_mb,
round(((f.bytes/d.bytes)*100),1) percent
from (select tablespace_name, file_id, file_name, sum(bytes) bytes from dba_data_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes from dba_free_space
group by tablespace_name, file_id) f
where d.tablespace_name in ('AUDYT')
and d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
order by tablespace_name, file_id;

TABLESPACE_NAME         FILE_ID FILE_NAME                                ALLOCATED_MB    FREE_MB    PERCENT
-------------------- ---------- ---------------------------------------- ------------ ---------- ----------
AUDYT                         5 /oradata/AFPROD/audyt01.dbf                     15360        179        1.2
AUDYT                         6 /oradata/AFPROD/audyt02.dbf                     15360        647        4.2
AUDYT                         7 /oradata/AFPROD/audyt03.dbf                     15360        161          1
AUDYT                         8 /oradata/AFPROD/audyt04.dbf                     15360        462          3
AUDYT                         9 /oradata/AFPROD/audyt05.dbf                     15360        616          4
AUDYT                        10 /oradata/AFPROD/audyt06.dbf                     15360        652        4.2
AUDYT                        12 /oradata/AFPROD/audyt07.dbf                     15360        806        5.2
AUDYT                        13 /oradata/AFPROD/audyt08.dbf                     15360        444        2.9
AUDYT                        14 /oradata/AFPROD/audyt09.dbf                     10240        149        1.5

9 rows selected.

ROZMIAR PRZESTRZENI TEMP

w powyższych zapytaniach nie jest wyświetlana przestrzeń TEMP. Jej rozmiar sprawdzamy oddzielnie:

select name,bytes/1024/1024 as bytes_MB from v$tempfile ;

NAME                                                 BYTES_MB
-------------------------------------------------- ----------
/home/oracle/app/oracle/oradata/BAZA/datafile/o1_m         60
f_temp_dfnr6xdk_.tmp

Wolne miejsce w przestrzeni możemy sprawdzić poleceniem

SQL> select tablespace_name,sum(bytes/(1024*1024)) "Free Mb"
from dba_free_space
group by tablespace_name;

TABLESPACE_NAME         Free Mb
-------------------- ----------
SYSTEM                 732.4375
APLIKACJE                6634.5
USERS                   6494.75
AUDYT                 4115.8125
SYSAUX                 147.9375
UNDOTBS1               2934.875

6 rows selected.

ROZMIARY SCHEMATÓW BAZY

Zapytanie zwracające rozmiar schematu (przykładowo schemat SH)

select round(sum(bytes)/1024/1024) as bytes_MB from dba_segments where owner='SH';

  BYTES_MB
----------
       798

Jeśli chcielibyśmy sprawdzić rozmiary poszczególnych schematów wykonujemy:

col owner format a25
select owner, sum(bytes)/1024/1024 schema_size_mb
from dba_segments
group by owner;

OWNER                     SCHEMA_SIZE_MB
------------------------- --------------
APEX_040200               193,875
MDSYS                     77,75
OUTLN                     ,5625
HR                        1,5625
CTXSYS                    3,8125
SYSTEM                    32,1875
SCOTT                     ,3125
DVSYS                     4,4375
AUDSYS                    1,4375
OE                        9,9375
DBSNMP                    ,375
GSMADMIN_INTERNAL         1,375
OJVMSYS                   ,375
ORDSYS                    ,4375
PM                        19,8125
SH                        798,4375
XDB                       68,75
ORDDATA                   16,0625
IX                        1,9375
SYS                       1021,6875
WMSYS                     7,1875
LBACSYS                   ,3125

22 rows selected.

Możemy też sprawdzić ile zajmują poszczególne schematy w naszej przestrzeni tabel (np.USERS)

select owner, sum(bytes)/1024/1024 as bytes_MB from dba_segments where tablespace_name = 'USERS' group by owner;

OWNER                       BYTES_MB
------------------------- ----------
HR                        1,5625
SCOTT                     ,3125
OJVMSYS                   ,375
OE                        9,9375
PM                        19,8125
SH                        798,4375
IX                        1,9375

7 rows selected.

ROZMIARY TABEL

Jak już wiemy ile zajmują nasze przestrzenie tabel pora dowiedzieć się ile zajmują nasze tabele w tych przestrzeniach w naszych schematach
Przykładowo aby sprawdzić ile zajmują tabele schematu HR wykonujemy zapytanie:

col owner format a10
col segment_name format a20
SELECT owner,
segment_name,
segment_type,
tablespace_name,
bytes/1024/1024 as bytes_MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'HR'
AND SEGMENT_TYPE = 'TABLE'
order by bytes desc;

OWNER      SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME              MB INITIAL_EXTENT NEXT_EXTENT    EXTENTS PCT_INCREASE
---------- -------------------- ------------------ -------------------- ---------- -------------- ----------- ---------- ------------
HR         JOB_HISTORY          TABLE              USERS                ,0625               65536     1048576          1
HR         EMPLOYEES            TABLE              USERS                ,0625               65536     1048576          1
HR         JOBS                 TABLE              USERS                ,0625               65536     1048576          1
HR         DEPARTMENTS          TABLE              USERS                ,0625               65536     1048576          1
HR         LOCATIONS            TABLE              USERS                ,0625               65536     1048576          1
HR         REGIONS              TABLE              USERS                ,0625               65536     1048576          1

6 rows selected.

Jeśli chcemy się dowiedzieć jakiego usera (schematu) tabela jest największa

select segment_name,segment_type,owner, bytes/1024/1024 as size_MB
from dba_segments
where segment_type='TABLE'
order by bytes;

SEGMENT_NAME         SEGMENT_TYPE       OWNER         SIZE_MB
-------------------- ------------------ ---------- ----------
IDL_CHAR$            TABLE              SYS                11
OBJ$                 TABLE              SYS                12
ARGUMENT$            TABLE              SYS                13
CUSTOMERS            TABLE              SH                 13
WWV_FLOW_PAGE_PLUGS  TABLE              APEX_04020         15
JAVA$MC$             TABLE              SYS                17
IDL_UB2$             TABLE              SYS                32
SOURCE$              TABLE              SYS                52
IDL_UB1$             TABLE              SYS               288

POWIĘKSZANIE TABLESPACE

Załóżmy, że zapchała się nam przestrzeń tabel USERS. Aby ją powiększyć należy najpierw sprawdzić jej rozmiar

select name,round(bytes/1024/1024) as bytes_MB from v$datafile where name like '%users%';

NAME                                                 BYTES_MB
-------------------------------------------------- ----------
/home/oracle/app/oracle/oradata/BAZA/datafile/            858
users01.dbf

W pierwszej kolejności pamiętajmy o sprawdzeniu czy na filesystemie znajduje się odpowiednia ilość miejsca aby powiększyć przestrzeń tabel.

Powiększamy obecny plik przestrzeni tabel:

ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/BAZA/datafile/users01.dbf' RESIZE 8000M;

lub dodać kolejny plik

ALTER TABLESPACE users ADD DATAFILE '/home/oracle/app/oracle/oradata/BAZA/datafile/users02.dbf' SIZE 5G AUTOEXTEND OFF;

I to by było na tyle wiedzy ogólnej o rozmiarach bazy Oracle 🙂

Ten wpis został opublikowany w kategorii Oracle i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz