create a function for the DML logging   create or replace function scn_to_timestamp_safe(p integer) return timestamp is  e_too_old_scn exception;  pragma exception_init(e_too_old_scn,-8181);begin  return      case        when p is not null then scn_to_timestamp(p)       else null     end;exception   when e_too_old_scn then    …

As a  DBA we need to make sure that all the tables in your database are have their uniquesness so that the rows are not duplicate and we avoid the redundant data in our databases.  below is the simple sql that…

Easiest way to switch between schemas just with a click of button in sqldeveloper 3.2 or lower versions.This doesnt work with sqldev 4 or higher General :  The Schema Select extension for Oracle SQL Developer provides a convenient drop-down list…

lets assume that there was a table ABC that was already existing in the database and you want to add an additional column with unique primary key values. sql to create table abc :   CREATE TABLE “ABC”    (   …

Performance – AWR Display a list of snapshots Produce a report To see the snapshot interval and retention period Change the snapshot interval Change the retention period Manually take a snapshot List all baselines Create a baseline Remove a baseline…

CONN / AS SYSDBAALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK; CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;GRANT READ, WRITE ON DIRECTORY test_dir TO scott; Note. Data Pump is a server-based technology, so it typically deals with directory objects pointing…

1. Scheduling a Job For a Specific Time The basic usage of cron is to execute a job in a specific time as shown below. This will execute the Full backup shell script (full-backup) on 10th June 08:30 AM. Please…

Oracle Database Commands and Queries: 1.To view all the table from dictionary :SQL> select table_name from dictionary; 2.To identify the database name :SQL> select name from v$database; 3.To identify the instance name :SQL> select instance from v$thread; 4.To know the…

This script can be used to drop all the objects in your schema,very useful when you want a fresh schema and start from scratch.no need to delete objects individually or recreate user. —————————————————————————–—–    plsql script to drop all the…