How to compare two tables of data????
<code>Hi Tom, I have two tables of values(dept1 and dept2). How do I compare all the data in these two tables??? It will return true if both tables contain the same data & false for otherwise... Another thing is that I do not know how to use CREA...

Steps to follow when migrating from a lower to higher version
Tom, Could you just point out the steps or thing to be considered when migrating from a lower version to higher version? Just an example, when migrating from 9i to 11g. I have seen many articles, but none of them say very clearly what to do step b...

Inserts with APPEND Hint.
<code>insert /*+ append */ into t select rownum,mod(rownum,5) from all_objects where rownum <=1000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- -----...

COMPATIBLE PARAMETER after upgrade from 9208 to 11.1.0
If I upgrade from 9208 to 11.1.6 Compatible parameter changes from its initial value of 9.2.0.0.0 to 10.1.0. 1) Under what circumstances is it advisable to leave this parameter value to 10.1.0. or to upgrade it to 11.1.0. I do want to use all ...

DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS
<code>Hi Tom What's the difference between connections, sessions and processes? I read a note from Metalink about the difference but I simply dont get it! May you give a brief explanation? Thank you </code>

User-Defined Aggregate with More than One Value Parameter
<b>I would like to create user-defined aggregate/window functions that allow multiple parameters</b> such as <u>weighted-averages</u>, <u>weighted-standard-deviations</u>, and various other statistical calculations for statistical process control cha...

Investigate Wait events & Poor response times using Statspack report.
<code>Hi Tom, Here is Statspack report that was obtained when the end-users complained about the poor reponse times in the system. Please advice on the following: (1) The Top wait event i.e. Latch Free. (2) Latches Activity. During this peri...

questions on LONGS
<code>Can you perform string functions on the datatype LONG? </code>

Determining which query caused deadlock.
<code>Our application is causing deadlocks. After doing research I'm almost certain it is due to not having an index on a couple of foreign key columns. *** 2002-11-04 09:17:47.998 *** SESSION ID:(12.346) 2002-11-04 09:17:47.960 DEADLOCK DETE...

Placement of DBAs within an organization.
At the company I have just joined the database group happens to fall under the applications department. This is in stark contrast to every other place I have been where the DBAs were a part of the infrastructure group. I feel that an important streng...

SQL Tuning
<code>I am new in tuning sql statements. Can u give a methodology of tuning the sql statements. </code>

SQL Query
<code>Tom: I have a table that initially stores information about items in a warehouse stored in different bins. Table look like this where manual inventories are done every 3 months. Effective date is the sysdate when record is inserted. Inv...

How to Update millions or records in a table
<code>Good Morning Tom. I need your expertise in this regard. I got a table which contains millions or records. I want to update and commit every time for so many records ( say 10,000 records). I dont want to do in one stroke as I may end up i...

CURSOR
<code>Hi Tom, I want to declare multiple cursors based on the values passed through a procedure ie, only where conditions of the cursors will change. The body of the procedure is same for all the cursors. Pls. suggegst a solution. Than...

Analytics question
<code> I have a table from a 3rd party application that is used to track an order through the various manufacturing operations. A subset of the information looks like this: ORDER OPN STATION CLOSE_DATE ----- --- ------- ----------...

What don't you like about Oracle?
<code>Hi Tom, I was wondering if there are some thing you don't like about Oracle? I personally wish more things came setup, out of the box such as autotrace, intermedia text, scott schema etc. Any thoughts on this? -Alex </code>

SQL Query
<code>Hi Tom, I have a table like this create table temp1 (f_name varchar2(100), f_date date, f_amount integer ); Records in this table will be like this: Ajay 15-JUL-02 500 Bhavani 15-JUL-02 700 Chakr...

extracting data to a flat file.
<code>How can i extract data from flat file to oracle table with plsql ? </code>

Pivot Query
<code>Please state an example of pivot query. I know that in cardinality I've 2 columns, so how can I show them in columnar form and I know the cardinality is 4. Please also illustrate method of Varray and Nested Tables. Regards Puneet </code...

Comparison between Oracle and Others
<code>Hi Tom, My question is something different. As an Oracle expert I always asked to provide strong reasons why to use Oracle Database and not to use for example SQL Server. Please, can you tell where to go in the Internet to search a...

2008-09-04
question on rownum
when I have a query of the form <code>select rownum rn, x.* from (select ... from ... order by ... ) x </code> is it guaranteed that the output is ordered by rn (without explicitly telling Oracle to order it by rn) ?

2008-08-20
database design
Hi Tom, I need your help and advice in designing data warehouse tables. We have requirement as to bring in data with specific date range and explode them into daily records (Table A). After this, we put them into weekly and monthly buckets (Tabl...

2008-09-04
New/Old features
Hi Tom: What are your favorite features in 11.1? Over the years you have mentioned many tools, like JDeveloper that you thought were great, but today, what do you think the essential tools to analyze problems are? It seems to me that RTFM an...

2008-08-20
Settling argument over UNDO
Our product includes a Pro*C program that, once a month, gathers all of the month's transactions into a staging table for billing purposes. After putting the raw data into the staging table, the program updates a column in the table: <code> ...

2008-09-02
SQL query to get the most recent entry from a group...
Hi Tom, Here are the table create and insert scripts: CREATE TABLE TEST ( HTENANT NUMBER NOT NULL, HMY NUMBER NOT NULL, SACTIONTYPE2A CHAR(1 BYTE), DTEFFECTIVE2B D...

2008-09-29
Placement of DBAs within an organization.
At the company I have just joined the database group happens to fall under the applications department. This is in stark contrast to every other place I have been where the DBAs were a part of the infrastructure group. I feel that an important streng...

2008-08-20
rule based optimizer used (consider using cbo)
When I look at an explain plan and see "rule based optimizer used (consider using cbo)" How do I track down the reason for the RBO being used, and change it so the CBO is used?

2008-08-20
Table Copy From One DB to Another DB from dblink
Dear Tom can u help me in how to do :-Table Copy From One DB to Another DB from dblink. i searched a lot but i m not getting satificatory asnwer. i dont want, that create a table n then through dblink insert a data from one DB to another DB. and...

2008-08-20
Mandatory subtype records. And subtype/cross-referential integrity
Suppose I have a supertype table with subtypes, and a mapping (cross-reference) table, like this simple example: <code>CREATE TABLE super_type ( id NUMBER constraint super_type_pk primary key , sub_type CHAR(1) ) / CREATE TABLE sub_type_A ( ...

2008-09-04
Does merge with append hint causes fragmentation?
Hi Tom, Thanks for taking this question. Quite a straight forward question, by the way: Does merge with append hint causes table fragmentation? I have table and its data is inserted only with the merge operation, using append hint (and cert...