Recycle Bin in Database

Oracle introduced in database 10g new feature called "Recycle Bin" to store the dropped database objects.
If any table is dropped then any associated object to this table such as indexes, constraints and any other dependent object are renamed with a prefix of bin$$.

Use of Recycle Bin

If user drop an important object accidentally, and he want to get it again.
With Recycle Bin feature user can easily restore the dropped objects.

Enable and Disable Recycle Bin

You can use the below query to distinguish which Recycle Bin is enabled or no

 SELECT Value FROM V$parameter WHERE Name = 'recyclebin';  

It will return on or off
on means that Recycle Bin is enabled and off is disabled.

You can enable and disable Recycle Bin per session and system, there fore you can use the below scripts to enable and disable Recycle Bin per session or system.

 ALTER SYSTEM SET recyclebin = ON;  
   
 ALTER SESSION SET recyclebin = ON;  
   
 ALTER SYSTEM SET recyclebin = OFF;  
   
 ALTER SESSION SET recyclebin = OFF;  

Oracle Database SQL Expert Certification

Hi all,

I am now an Oracle Database SQL Expert Certified  .

I wrote 1Z0-047 today and passed it with 100% score.

Thanks

Memory Management in PLSQL

While working with PLSQL we should take care of memory usage, So I will provide some tips to avoid memory overhead in PLSQL code.

1- Variables Length
You might  allocate large VARCHAR2 variables when you are not sure how big an expression result will be.
You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000.

PLSQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. 
When you specify a size of more than 4000 characters for the VARCHAR2 variable, PLSQL waits until you assign the variable, then only allocates as much storage as needed.

2- Subprograms into Packages
When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool.
Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package ages out of memory, and you reference it again, it is reloaded.

You can improve performance by sizing the shared memory pool correctly. Make it large enough to hold all frequently used packages, but not so large that memory is wasted.

3- Pin Packages in the Shared Memory Pool
You can pin frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it does not age out; it remains in memory no matter how full the pool gets or how frequently you access the package.

4- Use Compiler Warnings
The PLSQL compiler issues warnings about things that do not make a program incorrect, but might lead to poor performance. If you receive such a warning, and the performance of this code is important, follow the suggestions in the warning and make the code more efficient.

Thanks
Mahmoud A. El-Sayed

WITH Clause in SELECT statement

I will explain best practice and benefits of using WITH clause in Oracle Database.

WITH is used with SELECT query to collect the data set first and then query against collected data set in WITH clause, there for the query doesn't start with SELECT, it will start with WITH clause first.

Syntax of WITH clause

WITH with_clause_name AS ( SELECT STATEMENT)

SELECT *

  FROM with_clause_name;

Example

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

From previous example the WITH clause allow you to give name to SELECT statement and then later select from this named SELECT statement.

Generate list of dates and times

I want to create query returns list of dates and time for example

1-Jan-2012

1-Jan-2012 12:30:00 AM

1-Jan-2012 13:00:00 AM

1-Jan-2012 13:30:00 AM

1-Jan-2012 14:00:00 AM

.................

.................

1-Jan-2012 11:00:00 PM

1-Jan-2012 11:30:00 PM

To execute the previous requirement I can use the below query

   SELECT TO_DATE ('1-1-2012', 'DD-MM-RRRR') + (LEVEL - 1) / 48 DATE_TIME  
    FROM DUAL  
 CONNECT BY LEVEL <= 48;  

Execute Code in Page Load in ADF

I want to execute a piece of code in page load, For implementing this request I can do it using two solutions.

1- PagePhaseListener Interface

PagePhaseListener allows to write global code which executes in every page at my application.
I will create a class implements oracle.adf.controller.v2.lifecycle.PagePhaseListener.PagePhaseListener Interface and overide afterPhase method and then add this class as Phase Listener in  /META-INF/adf-settings.xml

2- BeforePhase Property of View

I will bind this property to a method in backing bean which contains the code I want to execute.

Find Unused Columns in Oracle Database

Sometimes during development of new systems, you may add new columns to tables and then you don't use it and forget dropping it.

So you want to know which these columns to drop.
Usually unused columns have NULL value, So I created a function to return array of column names in my schema have NULL value.

I created GET_NULL_COLUMNS function returns VARRAY of varchar2
It has only one parameter (IN_TABLE_NAME)
If I pass a value for IN_TABLE_NAME then it will return NULL columns in this table only, otherwise it will return NULL columns in entire schema.