Questions

Hello. I have a query that produces a result. We need to automate the result into a text file with pipe (|) delimited characters that will be used in another process. The problem is that creating this file using the UTL_FILE tool will store it on the databas...

Hello, Ask TOM Team.I have an application (is like a report tool) that reads data from a 18c Oracle Database. One day the results return quick but a couple days after it gets slow. When I execute "ALTER SYSTEM FLUSH SHARED_POOL" command the the results return ...

Tom, I have a few questions related to autoextends:1. Can you tell me the advantage and/or disadvantage of having "autoextend on" for every datafile I create in the database? 2. If I have a tablespace with all datafiles created to have autoextend on, then I t...

Hi TomI have installed SAP on Oracle DB there is one table space PSAPSR3 got created & we have only 1 sapdata FS like /Oracle/FS/sapdata.So can you help me how auto extend will work , will it extend all table space if we set auto extend?Thanks,Nitin...

Hi Any idea on what is a #hash view and creating triggers on #hash view? If you know please leave a script/query for the same.Schema Consolidation:There multiple schemas, For ex:Schema1Schema2Schema3Schema4Now i want to move all the objects & data of schema2, ...

Hello,rather basic question. Does database guarantee to return rows from external table, OS file, in the same order as they are positioned in file.I am well aware that only proper order by clause can achieve this. But we have some 10+ years old database job wh...

Hi,Can we merge multiple PDF documents into 1 PDF using plsql function?If so please suggest me the solution.Thank You,Vidya KBangalore...

Hi Tom,Can you explain, what is 'EDITION' and what is it meant as cross-edition triggers? How it can be useful for high availability.Thanks for you replies. It is a big learning experience....

I was wondering if there is some possible way that I can fetch the last record from a table. In this particular case, obviously one is unaware of the number of records in a table. All I want is to write a query whcih will simply fetch all the columns of the la...

Hi,Thank you for reviewing my question. Two nodes RAC database version 11.2.0.41. Query is generated at the application server2. Persistent connection to the database using the same account 3. The query is using a lot of IO. When there is more than 40 ex...

Tom,I am little confused about Oracle Database and instances. If I have created a database DB1, and started it,open for all users, does it mean it is one instance? How can I have multiple instances of a database and how do I find what Instance I am using?So, ...

Hi Tom,I am trying to extract multiple hostname out from a long strings (In fact, I will use this method for DBLink - host column later). However, my query is only able to extract the first hostname. I did try to put {1,0} after the match pattern '((HOST|HOST...

Hello Tom,Supposed my data file got crashed at 9PM IST & we to do point in time recovery.We have a full db backup which was started at 8AM IST & got finished at 7PM IST in that do we need to apply redo log archive backup?Can you tell me how will come to know w...

Hello Tom,I just wanted to know use of Checkpoint & scn.I know the basic definition but want to know in what are all the situation it occurs & how it will be use in case of Oracle recovery.Can you please explain me with an example.Thanks,Nitin...

Hi there,Is there a setting I can tweak in the database to adjust this behaviour and thus avoid the error?There's a few workarounds, but I need* to avoid an application code change.Through Oracle Support this has been raised as Bug 32703286 - DBMS_LOB.WRITEAPP...

The error below shown when I recompile a trigger with a new version in a productive database. However, I can recompile the same trigger successfully in a test database (Both the productive and test database are in version 12.2.0.1.0).<i>Error report -ORA-00604...

I have a page which includes 4 regions. All are Classic Reports. I set "Yes" for "CSV Export Enabled" under Attributes/Download of each Classic Report. Is there any way to build in functionality that allow user to <b>export</b> all of classic reports to Excel ...

Good Morning,Strictly from a performance standpoint, if we only have one 24 CPU core production server to support the schemas of totally five different applications, will any one of these options perform significantly better:1.) Create one non-CDB database tha...

I have a recursive query as below:<code>with addresses as( select cust_id,address addr from ( select 10 cust_id,'9 Help Street, Level 4' address from dual union all select 11 cust_id,'22 Victoria Street' address from dual union all select 12 cust_id,'1495...

Hello,I am trying to get the cotization of a product at a given date.I have made a table:<code>CREATE TABLE PRICES ( NAME, VALIDFROM, PRICE ) AS SELECT 'GOLD', DATE '2017-01-01', 36000 FROM DUAL UNION ALL SELECT 'GOLD', DATE '2017-09-25', 42000 FROM DUAL UNI...