Questions

Oracle provides an initialization method that will automatically run when the package is first accessed within a session. My question, is there a method that gets called when the session ends?The reason I am asking this is because I have a package that connec...

Hello Tom,What is the practical implementation of Instead of Triggers in the application? Can you provide some practical examples?...

Hi Tom,I'm trying to get rid of a performance problem in APEX with a pipelined function within an db-package. Reading ora-documentations and searching the web/comunnities wasn't successful. Sorry if my question is already answered - i haven't found it.My situa...

Greetings,How does the data structure of a compound index in Oracle look like? I'm wondering how Oracle is able to use a "skip scan" by using the second entry in a compound index? I know that the first field uses a b-tree structure, but I am unsure how the s...

I completed the course Database for developers foundation. I did not recieve an email of the certofocate but i did have the option to print the certoificate. but I want to have a URL that can show as proof of creidentials. where to get it? Please help I invest...

Hello All,I have a PL/SQL function that takes two INTERVAL DAY TO SECOND parameters and returns a value of the same data type.It works correctly as a regular PL/SQL function.I try to convert it to an equivalent SQL MACRO, both as a TABLE (in 19c and 23ai ) and...

Starting with 21c (and continued in 23ai) I've noticed that Oracle database does not "automatically sort" data when working with set operators such as UNION, MINUS, INTERSECT etc, while it used to sort data till 19c. Is this behavioral change documented somewh...

Hi Tom, I am practising Recovery . CASE 1)I have Development TEST DB in NON-ARCHIVE Mode. NEVER backed up. All Redo log files including the active one ,are dropped from the OS. How to recover such a DB. I dont mind loosing Transactions. Is it possible ?C...

I got a requirement to capture user login logout timestamp of database in any method like login via application, Sql development tool or any source. the purpose is to track all user login/logout information of database.We tried with logon and log off trigger b...

Good Morning.My question is sending signed emails from oracle. We were hoping to utilize an oracle built in functionality but it seems oracle requires a hardcoded passwords. Even oracle support stated I had to hardcode the password. I am coming here because I ...

Hi!We have a lot of Apex apps, so we check the LAST_UPDATED_ON value in both the production and dev apps before deploying to production.Like this:<code>select d.APPLICATION_ID, d.APPLICATION_NAME, d.ALIAS, d.OWNER DEV_OWNER, p.OWNER PERP01_OWNER, d.LAST_UPDATE...

Hi, I am dealing with dynamic sql statements that differ only in amount of "in list" elements and want to force a specific plan on them. Simplified example:<code>1)select * from tab1 t1 join tab2 t2 on t2.col2 = t1.col1 join tab3 t3 on t3.col4 = t2.col3 wh...

I have a PL/SQL package and want to find that package's code coverage. I am using the DBMS_PLSQL_CODE_COVERAGE utility. I can get the code coverage for the whole PL/SQL package. But I want to get the coverage at the Function/Procedure level.The coverage tables...

Hi!, What is the best way to defragment SYS.AUD$? It is possible to use DBMS_REDEFINITION.REDEF_TABLE ?Thank you in advance....

Hello Sir Is there any view avaiable through which we can know from which file our instance has started.Is it pfile or spfile.Without making any changes to file and then seeing it by opening it.Thanks ...

In 11g RAC, the init.ora file points the spfile in ASM, so sqlplus can find it.but in 19c, sqlplus can startup even without the spfile nor init.ora,how does that happen?(I understand when I startup with svrctl , it will create a init.ora automatically)Thank yo...

Hello,I'm trying within a trigger to compare two clobs to see if there is any change. I am trying to prevent any unnecessary writes.Prior to writing to audit trail I compare two values.<code> v_clob_compare := dbms_lob.compare( :old.clob_text, :new.clob_text ...

While we are working on dbms_cloud API in ATP DB, we are facing some issues. When we are calling the API directly from SQL Developer, it works perfectly fine and data is getting loaded into the tables.<code>BEGIN dbms_output.put_line('Start Time : '||TO_CHAR(...

HiI'm trying to understand the shrink space behaviors when dealing with multiple tables with multiple lob columns sharing a single tablespace. We've 3 tables (e.g. T1, T2, T3) with each table having a lob column. And the lob columns are stored in a single tabl...

It has been stressed by Oracle, for example by The Oracle Real World Performance Team, that Dynamic Connection Pools can be a killer for database performance and should be avoided.We therefore strive to persuade application server administrators to set up Stat...