Oracle Recycle Bin

Oracle Recycle Bin

Oracle 10g introduced the recycle bin. You can recover a table that you have dropped from the Oracle recycle bin by using the flashback table command as seen here:

SQL> DROP TABLE books;

SQL> FLASHBACK TABLE books TO BEFORE DROP;

The recycle bin uses the flashback table command.

However, the more time that has passed since the table was dropped, the less likely it will be in the Oracle recycle bin (The Oracle recycle bin is purged periodically based on a number of different criteria).

The contents of the Oracle recycle bin can be viewed from SQL*Plus by using the show recyclebin command:

SQL> show recyclebin;
 
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ————
BOOKS            BIN$D3XWKKUCQVq2EG8/vkjNDw==$0 TABLE        2005-05-30

Managing the Recycle Bin

The recycle binis a new feature in Oracle 10g which keeps dropped objects. When you drop an object, no space is released and the object is moved to the logical container called the recycle bin. In case you want to get the table back, issue the flashback drop command as it was explained in the previous scenarios. Each user has a view called recycle_bin which he can use to get the list of dropped objects.

You can query the dropped object by not restoring it from the recycle bin. This is done by using the special name that was given to the dropped object by Oracle, i.e. the object name starting with bin$. To get the name of all dropped objects, use the show recycle_bin command. More detailed information can be found by querying the user_recyclebin view. To understand the concept, see the following example:

SQL>
create
table tbl_rc_bin (id number);
Table created. 

SQL>
drop
table tbl_rc_bin;
Table dropped.

SQL>
show
recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME        OBJECTTYPE  DROP TIME
————- ——————–      ———– ——-
TBL_RC_BIN    BIN$fzdTKcxkrMDgQAB/AQAUbA==$0 TABLE  2010-02-09:22:06:47

SQL>
select
object_name, original_name, operation, type, droptime
from
user_recyclebin;

OBJECT_NAME              ORIGINAL_NAME   OPERATION  TYPE    DROPTIME
————             ————— ———– —-  ——-
BIN$fzdTKcxkrMDgQAB/AQAUbA==$0 TBL_RC_BIN   DROP TABLE       2010-02-
9:22:06:47

SQL>

Note: When running queries for used space and free space in a tablespace, segments that have moved to the recyclebin will not be listed as normal table/index segments consuming used space, but will reduce the free space.  So be aware of the size of the recyclebin when generating space usage reports for the database.

Recycle bin objects, i.e. dropped objects, will not be included during the Oracle export. Only the real objects can be exported. So after importing, there is no need to panic when you find that the total number of objects count is different from source to target.

Purging Objects From the Recycle Bin

To remote the tables and indexes from the recycle binand free the space that they consume, use the PURGE clause.

To purge the specific table or index, use:

SQL>
purge
table tbl_rc_bin;
Table purged.
SQL>
purge user_recyclebin;
Recyclebin purged.

To purge the objects in the user’s recycle bin, use:

SQL>
purge
recyclebin;
Recyclebin purged.

To purge all objects from the recycle bin, use:

SQL>
conn / as
sysdba
Connected.
SQL>
purge
dba_recyclebin;
DBA Recyclebin purged.
SQL>

To purge all objects of the specific tablespace, use:

SQL>
purge
tablespace users;
Tablespace purged.
SQL>

Disabling the Recycle Bin Functionality

There is a recyclebin parametern the parameter file whose default is ON.

SQL>
show
parameter recyclebin;

NAME                       TYPE        VALUE
————————– ———– —————————–
recyclebin                 string      ON
SQL>

To disable it to function, use:

alter system set recyclebin=off;

To disable it in the session level, use:

alter session set recyclebin=off;

To delete the table without putting it in the recycle bin, use the purge command at the end of the DROP TABLE clauseas follows:

SQL>
create
table tbl_rc (id number);
Table created. 

SQL>
drop
table tbl_rc purge;
Table dropped.

SQL>
show
recyclebin;
SQL>

Although this is enabled by default, if the parameter recyclebin is set to OFF at the instance level, dropped tables are not retained in the recycle bin.  Similarly, if the tablespace has low free space, older dropped tables are silently purged from the recycle bin.  So it is advisable to query the recycle bin immediately after the problem is identified.  Take care to ensure that a recycle bin is available before running your tests for flashback query on a dropped table or flashback table to before drop.

Purging Objects in the Oracle Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

PURGE TABLE BIN$jsleilx392mk2=293$0;

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

For more information on Oracle recycle bin, see my notes below:

RECYCLE BIN view tips

Oracle Concepts – Dropping Tables – Oracle Recycle Bin

Purge Recyclebin

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em RECYCLE BIN e marcado , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s