Oracle Táblaterek

Alapvető Database Administrator (DBA) feladat a táblaterek karbantartása. A legtöbb monitorozás által generált riasztás általában azzal szokott kapcsolatban lenni, hogy a táblatér szabad kapacitása túl alacsony, és ilyenkor valamit tenni kell. A most következő írásban az Oracle adatbázis táblatereit próbálom bemutatni jó néhány gyakorlati példán keresztül.

Mik azok a táblaterek?

Az Oracle adatbázis, ahogy a nevéből is látszik adatokat tárol. Ezen adatokat táblázatokba, táblákba foglalja.

Az oszlopok (COLUMNS) jelölnek egy bizonyos típusú adatot. Például név, beosztás, mikor vettek fel minket, stb. A sorok (ROWS) jelölnek egy-egy dolgozót, tehát a tényleges adatot. Az oszlopokkal meghatározzuk, hogy egy sor megfelelő mezőjébe milyen típusú adat kerülhet, vagy épp kötelező-e kitölteni vagy nem.

A különböző alkalmazás ezt a struktúrát használja adat tárolásra. Természetesen több táblánk is lehet, amiben más és más adatokat tárolhatunk, illetve másik táblára is hivatkozhatunk.

Ahogy a táblákban nőnek a sorok számai, úgy lesz az adatbázisunkban is egyre több adat tárolva. Természetesen ezek az adatokat valahol tárolnunk kell. A tábláinknak bizonyos táblaterekhez (TABLESPACE) kell tartozniuk. A táblaterek definiálnak egy bizonyos tárolási kapacitást. A táblák pedig ezt a táblatér kapacitást használhatják a bővülésük során.

Természetesen eljön az a határ, amikor már több adatnak nem tud az adott táblatér helyet biztosítani. Ekkor vagy új táblateret kell hozzárendelnünk, vagy a meglévőt megnövelni.

(Amennyiben a következő listázási parancsok össze-vissza nyomottan jelennek meg, abban az esetben adjuk ki a set lines 300 parancsot. Ez állítja be, hogy egy sorba mennyi karaktert jelenítsen meg az sqlplus)

A következő leírás nagyban támaszkodik Gyene András írásaira.

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

Látszik, hogy a SYSTEM és USERS táblatér eléggé növelésre szorul.

Természetesen a táblaterek mérete összefüggésben van az őt futtató operációs rendszer lehetőségeivel. Az alapértelmezett a táblaterek file-kból állnak. Kérjük le, hogy milyen táblatérhez milyen file tartozik.

SQL> select name from v$datafile;

Jól látszik, hogy az aktuális táblatér file-k mihez tartoznak. Ezen file-k méretét növelve a táblatér nagyságát is növelhetjük. A file-kat természetesen csak akkor van lehetőségünk növelni, amennyiben a OS filesysteme is lehetővé teszi.

Táblatér műveletek

Először is nézzük meg, hogy lehet egy új táblateret létrehozni.

SQL> CREATE TABLESPACE GIBBON_DATA
DATAFILE ‘/ora/oradata/GIBBON/gibbon_data.dbf’ SIZE 1024m
SEGMENT SPACE MANAGEMENT AUTO;

A következő parancs segítségével bekapcsolhatjuk az AUTOEXTEND opciót, tehát igény szerint a táblatérhez tartozó állományokat automatikusan meg fogja növelni az Oracle DB.

SQL> ALTER DATABASE DATAFILE ‘/ora/oradata/GIBBON/gibbon_data.dbf’ AUTOEXTEND ON;

Természetesen ez veszélyes is tud lenni, ha nem kontrollált egy DB „éhsége”. Ezért én a továbbiakban azt fogom bemutatni, hogy lehet AUTOEXTEND nélkül karbantartani egy adatbázist.

Meglévő táblatér file növelése

A file növelését az Oracle DB-ből tudjuk kezdeményezni.

SQL> alter database datafile ‘/ora/oradata/GIBBON/system01.dbf’ resize 1024M;

A fenti parancs segítségével 1024MB-ra növeltük a file méretét.

Ismételjük meg az USER táblatér file-jával is.

SQL> alter database datafile ‘/ora/oradata/GIBBON/users01.dbf’ resize 100M;

Most pedig ellenőrizzük le mit csináltunk:

Ahogy látszik 1024 és 100MB-ra nőttek a megjelölt táblaterek.

Táblatér növelés új file-al

Természetesen arra is lehetőségünk van, hogy egy adott táblateret egy teljesen új file-al növeljünk meg, és ne a meglévőt méretezgessük.

SQL> alter TABLESPACE USERS add datafile ‘/ora/test.dbf007’ size 100M;

Kérjük le az adat file-kat ellenőrzésként:

SQL> select name from v$datafile;

Megjelent az új file. Ellenőrizzük a táblatér adatokat:

Ahogy látszik fent kiemelve, az USERS táblatér 100MB-ról 200MB-ra nőtt.

Táblaterek és adatállományok listázása

Természetes igény ezek után, hogy láthassuk, hogy milyen táblatérhez milyen DBF file tartozik.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;

A fenti lekérdezés eredményeként megkapjuk, hogy milyen file milyen táblatérhez tartozik. Zölddel bejelöltem, hogy két file tartozik az USERS táblatérhez.

Táblák migrálása táblaterek között

Alapból minden felhasználó az USERS táblateret használja. Viszont gyorsan felmerülhet az igény, hogy külön területet birtokolhasson egy felhasználó. Akár sebesség, akár megbízhatóság, vagy biztonsági megfontolásból. Nézzük, hogyan tudunk táblákat átmozgatni egyik táblatérről a másikra, illetve azt, hogy tudjuk egy felhasználónak beállítani az alapértelmezett táblaterét.

A következő parancs listázza ki, hogy milyen felhasználóhoz, milyen táblaterek tartoznak.

SQL> select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME;

Jól látszik, hogy a PHPUSER felhasználó az USERS táblatérhez tartozik most. Most listáztassuk ki, hogy az USERS táblatér mekkora helyből, mennyit fogyaszt:

Látszik, hogy a 150MB-ból csupán 65MB szabad. A már bemutatott módon növelhetnénk az USERS táblateret. Viszont most a mellett döntöttünk, hogy átmozgatjuk. Az új táblatér egy 1024MB méretű GIBBON_DATA nevű táblatér lesz.

Most listázzuk ki, hogy hány táblája van a PHPUSER nevű usernek:

SQL> Select Table_Name
From All_Tables
Where Owner = ‘PHPUSER’;

Nézzük meg, hogy aktuálisan ez a két tábla milyen táblatérhez tartozik.

SQL> select owner, table_name, tablespace_name
from dba_tables
where table_name=’WISHERS’;

SQL> select owner, table_name, tablespace_name
from dba_tables
where table_name=’WISHES’;

Tehát a fenti két WISHES és WISHERS nevű táblát tegyük át a USERS tablespace-ről a GIBBON_DATA tablespace-re:

SQL> ALTER TABLE PHPUSER.wishers MOVE TABLESPACE GIBBON_DATA;
SQL> ALTER TABLE PHPUSER.wishes MOVE TABLESPACE GIBBON_DATA;

Ellenőrizzük mi történt:

SQL> select owner, table_name, tablespace_name
from dba_tables
where table_name=’WISHERS’;

SQL> select owner, table_name, tablespace_name
from dba_tables
where table_name=’WISHES’;

Ahogy látszik a táblák táblatere megváltozott. Most állítsuk át a PHPUSER felhasználó alapértelmezett táblaterét. Ez azért fontos, hogy a jövőben létrehozott táblák alapból már az új táblatérhez tartozzanak.

Újraindexelés áthelyezés után

Könnyen bele lehet futni, hogy egy tábla átköltöztetése után minden egyes műveletnél a következő hibába futunk:

ORA-01502: index ‘PHPUSER.WISHERS_PK’ or partition of such index is in unusable state

Ez annyit tesz, hogy a tábla indexei megsérültek, ezért UNUSABLE státuszba került, és nem tud végrehajtani semmilyen műveletet.

Listázzuk ki az UNUSABLE státuszú indexeket:

SQL> select index_name, status, owner from
dba_indexes where STATUS = ‘UNUSABLE’;

Ezeket az indexeket újra kell generálni a következő parancs segítségével:

SQL> alter index PHPUSER.WISHES_PK rebuild;
SQL> alter index PHPUSER.SYS_C008170 rebuild;
SQL> alter index PHPUSER.WISHERS_PK rebuild;

Illetve van egy lekérdezés, ami automatikusan kigenerálja a kiadandó parancsokat az UNUSABLE index-ekhez:

SQL> select ‘alter index ‘||owner||’.’||index_name||’ rebuild;’ from
dba_indexes where STATUS = ‘UNUSABLE’;

Ezek után a UNUSABLE indexeket lekérő parancsnak nem kell hogy kimenete legyen.

SQL> select index_name, status, owner from
dba_indexes where STATUS = ‘UNUSABLE’;

Ezek után a tábla tökéletesen működik.

Táblaterek működés közben

Nézzük ezt most számokban. A move előtt mind két tábla a USERS tablespace-en volt, durván 66MB free space volt rajta:

A táblák „MOVE”-olása után látszik (USERS-ről GIBBON_DATA-ra) a USERS-en felszabadult a hely, ellenben a GIBBON_DATA-n csökkent a szabad hely, azaz a látszik, hogy át lettek téve a táblák.

Ezek után kétszázezer új sort adtunk hozzá a WISHER és WISHES táblához. Jól látszik, hogy a GIBBON_DATA táblatér szabad területe csökken csupán.