Search every field of every table for a particular value in oracle

In case you want to search a string or any other number in all tables inside a schema. Then there is a free tool available for it. I have tried many scripts and PL/SQL procedures to do this, but by far the best is the freeware tool DBSEARCH. It can be downloaded free from the … Continue reading Search every field of every table for a particular value in oracle

Create a copy of an Oracle table without copying the data?

Do a "create table" as command without selecting any rows create table abc_new as select * from abc where 1=0;

Flashback Table to Timestamp 11gR2

Flashback Technology provided by Oracle is one of the most useful in a production environment. It is a life saver. You can even rollback a query to a point in time without requiring traditional database restore. I will demonstrate below how to do flashback a table to a point in time. But first you have … Continue reading Flashback Table to Timestamp 11gR2

Proof that count(*) is faster than count(1)

The final answer to the age old debate of count(*) vs count(1).Absolute proof that count(*) is faster than count(1). sql->alter session set events '10046 trace name context forever, level 12'; Session altered. Elapsed: 00:00:00.10 sql->select count(1) from dual; COUNT(1) ---------- 1 1 row selected. Elapsed: 00:00:00.40 sql->select count(*) from dual; COUNT(*) ---------- 1 1 row … Continue reading Proof that count(*) is faster than count(1)

Method to delete a huge table with more than 100 million rows in Oracle 10g.

Oracle Database philosophy is Data Recover-ability. This is the reason if you have a huge table with more than 100 million records and indexes on it. Cleaning it up can be a mess. Truncate  is required when you have to empty the entire table. Ofcourse that is much quicker. But sometimes you need a conditional … Continue reading Method to delete a huge table with more than 100 million rows in Oracle 10g.