Tom Kyte

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

Neat and optimized code

Fri, 2017-10-20 14:26
I need the logic to derive the direction and line hour direction is Inbound if either one of org and dest or both falls under the jrny_in list and the rt list direction is outbound if either one of org and dest or both falls under the jrny_out ...
Categories: DBA Blogs

Performance - Index tablespaces with block size greater than data tablespace ?

Fri, 2017-10-20 14:26
Hi, I would like to know if it would it be good practice to create index tablespaces with block size larger than the data tablespace? Recently, a performance consultant company guided us to maintain indexes on tablespace with block size of 16K,...
Categories: DBA Blogs

Change a DBMS_SCHEDULER job's next run date

Fri, 2017-10-20 14:26
I have a job scheduled via DBMS_SCHEDULER. It is scheduled to run weekly. It has been running fine for some time now. All I want to do is alter the next_run_date. I cannot find any way to do this in the documentation. I can alter the original start d...
Categories: DBA Blogs

Dynamically Create Database Link

Fri, 2017-10-20 14:26
I have several scripts that are hard wired when creating a database link and all works well. The time has come though to take them to the next level and expand the user community. To do that the CREATE DATABASE LINK statement needs to be dynamicall...
Categories: DBA Blogs

Receive http requests with more than 32k

Fri, 2017-10-20 14:26
Hi Tom, on more question :) I'm using the utl_http package to exchange xml-data between different oracle databases. Until now i've requested the mod_plsql (until 11g) or the apache (from 12c) on the other database, posted the data with utl_http....
Categories: DBA Blogs

audsid

Fri, 2017-10-20 14:26
I am trying to track down the source (ie. machine and program) of a SQL that is erroring out with an ORA-00600. When the session produces a trace file, it identifies the session using the SID, serial# combination : *** SESSION ID:(1346.55751) 2017-10...
Categories: DBA Blogs

error ORA-01422 in select query

Fri, 2017-10-20 14:26
Hi, I am facing "ORA-01422: exact fetch returns more than requested number of rows" in my plsql procedure. Here are the details of test I am trying: create table cust_bug_metadata(product_id number, component varchar2(50),sub_component varchar...
Categories: DBA Blogs

Allocation memory for varchar2 index

Thu, 2017-10-19 20:06
Hello! I have read much information about allocation memory for varchar2 as pl\sql variable and column of table. So now we have memory problem (assign awr report) with a big report. Can u explain please: 1) type StringTable is table of varchar2(320...
Categories: DBA Blogs

Alternative for SQLPlusW

Thu, 2017-10-19 20:06
Hi All, I've been reading a lot on the internet and am searching for a replacement for sqlplusw.exe. The problem is that I've written a lot of scripts and with sqlplusw.exe you could set a path by reading in an sqlfile. The solutions I've seen sofar...
Categories: DBA Blogs

Java Oracle ETL using CursorExpressions

Thu, 2017-10-19 20:06
Hi, I am attempting to export a large amount of data from multiple separate tables from Oracle 11 into a NoSQL database via a Java app utilising JDBI. The data is being read from the following tables: store, store2, staff and product. The final...
Categories: DBA Blogs

How to write a SQL to join with multiple tables and select results in a query from any on of the table (like in queue)

Thu, 2017-10-19 20:06
Hi , I have following tables . I am getting orders data from various sources in table test_orders. Then joining the items to various table for revenue percentage and then send the orders along with revenue information to another 3rd party tool for re...
Categories: DBA Blogs

Dynamic SQL in CURSOR

Thu, 2017-10-19 01:46
Hi, I am trying to create a procedure that will display logs. It has an IN parameter which is the table nam, the cursor will SELECT data based on that parameter. I cannot compile my procedure. Hope you can help me. :) <code>CREATE OR REPLACE PROCE...
Categories: DBA Blogs

Instead of Triggers - where to use?

Thu, 2017-10-19 01:46
Dear Mr. Tom, 1) First of all when I tried creating an INSTEAD OF Trigger it gave me ORA-00439: feature not enabled: error Kindly explain why so....? 2) Where exactly would u advise me to use Instead Of trigger ...
Categories: DBA Blogs

How to enforce conditional unique on multiple columns

Thu, 2017-10-19 01:46
Hi, Tom, I have a table create table project (project_ID number primary key, teamid number, job varchar2(100), status number(1)); status=1 means it is an active project, otherwise it is archiv...
Categories: DBA Blogs

Merging 55 millions of records along with hash key

Thu, 2017-10-19 01:46
Hi Tom, I need to load my table with 55 millions of records using Merge statement 1. Those 55 millions records are being fetched from many different tables. For example T1,T2,T3 etc. 2. i have a date table from where i fetch business weeks ba...
Categories: DBA Blogs

Calling a linked procedure with out cursor parameter in another procedure

Thu, 2017-10-19 01:46
Hello, I need to have a procedure with out cursor parameter which I am calling it via a db link, inside a procedure, how can I implement that? procedure Myouterprocedure(..., my_out_cursor out cursor, ...
Categories: DBA Blogs

sqlcl command line tool warning

Wed, 2017-10-18 07:26
Hi I am using sqlcl quite a while and constantly get this null pointer exception. Any Idea Apr 15, 2016 9:28:22 AM oracle.dbtools.plusplus.JDBCHelper getOH WARNING: oracle.dbtools.plusplus.JDBCHelper.getOH(JDBCHelper.java:83) java.lang.NullP...
Categories: DBA Blogs

Merge statement

Wed, 2017-10-18 07:26
Hi Tom, consider this: create table test ( id number(10), username varchar2(100), reason varchar2(100), timestamp_ number(10), cnt number(10), CONSTRAINT test_pk primary key (id) ); insert into test(id,username,reason,timestamp_,cn...
Categories: DBA Blogs

Optimize Query

Wed, 2017-10-18 07:26
I want to optimize the following query SELECT t.merchant_id, t.brand_id, t.transaction_type, t.trns_currency_code, <b>COUNT (*) AS total_no_tx, SUM (t.mv_is_trnx_approved) AS approved_no_tx, ...
Categories: DBA Blogs

DDL for tables and constraints

Wed, 2017-10-18 07:26
Team, I am using SQL Developer 17.3 and SQLCL 17.3 for this demo. while doing DDL from SQLCL we got this <code> demo@ORA11G> show ddl STORAGE : ON INHERIT : ON SQLTERMINATOR : ON OID : ON SPECIFICATION : ON TABLESPACE : ON SIZE_BYTE_K...
Categories: DBA Blogs

Pages