Oracle instance properties script (ORACLE_HOME, STATE, VERSION ...)

This script will give some important properties of your instance

SELECT status,
       startup_time,
       instance_name,
       version,
       host_name,
       (SELECT SUBSTR (file_spec, 1, INSTR (file_spec, 'lib') - 2)
          FROM dba_libraries
         WHERE library_name = 'DBMS_SUMADV_LIB')
          ORACLE_HOME
  FROM v$instance;

to_char or to_date which one to use

Our developper ask me often which on to use between to_char and to_date, i think they should

• Use to_char to DISPLAY a date datatype 
• Use to_date to STORE a date datatype in a row

to_char converts a date to a varchar and to_date converts a varchar to a date

A way to remove the duplicate records

First you need to query the database to find all duplicate rows, and then you need to run a statement to delete one of each duplicate record that is found

DECLARE
  CURSOR emp_cur IS
  SELECT *
  FROM   employees
  ORDER BY employee_id;
  emp_count         number := 0;
  total_count         number := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('You will see each duplicated employee listed more ');
  DBMS_OUTPUT.PUT_LINE('than once in the list below.  This will allow you to ');
  DBMS_OUTPUT.PUT_LINE('review the list and ensure that indeed...there are more ');
  DBMS_OUTPUT.PUT_LINE('than one of these employee records in the table.');
  DBMS_OUTPUT.PUT_LINE('Duplicated Employees: ');
-- Loop through each player in the table
  FOR emp_rec IN  emp_cur LOOP
-- Select the number of records in the table that have the same ID as the current record
     SELECT count(*)
     INTO emp_count
     FROM employees
     WHERE employee_id = emp_rec.employee_id; 
-- If the count is greater than one then a duplicate has been found, so print it out.
     IF emp_count > 1 THEN
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || emp_rec.first_name ||
                   ' ' || emp_rec.last_name || ' - ' || emp_count);
                  total_count := total_count + 1;
     END IF;

  END LOOP;
END;


Next, you need to delete the duplicated rows that have been found. The following DELETE statement
will ensure that one of the duplicates is removed:

DELETE FROM employees A WHERE ROWID > (
SELECT min(rowid) FROM employees B
WHERE A.employee_id = B.employee_id);

When to use SYS_CONTEXT function

The SYS_CONTEXT function allows you to define a namespace and then place parameters
within it so that they can be retrieved for use at a later time. The general syntax for the use of
SYS_CONTEXT is as follows:

SYS_CONTEXT('namespace','parameter'[,length])

A namespace can be any valid SQL identifier, and it must be created using the CREATE_CONTEXT
statement. The parameter must be a string or evaluate to a string, and it must be set using the
DBMS_SESSION.SET_CONTEXT procedure. The call to SYS_CONTEXT with a valid namespace and parameter will result in the return of a value that has a VARCHAR2 datatype.
The USERENV namespace is automatically available for use because it is a built-in namespace
provided by Oracle. The USERENV namespace contains session information for the current user.

When to use "SELECT FOR UPDATE NOWAIT"

• Verify the data has not changed since you queried it out (preventing lost updates). 
• Lock the row (preventing the UPDATE or DELETE from blocking).

Solution to a blocked SELECT FOR UPDATE

add the NOWAIT clause and it will no longer block. Instead, your application will report back to the end user that the row is already locked

query to view the text of a procedure

SELECT text FROM user_source WHERE name ='&PROC_NAME';