SQL and PL/SQL Articles

Killing Sessions In Oracle


Author JP Vijaykumar
Date Jan 27th 2009
Modified Jan 29th 2009

One of my procedure is taking long time to complete. I want to kill my job and
re-run the procedure after incorporating HINTS.

Let us explore the options to kill long running jobs.

From the v$session, I found that my session is active.

select username, osuser,sid,serial#,status from v$session where sid=57;

Performanace Issue


I have worked in a table with one timestamp field namely Actiondate. We have updated the sysdate value of inserting time to that field. It contains around 30 lakhs records.

I want to get the records which are inserted a particular day.

I run the following 2 queries..

select * from history
where trunc(actiondate) > trunc(sysdate)
order by docintno


select * from history
where trunc(actiondate) > '01-dec-2009'
order by docintno

Both are taking too much time to give the results.

SQL and PL/SQL Questions


Both SQL Trace and TKPROF help to find the statistics of an SQL statement which could be used for query optimization.

Start Oracle SQLTRACE:

In Oracle, to start an SQLTRACE for the current session, execute:


Stop Oracle SQLTRACE

In Oracle, to stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Read More

Proc SQL for SAS Programmers


SQL for SAS Programmers - Introduction

What is SQL?

SQL stands for Structured Query Language and was designed for development and maintenance within a Database Management System (DBMS). A DBMS consists of one or more tables of data, typically joined in a hierarchical fashion, and a series of programs for organizing the data.
Typical tasks performed with SQL code include the following:

Killing my Oracle sessions


Many a time, developers ask for procedures to kill there own sessions in the oracle db.
Many of our developers are scattered around the globe in different time zones.
During on-call rotation, developers will be calling me, round the clock, to kill their sessions.

Oracle10g Regular Expression


Oracle10g Regular Expression

Oracle database 10g added a new feature "Regular Expression" enhancement that can be used with SQL and PL/SQL statements.

There are four regular expression functions :-


The functions REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_REPLACE basically extend the functionality of other well known string functions SUBSTR, INSTR and REPLACE.

REGEXP_LIKE is basically an operator that is similar to existing LIKE operator.

Kevin Meade's picture

Foreordain my Transaction to Fail


I saw one of those really interesting pieces of code recently. A guy wanted to run his transaction and make it fail when it was all done. Normally one would put ROLLBACK at the end of the transaction in order to undo a transaction's work. But this guy did not want to do that. He wanted to keep his transaction code unchanged, commit at the end and everything. He had several reasons for this, among them being that he did not have access to all the code he was working with and thus could not put the ROLLBACK where it was needed, and indeed suspected (as we eventually found to be true), that somewhere in the code stream there was a commit being done without his permission thus splitting his transaction in ways he did not intend. So he wanted a way to FOREORDAIN (determine ahead of time) that his transaction would fail no matter even if it went to conclusion without error. For this he came up with a I think a clever hack. Seems to me this might have some use, if I can figure out what that use might be. So here is the cool solution.

Kevin Meade's picture

Creating and Unpacking Delimited Strings in SQL


Recently a friend asked me for this. I see it a lot on OraFaq as a question in the forums so here are the basics of working with delimited strings. I will show the various common methods for creating them and for unpacking them. Its not like I invented this stuff so I will also post some links for additional reading.

Interchange the Values of 2 Columns


You might face a situation where you need to interchange the values of two columns in an Oracle database table. This article will explore ways to achieve this.

Info on PIVOT


Database tables are structured in columns and rows. However, some data lends itself to switching row data as column data for expository purposes. The pivot operation in SQL allows the developer to arrange row data as column fields. For example, if there are two customers who have both visited a store exactly four times, and you want to compare the amount of money spent by each customer on each visit, you can implement the pivot operation.


Subscribe to RSS - SQL & PL/SQL