Tom Kyte

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

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

Is there a way I can press a button on home page just after logging automatically in apex application

Wed, 2017-10-18 07:26
Hi, I have an apex application https://apex.oraclecorp.com/pls/apex/f?p=32888:1:4894003287217::::: and I want to click Search button on home page automatically every time once a user login to this application. I need to do this to submit the val...
Categories: DBA Blogs

Select and duplicates

Wed, 2017-10-18 07:26
Hello, I'd like to remove duplicates from ma table let say V1 is the key column. the key should be max 5 digits as a length Example: V1 01254T 50300 50300W 45662 Here I should keep the first one (it has no duplicates) , ...
Categories: DBA Blogs

Different in costs for selecting sysdate from dual and using directly as sysdate

Wed, 2017-10-18 07:26
The below 2 queries returns the same results. The first one takes very long time and the cost of fetching the records from the custdata is around 82000. I cancelled the query since it takes longer. But the second query takes the cost of only 2 an...
Categories: DBA Blogs

Converting compressed securefile LOBS to basicfile

Wed, 2017-10-18 07:26
Hello Tom, We are migrating a database which is using Advance compression for LOBS. We do not have licenses for the new environment to replicate the as-is. What are the available options we have and how to convert compressed securefile LOBS to s...
Categories: DBA Blogs

DBMS_CRYPTO.HASH HASH_SH256 does not match openssl

Tue, 2017-10-17 13:06
OM - I need to create a (large) hash both inside the database and outside of it that yields the same result. Everything I am reading says SHA256 will satisfy the requirement, but I am not able to write SQL that achieves the same answer as the Li...
Categories: DBA Blogs

Hidden parameters

Tue, 2017-10-17 13:06
Will the below hidden parameter have any issues. Database version : 11.2.0.4.0 _always_anti_join off _always_semi_join off _disk_sector_size_override TRUE _hash_join_enabled FALSE _kgl_large_heap_warning_threshold 20485760...
Categories: DBA Blogs

Contains operator with "not" or "no" query terms does not work

Tue, 2017-10-17 13:06
I have a large free-text field that I am trying to query for specific occurrences. I have three query terms that need to be near each other, so I want to use the contains near operator. But one of the query terms is either "no" or "not". When I t...
Categories: DBA Blogs

Getting word count from context text index by joining to indexed rows

Tue, 2017-10-17 13:06
Hello Tom, I would like to be able to roll up a word count (for a WordCloud control in a UI) based on a query ( a spatial query in this case). I have a table as such <code> create table items (id raw(16), text clob, geom sdo_geometry ) </code...
Categories: DBA Blogs

end loop commit; on the same line gives no error

Tue, 2017-10-17 13:06
Hello, I created a simple table. <code>create table test(t number);</code> I tried to insert values in it. I created o simple PLSQL block to insert values. <code>declare i integer; begin for me in 1..2 loop insert into test(t) va...
Categories: DBA Blogs

Parallel DML Update without any parallel hint or parallel degree

Mon, 2017-10-16 18:46
Hello All, In our production environment, one DML update is running in parallel and which is sporadic in nature. Please note the session is enabled for parallel dml. Find below one pseudo code of that update statement. --------------- <co...
Categories: DBA Blogs

Getting ORA-01861 literal does not match format string error in stored procedure

Mon, 2017-10-16 18:46
Hi, We have a requirement to get the date in 'yyyy-mm-dd' format but unable to do using below procedure, <code>create table emp_test (emp_id number, customer_due_date date) / create table dep_test (due_days number) / <Insert>, insert...
Categories: DBA Blogs

How to replace sqlloader control file with external table

Mon, 2017-10-16 18:46
Hello, I have a flat file structured like this: HH00001 0000000048JZFRV288SNGAA814 DD11111111 desc1 000000000000000000012 100002 DD02324234 desc2 000001200000000000000 00 300012 100002 DD5453...
Categories: DBA Blogs

Move lob index to a different tablespace

Mon, 2017-10-16 18:46
Hello, I have a table which contains one CLOB column and there is an Index on that CLOB Column , this table is on TEST tablespace. My question is: how to move CLOB Index only on TEST_IDX tablespace without moving the column along with the Index??? ...
Categories: DBA Blogs

Pages