Tag Archives: Oracle Tips and Tricks

How to use LPAD and RPAD Function

LPAD and RPAD function

If you want to pad any character on the left side or right side of a string, you can use LPAD and RPAD functions. In this article you will see how to use LPAD and RPAD functions in Oracle. LPAD function: The LPAD function is used to pad the left side of a string with

Fixed – ORA-01033: ORACLE initialization or shutdown in progress

Ora-011033 Oracle error

When you login into Oracle Database and it throws following error message: ORA-01033: ORACLE initialization or shutdown in progress, then follow the below mentioned steps to solve this issue. The following video is a practical demonstration to resolve ORA-01033: ORACLE initialization or shutdown in progress error. As a DBA, you must know that Shutdown, Shutdown

Fixed – ORA-12560: TNS: protocol adapter error

Ora-12560 Error

When you login into Oracle Database and it throws an error message: Ora-12560: TNS: protocol adapter error Then follow the below mentioned steps to solve this issue. First of all, make sure that you have set the correct ORACLE_SID: C:\>sqlplus system/manager@xe SQL*Plus: Release 11.2.0.2.0 Production ERROR: ORA-12560: TNS:protocol adapter error Enter user-name: C:\>set ORACLE_SID=xe C:\>sqlplus

Fixed – ORA-00844: Parameter not taking MEMORY_TARGET into account

Ora-00844 Error

When you start your database and it throws following error: ORA-01078: failure in processing system parameters ORA-00844: Parameter not taking MEMORY_TARGET into account ORA-00851: SGA_MAX_SIZE 9445460234 cannot be set to more than MEMORY_TARGET 4547687648. Cause: The SGA_MAX_SIZE parameter is larger than MEMORY_TARGET parameter. May be you forgot to set the MEMORY_TARGET in the spfile after

How to Enable/Disable Archive Log Mode in Oracle 10g/11g

Enable Archive Log Mode

It’s a prime responsibility of Oracle DBA to keep database up 24×7 but there may be some chances when your database gets crash. If your database gets crash and you want to recover it using archive log files then your database must be in archive mode. If your database is in archive made then you

How to Create Index in Oracle SQL with Example

Create index in Oracle SQL

What is an Index? Index is created on existing tables to retrieve data more quickly. This is a performance tuning method for faster retrieval of records from a table. By default oracle creates B-tree indexes when you create an index on a table. If you want to create an index on a table you can

NVL, DECODE, NVL2, COALESCE-NULL Functions in Oracle

This article will provide you a summary of the NULL functions available for handling null values in Oracle. All examples are described with the use of following table. CREATE TABLE TEST_NULL_VALUE ( test_id NUMBER(4), value1 VARCHAR2(12), value2 VARCHAR2(12), value3 VARCHAR2(12), value4 VARCHAR2(12), value5 VARCHAR2(12) ); INSERT INTO TEST_NULL_VALUE values (100, 'FIRST','SECOND','THIRD','FORTH','FIFTH'); INSERT INTO TEST_NULL_VALUE values

Difference in RANK, DENSE_RANK, FIRST and LAST Analytic Functions

Rank Dense_Rank

This article gives you an overview of the RANK, DENSE_RANK, FIRST and LAST analytic functions. First create a table STUDENT and insert some sample records to perform the task. CREATE TABLE STUDENT ( STUDENT_ID NUMBER(4), DEPT VARCHAR2(20), SCHOLARSHIP NUMBER(5)); INSERT INTO STUDENT VALUES(2001,'CS',800); INSERT INTO STUDENT VALUES(2002,'CS',950); INSERT INTO STUDENT VALUES(2003,'CS',1100); INSERT INTO STUDENT VALUES(2004,'EC',1250);

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