Tom Kyte

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

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

Count groups when next value = current value plus one

Mon, 2016-08-01 12:46
I have this table: create table test (myname varchar2(1), dts_id number); With this data: insert into test (myname, dts_id) values ('A',1); insert into test (myname, dts_id) values ('A',2); insert into test (myname, dts_id) values ('A',3); in...
Categories: DBA Blogs

Pivot table row to column covert

Mon, 2016-08-01 12:46
Hi Tom, Please consider below scenario. Table A ID Attribute Value 1 'X' 3 1 'Y' 4 2 'X' 5 3 'X' 6 3 'Y' 7 3 'Z' 8 3 'A' 9 Expected output ID X Y Z A 1 3 4 nul...
Categories: DBA Blogs

Please give the sql to get query performance in oracle database, i don't want to use statspack.

Mon, 2016-08-01 12:46
I was SQL Server DBA over 1 year, now i am oracle DBA so i used to use below query against sql server database to get query performance. <code>SELECT TOP 5 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.obje...
Categories: DBA Blogs

DBMS_DATAPUMP

Mon, 2016-08-01 12:46
Can one export and import data only using dbms_datapump? I do not want to specify a schema on the export nor do I want it to default to the user's schema. I do not want to specify a "from" schema on the import. Is this possible?
Categories: DBA Blogs

email attachment but not enable to open it

Mon, 2016-08-01 12:46
I am trying to send mail with attachment pdf file i recieved the mail but when am trying to open the pdf file is return error massege please help am stuck DECLARE v_From VARCHAR2(80) :='test@ttt.net'; v_Recipient VARCHAR2(80) := 'r...
Categories: DBA Blogs

IO throughput problem

Mon, 2016-08-01 12:46
Dear Sirs, My question is about throughput\bandwith problem. We have a data warehouse solution, database size aroud 1 TB, nightly\morning run times around 15 hours. The optimal performance would be that users can have their daily generated repo...
Categories: DBA Blogs

Real Time Scenarios & duplicate rows

Fri, 2016-07-29 11:26
I have two questions 1.) Deleting Duplicate rows DELETE FROM EMP1 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP1 GROUP BY EMPNO); the above command deletes the duplicate rows from a given table such as emp1 in this case, group by eliminat...
Categories: DBA Blogs

Cascading calculated LAG values

Thu, 2016-07-28 17:06
Oracle 11.2.0.4 Example SQL to explain my question: https://livesql.oracle.com/apex/livesql/file/content_DMZ4GO3W9QL4BONQQB8I7NP5X.html The SQL I am working on is monthly data based on two categories (CATEGORY_A and CATEGORY_B). While we get valu...
Categories: DBA Blogs

DDL timeout parameter limit

Thu, 2016-07-28 17:06
Hi Tom I have read in the below link that DDL will not require any downtime starting from Oracle 11g (understood that same applies to Oracle 12c): http://www.oracle.com/technetwork/database/features/online-ops-087977.html I am using Oracle 12c...
Categories: DBA Blogs

Return first character of each word

Thu, 2016-07-28 17:06
Sir can you help me regarding this query in SQL by using character functions only.... I/P : ABC DEF GHI Output: ADG
Categories: DBA Blogs

ORA-00980 translation is no longer valid

Thu, 2016-07-28 17:06
Dear Sir, I am exporting a database from one system and importing it in another system. after importing its shows the error ORA-00980 tranlation is no longer valid everywhere i can see the solution but my question is 1)its working well with wit...
Categories: DBA Blogs

Pages