Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 5 hours 16 min ago

ORA-06548: no more rows needed

Wed, 2016-08-03 19:46
Hi Team, Below is the sql used SELECT * FROM TABLE(pkg.fn_api('PR_LOAD_PL_PROT_TO_SITE','ROLE_ABBR') Below is the code snippet of pkg TYPE t_parm_val_txt_rec IS RECORD ( o_parm_value_txt VARCHAR2 (4000 BYTE) ); TYPE t_...
Categories: DBA Blogs

how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?

Wed, 2016-08-03 19:46
how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?
Categories: DBA Blogs

Waiting for parallel query to finish

Wed, 2016-08-03 19:46
Is there any way I could know the status of a parallel query i.e. the query has finished the job. I am trying to use few parallel updates and inserts in a VB6 application and finally a truncate. The truncate query causes "ORA-00054: resource busy a...
Categories: DBA Blogs

TRIGGER FIRE

Wed, 2016-08-03 19:46
HI TOM, Which will fire default first statement level or row level trigger?
Categories: DBA Blogs

Advanced Queuing & PL/SQL Notification & Oracle User

Wed, 2016-08-03 19:46
I have a queue and i register a PL/SQL - Routine fpr this QUEUE. When the routine is executed it will run under user SYS. I need that the procedure run under my user (e.g. ICIS_SERVICES). How i can reach this? Here my code example: =====...
Categories: DBA Blogs

INACTIVE sessions keeping a handle on CPU

Wed, 2016-08-03 19:46
Hi! We had an issue recently where there was a spike in CPU, and this caused an issue with the end users. We got it sorted by killing off some sessions that were running that shouldn't be. This fixed the issue, and we are working on the route caus...
Categories: DBA Blogs

Need some help developing sql analytical query to find consecutive rows changing between two values

Wed, 2016-08-03 19:46
Hello, I have master table having information for Trouble_Ticket_ID, Customrer_Circuit_ID,Location and details table B having the following information:Trouble_Ticket_ID,User_ID,Status, Newgroup. I need to calculate the no.of time the ticket h...
Categories: DBA Blogs

Select count(*) on a billion records table.

Wed, 2016-08-03 01:26
Hello Tom, Thanks for all the great help. I am writing a simple query which is Select count(*) from wsh_exceptions. Currently this table contains 1,091,130,564 records. So because of this more number of records in this table, select count(*) ...
Categories: DBA Blogs

CROSS APPLY and OUTER APPLY in 12c

Wed, 2016-08-03 01:26
Hi, I have learned that we have CROSS APPLY and OUTER APPLY in 12c. However, I see results are same for CROSS APPLY and INNER JOIN, OUTER APPLY and LEFT / RIGHT OUTER JOIN. So when INNER JOIN and LEFT/RIGHT OUTER JOIN are ANSI Standard and yi...
Categories: DBA Blogs

CPU Waits

Wed, 2016-08-03 01:26
Hi, When i am trying to analyze my database performance through AWR reports, my findings are Wait Wait Time Total Avg(ms) %DB Time DB CPU 2,731,581.78 75.49 * System I/O db file parallel write 882,568.7...
Categories: DBA Blogs

Contraint/Check _ Prevent any value to be inserted more than twice in any table.

Wed, 2016-08-03 01:26
I have a table say xyz which contains a field say roll_number, now I want to implement a check so that any roll number is allowed only twice in xyz. OR Suppose I have a table xyz containing roll numbers, and another table abc containg roll numb...
Categories: DBA Blogs

Difference Between Two Dates With Time

Wed, 2016-08-03 01:26
Sir, There are two input dates with format 'DD/MM/YYYY HH:MI:SS' How can I get difference between these two dates like Input ---------------------------------------------- '15/11/2015 11:19:58' '14/10/2014 10:18:57' -----...
Categories: DBA Blogs

Is it feasible to use Dynamic SQL for a statement with "into :num" clause?

Wed, 2016-08-03 01:26
We have the following statement executed in the database by the outside-of-the-database application: "SELECT COUNT(*) INTO :num FROM TABLE1" I'd like to be able to execute this statatement at will via Dynamic SQL to preserve the same sql_id. ...
Categories: DBA Blogs

Creating a view using dynamic pivot table

Wed, 2016-08-03 01:26
Hi Tom I apologize if this has been posted before. I am trying to build a web page that will provide information from AWR tables and I would like to compare two months data in a chart. select * from ( select to_char(end_time,'dd') Day ...
Categories: DBA Blogs

Reading a text in tag in a clob column

Tue, 2016-08-02 07:06
I have a column in DB which is of clob type. I need to extract the text in one of the tag using a sql query but i am getting a xml parsing error. xml in the clob col: <ns2:reference xmlns:ns2=""> <ns2:meta-data> <content-type>REFERENCE</c...
Categories: DBA Blogs

ora-12224 tns no listener

Tue, 2016-08-02 07:06
sir i change host name with ip address in user computer i got server ip address by oracle command select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual; result 127.0.0.1 i change this ip address with host orcl = (DESCRIPTION ...
Categories: DBA Blogs

Issue in generating bill using Oracle Programming

Tue, 2016-08-02 07:06
I want to generate newspaper bill according to the following criteria. In a newspaper publisher, the cost of newspaper on Tuesday and Thursday is Rs.3/- and on other days it is Rs.2/-. So my requirement is to generate billing of newspaper as per t...
Categories: DBA Blogs

Named Parameters w/REF CURSOR

Mon, 2016-08-01 12:46
Hi Tom, This question is about the named parameter syntax. I've successfully called functions using this method: v_order_id := CREATE_CUSTOMER_ORDER ( p_customer_id => p_customer_id, p_total_amount =...
Categories: DBA Blogs

HASH JOIN Vs SORT MERGE

Mon, 2016-08-01 12:46
Hello Asktom Team, Hope you could throw some light on this. We have two Oracle 11g instances where one MERGE statement has radically different execution plans. One finishes in less than a minute and one times out with ORA-01652: unable to exten...
Categories: DBA Blogs

Distribution of DOP

Mon, 2016-08-01 12:46
I'm using Oracle 12c. I'm calling mutiple procedures from Python in parallel (usually 4-5). I make sure nothing else is running at that time. All the target and source tables has Parallel 32 Clause . I'm using hints ENABLE_PARALLEL_DML, PARALLEL in...
Categories: DBA Blogs

Pages