AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 52 min 15 sec ago

Tom Kyte's books

Sun, 04/20/2014 - 00:00
Hi Tom, I have some of your fine Oracle books. Effective Oracle by design and Expert Oracle Database Architecture (latest version). I prefer reading on an ipad rather than carrying them around with me. Is there a PDF version of these books? Do ...

Merge Insert 50 million records by finding only new records

Sun, 04/20/2014 - 00:00
Hi Tom Im having a Full dump of 50ish million records arriving every week. Every week there would be,lets say, a 5 % increase in the Full Dump. My task is to obtain this set into STG(Staging) table as it is , then move this into a DIMension ...

Error using Bulk Collect

Fri, 04/18/2014 - 00:00
Hi Tom, I've got a procedure that bulk collects a bunch of data from one table and uses that data to update another table. The issue I am having is when an exception occurs, the whole process stops and rollsback with no data being updated. My quest...

Identify Non-duplicated value within a column

Fri, 04/18/2014 - 00:00
Hi Tom, I have below table and data, and wanted to identify which analytical functions and how to use them to obtain the following desired result. I am expecting that the row Case_A version 3 with ID value of 10060800 to be displayed, as that i...

PK size

Fri, 04/18/2014 - 00:00
Hi Tom. I have a table with following definition: SQL> desc OAUTH2_REFRESH_TOKEN Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...

[Error] PLS-00201 (3: 14): PLS-00201: identifier 'VCARRAY' must be declared

Thu, 04/17/2014 - 00:00
This is my following code for stragg -- CREATE OR REPLACE type APPS.ccl_string_agg_type as object ( data vcArray, static function ODCIAggregateInitialize(sctx IN OUT ccl_string_agg_type ) r...

Creating view with size 40K on remote DB using DBMS_SQL.PARSE

Thu, 04/17/2014 - 00:00
Hi Tom, This is my first post here. First of all let me thank you for all the help i got from your site over the years. :) Now the issue, I am trying to create a view from local DB (11.2.0.3) into remote DB (10.2.0.4). my code worked for smaller s...

steps of query optimization

Tue, 04/15/2014 - 00:00
Hi Tom, I faced with a question and I was in doubt about the correct answers. In my opinion, not just two answers are possible, even if the question was "which two tasks are performed during the optimization stage of a sql statement" And here is ...

Oracle Advanced Queueing Limiting Concurrent Processes on a Queue

Tue, 04/15/2014 - 00:00
Hi Tom, I'm currently working on a project to migrate a custom queue into an Oracle Streams Advanced Queueing queue table and queue. My question is, when using a multiconsumer queue to hold data that is in constant flow (dequeue happens on enqueu...

Redo Log Switch Rule of Thumb on No Archivelog mode databases

Tue, 04/15/2014 - 00:00
Hello Tom, The 15-30 minute redo log switch time interval is common knowledge. However, I would like to know if this rule of thumb applies for tuning redo log switching on a database that is set to "no archivelog" mode? To get a better under...

What is an "abstract lob"?

Tue, 04/15/2014 - 00:00
Hi Tom, When quering the v$temporary_lobs view we can see a column named "abstract_lobs": <code>desc v$temporary_lobs Name Null Typ ------------- ---- ------ SID NUMBER CACHE_LOBS NUMBER NOCACHE_LOB...

Materialized view query

Tue, 04/15/2014 - 00:00
Hello Mr. Kyte, Can you please explain to me how can I use materialized views to replicate a subset of the data of a table based on the comparison of the date found in a column with the current time. The logical query would be of type <code>...

is "the" a keyword?

Tue, 04/15/2014 - 00:00
I saw "the" in aSQL from java code and I thought it was a mistake, but it wasn't: <code> >select eamrid, action_code, fault_msg, execution_time, timeout_value 2 from ( select cast( pkg_ode.parse_response('METERREADINGS', '1.4', :v_xml ) as o...

SEQUENCE

Tue, 04/15/2014 - 00:00
hi tom, during one interview i got one question in sequence i.e if there is one sequnce whose max value is 40,but after got nextval 20.without execute the select query 20,000 times and without alter the sequence i want to get 20,000 in the nextva...

I am creating Log Trail but result does'nt appered in log table.

Tue, 04/15/2014 - 00:00
Dear tom, I am trying to create log trail but when i am using the below code and performs task logon and logoff to the database nothing appears in log table. create table log_trail (name varchar2(30), time date, action varchar2(10)); create or...

Input File Processing via Array

Tue, 04/15/2014 - 00:00
What is the best practice as far as processing an input file in PL/SQL? Is it best to place all the records in an array first and then loop through the array? Or is it just as well to process each record as you read it in from the input file? The n...