Oracle : Flashback Technology / Recycle Bin

A wonderful feature in Oracle 10g is "Flashback Technology".

What Flashback is?
 Flashback is a feature in Oracle 10g, through which you can recover accidently dropped tables, packages, rows/data. When an object is dropped in Oracle database, then it is stored in a container 'Recycle Bin' (just like Windows).From this Recycle-bin objects can be recovered; unless permanently purged.

What is Recycle Bin (In Oracle Flashback)?
 Recycle Bin is a container in Oracle database, which stores all dropped objects. We can say this is a virtual container, because dropped objects (like tables, indexes, constraints, packages etc) are not moved here, rather it just stores their information & dropped objects are renamed with prefix "BIN$$"

To get information about all dropped objects following query can be used :-

SELECT * FROM RECYCLEBIN;

 It has original object name in 'ORIGINAL_NAME' column, dropped date/time in 'DROPTIME' column, and type of object (like TABLE, INDEX etc) in 'TYPE' column. Following is table structure of RECYCLEBIN:

Column Name	ID	Data Type	Null?
OBJECT_NAME	1	VARCHAR2 (30 Byte)	N
ORIGINAL_NAME	2	VARCHAR2 (32 Byte)	Y
OPERATION	3	VARCHAR2 (9 Byte)	Y
TYPE	4	VARCHAR2 (25 Byte)	Y
TS_NAME	5	VARCHAR2 (30 Byte)	Y
CREATETIME	6	VARCHAR2 (19 Byte)	Y
DROPTIME	7	VARCHAR2 (19 Byte)	Y
DROPSCN	8	NUMBER	Y
PARTITION_NAME	9	VARCHAR2 (32 Byte)	Y
CAN_UNDROP	10	VARCHAR2 (3 Byte)	Y
CAN_PURGE	11	VARCHAR2 (3 Byte)	Y
RELATED	12	NUMBER	N
BASE_OBJECT	13	NUMBER	N
PURGE_OBJECT	14	NUMBER	N
SPACE	15	NUMBER	Y

How to recover objects using Oracle Flashback technology (From Oracle Recycle bin)?

Recovering objects from Oracle Recycle Bin is interesting: Let's take an example:

1. 
   
   Create a table & Drop it:
   --Create a dummy table for testing purpose
   CREATE TABLE testing_oracle_flashback
   AS
   SELECT * 
   FROM user_objects
   WHERE ROWNUM =10;
   /
   
   
   SELECT * FROM testing_oracle_flashback;
   /
   
   
   --Drop the table 
   DROP TABLE testing_oracle_flashback;
   /
   
   
   SELECT * FROM testing_oracle_flashback;
   /
   --There will be error that table or view does not exist.
   
   
   
   
2. 
   
   Check Recycle Bin:
   SELECT *
   FROM   RECYCLEBIN
   WHERE  UPPER(TYPE) = 'TABLE'
   AND    UPPER(original_name) = 'TESTING_ORACLE_FLASHBACK';
   
   
3. 
   
   Recover Table through Oracle Flashback (To Previously Dropped Version – with same name)
   FLASHBACK TABLE testing_oracle_flashback TO BEFORE DROP; 
   
   
4. 
   
   Recover Table through Oracle Flashback (To Previously Dropped Version – With different name)
   FLASHBACK TABLE testing_oracle_flashback TO  BEFORE DROP RENAME TO testing_or_flashback_1;
   
   
5. 
   
   Recover Table through Oracle Flashback (To any given DateTime)
   FLASHBACK TABLE testing_oracle_flashback TO TIMESTAMP ('MAR-10-2010, 02:33:00');
   
   

For further details, please visit :
http://srinisreeramoju.blogspot.com/2010/03/oracle-flashback-technology-recovering.html


For best oracle articles, visit:
http://srinisreeramoju.blogspot.com/

Enjoy ....

Oracle, SQL, PL/SQL, Flashback, FLASH BACK, Oracle Flash back technology, Oracle Flashback Technology, Flashback Technology, Recyclebin, Oracle Recyclebin, RECYCLEBIN, purge table, recover oracle tables, recover oracle objects, recover dropped objects, recover dropped tables, oracle dba, table recovery, oracle constraints recovery

Oracle Empty String IS NULL

(Oracle Empty String ?? ) This is a very common error in Oracle PL/SQL programs. Remember, there is nothing like zero length string or empty string in Oracle. Oracle treats empty string (i.e. '') as NULL.

This type of mistake is common in SQL's WHERE clause, & PL/SQL's IF condition.

e.g.:

SQL's Where clause: WHERE TRIM(color_name) = ''

PL/SQL's where clause: IF TRIM(v_color_name) = '' THEN


As there is NO EMPTY STRING in Oracle, so always writ code dealing with empty string as:

SQL : WHERE TRIM(color_name) IS NULL

PL/SQL : IF TRIM(v_color_name) IS NULL THEN


Always consider following:

• Clause written as variable_name = '' , never RETURNS TRUE (if value of variable_name is empty string). Because in this case we are trying to compare NULL with NULL value.
  
• NVL(variable_name, '') = '' IS never TRUE. In such condition; always use some un-expected value of variable like NVL(v_color_name,'****') = '****'
  
• '' = '' never RETURNS true. In Where clause, of IF condition, this condition is always FALSE.
  
• If data is fetched from Database, then empty string is always returned as NULL value. So take special care when writing IF OR WHERE clause.
  

If you like this small article, be follower of code4coder.com and tell your fellows about this site. If you want to be part of Code4Coder.Com, then do tell us … Comments and suggestions are always welcomed.

Oracle, SQL, PL/SQL, Empty String, NULL, String comparision with NULL, NVL, NVL String, '', empty string, oracle empty string comparison, oracle string comparison, code4coder, code4coder.com, oracle, best performance, optimized code, common sql mistakes

C# - Extension Methods

Note: There will be more stuff on MSMQ specially w.r.t Windows Communication Foundation (WCF) soon, Just posting on one of new features (although it is not new any more J ), i.e. Extension Methods.

 
Let me ask:
Can we extend a "sealed" class? You may answer "No" .. But what if we really want to extend a sealed class? .. What if I want to add functionality in "Integer" class?. Say always wants a formatted output of integer…. E.g.:
int a;
You have option .toString(), but you do not want to provide formatting string every time. You want to add some method in "int" which gives you formatted output. Or may be you are using an enumeration
public enum colorEnum {Red,Blue,Green}
What you want is that whenever user selects any value from this enumeration, program should fetch something from Database and provide that value, say for Red you want description from DB as "Red color is dangerous", when you call colorEnum.Red.GetDescription();
Certainly you can't add GetDescription() method in Enumeration, what could be possible solution ?? A helper class and a method (may be static) and every time call that class's method to get description of Red color,
A very good solutions to such type of problems is : Extension method. What you have to do is create helper class in same way as you used to create, but helper class's methods will be linked to your class (or any 3^rd party provided class.. e.g. Int32)

 
Following example is pretty much self explanatory (using my favorite C# language for describing extension methods—No offense to VB programmers, but VB seems really clumsy, although you can do everything in VB!!......we are going out of track !! J ….back to Extension methods example : )
Sealed class is :
public sealed class MySealedClass 
{
   public string FirstName { get;set;}
   public string LastName { get;set;}
}

 
Now suppose we want extend this class and a method in it GetFullName(), which may give us concatenated First & Last Names.
For this we will create a helper class, you can give any name to this helper class, but you should consider following points while creating helper class

• Helper Class should be static
• Helper method should be static
• Argument passed to helper method will decide its class J
• In argument list, before argument write "this".. which will tell compiler that this is helper method of some class:
• Namespaces of (sealed class/base class) and helper class should be added where you want to use helper method:

So helper class may look like following:

public static class MyHelperClass
{
   public static string GetFullName(this MySealedClass s)
   {
       return s.FirstName + " " + s.LastName;
   }
}

 
Now MySealedClass will have virtually one more method, i.e. GetFullName() … This can be tested through following test program:-
public class Program
{
   static void Main(string[] args)
   {
      MySealedClass ms = new MySealedClass();
      ms.FirstName = "Code4Coder";
      ms.LastName = "C# Programmer";

      Console.WriteLine(ms.GetFullName());
   }
}
  
Comments/Suggestions for improvements will be highly appreciated…

C#, Extension Methods, Extending sealed class,New features of C#,Extension Methods, VB,static,Helper Class,public static class,C# class,C# features, new C# features

C#, Extension Methods, Extending sealed class,New features of C#,Extension Methods, VB,static,Helper Class,public static class,C# class,C# features, new C# features