Questions

I'm using a UDT with dataset inside there as a table and i want to use that UDT on Merge statement. When using that UDT, i put that UDT inside "ON (" as following: "... ON (SELECT * FROM TABLE (UDT)) ..." then, how can i to use that UDT inside INSERT statment ...

Hi Tom,first at all, thanks for all your support. I wish a blessed christmas time for you, your team and families.I´m looking forward for the best practise of a cursor to fetch just one (or a few) row(s) on my own sorting and lock them. Without locking the who...

We have a production DB that is used for reporting is been attempted to clone with NetApp Snapmirror technology. The current clone is done with old method placing the tablespaces of PROD DB in backup mode and then copying the files using OS “cp” tool to the d...

I'm using external tables everyday to load csv files but I'm having great difficulty loading my first FIXED WIDTH file. I've tried several different access parameter variations and the CREATE TABLE command compiles without error but when I select the table, I ...

I am trying to create an sql script that creates a user and if a password is supplied as a parameter to use it and if no parameter is passed have the sql prompt for the password.This is what i tried but I am obviously a bit off since it asks for the Value of &...

<code>Team,Good Monday Morning !!!!!!we are facing difficulty in sending mail through oracle code, for the data being retrieved from one or more data base tables .(Issue arises when the data is huge)The records fetched from the query shall be in the form of H...

Oracle documents say Log buffer is normally small compared with the total SGA size, its key ratio is the space request ratio: redo log space requests / redo entries.Could you tell me what is the key ratio we are looking when adjust log buffer in initsid ora fi...

Hello experts,I am in 12.2, multi-tenant architecture, no RMAN catalog, auto backup control file.I have a problem to restore with RMAN a deleted tablespace.I create it and I made a complete backup of my container with the PDB and the tbs.<code>SQL> CREATE TABL...

Hello,I've been struggling with "strange" behavior with handling "extreme " timestamp values.Our applications in .NET execute stored procedures with timestamp parameter and in some cases pass "31/12/9999 23:59:59.9999999" - it is value of Datetime.MaxValue fun...

Hi TomGreetingsWe had very high critical Application(with zero downtime) running in 4 Node RAC Environment with same HA Configuration. We are planning to perform defragmentation(Index) with minimal downtime.We are planning the following steps1) Disable Apply a...

I want to get difference between two dates say(10-FEB-2000 - 02-JAN-1999)and the result has to be displayed in following format1 year, 1 month, 8 days ...

Hello,We use Oracle 19c with c# ADO and EntityFramework.We have performance issue when use ALTER SESSION SET NLS_COMP = LINGUISTIC NLS_SORT = BINARY_CI;Our system use this for all queries. I use this simple table for test:<code>CREATE TABLE app."Customer" ( "...

Noticed 2 databases ran on the same disk got different performance on same query in identical plan. Ran AWR and saw big difference in IO stats section. i.e Buffer Cache Reads is in us level but another one is in ms. What could caused this difference if on the ...

Hi Tom,I’m encountering an issue with my Oracle Database 23Free. It throws the ORA-12954 error: "The request exceeds the maximum allowed database size of 12 GB." After investigating the database files, I discovered that the SYSAUX tablespace is the main contri...

Hi ask Tom team, at first sorry for my lacked knowledge of DBA. I have a BLOB Table and BLOB total Length from the beginning ist over 3GB, and ofcourse the TABLESPACE size is more than 3GBNow I deleted 3GB BLOB Files and only around 30MB left. But I cannot shr...

Hi Tom, First of thank for your great work about helping so many people with brilliant explanations example.My question is, when I am creating a table with primary keys, and then regenerating the redo data/statements information using log miner ordered by SCN,...

I have three tables. Clients, Invoices and Payments and I try to make a sum of invoiced amount and a sum of payments in the same scriptIf I run the sum of invoiced amount, all things are good, like belowselect a.id, a.name, sum(b.quantity*b.unitprice) from cli...

Kindly ask for Your opinion regarding ORACLE_SID setting of databases in standby configuration ..We have different settings in our standby databases and both configurations are working without any complains.• ORACLE_SID = db_unique_name ( ORACLE_SID in...

We have nagging plan stability issues which are often traced back to bad cardinality estimates from our time dimension table. There are numerous workarounds (hinting, baselines, profiles, etc) but it would be so much easier if the CBO just got it right. I thin...

Hello,We have a batch process that commits on completion of each insert, which involves just a few bytes. It should ideally be done in batches (commit after processing 1000 entities etc). It is a legacy application and modifying the code is not possible. Will ...