Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 day 18 hours ago

SQL query join by GUID

Tue, 2020-02-04 00:02
Hello, Ask Tom Team. I have to create a report to show some business data. The data to show is stored in two different Oracle databases. I have to write a SQL query joining two the databases (dblink). The two databases were designed for very diffe...
Categories: DBA Blogs

Oracle Flashback Data Archive

Sat, 2020-02-01 15:01
Hi All, We are using Oracle Flashback Data Archive in our database to track Audit in most of the critical tables. We initially planned to store data for 6months but there are few errors / issues we face making it unstable. So we have it as retentio...
Categories: DBA Blogs

Java procedure for host calls on Unix environment

Sat, 2020-02-01 15:01
Steve, I'm looking for a Java-procedure executing host calls on a Unix environment from the Oracle-server. I know the standard way of doing it by means of ProC (for Oracle versions before 8.1) but I do not have the ProC compiler available. I do...
Categories: DBA Blogs

Audit Vault and Database Firewall licensing

Sat, 2020-02-01 15:01
Dear Experts, Imagine that a partner as 8 Database EE Embedded licenses for end users and 4 Database EE Full licenses for their own internal application. Now, they want to implement only one Audit Vault appliance to collect and audit logs for all th...
Categories: DBA Blogs

Memory parameters - simple and auto tune

Sat, 2020-02-01 15:01
Hi Tom, We have 16 GB of memory on Windows server. Database is Oracle 12.2.01. 6.6 GB is using Oraclekernel exe. We have set: SGA_MAX_SIZE = 10016 M SGA_TARGET = 6016 M PGA_AGGREGATE_LIMIT = 6000 M PGA_AGGREGATE_TARGET = 2900 M I hop...
Categories: DBA Blogs

Calling executable from Scheduler

Sat, 2020-02-01 15:01
<b></b><code></code><u></u>Hi Tom I have been trying to call an executable shell script placed on AIX 7.2 from Oracle 12.2 via following code: <code>BEGIN DBMS_SCHEDULER.create_job ( job_name => 'GEN_DAILY_CNT_FILE', job_typ...
Categories: DBA Blogs

Data Safe private endpoints

Sat, 2020-02-01 15:01
Hi, customer is using Data Safe with DB System on OCI, but it wants to use DB System on private subnet. Now is possible to use Data Safe only with a DB System on a public subnet but this is not acceptable for customer security department. When will ...
Categories: DBA Blogs

How To Pass a List Object From C# to an Oracle Stored Procedure?

Sat, 2020-02-01 15:01
Dear Sirs, I know you?re probably tired from shaving your yak, but I?ve been doing research on this topic for a few months now with very little luck. Is there a way you can pass a list object from C# over to a stored procedure? I was able to do s...
Categories: DBA Blogs

How to find last DDL / DML timestamp for IOT tables

Sat, 2020-02-01 15:01
Hi, One of my Customer is asking for last DDL/DML timestamp change for IOT tables. They have many tables and they need to drop some of them which is not used / modified / altered frequently. I checked internally for IOT and Normal tables using OR...
Categories: DBA Blogs

Unpivoting billion rows

Sat, 2020-02-01 15:01
Hi ask Tom team, Hope you people are doing great in 2020. I am working in migration team ,our goal is to take csv file from client and ultimately transform data as per our production table structure . Requirement :- Recenty we have rec...
Categories: DBA Blogs

To compare two same tables from different schema without primary key and not same number of columns

Fri, 2019-12-20 08:55
We have table 'CUSTOMER' in two different schema's. Both are not having any primary key and the column numbers in both table do not match(i.e schema1 table can have 97 column other schema table has 101).the column names are same which are present in ...
Categories: DBA Blogs

PL/SQL code in packages on DB vs on APEX sites - how it affect performance?

Fri, 2019-12-20 08:55
Dear Tom, As you develop in APEX you can move all your PL/SQL code into packages or you can put all on APEX. How this affect performance? I know that it is better to move code to packages to make quick changes and have more control over code. ...
Categories: DBA Blogs

PLSQL

Fri, 2019-12-20 08:55
Requ:- If table do not have records then need to be inserted if have already the END DATE column only update with an no.of years based on Terms( For example If Term year is 10, then 10*12=120 Months, means..10 years needs to added to the END DATE col...
Categories: DBA Blogs

Merge Delete

Fri, 2019-12-20 08:55
How do I delete with a merge statement? I want to update MERGE_DELETE_TEST2 to match MERGE_DELETE_TEST1 (think ETL). I cannot get merge delete to remove the row that exists in MERGE_DELETE_TEST2 that does not exist in MERGE_DELETE_TEST1.
Categories: DBA Blogs

Import Production Dump to new schema

Fri, 2019-12-20 08:55
Hi, We have a live project which has only 1 schema. We are upgrading some features and so we are going to release it as version 2.0 . My question is that the production dump which is only one schema has to be to imported to 4 different schema o...
Categories: DBA Blogs

Check Constraints and Explain Plan Filter Predicates

Fri, 2019-12-20 08:55
Why does the Oracle SQL Optimizer include a filter predicate for a Check Constraint when generating an execution plan for a SELECT statement? If the constraint is valid (according to DBA_CONSTRAINTS), then the table rows are all compliant with the co...
Categories: DBA Blogs

Dynamic filters and arriving bind variables for them.

Fri, 2019-12-20 08:55
Team, we have an application, that used to search using any kind of filters on any colums - something like below. the procedure is used to return the resultset to the application, based on the WHERE clause being passed as input. when running...
Categories: DBA Blogs

Error while relocating database service

Fri, 2019-12-20 08:55
Hello, Ask Tom Team. <b>My environment: </b> I have a database running on 2-node RAC. I created a database service with TAF and transaction guard srvctl add service -db dbprod -service dbprod1_xa -preferred dbprod1 -available dbprod2 -fail...
Categories: DBA Blogs

Clob vs Binary XML storage

Sun, 2019-12-15 17:54
Hello Team, While doing poc for storing XML in ClOB storage and Binary XML storage ,I could see storing XML in Binary XML takes less table space as compared to CLOB .As far as I know both store XML in LOB storage.so why there is difference betwee...
Categories: DBA Blogs

ora-24247 when making an https call

Sun, 2019-12-15 17:54
Hi, I have a problem when making an https call inside a package. It doesn't appear to recognise the privileges granted to access the acl. When I call utl_http.begin_request in an anonymous plsql block or in a procedure with authid defined as cu...
Categories: DBA Blogs

Pages