Create a New PL/SQL Library
The steps in this section will show you how to create a new PL/SQL library, then create a function that will live in this library. To create the library: Launch…
Read MoreHow to fix INVALID status for RAC component in dba_registry
Before upgrade or after upgrade, database may show INVALID status for component Real Application Clusters in dba_registry as below . SQL> select comp_name,version,status from dba_registry; … Oracle Real Application…
Read MoreGenerate DDL By comparing 2 Schema.
Table of Contents Overview Technologies and Tools Used Use Case Architecture Screen Shot 1. Overview This document will be helpful to compares Prod and Dev schema and generates scripts for missing…
Read MoreJob DDL
Creates the DDL for the specified job:- SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY',…
Read MoreDDL of Foreign key
Script to create foreign key DDL on all tables. SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param…
Read MoreThe SQLAccess Advisor
DBMS_ADVISOR :- 1. Gather a number of SQL statements that will form the tuning workload. 2. Check that the user running DBMS_ADVISOR has the ADVISOR privilege, and has SELECT access…
Read MoreConverting rows to columns using Oracle Pivot clause
The Oracle introduced the PIVOT clause from oracle 11g. This makes converting rows to column easy. The value in the new columns must be the result of an aggregate function…
Read MoreFind the Invalid Email Address using Oracle SQL and PL/SQL
Objective: To find the validity of the email address using Oracle Pl/sql and to find the invalid email address from a object like table using Oracle Sql. Using Pl/Sql: DECLARE…
Read MoreTo Find IP Address of the User who login into Apex Application
This query will help to sql, Plsql and Apex Developer to find the user IP Address. Normally we tracking only page id, username and time of the user for login…
Read MoreQuery to Check locks on a table
Select object_id, session_id, oracle_username, os_user_name, Process, locked_mode From sys.v_$locked_object; Select a.object_name, b.oracle_username From all_objects a, v$locked_object b Where a.object_id = b.object_id And a.object_name like ‘po%’;
Read More