Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 3 weeks 2 days ago

Impact of Index Monitoring in production server

Mon, 2019-11-11 11:49
Hi, we are planning to change global index to local index for partitioned table in production. Since we need to drop an index and create it again and we don't know the time it will take, we decided to monitor the index which are being used by the app...
Categories: DBA Blogs

Generating DDL in Oracle SQL Developer

Mon, 2019-11-11 11:49
Dear AskTom-Team! Is there a possibility in the Oracle Developer to suppress duplicate DDL code? For example when generating the DDL from my relational model the foreign key constraint is generated twice, i.e. for both tables that are involved in th...
Categories: DBA Blogs

Clustered Indexes

Fri, 2019-11-08 02:48
Is clustered Index is faster than Non-clustered Index?
Categories: DBA Blogs

Platform list on whicb JAVA_JIT_ENABLED paramter can be enbaled

Fri, 2019-11-08 02:48
<b>Question:</b> As my application is using some JAVA function as UDF using <b>loadjava</b>. So, developers are suggesting to set <b>JAVA_JIT_ENABLED</b> parameter as <b>true</b>. I have read (https://docs.oracle.com/cd/B28359_01/server.111/b28320/in...
Categories: DBA Blogs

How can i extract data from a URL string in SQL?

Fri, 2019-11-08 02:48
Hi TOM, i am trying to extract some data from a URL and running into some issues. The URL's will look something like this: http://192.168.201.242:8000/express_get?time=$startDate&from=$phoneNumber&to=$CAMPAIGN&Id_agent=$idExternalUser&spent=$durat...
Categories: DBA Blogs

JAVA CLASSPATH Specificaiton

Fri, 2019-11-08 02:48
Hi, We have an Oracle 10g database running java on the database as java stored procedures. It is hosted on an IBM AIX frame. There are both 64bit and 32bit jvms installed. How do you specify the java classpath for java running in the database? W...
Categories: DBA Blogs

Cursors

Fri, 2019-11-08 02:48
Hi Tom, The Oracle users in the world should be thankful to you for all your help all the way .Many thanks for that. We are learning many things about Oracle that we could not learn by reading several Oracle books (other than your books) though ...
Categories: DBA Blogs

Alternate to pro*c batch programming

Fri, 2019-11-08 02:48
Dear Team, We would like to have suggestion on the topic related to alternate programming for the existing batch system in pro*c with oracle 12c database. Currently we have many number of batch jobs which are in pro*c (by interacting with oracl...
Categories: DBA Blogs

Create materialized view with ENABLE QUERY REWRITE for SDO_RELATE getting ORA-30373

Fri, 2019-11-08 02:48
I write a query like this: <code>CREATE MATERIALIZED VIEW MV REFRESH FORCE ON DEMAND start with (sysdate) next (sysdate+1/1440) ENABLE QUERY REWRITE AS SELECT O.ID DIREK_ID, MAX(LK.ADI) ISLETME_GERILIMI FROM xxx O, yyy AA, SY...
Categories: DBA Blogs

Copy row value down until next non-null row

Fri, 2019-11-08 02:48
Hi, Is there a simple way to achieve the following output in SQL? Wanted to populate the null values with the Country name until there is a new country name. Thanks with data as ( select 'Afghanistan Afghani' as country_currency, N...
Categories: DBA Blogs

Problem with very large varchar2 sorting in oracle 10g r2? Sorry, I can't verify my problem in LiveSQL because I need to run it in Oracle 10g r2

Fri, 2019-11-08 02:48
I am very sorry to waste your time with something that is probably VERY SIMPLE, but IT IS something I cannot understand ... I must be obfuscated <code>select level, lpad(to_char(level), 1000) from dual connect by level<=20 order by 2;</code> ...
Categories: DBA Blogs

Change column to row

Fri, 2019-11-08 02:48
I have a data like below in a table <code>create table test ( sr varchar2(1),col1 number,col2 number,col3 number ) ; insert into test values ('a',1,2,3); insert into test values ('b',4,5,6); insert into test values ('c',7,8,9);</code> Want...
Categories: DBA Blogs

Global non partitioned index on table partitions

Fri, 2019-11-01 18:47
Hi, I have recently got some sql statements that is not performing well. <code>select * from v where a=? and b not in(,,,,....) and c =? and rownum<-100 </code> where v is a view. Original sql statement is similar to above statement. From explai...
Categories: DBA Blogs

Event Based Job is not working

Fri, 2019-11-01 18:47
Hi, I am struggling several days with following issue. I am trying to implement event based job. At start all was working fine. But after several payload type modifications + several times recreated queue + recreated scheduled job ... schedu...
Categories: DBA Blogs

Database Design Question

Thu, 2019-10-31 09:47
Hello, Ask Tom Team. I have a table A with columns (client_id,invoice_number,invoice_type) with a composite primary key (client_id,invoice_number). At business level there are 5 invoice_types (70,71,72,73,74). The invoice_number always brings the ...
Categories: DBA Blogs

finding out the source of the data in a table

Thu, 2019-10-31 09:47
Hi Tom, I am working on a database application, and i need to know how a table is being populated in the database schema. I have tried querying xxx_dependencies and xxx_source but of no use. I believe that this table might be populated from an...
Categories: DBA Blogs

How to change the plan of a query in execution??

Thu, 2019-10-31 09:47
So I often face an issue when a query generates an execution plan assuming wrong carnality and since it assumes it as 1 it goes into a M3RG3 CART3SIAN join. Now if I gather the stats the query does not automatically picks up the new plan. Is there a ...
Categories: DBA Blogs

List of event codes for traces

Thu, 2019-10-31 09:47
Hello Masters, Can you give me a link on docs.oracle.com where are listed all the system event codes for the trace like 10046, 10053... and, most important, with there signification? I know the 10046, 10053 codes but I am sure there are many ...
Categories: DBA Blogs

Performance of a VIEW on multiple tables (historical plus current)

Thu, 2019-10-31 09:47
Good afternoon, I have a customer that manage millions of data with a lot of tables. The thing is that we have a big table from 2013 until now which we would like to divided in two, one for the last three months and one for the rest (which we call...
Categories: DBA Blogs

Restrict Application access to developers in same workspace

Thu, 2019-10-31 09:47
How to show a user only certain amount of applications in app builder(i.e user should not be able to see all applications in app builder)? In a Oracle Apex workspace, I need to create a new user(admin role) such that the new user can only see sele...
Categories: DBA Blogs

Pages