Questions

I’m using a Polymorphic Table Function (PTF) and adding a new column TIMESTAMP_CALCULATED of type TIMESTAMP with fractional seconds scale 9. In FETCH_ROWS I populate it with a constant value:2026-01-01 01:01:01.123456789However, when selecting the PTF output, ...

Hi all,I have a hierarchy between levels in my_hierarchy table (between id_lvl and id_lvl_sup).Each level (lvl) may have items (id_item), and each item has a category (id_item_sup). These data is stored in my_items table.Now I want to build a report based on t...

Hi Tom,I got a Web Form that is able to display data to users and read user input back into the database. It is very important to be able to read user input back into the database. This is why I did not use out-of-the-box solutions like "owa_utils.read SQL int...

I would like to be able to analyze large PL/SQL code base to identify internal PL/SQL subprograms called in PL/SQL application code.I know that I can get package level dependencies with DBA_DEPENDENCIES and that PL/Scope should give subprograms level informati...

Hello Connor/Chris,Hope you are doing well.Recently, I came to know about the PART_NAME parameter for multiple APIs of DBMS_REDEFINITION package from the documentation. However, I could not find much about what kind of changes are permitted (and prevented) at ...

Hi TomHow do I confirm that the following event has been set:SQL>alter system set events '12899 trace name errorstack level 3';?ORA-12899 has occured after the event was set but no trace file is found in the udump catalog. The event was not set by me and I am...

HI TOM,Tnx for this oppurtunity again, Can You explain how does the ORA-4068 occurs and how can I go about it? How does oracle manages the freelist (8i) on LMT's? And how can we track the freespace, used space, and fragmentation on LMT's?What's the difference ...

Hi,I have created an application that is tracking a set of processes that are being run from a master table. When a process is completed, a flag in a column name PROC_EXEC_STATUS changes from N to Y for each process. I want my oracle apex application to refres...

Hi Tom,I have SQL HC for a SQL ID which shows the query have two Hash Plans one is Optimal and another which elapse a lot. And my query is taking long time to run which means that it is using the worst plan.I want to force my query to use the Optimal plan.Plea...

We run a production Exadata x8 and a dev Exadata x8 that sit next to each other in our datacenter and share NFS mounts. When refreshing the lower environments, i.e prod --> dev, I simply run an export and import since the refreshes only need to have one schem...

We don't seem to be able memoptimize a table for read when there are triggers on it (this seems to be undocumented however ?).But: we can add the triggers later on without problem and the table continues to be memoptimized for read.Question: WHY ? Is it a bug ...

HiSorry about the database version which I have mentioned while filling out this form.This is a generic question, kindly consider it as for the latest stable release of 19c database.As per my understanding a "connection" is a physical path and a "session" is m...

<b>My goal</b> is to create a fast refreshable materialized view (MV) that holds all the combinations of records in specific related tables. So also the 'incomplete combinations'. So the query that is needed contains a full outer join.<b>First a disclaimer abo...

Hi folks,I wrote a procedure that downloads spatial information for power lines via WFS from a web site and merges this into an existing table in my ADB. I wrote it in a way that it should use the parallel mode (pipelined table function, no triggers, jala jala...

creating unique key and primary key on same column is possible? already unique key and not null constrains there on same column but still i want to create primary key without dropping unique key. ...

I am implementing a 4-Schema SmartDB architecture for an Oracle APEX project to strictly enforce the principle of least privilege. My schemas are: DATA_LAYER (Tables), LOGIC_LAYER (Private Logic), API_LAYER (Public Interface), and APEX_UI_LAYER (Parsing Schema...

How can you make an oracle table, index or partition 'lose' its storage definition and revert to tablespace defaults...I have a 'legacy' table in production that was built with storage parameters inherited from the 'consolidate storage' option on the old expor...

Hello , during the connection 'storm' (for example after restarting app servers , because of DB maintenance tasks )i observe high amount of these commands: <b>update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where us...

Hey Guys,We are attempting to export selective partitions of a table and would like to filter records for each partition. Would you please explain how we can achieve using one parameter file? Below is an example of the parameter file that we are using.For exam...

Hello, Thanks to this forum a lot of my answers were answered. I noticed that most of the queries in an application that am working on is using the MATERIALIZE hint in the subqueries. Our queries heavily use CTEs and relies on multiple joins. Our query perform...