Tom Kyte

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

sqlcl command line tool warning

6 hours 2 min ago
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

6 hours 2 min ago
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

6 hours 2 min ago
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

6 hours 2 min ago
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

6 hours 2 min ago
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

6 hours 2 min ago
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

6 hours 2 min ago
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

6 hours 2 min ago
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

dbms_ldap.bind_s vs dbms_ldap.simple_bind_s

Mon, 2017-10-16 18:46
Hello Tom, I will need your help in understanding the difference between DBMS_LDAP.BIND_S and DBMS_LDAP.SIMPLE_BIND_S ( DB version is 11.1.0.2 ). Thank you!
Categories: DBA Blogs

Memory_target or sga_target+pga_aggregate_target for dw databases.

Mon, 2017-10-16 18:46
Hi I don't know but I have been told, that "12c db creation wizard doesn't even allow AMM (MEMORY_* parameters) anymore for databases with more memory than... 4 G if memory serves me" So what is the current wisdom for big (?) dw databases ? S...
Categories: DBA Blogs

IN (vs) EXISTS and NOT IN (vs) NOT EXISTS

Mon, 2017-10-16 18:46
Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. Because I have read that EXISTS will work better than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). Regard...
Categories: DBA Blogs

Pages