Archive for

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

Flashback DROP

Flashback DROP 

• The Flashback drop command applies only to tables, but all associated objects will also be recovered,except for foreign key constraints.
• In earlier releases of the Oracle Database, when a table was dropped all references to it were removed from the data dictionary.
• If it were possible to see the source code for the old DROP TaBLE command, you would see that it was actually a series of DELETE commands against the various tables in the SYS schema that define a table and its space usage, followed by commit. 
• There was no actual clearing of data from disk, but the space used by a dropped table was flagged as being unused and thus available for reuse.
• Even though the blocks of the table were still there, there was no possible way of getting to them because the data dictionary would have no record of which blocks were part of the dropped table.the only way  to recover a dropped table was to do a point in time recovery, restoring a version of the database from before the drop when the data dictionary still knew about the table.
• In release 10g of the Oracle database , the implementation of the DROP TABLE command has been completely changed.
• tables are no longer dropped at all, they are renamed.
• You can see that a table, EMP occupies one extent of 64KB, which starts in the 25the of file 4.After the rename to NEW_NAME, the storage is exactly the same; therefore the table is the same,
  Querying the view DBA_OBJECTS would show that the table’s object number had not changed either.
• The release 10g implementation of the DROP TABLE command has been mapped internally onto a RENAME command, which affects the table all its associated indexes, triggers and constaints with the exception
  of foreign key constraints, which are dropped.
• If they were maintained, even with a different name, then DML on the non dropped parent table would be constrained by the contents of a dropped table, which would be absurd.
• The dropped objects can be queried by looking at the recycle bin to obtain their names.
• There is a recycle bin for each other, visible in the USER_RECYCLEBIN data dictionary view or DBA_RECYCLEBIN.
• The space occupied by the recycle bin objects will be reused automatically when a tablespace comes under space pressure(after whic
  time the objects cannot be recovered), or you can manually force Oracle to really drop the objects with the PURGE command.

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4                  25      65536
 

SQL> alter table EMP rename to EMPLOYEE;
alter table EMP rename to EMPLOYEE
*
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> conn scott/tiger
Connected.
SQL> alter table EMP rename to EMPLOYEE;
 

Table altered.

SQL> conn sys/sys@riyadh as sysdba
Connected.
SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

no rows selected
 

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMPLOYEE’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4               25             65536
 

Flashback DROP Example :
 

Step 1
 

Create user
 

SQL> show user;
USER is “SYS”
SQL> create user dropper identified by dropper;
 

User created.
 

SQL> grant connect,resource to dropper;
 

Grant succeeded.
 

Step 2
Create a table, with an index and a constraint and insert a row.
 

SQL> connect dropper/dropper@riyadh;
Connected.
SQL> create table test(name varchar2(10));  

Table created.  

SQL> create index test_idx on test(name);  

Index created.  

SQL> alter table test add(constraint name_u unique(name));  

Table altered. 
SQL> insert into test values(‘Azar’);
 

1 row created.
 

SQL> commit;
 

Commit complete.
 

SQL> select * from test;
 

NAME
———-
Azar
 

Step 3
 
Confirm the contents of your schema
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

TEST_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

Step 4 

Drop the table
 

SQL> drop table test;
 

Table dropped.
 

Step 5
 
Re-run the queries from step 3.Note that the objects and the constaints do still exist but that they now
have system generated names, all prefixed with BIN$.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
BIN$Zl4wALngQtKfJL+9mgzJgg==$0
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U BIN$Zl4wALngQtKfJL+9mgzJgg==$0
 

Step 6 

Query your recycle bin to see the mapping of the original name to the recyclebin names.
 

SQL> select object_name,original_name,type from user_recyclebin;
 

OBJECT_NAME                    ORIGINAL_NAME
—————————— ——————————–
TYPE
————————-
BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0 TEST_IDX
INDEX
 

BIN$Zl4wALngQtKfJL+9mgzJgg==$0 TEST
TABLE
 

Step 7
 

Query the recycle bin but that you cannot do DML against it.
 

SQL> select * from “BIN$Zl4wALngQtKfJL+9mgzJgg==$0”;
 

NAME
———-
Azar
 

SQL> insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’);
insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
 

Step 8
 

Recover the table with Flashback Drop 
SQL> flashback table test to before drop;
 

Flashback complete.
 

Step 9
 

Rerun the step 3.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U TEST
 

Step 10
Rename the index and constraint to the original names.

SQL> alter index “BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0” rename to name_idx;
 

Index altered.
 

SQL> alter table test rename constraint “BIN$BBKVasdjRsqyYqne8rM8VQ==$0” to name_u;
 

Table altered.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

NAME_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

SQL> select * from test;
 

NAME
———-
Azar

Configuring Flashback Database

C:\Documents and Settings\mazar>set oracle_sid=riyadh

C:\Documents and Settings\mazar>sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 18 11:17:37 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys/sys as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

Step 1

Ensure that the database in archivelog mode.

Archivelog mode is a prerewuistite for enabling flashback database.

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

Step 2

Set up a flash recovery area.

The flash recovery area is the location for the flashback logs. YOu have no control over them other than setting the flah recovery area directory and limiting its size. It is controlled with two instance paramenters: DB_RECOVERY_FILE_DEST specifies the destination directory;
DB_RECOVERY_FILE_DEST_SIZE restricts that the flash recovery area is used for purposes other than flashback logs and it will need to be sized appropriately.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      D:\flashrecovery
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

Step 3

Set the lifespan for the flashback retention target.

This setting is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which specifies a time in minutes (the default is one day).

The flashback log space is reused in a circular fashion, older data being overwritten by newer data.This parameter instructs Oracle to keep flashback data for a certan cumber of minutes before overwriting it

SQL> alter system set db_flashback_retention_target=240;

System altered.

It is only a target(four hour in this example), and if the flash recovery area is undersized, Oracle may not be able to keep it.but is principle, you should be able to flash back to any time within this target.

Step 4

Cleanly Shutdown the database and mount

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  247463936 bytes
Fixed Size                  1248332 bytes
Variable Size             104858548 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.

Step 5

Enable flashback logging, while in mount mode.
SQL> alter database flashback on;

Database altered.

This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.

Step 6

Open the database.

SQL> alter database open;

Database altered.

Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.

Step 7

Check Whether Flashback On or Off.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES