Tag Archives: Oracle Tips and Tricks

SQL Tips: LAG and LEAD Analytic Functions

LAG and LEAD functions

LAG and LEAD functions can be used in Oracle PL/SQL and apply on Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i versions. Let’s understand the difference between LAG and LEAD analytical functions in Oracle. First create a table called Student and insert some records in the table. CREATE TABLE STUDENT ( STUDENT_ID

Unix Shell Script to Execute SQL Query in Oracle

unix shell script

There are many ways to run SQL query using Unix shell script. Here we have posted a shell script that will accept oracle user credentials (username & password) and SQL query to execute and display the output of SQL. You can run the script automatically in the background or use the cron job to generate

How to Multiplex Redo Log Files in Oracle

multiplex redo log files

As a DBA, it’s your responsibility to multiplex redo log files just like multiplexing control files to protect your organization data loss due to media failure or redo log file corruption. Follow below steps to multiplex redo log files. Step-1: Connect to a database Connect to SQL*Plus as the system/manager@yourdb user. SQL> CONNECT system/manager@testdb AS

How to Multiplex Control Files in Oracle

multiplex control file

As a DBA, you know that control files play a very important role in oracle database. If the control files are corrupt, can you start your database? No, you can’t do it. So what should be done to avoid such type of corruption? As a DBA, it’s your responsibility to multiplex control files to protect

How to Escape Special Characters in Oracle SQL queries

escape special characters in oracle sql

Whether you are working as PL/SQL developer or ETL developer or Data Warehousing Developer or using SQL and PL/SQL, you must know about how to escape special characters in SQL queries. In the article, you will learn: 1) How to escape ampersand (&) characters in SQL? 2) How to escape wildcard characters in SQL? 3)

Transpose Rows to Columns and Columns to Rows in Oracle

transpose rows to columns

This is the most popular question asked in an interview as well as over the internet. How to transpose rows to columns in SQL? How to transpose columns to rows in SQL? How to create PIVOT table in SQL? How to Create a Matrix in SQL? We can Transpose Rows to Columns and Columns to

Oracle 11g Security Features: ORA-28001: The Password Has Expired

Oracle 11g Security

My friend called me today and told about Ora-28001: The password has expired. He told how it is possible.  How password can be expired in Oracle? He was not aware about this security feature in Oracle 11g. He was treating it as an error message while it is a security feature of Oracle 11g. Many

Difference Between DDL, DML and DCL Commands


This is the most important question asked in an interview. How to differentiate DDL, DML and DCL commands? What are DDL commands? DDL – Data Definition Language commands are used to define the structure or schema of a database. There are many DDL commands like below: CREATE: This command is used to create database objects

Siteground SALE
60% OFF SiteGround WordPress Hosting – $3.95/month (Exclusive Offer !!!)