Tom Kyte

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

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

Restore using duplicate is failing

Tue, 2016-09-06 09:46
Hi, I am trying to restore target database with below command : rman auxiliary / run { allocate auxiliary channel ch1 type disk; allocate auxiliary channel ch2 type disk; allocate auxiliary channel ch3 type disk; allocate auxiliary chann...
Categories: DBA Blogs

Oracle

Tue, 2016-09-06 09:46
1)If I created a procedure in trigger after that i modify the procedure. my question is the trigger is working fine or procedure changes, effected trigger also?.. 2)There is one table. table1(inpt)...........target(output)... 1 ...
Categories: DBA Blogs

DB time

Tue, 2016-09-06 09:46
Hi Tom, Lucky I've got chance again. Can you please explain about the db time now shown in statspack reports (10g). The explanation I know of is: it is the time spent in database since instance startup. Can you please elaborate this. I can...
Categories: DBA Blogs

Problem with POST JSON data using utl_http

Mon, 2016-09-05 15:26
I am trying to post JSON DATA to a rest point in cloud. create or replace procedure EBS_Rest_API_Call ( p_Email_ID in varchar2, p_Fisrt_Name in VARCHAR2, p_Middle_Name in Varchar2, p_Last_Name in Varchar2, p_Phone_Number in varchar2, ...
Categories: DBA Blogs

Successfuly compiled package becomes invalid over couple of seconds

Mon, 2016-09-05 15:26
Hi, Tom. I'm experiencing a strange problem. In database A i got package that uses dblink inside it, just to open cursors. So <code>select * from dba_dependencies</code> ? OWNER NAME TYPE REFERENCED_OWNER REFERENC...
Categories: DBA Blogs

ACID versus BASE

Mon, 2016-09-05 15:26
Team, Please let us know your comments on this <u>http://www.johndcook.com/blog/2009/07/06/brewer-cap-theorem-base/</u> Connor - Been with this industry since 1990 - Please share your view on this. <code>... Ultimately I think the peak of the...
Categories: DBA Blogs

Use of & and prompt

Mon, 2016-09-05 15:26
Hi Tom, I'd like to be able to run a script in SQLPlus with an input parameter (i.e. SQL> @My_Proc ABC). However, if I do not enter the input parameter, SQLPlus displays a prompt: Enter a Value for 1: How do I suppress the SQLPLus prompt and...
Categories: DBA Blogs

Pages