Tom Kyte

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

Missing RMAN Duplexed location

Thu, 2018-07-26 16:26
Hi, We are carrying out some RMAN prototyping in our offline environment for duplexing backup sets to two different locations. We have added the following RMAN persistent configuration settings: CONFIGURE DEVICE TYPE DISK PARALLELISM 2; CONFIG...
Categories: DBA Blogs

Multiple analytical functions in a query

Thu, 2018-07-26 16:26
Dear Tom, Thanks for this wonderful platform where there is always opportunity to learn something new on things we think we already know. Really appreciate it. I have a query regarding the analytical functions in Oracle. Query is based on the Emp...
Categories: DBA Blogs

online redo log corruption

Thu, 2018-07-26 16:26
suppose i have 3 online redo log groups with two members on each group. Suppose any group is corrupted, then how to find the the corrupted exact group??. How to find weather the data in online redo log file are weather written or not on disk (datafil...
Categories: DBA Blogs

Oracle client for MACBOOK

Thu, 2018-07-26 16:26
When can we have full Oracle client software for Mac iOS platform as MacBook is widely used by Oracle users.
Categories: DBA Blogs

list reports for a given dates range

Wed, 2018-07-25 22:06
<b></b>Hello there!! i am new in PL SQL so i have been given a task to develop a procedure by joining two tables and the program should give a report of invoices within a given range of dates.I want my program to show reports as per my specificati...
Categories: DBA Blogs

how to return result set from stored procedure.

Wed, 2018-07-25 22:06
We are developing an intranet application using Asp IIS and oracle 8i. I want to return a resultset from oracle stored procedure to Asp page. How do I do it? Like in sybase you say create procedure test (parameters) resultset (col1,col2 ) Lik...
Categories: DBA Blogs

PLSQL Performance Tuning for conditional logic

Wed, 2018-07-25 22:06
I have written a validation parser which gets called using webservice (meaning need high performance code to be run), I have added the Example link in Live sql where I have added conditional clauses (for null checking several input parameters and ...
Categories: DBA Blogs

Is my problem solvable by use of an hierarchical query?

Wed, 2018-07-25 03:46
Hello, I have this problem I haven't been able to solve so far, though I suspect it should be using a hierarchical query. I included sample/test data in the LiveSQL script I saved and shared, it includes the desired result the hypothetical query w...
Categories: DBA Blogs

Getting error as ORA-29283: invalid file operation

Tue, 2018-07-24 09:26
Hi, we have created this anonymous block to capture the data using DB link and write into CSV file. The details are as below, <code>--Table create table emp (empno number) / --insert insert into emp vaules (10) / insert into emp vaul...
Categories: DBA Blogs

External table: only one rejected record is loaded into bad file

Tue, 2018-07-24 09:26
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production I am using following code to create external table. <code> V_SQL := 'CREATE TABLE ' || L_EXT_TABLE || ' (' || L_CNAMES || ') ORGANIZATION EXTERNAL ( TYPE...
Categories: DBA Blogs

performance tunnig ==> excessive child cursor

Tue, 2018-07-24 09:26
hi Team , my cursor_sharing parameter is set to exact , amount of parsing i have checked is less but some query have very high number of child cursor . One of update statement have 506 child cursor . can you help me out how to deal with this...
Categories: DBA Blogs

Need solution for, No of columns in the update statement are dynamic

Tue, 2018-07-24 09:26
Hi Tom, I want to write an UPDATE statement in which the number of columns may vary for each run based on the user choice. Eg: DDL <code>CREATE TABLE "XX_MASK_REF" ( "TABLE_NAME" VARCHAR2(150 BYTE), "COLUMN_NAME" VARCHAR2(150 BYTE), ...
Categories: DBA Blogs

Application vs Middle Tier Connection Pooling

Tue, 2018-07-24 09:26
Do you have a recommendation for use of connection pooling in the application vs a middle tier pool? Would you suggest WLS to manage a conn pool at the middle tier? any issue for .net or python applications to access the pool for connections?
Categories: DBA Blogs

Create Age Group Dynamically Based on Lower and Upper Age limit and age band

Tue, 2018-07-24 09:26
Hi, I have following requirement and seeking help to achieve this via SQL only (no PL/SQL block) - Table A contains following columns - 1. Employee_ID 2. Employee_Age 3. Employee_Dept The end user needs to provide following 3 prompt val...
Categories: DBA Blogs

Using BULK COLLECT/FORALL

Mon, 2018-07-23 15:06
I have a procedure that was previously using a "slow-by_slow" type procedure. I've converted it to BULK COLLECT and FORALL (test_cur, see livesql). The procedure is obtaining data from 2 tables then inserts this data into 2 corresponding tables. ...
Categories: DBA Blogs

API for context

Mon, 2018-07-23 15:06
Hi! What is the best way to get context value in Oracle? We have sys_context, which allows get value from context, but it forces to hardcode constants for namespaces and keys. Is there more graceful solution for it? For myself I've made a sepa...
Categories: DBA Blogs

AWR and ASH guide lines.

Mon, 2018-07-23 15:06
Dear Team, Please share some bokks details for AWR and ASH report analysis in depth. Thanks Pradeep
Categories: DBA Blogs

SQL Query optimization : is scalar subqueries killing the performance?

Mon, 2018-07-23 15:06
I need help on query optimization. The below-mentioned query is just a sample. Actual query has 50+ scalar subqueries. Base table table_xyz has over 5 million records. <code>SELECT id, seq, ( SELECT functio...
Categories: DBA Blogs

Random date between two date ranges

Mon, 2018-07-23 15:06
I need to pick one random date per month from July 2017 to June 2018. I have the below mentioned code which is only selecting them for entire 2017 Calendar year whereas I need it from July 2017 to June 2018. Any advise? with tst as ( Select lev...
Categories: DBA Blogs

table delete,truncate

Mon, 2018-07-23 15:06
Hi Team, In Our production environment When are checking the report is not working, after to analysis we found that there was master table has been truncated or deleted by somebody. Now we are trying to identify the sql_text against that table....
Categories: DBA Blogs

Pages