RecycleBin czyli śmietnik oraclowy

RecycleBin (inaczej zwany śmietnikiem/koszem itd) ogólnie służy do szybkiego odzyskania usuniętych tabel. Oczywiście jeśli go mamy włączonego (od wersji 10g jest on domyślnie włączony).

Aby sprawdzić czy mamy go właczonego należy sprawdzić parametry bazy związane z recyclebin’em.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

Jak widać jest on włączony. Aby go wyłączyć globalnie na całej bazie należy wykonać polecenie

SQL> alter system set recyclebin=off scope=spfile;

System altered.

i wykonać restart bazy. Można również wyłączyć śmietnik dla danej sesji :

SQL> alter session set recyclebin=on;

System altered.

Każda pracująca produkcyjnie baza zawiera wiele elementów usuniętych które znajdują się w śmietniku. Aby sprawdzić ich rozmiar wykonujemy:

SQL> select sum(space*8)/1024 space_in_MB from dba_recyclebin;

SPACE_IN_MB
-----------
      19614

Można sprawdzić ile zajmują śmiecie dla konkretnych userów

select owner,sum(space)* 8 / 1024 "Size in MB" from dba_recyclebin group by owner order by sum(space) desc;

OWNER                          Size in MB
------------------------------ ----------
KOWAL329                          5793.25
PASZKMA4                          5366.75
WPR_MDEVA_R4                     4193.375
BO4_META_PROD                    3088.875
PRZADKAM                              426
KRZYSGAS                          292.125
JOZWIAR1                            274.5
WPR_PPRODA_R4                           0
WPR_UATA_R4                             0

9 rows selected.

Szerszy opis widoku dba_recyclebin znajdziecie na stronie
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4046.htm

Jak widać jest tego sporo (około 19GB). Dlatego co jakiś czas warto wykonywać czyszczenie śmietnika PURGE (szczególnie jak wyświetlenie obiektów zaczyna trwać znacznie dłużej niż normalnie)

Wykonujemy to następującym poleceniem:

SQL> purge dba_recyclebin;

Usuwanie tabel i grzebanie w śmietniku

Pokażę przykład jak odzyskać tabelę ze śmietnika. I tu mała uwaga
ZE ŚMIETNIKA MOŻNA ODZYSKIWAĆ TYLKO TABELE OSUNIĘTE PRZEZ UŻYTKOWNIKÓW NIE-SYSów. Jeśli usuniemy tabelę z użytkownika SYS nie można jej odzyskać.

Tak więc w naszym przykładzie najpierw musimy utworzyć nowego użytkownika i dać mu odpowiednie prawa:

SQL> create user haker identified by pa$$w0rd;
SQL> grant connect to haker;
SQL> GRANT UNLIMITED TABLESPACE TO haker;
SQL> grant create table to haker;

A teraz logujemy się na użytkownika HAKER i wykonujemy poniższe polecenia.
Tworzymy nową tabelę.

HAKER-SQL> create table test_tab_bin (id number);

Table created. 

Wykonujemy kilka insertów

HAKER-SQL> insert into test_tab_bin values ('15');
insert into test_tab_bin values ('10');
insert into test_tab_bin values ('20');

1 row created.

Sprawdzamy czy się zapisały do tabeli

HAKER-SQL> select * from test_tab_bin;

        ID
----------
        15
        10
        20

A teraz usuwamy naszą tabelę.

HAKER-SQL> drop table test_tab_bin;

Table dropped. 

tabele usunięta. Wyświetlamy zawartość śmietnika.

HAKER-SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST_TAB_BIN     BIN$XZ33JULnC0jgVQAAAAAAAQ==$0 TABLE        2017-11-10:09:47:06

Jak widać w śmietniku jest nasza tabela. Rozmiar śmietnika jest mały bo tylko ta jedna tabela tam się znajduje.

SYS-SQL> select owner,sum(space)* 8 / 1024 "Size in MB" from dba_recyclebin group by owner order by sum(space) desc;

OWNER                     Size in MB
------------------------- ----------
HAKER                          .0625

Można też wydać zapytanie do widoku user_recyclebin pokazujący tylko tabele usera :

HAKER-SQL> col OBJECT_NAME format a35
col ORIGINAL_NAME format a15
col TYPE format a10
col TS_NAME format a10
col PARTITION_NAME format a20
select object_name, original_name, operation, type, droptime from user_recyclebin;

OBJECT_NAME                         ORIGINAL_NAME   OPERATION TYPE       DROPTIME
----------------------------------- --------------- --------- ---------- -------------------
BIN$XZ33JULoC0jgVQAAAAAAAQ==$0      TEST_TAB_BIN    DROP      TABLE      2017-11-10:10:05:45

z poziomu SYS-a możemy sprawdzić widok dba_recyclebin pokazujący wszystkie tabele w śmietniku

SYS-SQL> col OWNER format a10
col OBJECT_NAME format a35
col ORIGINAL_NAME format a15
col TYPE format a10
col TS_NAME format a10
col PARTITION_NAME format a20
select * from dba_recyclebin;

OWNER      OBJECT_NAME                         ORIGINAL_NAME   OPERATION TYPE       TS_NAME    CREATETIME          DROPTIME               DROPSCN PARTITION_NAME       CAN CAN          RELATED BASE_OBJECT PURGE_OBJECT      SPACE
---------- ----------------------------------- --------------- --------- ---------- ---------- ------------------- ------------------- ---------- -------------------- --- --- ---------- ----------- ------------ ----------
MICKIA     BIN$XZ33JULoC0jgVQAAAAAAAQ==$0      TEST_TAB_BIN    DROP      TABLE      USERS      2017-11-10:09:45:59 2017-11-10:10:05:45    2735452           YES YES       92714       92714        92714          8

Jesli sprawdzimy user_recyclebin’a z poziomu SYS’a nic nam nie pokaże bo nie ma w śmietniku tabel usera SYS tylko HAKER’a

SYS-SQL> select * from user_recyclebin;

no rows selected

SYS-SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

no rows selected

MICKIA-SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

OBJECT_NAME                         ORIGINAL_NAME   TYPE       UND PUR DROPTIME
----------------------------------- --------------- ---------- --- --- -------------------
BIN$XZ33JULoC0jgVQAAAAAAAQ==$0      TEST_TAB_BIN    TABLE      YES YES 2017-11-10:10:05:45

Odzyskiwanie tabel ze śmietnika

Możemy to wykonać zarówno z poziomu usera HAKER jak i z poziomu SYS’a. Polecenie odzyskiwania jest takie samo.

MICKIA-SQL> select * from test_tab_bin;

ERROR at line 1:
ORA-00942: table or view does not exist

Jak widać tabela nie istnieje.

SYS-SQL> col OWNER format a10
col OBJECT_NAME format a35
col ORIGINAL_NAME format a15
col TYPE format a10
col TS_NAME format a10
col PARTITION_NAME format a20
select * from dba_recyclebin where original_name='TEST_TAB_BIN' AND OWNER = 'MICKIA';

OWNER      OBJECT_NAME                         ORIGINAL_NAME   OPERATION TYPE       TS_NAME    CREATETIME          DROPTIME               DROPSCN PARTITION_NAME       CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
---------- ----------------------------------- --------------- --------- ---------- ---------- ------------------- ------------------- ---------- -------------------- --- --- ---------- ----------- ------------ ----------
MICKIA     BIN$XZ33JULnC0jgVQAAAAAAAQ==$0      TEST_TAB_BIN    DROP      TABLE      USERS      2017-11-10:09:45:59 2017-11-10:09:47:06    2734314                      YES YES      92714       92714        92714          8

Znajduje się nadal w śmietniku

MICKIA-SQL> flashback table mickia.TEST_TAB_BIN to before drop;

Flashback complete.

Sprawdzamy czy tabela istnieje i ma swoje wpisane wiersze.

MICKIA-SQL> select * from test_tab_bin;

        ID
----------
        15
        10
        20

MICKIA-SQL> show recyclebin;

Nic nie pokazuje, bo w śmietniku już nic nie ma.

Odzyskiwanie z poziomu SYS’a wygląda tak samo.

Opróżnianie śmietnika

Możemy usunąć wszystkie śmieci ze śmietnika
SQL> purge user_recyclebin;

Recyclebin purged.

lub tylko śmiecie użytkownika (oczywiście logując się na tego użytkownika)

HAKER-SQL> purge user_recyclebin;

Recyclebin purged.

śmiecie użytkownika SYSDBA

SQL> conn / as sysdba

Connected.


SQL> purge dba_recyclebin;

DBA Recyclebin purged.

lub usunąć tylko wybrane tabele (np jakieś olbrzymie które niepotrzebnie zajmują miejsce)
SQL> purge table tbl_rc_bin;

Table purged.


SQL> PURGE TABLE BIN$jsleilx392mk2=293$0;

Table purged.

lub tylko tabele z konkretnej przestrzeni tabel

HAKER-SQL> purge tablespace mojaTBS;

Tablespace purged.

Codzienne wynoszenie śmieci 🙂

Możemy również wygenerować skrypt który będzie nam usuwał śmiecie starsze niż miesiąc 🙂

spool runme.sql

select
-- owner,
-- original_name,
'purge table "' || owner || '"."' || object_name || '";'
from
dba_recyclebin
where
can_purge = 'YES'
and
type = 'TABLE'
and
to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS') < sysdate - 30
order by 1, 2;

uruchomienie skryptu

@runme

I to by było na tyle a propos śmietnika Oracla 🙂

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

Dodaj komentarz