Skip to content

Archive for

Jan 25

Managing Recyclebin

Recycle Bin :

  1. When a tablespace is completely filled up with recycle bin data such
    that the datafiles have to extend to make room for more data, the tablespace is said to be under “space pressure”.
  2. Objects are automatically purged from the recycle bin in a first in  first out manner.
  3. the dependent objects(such as indexes) are removed before a table is removed.
  4. Space pressure can occur with user quotes as defined for a particular tablespace.
  5. The tablespace may ahve enough free space, but the user may be running out of his or her allotted portion of it.
  6. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.
  7. If your datafiles have the AUTOEXTEND attribute enabled, Oracle will not in fact autoextend  the datafiles until all space occupied by dropped objects has been reassigned.it will overwrite the recycle bin in preference to increasing the datafile size.

Example :-

  1. A 1MB tablesapce,called SMALL, has beeen completely filled by one table,called LARGE.
  2. The space usage alerts will have fired, and querying DBA_FREE_SPACE reports no space available. Then the table is dropped.
  3. The alert will clear itself and DBA_FREE_SPACE will report that the whole tablespace is empty ,but
    querying the recycle bin , or indeed DBA_SEGMENTS will tell the truth.

Sql>select sum(bytes) from dba_free_space where tablespace_name=’SMALL’;
sum(bytes)
———-

sql>select segment_name,bytes from dba_segments where tablespace_name=’SMALL’;
SEGMENT_NAME             BYTES
————-           ——
LARGE                   983040

sql>drop table large;
Table dropped.

sql>select sum(bytes) from dba_free_space where tablespace_name=’SMALL’;
sum(bytes)
———-
983040

sql>select segment_name,bytes from dba_segments where tablespace_name=’SMALL’;
SEGMENT_NAME             BYTES
————-           ——
BIN$                    983040

  1. This apparently contradictory state is resolved by Oracle reusing space as it needs it.
  2. If space is required in the tablespace for a new segment, then it will be taken and it will no longer be possible to retain the dropped table.
  3. If there are many deleted objects in the recycle bin, Oracle will overwrite the object that had been in there for the longest time.THIS FIFO or first in first out algorithm assumes that  ojects dropped recently are most likely candidates for a flashback.

—-

Each user has his own recycle bin

SQL> conn scott/tiger;
Connected.
SQL> show user;
USER is “SCOTT”
SQL> create table trial as select * from dept;

Table created.

SQL> drop table trial;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1vHeLSXYTROOaynkYZGpCg==$0 TABLE        2010-01-25:11:29:00

SQL> desc dba_recyclebin;
ERROR:
ORA-04043: object “SYS”.”DBA_RECYCLEBIN” does not exist

( Sys user only can access this above view).

SQL> select original_name,type,ts_name,droptime,can_undrop,space from user_recyc
lebin;

ORIGINAL_NAME                    TYPE
——————————– ————————-
TS_NAME                        DROPTIME            CAN      SPACE
—————————— ——————- — ———-
TRIAL                            TABLE
USERS                          2010-01-25:11:29:00 YES          8
SQL>

Drop the table and do not move it to the recycle bin

SQL> create table test as select * from dept;

Table created.

SQL> drop table test purge;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1vHeLSXYTROOaynkYZGpCg==$0 TABLE        2010-01-25:11:29:00

Remove the table from the recycle bin.If there are several objects with the same Original name, the oldest is removed.

SQL> drop table test;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TEST             BIN$a2qVloT+Q5q1roJlQz+ZKw==$0 TABLE        2010-01-25:11:37:21

TRIAL            BIN$1r9D5xVUQtu0IBhxh8q6kA==$0 TABLE        2010-01-25:11:36:44

SQL> purge table test;

Table purged.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1r9D5xVUQtu0IBhxh8q6kA==$0 TABLE        2010-01-25:11:36:44

Remove an index from the recycle bin

sql>purge index <index_name>;

Remove all dropped objects from the tablespace

SQL> purge tablespace users;

Tablespace purged.

SQL> show recyclebin;

Remove all dropped objects belonging to one user from the tablespace

sql>purge tablespace <tablespace_name> user <user_name>;

Remove all your dropped objects

sql>purge user_recyclebin

Remove all dropped objects.You will need DBA privileges to execte this.

sql>purge dba_recyclebin