AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 43 min 33 sec ago

99% wait time on SQL*Net more data to client

Tue, 01/21/2020 - 00:00
Hello Team, We are transferring 4 million records over the network and SQL*Net more data to client is 99%. We tried multiple drivers like ODBC, OLEDB , Oracle Drivers, QlikSense application Oracle drivers but the % didn't get reduced. We have ...

how to load XML file into oracle table and using XML query to get result

Sun, 01/19/2020 - 00:00
Hi Tom, I have XML file from Web Report. there are some error records in the file, it is difficult to find the error in XML file, I want to load this file into oracle table. then use XML query. below is sample file and expected result. This is fir...

Benchmark summary processing technique

Tue, 01/14/2020 - 00:00
I have a package that is responsible for processing data from Detail tables. <b>All query logic and other parameters that is used to process data is stored inside a table</b>, which acts as a configuration table (sample example of configuration table...

changing where clause using case statement

Tue, 01/14/2020 - 00:00
Hello Tom Is it possible to change the where condition (using case statement) based on certain variable? For example <code> var T varchar2(1) exec :T := 'E'; var E number; exec :E := 7788; var N varchar2(20) exec :N := 'MILLER'; sele...

Is “With clause” not allowed within a cursor?

Thu, 01/09/2020 - 00:00
Hi Tom, We need to drop old partitions in regular basis for some tables partitioned by interval. To keep 60 days data for the TEST table partitioned by day interval, for instance, I could get partitions name older than two months by using the f...

Time needed to move 1 datafile from 1 diskgroup to another in ASM.

Tue, 01/07/2020 - 00:00
Hi,we have 1 TB datafile in a diskgroup with normal redundancy. We are planning to move that datafile to another diskgroup with external redundancy. The reason for that is because we have a huge diskspace consumption and if we get rid of redundancy, ...

Tablespace TDE & RMAN Backups

Tue, 01/07/2020 - 00:00
Through attrition our team has lost the password to the Oracle Keystore (Wallet) on our current Oracle 12c database(A). We would like to backup this database and restore it to a new instance(B) which is also 12c. The database(A) contains TDE Tablespa...

Strange behaviour of Automatic Memory Resize

Wed, 12/18/2019 - 00:00
Hi, we are facing a strange issue behaviour with our database, we are encountering ORA-04031 error. ---------------------------------------------------------------------------------------------------- ORA-04031: 56 Byte des Shared Memorys konn...

xTTS Oracle Data migration from big endian to little endian

Wed, 12/18/2019 - 00:00
Hi Tom, We have a SAP Oracle database with large no. of objects count. DB Size is less which is around 500GB. But tables count is 75000, indexes are around 95000 and other objects around 10000 each. When we do the TTS migration from source to t...

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

Wed, 12/18/2019 - 00:00
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 ...

Merge Delete

Tue, 12/17/2019 - 00:00
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.


Thu, 11/28/2019 - 00:00
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...

Multiple Schema Oracle Wallet

Thu, 11/28/2019 - 00:00
Dear AskTom, I have a shell script that connects as several different users to the same database. From Oracle: You can store multiple credentials for multiple databases in one client wallet. You cannot store multiple credentials (for logging i...

Password change of remote database user by dblink but showing error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another database &#x27;SYS_DBALINK"

Thu, 11/28/2019 - 00:00
HI,i created a form in Oracle Apex and when i click the submit button then Stored Procedure runs that changes the remote database user password over dblink but iam getting the error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another...

HA and Failover in Oracle RAC

Fri, 11/22/2019 - 00:00
Hello, Ask Tom Team. I have some many questions about Oracle RAC HA and Failover. I was reading the info in below link and it help me a lot. But I still have some questions.

insert records into multiple tables

Fri, 11/22/2019 - 00:00
Hai bro, How to insert a records for multiple tables with out using INSERT ALL and with single INSERT statement only, is that possible ?


Wed, 11/13/2019 - 00:00
Hello Connor & Chris :-), A few days ago I found this fantastic SQL script checking Tablespace Free Space being written by Tom Kyte - <b><i></i></b>. So I spent some time formatting and aligning it (BTW c...

Local temporary tablespace

Tue, 11/12/2019 - 00:00
Since Oracle 12.2, Oracle has introduced a new feature of local temporary tablespace. The documentation does not provide any information on the pros and cons of this new feature. Can you please provide some information on why this feature was introdu...

Generating DDL in Oracle SQL Developer

Fri, 11/08/2019 - 00:00
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...

Parallel refresh of materialized view over database link

Wed, 10/30/2019 - 00:00
Hi Tom, My goal is to populate local table using data from remote database using PARALLEL hint. My problem is that the query is not being executed in parallel when it is part of an INSERT ... SELECT block that uses join. My test case: 1) Co...