Tom Kyte

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

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

oracle pl/sql

Wed, 2016-09-07 22:26
how to breck large database table result in reguller interwol
Categories: DBA Blogs

JSON_VALUE() and JSON_TABLE(...COLUMNS...NUMBER...) honour NLS_NUMERIC_CHARACTERS while they shouldn't.

Wed, 2016-09-07 04:06
The following shows that the same (JSON) table data and the same queries yield different results when different NLS_NUMERIC_CHARACTERS are used. The above sentence is no surprise, obviously, but in this case I consider the behavior to be wrong; Plea...
Categories: DBA Blogs

Oracle database not responding

Wed, 2016-09-07 04:06
We have a Java application that uses a connection pool with Oracle database, during the week these conections are frequently refreshed (closed and opened again), the problem is at some point (after two or three days) the application is unable to acqu...
Categories: DBA Blogs

connection issue

Wed, 2016-09-07 04:06
Hi Tom, I have deployed Oracle RDBMS software & created new database in Azure cloud.And also created Application users in this database. If I connect application user using SQL Developer I am getting error. IO Error: Connection reset ...
Categories: DBA Blogs

Checkpoints

Wed, 2016-09-07 04:06
Under what conditions checkpoint occurrs
Categories: DBA Blogs

UTL_FILE.FCOPY

Wed, 2016-09-07 04:06
I am having a problem using FCOPY to make a few copies of a file that I've created with UTL_FILE.PUT_LINE. The goal is to generate a txt file and dump into a directory .../output_1/file_1.txt and then copy & rename this file into another directory...
Categories: DBA Blogs

export jobs hang db

Wed, 2016-09-07 04:06
Hi team, In our production database we are using logical backup and there is 20 users and the size of data 200 GB each user So it is configure mid night but export started after some time my entire database getting very slow it is find when we ...
Categories: DBA Blogs

Avoiding generating too much redo.

Tue, 2016-09-06 09:46
Hi, In my company most of the developers generate too much redo because :- 1. they load txt files into staging table. 2. update the table with missing information "lockup table and select for each rows" 3. insert the data from the staging table...
Categories: DBA Blogs

Smart Running Sum - Reset when total > 100

Tue, 2016-09-06 09:46
Hi, I m trying to add a culomn which will calculate a running sum of the date diffrents between each consecutive flights (In Minutes) Per Destination but with one tweak: every time that the running sum reach spesific amount (Prompt Value = 100 Min....
Categories: DBA Blogs

temporary tablespaces

Tue, 2016-09-06 09:46
On a database housing an Oracle Text index there is a user LOADMSST who runs an application that executes ctx_ddl.sync_index, there is another user EDMAPPS who is used by applications that pose Text queries. The table column on which the Oracle Text...
Categories: DBA Blogs

Pages