Tom Kyte

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

DML Operation in PL/SQL Function

Thu, 2019-05-02 21:46
Sir, 1) Can I perform DML (Insert, Update, Delete) also into a PL/SQL function.....? 2) Can I call a Trigger Explicitly.
Categories: DBA Blogs

Migrating Oracle database

Thu, 2019-05-02 21:46
Hello, Thanks for taking up this question. There is an enterprise java application hosted on a huge (40 TB) Oracle database. Can this Oracle database be migrated to : 1. SQL Server or any other RDBMS? 2. Any No SQL database? I have been as...
Categories: DBA Blogs

When-Tree-Node-Selected question.

Thu, 2019-05-02 21:46
I've created a tree and it is populating correctly during the when-new-form-instance trigger, but my question is that when I click the mouse on a tree node, the when-tree-node-selection trigger fires properly and the node is highlighted. But wh...
Categories: DBA Blogs

Rebuilding local non prefix index raises ORA 02149: Specified partition does not exist

Tue, 2019-04-30 16:06
I truncated the partition on a table with local partition index. After inserting the records again in the same partition If I try to rebuild the index on that partition it gives ORA 02149: Specified partition does not exist. However i can see the res...
Categories: DBA Blogs

Combine json objects into one document

Tue, 2019-04-30 16:06
I need to combine some existing json objects into a new json object. Using the syntax: <code>json3 := json_object('json1' value json1, 'json2' value json2);</code> the existing json objects get wrapped in an extra set of double quotes, and t...
Categories: DBA Blogs

Query Rewrite to improve performance.

Tue, 2019-04-30 16:06
Hi, I have this query which takes about 45 minutes to populate the data into AFS_TABLE. The AFS_TABLE is truncated before this insert runs and it does not have any indexes or constraints. The Oracle version is 11.2.0.4. The VW_PAYMENT table is a very...
Categories: DBA Blogs

Writing data to a clob

Tue, 2019-04-30 16:06
Hi Tom how to write the table data into clob column in another table in oracle 12.1 version <code>create table abc(sno number(2) not null,file_clob clob); insert into abc(sno) values(1); insert into abc(sno) values(2); insert into abc(sno) va...
Categories: DBA Blogs

Get the most recent 3 transaction records for a customer

Tue, 2019-04-30 16:06
I have a customer session table that has session id and session date along with other columns. I want to see the 3 most recent sessions of each customer. Provided the sample SQL for creating data below: <code>CREATE TABLE Customer ( Custo...
Categories: DBA Blogs

Generate Random number and compare it for uniqueness in database

Tue, 2019-04-30 16:06
Hi Tom, I have a situation where I have to create a function which will return a unique number (Suffix R and 7 numbers) which will than be compared with data present in database with a particular column which is primary key. if the number generate...
Categories: DBA Blogs

Issue Global temporary table

Tue, 2019-04-30 16:06
Hi Tom I have Stored procedure return some data, <code>create PROCEDURE "SP1" (CV_1 IN OUT SYS_REFCURSOR) /*CREATE GLOBAL TEMPORARY table TT_TABL2 ( ORDER_ID NUMBER, REQ_ID NUMBER, TXN_ID NUMBER, ...
Categories: DBA Blogs

Oracle Insert

Tue, 2019-04-30 16:06
Hi Tom, How exactly big insert or update getting processed in oracle? Suppose I have to insert >=10GB more records in this table will this much of insert floods the db buffer cache or how it affect the SGA. Is it the same case with update...
Categories: DBA Blogs

problem to drop tables

Tue, 2019-04-30 16:06
hi i have a problem when i am trying to drop tables i get this error Error dropping CITIZEN: ORA-04098: trigger 'SYS.DELETE_ENTRIES' is invalid and failed re-validation thanks israel
Categories: DBA Blogs

.NET Core Connection String to RAC database

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. We have a .Net Core app connecting to a Oracle 18c RAC database. We are not using tnsnames.ora file (there is no oracle client installed on the client-server side). We are using managed .Net Core oracle client and the followin...
Categories: DBA Blogs

Stored Procedure behaves randomly - sometimes takes 15 minutes where it should take 15 milli-seconds.

Mon, 2019-04-29 02:46
We developed a procedure to be used in database-A and in database-A. In this procedure we send an input parameter on the basis of which a select statement searches the data from a table located in remote database-B. The selected columns are set in Ou...
Categories: DBA Blogs

Cluster Waits Rac database

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

High commit wait on RAC database

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

Redo log file size and Database Performance

Mon, 2019-04-29 02:46
Hi there, We have a database setup as follows 1) SQL server -- Where transactions are happening here 2) Oracle(DSS)-- we are transferring the incremental data from SQL server(NOT ALL THE TABLES ONLY 22 TABLES) with the help of a 24/7 running ...
Categories: DBA Blogs

Redo logs Sizing

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. What is the best practice: small redo log groups or have a few but with a bigger size? Thanks in advanced. Regards,
Categories: DBA Blogs

RMAN - Full vs. Incremental - performance problem

Thu, 2019-04-25 22:46
Hi. I am testing RMAN. I have run an incremental 0 and an incremental 1 cumulative test without having any database activity in-between these test. I am using OmniBack media manager with RMAN. I have three databases. Here are the timing results ...
Categories: DBA Blogs

Subtract time from a constant time

Wed, 2019-04-24 10:06
Hello there, I want to create a trigger that will insert a Time difference value into a table Example: I have attendance table Sign_in date; Sign_out date; Late_in number; Early_out number; Now I want to create a trigger that will insert la...
Categories: DBA Blogs

Pages