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: 2 hours 32 min ago

How to implement a collection of type object

6 hours 48 min ago
Hi, I have create a object like Create or replace Obj_Bulk AS Object ( customer_id NUMBER(6) , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(20) , cust_address CUST_ADDRESS_TYP , phone_numbers ...

How can we diagnose a process that does not report to the wait interface?

Wed, 09/02/2015 - 00:00
From time to time, we see a process that uses a full cpu core, but does (almost) not log any waits. A 10046 trace shows fx an i/o wait roughly every minuts, taking few milliseconds. A hanganalyze trace shows the exact same picture. Are there a no...

SQL Query: how to break down a range of dates

Wed, 09/02/2015 - 00:00
Hello, I have the following table: create table temp ( date_from date, date_to date, rate number); with this data in it: insert into temp values (date '1973-07-01', date '1992-12-31', 6.75); insert into temp values (da...

Explain plan show dynamic sampling used for this statement (level=5) but optimizer_dynamic_sampling = 2

Wed, 09/02/2015 - 00:00
Hi, I don't understand why Oracle use dynamic sampling (and why at level 5). The parameter optimizer_dynamic_sampling is set to 2. Statistics on table is computed (default Oracle stat) select num_rows,blocks,sample_size,last_analyzed from a...

Restricting user deletes (sort of)

Wed, 09/02/2015 - 00:00
Hi, As part of normal operational activities, circumstances arise where manual updates / deletes to our production databases occur. These updates & deletes are made by our support team. We need to restrict the amount of rows deleted or updated at ...

Customized version of PERCENTILE_CONT aggregated function

Wed, 09/02/2015 - 00:00
Hi, I would like to implement, in pl/sql, a customized version of PERCENTILE_CONT aggregated function. The following code snippet shows the definition of percentile that I would like to use: /** Percentile function vec: array of profi...

how can alter table

Wed, 09/02/2015 - 00:00
Can we add a new column between two columns.If yes,please tell me command

Need to have a package that contains a procedure with an OUT SYS_REFCUSOR variable to work with .Net application

Wed, 09/02/2015 - 00:00
Hi, I have been searching and searching to find the answer to this question, so I'm very sorry if it's been asked before. I am trying to have a C# host application get information from my database using a refcursor and a package. Here is the...

In-memory on Partition Exchange loads

Wed, 09/02/2015 - 00:00
Tom, Can you help me to understand why the In-memory option doesn't work with Exchange partition loads ? https://community.oracle.com/thread/3691663?sr=inbox

Formatting a number as zoned (COBOL style)

Wed, 09/02/2015 - 00:00
I need to read and write data files that are processed by COBOL programs. Reading (loading) is easy; I created an Oracle loader-type external table using field definitions like "field1 POSITION(nnn) ZONED(7,2)". But I also need write zoned-formatted ...

Bind Variables on Extensibile optimizer

Wed, 09/02/2015 - 00:00
Tom, Is the Extensible Optimizer doesnot support bind variables? please see below when using bind variables, estimated_rows=1 but when not-binded estimated_rows=4 why so? I am on 11.2.0.4 on 64 bit linux. <code>rajesh@ORA11G> variable x var...

gather_plan_statistics question

Wed, 09/02/2015 - 00:00
Tom, Can you help me to understand what does "OMem" , "1Mem" and "Used-Mem" in the below plan represents? thanks. <code>rajesh@ORA11G> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------...

HYBRID TSM/HWMB in Explain plan

Wed, 09/02/2015 - 00:00
Tom, what does this "HYBRID TSM/HWMB" in the below plan indicates? I see this in 12.1.0.2 <code>rajesh@PDB1> explain plan for 2 insert /*+ enable_parallel_dml parallel(t1) */ into t1 3 select /*+ parallel(t) */ * from t ; Expla...

In-Memory parallel execution in 11g

Tue, 09/01/2015 - 00:00
Tom, I was reading about in-memory parallel execution here https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#BEICFFGB but i am not able to suceed with a sample test script to demonstrate this feature. can you help me wi...

Why cost for TABLE ACCESS BY INDEX ROWID to high for only one row

Tue, 09/01/2015 - 00:00
Dear Tom, I have problem with query on table have function base index. create index : <code> create index customer_idx_idno on Customer (lower(id_no)) ; --- id_no varchar2(40) </code> <b>Query 1:</b> execute time 0.031s but cost 5,149, 1 row ...

Performance problem passing nested table arrays to SELECT sub queries

Tue, 09/01/2015 - 00:00
Hi, In PL/SQL we have procedures that get passed a nested table array. In some cases we want to use the values in this array in a SELECT statement. This technique works but we have found in some cases that the performance can be terrible and can d...

Gather stale stats at table level using prefs as stale_percent

Tue, 09/01/2015 - 00:00
Hi, I need to gather table level stats using customised stale_percent value stored in some xyz table.when I run below code in a stored procedure,the code gather stats for the table even if it was not stale .What parameter or options are there to ...

Query block names disappear

Mon, 08/31/2015 - 00:00
Sometimes when I use a QB_NAME hint, when I later look at the execution plan, there is no record of that query block name. Why is this the case ?