How To – Find the locking object and release object lock

Many time we faced the performance issue because of Locks on rows and it cause the delay to finishing the other transactions. So with the help of following steps you will able to find the locking objects and can release the object locks in Oracle Database. Let us create the example to understand clearly Terminal – 1 Step -1:  Here we have execute the Update Statement in the SCOTT Schema without Commit Terminal – 2 Step -2: Now we try

» Read more

Toad – SQLNET Editor and TNS Names Editor Disabled

While trying to connect to a Database using Toad with TNS, Even the TNS entry is created in tnsnames.ora file under Oracle Home\NETWORK\ADMIN Issue 1: SQLNET Editor and TNS Names Editor Disabled as shown in below snapshot  Issue 2: Not able to find the TNS Name in the dropdown as shown in below snapshot Cause: TNS entry missing in the environmental variables Resolution:  Go to environmental variables and create a new variable Name as “TNS_ADMIN” and set the value as  Oracle Home\NETWORK\ADMIN

» Read more

Oracle – Create tablespace and User / Schema

Creating a tablespace and user is a very common task after creating an oracle database. Before going further must read the post Schema vs. User In this post we will see how to create tablespace and user with example Tablespace Each tablespace in an Oracle database consists of one or more files called data files. A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files. Script 1: Create Tablespace CREATE TABLESPACE "test01" LOGGING DATAFILE

» Read more

Oracle Client Installation

Installation Steps: Extract the OracleClientODBC-32Bit or OracleClientODBC-64Bit zipped folder according to the Machine (32bits or 64bits). Now set 4 environment variables at client machine as System Variable. My Computer Properties >> Advanced >> Environment Variables PATH: Set the path. Put ‘;’ at the end of existing path and give the path of folder where the files are extracted. LD_LIBRARY_PATH: New environment variable is created with the path of same folder. ORACLE_HOME:New environment variable if not present with same path. TNS_ADMIN:

» Read more

Oracle – Import data from CSV file using SQL Loader

In this post we will see how we can transfer the data from CSV to oracle database using SQL Loader. SQL Loader is a bulk loader utility used for moving data from external files into the Oracle database. One can use SQL Loader utility to transfer the data for high performance. Syntax: sqlldr username@server/password control=control_file.ctl sqlldr username/password@server control= control_file.ctl Exmaple: Let‘s take a CSV file named as test_data.csv First we need to create a table where we want to transfer

» Read more

Oracle – Table Row count for all tables

Here is a simple SQL script with the help we can find the Table Row count for all tables. Yes we can find the row count using select count(*) for each table. This will be useful if you large number of tables are there in your database. Example Script: — Create a table CREATE TABLE ROW_COUNT_ALL_TABLE ( TABLE_NAME VARCHAR2(100), ROW_COUNT VARCHAR2(100) ) — PL-SQL Block to count Row Count for all tables as you specify DECLARE V_ROW_COUNT VARCHAR2(100); V_SQL_STRING VARCHAR2(1000);

» Read more

Oracle – ORA-01653 – unable to extend table in tablespace – Fix

Error: ORA-01653: unable to extend table in tablespace Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated. Note: Change the table space name for that you need the information in the below Scripts (Script: 1 & 2) Solution: Script: 1 Execute the below query to find the Used Size, Free Size, Total Size of a table space select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace – tu.totalusedspace) "Free MB", df.totalspace

» Read more

Oracle – Auto Generate Insert Statement with Toad

This is very quick way to generate the Insert Statement for all of the data in your table. Let us take an example; we have a table as shown below… Right click on the Result Grid and go to Option à Export Dataset Select the Export Format as “Insert Statement” Select the Output file location and name You can also change the Schema (if required) Required Out file is ready with Insert statement as If you have any comment/suggestion please

» Read more

Oracle – Get IP Address and Host Name

With UTL_INADDR The UTL_INADDR package provides a PL/SQL procedure to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts. Example select UTL_INADDR.GET_HOST_NAME Host_Name, UTL_INADDR.GET_HOST_ADDRESS IP_Address from dual ; With SYS_CONTEXT SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements Example select SYS_CONTEXT('USERENV', 'IP_ADDRESS') IPAddress from dual; select SYS_CONTEXT('USERENV', 'HOST') HostName from dual; For more detail one can

» Read more

SQL – Char vs. Varchar

Memory allocation is static for char data type variables but in case of varchar memory allocation is dynamic. This is the basic difference between char and varchar. Example create table test1 ( name1 char(10), name2 varchar(10) ); Insert into test1 values ('Varinder','Varinder'); select * from test1; I hope this will help.

» Read more

Oracle – ORA-01007: variable not in select list

Error: ORA-01007: variable not in select list Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error. Action:

» Read more
1 2 3