Questions

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...

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 ...

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...

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...

how to download a free oracle magazine in pdf about getting latest oracle database and it's technology relevant information as like MSDN of microsoft....

hi,Refer to object-relational-developers-guide/Nested Table Locators‘For large child sets, the parent row and a locator to the child set can be returned so that the child rows can be accessed on demand; the child sets also can be filtered. Using nested table l...

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...

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 i just wanted to ask you the (CASE WHEN THEN END;) is the ternary operator in oracle sql - pl/sql? thanks. I provided some example code above of java and pl/sql thanks!.<code>/*plsql code*/DECLARE myage NUMBER := 28;statusage VARCHAR(200) := NULL;BEGINst...

This query:<code>with T1 as (select 'A4' from dual unionselect 'A3' from dual unionselect 'A100' from dual unionselect 'A20' from dual )select * from t1;</code>produces the normal sort:<code>A100A20A3A4</code>In recent Oracle versions ( 19 and later ) is there...

Hello,I know PL/SQL package DBMS_OUTPUT and I know the procedure DBMS_OUTPUT.PUT_LINE.I see sometimes in code published on the web usage of DBMS_OUTPUT_LINE procedure.I would like to know if this procedure has ever existed and if yes in which Oracle version.Th...

Hello All,I encounter an error trying to use a SQL MACRO (TABLE) in freesql.com, in both versions 26ai and 23ai.The function top_n compiles successfully, but trying to call it fails:<code>create or replace function top_n (p_table DBMS_TF.TABLE_T, p_rows NUM...

Hi,How can i connect to SQL developer using oracle wallet based security.from sql plus we can connect using the command like /@sid, but how can i connect to SQL developer (or) toad using the wallet based security.Thanks in advance. ...

Hi all,I have a problem with liveSQL. I can't create a sharable link. Even with FreeSQL, can't do so. Appreciate any help.Thnks...

<u></u>table has two rowsselect id,value from IIO_SYSTEM_PARAMETER;200 1027/1029/1000199 1027/1029/1000when I ran the below query, it is giving 7 records, instead of 3 records<b>query1:</b><code> SELECT REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) DEST ...

I am troubleshooting a severe storage efficiency issue on a batch logging table in our Oracle 19c database on the below tableTable DDL: <code>CREATE TABLE APP_BATCH_LOGS( BATCH_ID VARCHAR2(100 CHAR) NOT NULL, -- Partition Key PROCESS_ID...

When querying a mounted database, an ORA-01219 occurs when querying anything besides instance views such as v$database. This is even the result when using a WITH clause that, itself, is an allowed query for a mounted database. For example:<code>SQL> select sta...

I'm trying to create a database link using the <b>CONNECT WITH</b> clause that's mentioned in the documentation for CREATE DATABASE LINK so that I can use a database credential in the DDL statement.The doc I'm referring to is : https://docs.oracle.com/en/datab...

A tuning book says window functions and the 12c row limiting clause often fail to trigger Top‑N Stopkey even with a perfect index, so I tried to reproduce that behavior and saw the same thing—ROWNUM uses stopkey, but ROW_NUMBER() and FETCH FIRST don’t. Here’...