Zmiana rozmiaru przestrzeni tabel w Oracle

Domyślnie przestrzenie tabel (tablespace) mają ustawione autorozszerzanie co powoduje, że powiększają swój rozmiar w zależności od potrzeb (same się nie zmniejszają).
Jeśli jakaś przestrzeń się zapycha trzeba ją powiększyć. Można powiększyć znajdujące się na dysku pliki należące do danej przestrzeni lub dodać nowe (np na innym dysku).

Logujemy się do bazy

sqlplus "/as sysdba"

Najpierw możemy sprawdzić wielkość całej bazy:

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Database Size        Used space           Free space
-------------------- -------------------- --------------------
175 GB               144 GB               31 GB

a następnie zajętość w przestrzeniach tabel zapytaniem:

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
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
------------------------------ ---------- ---------- ---------- ----------
JSON                                 1229       1161         94          6
UNDOTBS1                              280   240.0625         86         14
USERS                                  10     8.3125         83         17
EXAMPLE                                50     25.375         51         49
SOE                                  2500       1053         42         58
CCDATA                               1741        459         26         74
SYSAUX                               1024    218.375         21         79
SH                                   2253        476         21         79
SYSTEM                               1500    154.625         10         90
TPCDSLIKEINDEXES                      372         18          5         95
TPCDSLIKE                             632         31          5         95
TEMP                                 1434          0          0        100

12 rows selected.

Przykładowo powiększymy przestrzeń TPCDSLIKE (wolnego 5%)

Sprawdzamy ilość wolnego miejsca na dysku

SQL> !df -h

Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             3.8G     0  3.8G   0% /dev
tmpfs                3.8G     0  3.8G   0% /dev/shm
tmpfs                3.8G   17M  3.8G   1% /run
tmpfs                3.8G     0  3.8G   0% /sys/fs/cgroup
/dev/mapper/ol-root   26G  6.8G   20G  27% /
/dev/xvdc1            40G   19G   19G  50% /ORACLE
/dev/xvda1          1014M  174M  841M  18% /boot
tmpfs                773M     0  773M   0% /run/user/1000

Bazy znajdują się w filesystemie /ORACLE więc miejsca jest sporo.

Wyświetlamy nazwy przestrzeni tabel

select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC     CON_ID
---------- ------------------------------ --- --- --- --- ----------
         1 SYSAUX                         YES NO  YES              0
         0 SYSTEM                         YES NO  YES              0
         2 UNDOTBS1                       YES NO  YES              0
         4 USERS                          YES NO  YES              0
         3 TEMP                           NO  NO  YES              0
         6 EXAMPLE                        YES NO  YES              0
         7 SOE                            YES YES YES              0
         8 SH                             YES YES YES              0
         9 CCDATA                         YES NO  YES              0
        10 TPCDSLIKE                      YES NO  YES              0
        11 TPCDSLIKEINDEXES               YES YES YES              0
        12 JSON                           YES YES YES              0

12 rows selected.

Sprawdzamy czy nasza przestrzeń tabel nie jest typu BIGFILE. Jeśli tak to nie będzie można dołożyć nowego pliku. W naszym przypadku ma ona wartość NO czyli posiada normalne pliki danych.

Wyświetlamy nazwy plików przestrzeni tabel

SQL> col name format a50
SQL> select name,bytes/1024/1024/1024 as ROZMIAR_GB from v$datafile;

NAME                                               ROZMIAR_GB
-------------------------------------------------- ----------
/ORACLE/app/oracle/oradata/orcl/system01.dbf       1.46484375
/ORACLE/app/oracle/oradata/orcl/soe01.dbf          2.44140625
/ORACLE/app/oracle/oradata/orcl/sysaux01.dbf                1
/ORACLE/app/oracle/oradata/orcl/undotbs01.dbf        .2734375
/ORACLE/app/oracle/oradata/orcl/example01.dbf      .048828125
/ORACLE/app/oracle/oradata/orcl/users01.dbf        .009765625
/ORACLE/app/oracle/oradata/orcl/sh01.dbf           2.20019531
/ORACLE/app/oracle/oradata/orcl/ccdata.dbf         1.70019531
/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf             .61719
/ORACLE/app/oracle/oradata/orcl/tpcdidx01.dbf       .36328125
/ORACLE/app/oracle/oradata/orcl/json01.dbf         1.20019531

11 rows selected.

Jeśli plików jest sporo (np kilkaset) i ciężko cokolwiek znaleźć można zawęzić wyszukiwanie

select name,bytes/1024/1024/1024 as ROZMIAR_GB from v$datafile where name like '%tpcd01%';

NAME                                               ROZMIAR_GB
-------------------------------------------------- ----------
/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf                     .6171875

POWIĘKSZENIE PLIKU TABLESPACE

Powiększamy plik tpcd01.dbf do 800MB:

ALTER DATABASE DATAFILE '/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf' RESIZE 800M;

Database altered.

Sprawdzamy

select name,bytes/1024/1024/1024 from v$datafile where name like '%tpcd01%';

NAME                                               BYTES/1024/1024/1024
-------------------------------------------------- --------------------
/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf                       .78125

Jeśli autorozszerzanie jest wyłączone możemy je właczyć poleceniem:

ALTER TABLESPACE TPCDSLIKE AUTOEXTEND ON NEXT 100M;

Tablespace altered.

DODANIE NOWEGO PLIKU TABLESPACE

ALTER TABLESPACE TPCDSLIKE ADD DATAFILE '/ORACLE/app/oracle/oradata/orcl/tpcd02.dbf' SIZE 100M AUTOEXTEND OFF;

Tablespace altered.

select name,bytes/1024/1024/1024 from v$datafile where name like '%prod_sun_data%';

NAME                                               BYTES/1024/1024/1024
-------------------------------------------------- --------------------
/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf                     .6171875
/ORACLE/app/oracle/oradata/orcl/tpcd02.dbf                     .1021345

Uwaga jest plik tablespace jest typu BIGFILE to nie można do niego dokładać plików. Otrzymamy wtedy komunikat:

ALTER TABLESPACE TPCDSLIKE ADD DATAFILE '/ORACLE/app/oracle/oradata/orcl/tpcd02.dbf' SIZE 100M AUTOEXTEND OFF
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

ZMNIEJSZENIE PLIKU TABLESPACE

Na postawie zapytania o rozmiar i zajętość tablespace możemy orientacyjnie określić minimalną wielkość do której możemy zmniejszyć plik tablespace jednak jeśli w wolnej przestrzeni znajdują się jakieś używane bloki danych zmniejszenie poniżej tego poziomu skończy się błędem.

ALTER DATABASE DATAFILE '/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf' RESIZE 500M;

ALTER DATABASE DATAFILE '/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf' RESIZE 500M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Próbujemy z większą wartością

ALTER DATABASE DATAFILE '/ORACLE/app/oracle/oradata/orcl/tpcd01.dbf' RESIZE 650M;

Database altered.

Shrinkowanie tablespace:

alter tablespace TPCDSLIKE shrink space keep 20M;

alter tablespace TPCDSLIKE shrink space keep 20M
*
ERROR at line 1:
ORA-12916: cannot shrink permanent or dictionary managed tablespace

Nie możemy shrinkować przestrzeni typu PERMAMENT.

ZMNIEJSZANIE PLIKU TEMP

Poniższe polecenie zmniejsza tymczasową przestrzeń tabel TEMP do rozmiaru 20MB

alter tablespace temp SHRINK SPACE KEEP 20M;

Tablespace altered.

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
TEMP                                   20         19         95          5

lub zeshrinkujemy do „zera”

ALTER TABLESPACE temp SHRINK TEMPFILE '/ORACLE/app/oracle/oradata/orcl/temp01.dbf';

Tablespace altered.

Sprawdzamy wielkość i zajętość:

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
TEMP                            1.9921875          0          0        100

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

Dodaj komentarz