Tom Kyte

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

Max of consecutive numbers when greater than 0

Sat, 2016-09-10 05:06
Hi Tom, I am looking for a solution to get MAX COUNT of CONSECUTIVE NUMBER of ROWS for each BID where DUE GREATER THAN 0, when 0 appear reset counter for that BID. In below example I have max 3 consecutive row of BID 111 for MAR/APR/MAY and have...
Categories: DBA Blogs

AUTONOMOUS_TRANSACTION

Sat, 2016-09-10 05:06
Hello, What are all real practical purpose/code have you seen people use with AUTONOMOUS_TRANSACTION?(Other than auditing) AUTONOMOUS_TRANSACTION can start multiple transactions in the same session, I am trying to understand what people use it ...
Categories: DBA Blogs

how to restore application in my apex workspace

Sat, 2016-09-10 05:06
Hi i have a workspace in oracle apex 4.2.2 the name is (calldetail) the worksapce contain 5 apllication(the appliaction id is 100,101,102,104,104) i backup it and then after 1 minut delete it i forget take backup from application but i get a full ...
Categories: DBA Blogs

Oracle optimizer stop using Indexes when there is a CASE in WHERE clause.

Fri, 2016-09-09 11:06
Hi. I am forced to use multiple UNION ALL just because ORACLE will disregard all my Indexes if I use CASE statement in WHERE clause. This is a huge inconvenience for me because I have a large query and a lot of CASE(s), so I have to repeat the same...
Categories: DBA Blogs

SQL Statement ORA-00936: missing expression

Fri, 2016-09-09 11:06
Hi Tom, What is wrong with this statement? I want to initiate an initialexport from a certain category of persons. UPDATE planung SET dienstposition = dienstposition WHERE (planung.datum between '2016-01-01' AND '2016-01-02') AND (select ...
Categories: DBA Blogs

CTAS dbLink Parallel Hints

Fri, 2016-09-09 11:06
Hi Tom, Will Parallel hints works with CTAS selecting data from a Remote object? Eg: In the below example, please tell me how parallel works? create table table_name parallel 4 as select * from remote_table@dbLink;
Categories: DBA Blogs

handling duplicate rows with cursors

Fri, 2016-09-09 11:06
Hi Tom, Iam Trying to create a procedure to print source code of stored porgrams below is the procedure i created. create or replace PROCEDURE pr_source(p_obj_name IN VARCHAR2) IS l_name all_objects.object_name%type; CURSOR c_c...
Categories: DBA Blogs

Wildly inconsistent CTE (WITH clause) performance

Fri, 2016-09-09 11:06
I was a SQL Server guy until a few weeks ago. I'm trying to avoid temp tables since there are (amazingly) no local temp tables in Oracle and using global temps to break a query into steps is apparently frowned upon, or at least not an Oracle "best p...
Categories: DBA Blogs

SELECT statement in the FROM clause

Thu, 2016-09-08 16:46
I have a script that calls a table from our database but that table is replaced by newer tables quarterly. The format for the tables is like <i>TABLENAME_YYYYMMDD</i>. I would like to call the most current table in the script so that I don't have to ...
Categories: DBA Blogs

Automate the generation of SQL query output to CSV

Thu, 2016-09-08 16:46
Hello, I have a SQL query which generates an output of nearly 200k records. The need is to : 1) Generate the output of this query in text/csv format. 2) Schedule it to be run daily in the morning. 3) I have included select /*CSV*/ in the code....
Categories: DBA Blogs

SUM OVER PARTITION (WITH CONDITION)

Thu, 2016-09-08 16:46
Hi, I have to calculate accumulated value for a column, but I have to filter some rows (and it depends on that accumulated value). For example: <code> CREATE TABLE dummy_table ( var_id NUMBER(5) PRIMARY KEY, prc number...
Categories: DBA Blogs

Timeout db statement after some time without keeping anything any locks on the database side

Thu, 2016-09-08 16:46
Hi Tom, I'm using spring-jdbc(JDBCTemplate) with oracle.jdbc.pool.OracleDataSource to execute few queries(copying data from one table(table 1) to another(table 2) and deleting copied entries from table 1) within a single transaction(transactions a...
Categories: DBA Blogs

Row lock doesn't released after connection closed from client side due to network level failure

Thu, 2016-09-08 16:46
Hi Tom, I have a problem related to row lock handling when there are can be network level failure between client and Oracle db server. I want to make sure that there won't be any stale locks at the server side. Let me explain my issue with an exam...
Categories: DBA Blogs

Repeated rows in 'connect by prior' clause in oracle

Thu, 2016-09-08 16:46
I am trying to understand 'Connect by prior' clause in oracle. I have created below table : <code>create table EMP_MGR ( mgr VARCHAR2(10), emp VARCHAR2(10) )</code> Below is the data of table: MGR EMP null f f a f b f ...
Categories: DBA Blogs

Large row by row table update with values from another table using cursor

Wed, 2016-09-07 22:26
Hello, I have a requirement to update 2 columns (A, B) in a arbitrarily (very, very) large table. The values that needs to be updated relies on values from another table and must be consistent with the values from the other table. Since the values...
Categories: DBA Blogs

How to ensure that both parent and child rows are created in 1:1 relationship

Wed, 2016-09-07 22:26
Hi Tom, One of our applications has a basic 'customer' type schema: customer table, address, emails, orders, etc. At the heart of it is 'customer' itself defined with the following (all not null) <code> id int (pk), customer_type char(1), cus...
Categories: DBA Blogs

ORA-01034: ORACLE NOT AVAILABLE

Wed, 2016-09-07 22:26
connect system password : password. i got following errors ORA-01034: ORACLE NOT AVAILABLE ORA-27101: shared memory realm does not exist
Categories: DBA Blogs

How to optimize these two Queries

Wed, 2016-09-07 22:26
How Can we optimize the below query MERGE INTO Department dept USING (SELECT DISTINCT emp_name, hire_date FROM employees WHERE emp_id = '10') emp_details ON (dept.emp_name = emp_details.emp_name AND emp_details.hire_date IS NOT NULL...
Categories: DBA Blogs

Recover tablespace on anothe host or database

Wed, 2016-09-07 22:26
Hello, friends! I can't find any info to solve my issue. We need to restore some old data from table. Full database is too large and we don't have much space for restore full database. That's why we need to restore only tablespace or (ideally) t...
Categories: DBA Blogs

Compress for OLTP

Wed, 2016-09-07 22:26
Hi, We have reached 90% of our tablespace (disk space) full, and we thought to compress the tables so that we may free up some space. <code> select segment_name, segment_type, bytes/1024/1024 MB from user_segments where segment_type='TA...
Categories: DBA Blogs

Pages