Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 13 min 4 sec ago

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

PL/SQL procedure and HTTPS request

Fri, 2018-07-20 14:06
Hi Tom, I'm trying to invoke a RESTful service (using HTTPS and JSON objects) from a procedure. The version of Oracle installed is: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2...
Categories: DBA Blogs

group records by interval of 3 seconds

Fri, 2018-07-20 14:06
Hi Tom, I am not able to find the exact answer to my question although I think I might need to use LAG and LEAD: I have records like this: Time Amount 11/22/2003 12:22:01 100 11/22/2003 12:22:03 200 11/22/2003 12:22:04 ...
Categories: DBA Blogs

enable trace for specific schema

Fri, 2018-07-20 14:06
Hi, We are having slowness issues so, I would like to activate the trace for 1 hour in my database for an specific schema, I have reviewed in several websites and I have these steps: 1- ALTER SESSION SET TRACEFILE_IDENTIFIER = "TEST_TRACE"; 2- A...
Categories: DBA Blogs

add subpartitions to partitioned table using alter table

Fri, 2018-07-20 14:06
Hi Tom, I have a list based partitioned table as shown below. create table t( col1 number, col2 date, data_id varchar2(10), archived char(1)) partition by list (data_id) (partition a_data_id values ('A'), partition b_data_id values ('B') )...
Categories: DBA Blogs

"Table access by index rowid" is slower than "table access by user rowid"

Fri, 2018-07-20 14:06
Hi Tom, Anonymous procedure having a type for table rowid on one column for a billion plus record table, i open a cursor selecting rowid of the table for rownum less than 200000, then i fetch a cursor bulk collecting from the table by limiting ever...
Categories: DBA Blogs

Select for update skip locked failing

Fri, 2018-07-20 14:06
Hi, I have a scenario where I have to get first 10 rows from filtered and ordered result set for update. As we are on multi user environment we are trying to use SELECT FOR UPDATE SKIP LOCKED. Let me put it in a simple example Scenario-1...
Categories: DBA Blogs

Rowtype variable in dynamic SQL

Thu, 2018-07-19 20:06
How to pass below variable in dynamic sql? v_emp_rec org.emp%rowtype; I am getting ORA-01008: not all variables bound error for below code. declare v_sql varchar2(1000); begin v_sql:='demo_pkg.demo_rates(p_rec=>v_emp_rec);'; execute ...
Categories: DBA Blogs

Line Level Logging using controlled FND_LOG\DBMS_OUTPUT statements vs DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Thu, 2018-07-19 20:06
Hi Tom, I have used DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for understanding the exact line where error occurred in a package which is exposed using a service outside. However one of my client feels I should write diagnostic message after certain set ...
Categories: DBA Blogs

hierarchical query with date consideration

Thu, 2018-07-19 20:06
How to include an effectivity date range check in hierarchical query? Such that a loop may exist if the date range is excluded, but considering the range a loop would not. <code>with output_tab as (select 'P1' as output_id, 100 as output_key, to_...
Categories: DBA Blogs

Pages