Questions

Hi Tom,we need to purge, ARCHIVE and cleanup our SYS.$AUD table.WE do have a RAC x 2 node as an Active DG too.We have a self-defined procedure , which is working appropriately (using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL).However the table is >700GB and >700 Mil r...

1. We have 2 node Oracle RAC 19c (19.3.0.0) database installed over Oracle Linux 7.6 x86-64 (UEK). We gracefully shutdown cluster on each node and restart it turn by turn. The secondary node started normally after server restart and joined cluster services. Ho...

Hi Tom!In my organization, an S7-2 sparc server was purchased in 2019 to run Oracle 19c SE 2. Version 21c was released for multiple operating systems except Solaris Sparc and now the latest version 23c is also not available for Solaris Sparc.Could you explain ...

It is probably apparent from my job title that my role is far from a dba. I am, however, a frequent user of Oracle SQL Developer. I work in mass appraisal and use SQL Developer to select, update, insert, delete, etc., on a regular basis. I would like to bring...

Howdy,Just curious what (if any) deleterious effects we would see in the standby databases in an active data guard set up from switching redo logs every 2-3 minutes on the primary?I’m also curious what would be the best diagnostic methods to troubleshoot issue...

I have a table as follows which is currently partitioned AND sub-partitioned on the same column PRD_DESC fied. The partitions are one per year and 12 monthly sub-partitions per partition<code>CREATE TABLE TESTTAB ( "PRD_DESC" VARCHAR2(6 BYTE)NOT NUL...

Hello All,I upgraded the version Oracle DB from Oracle Databes 12c Enterprise Edition Release 12.1.0.2.0 to Oracle Databes 19c Enterprise Edition Release 19.0.0.0.I'am trying to load data into my database using an external table.My external table definition:<c...

Hi Tom,I created the procedure send_mail as in your book in the user System. create or replace procedure send_mail( p_sender in varchar2, p_recipient in varchar2, p_message in varchar2 )as l_mailhost varchar2(255) := '10.228.1.75' ; l_mail_conn utl_smtp....

Dear Team,I have created stored procedure which return output in refcursor.If I run procedure by "right click => Run", then I can see output in tab "Log => output variables".But, I can't export output from there.Then I tried below command: --------------------...

Hello All ,can we able to find the select statement which is used for sys_refcursor ?<code>function f_my (id in number ) return sys_refcursor is l_rc sys_refcursor; begin open l_rc for select * from emp where empno=id; return l_rc; en...

Hello,I wanted to ask about new 23C feature - Schema-level Privileges: is it possible to combine it with "WITH GRANT OPTION"?According to documentation https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/configuring-privilege-and-role-authoriza...

Hi Tom,The company I work for has quite a number of Oracle databases,on the order of 200 or so, including development, test, preprod,production, etc, all hosted on Solaris, currently. We have a pretty large number of unix clients of various flavors, mostly So...

We have an application in Oracle APEX where we do not have a large database and the number of pages used in the application would be max 250. However, there is a 500 Internal Server error that we are encountering since the last 3-4 days and we have not made an...

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

Hi Tom,What plan do you suggest to purge a table which is of size 377Gb approx?We are currently following this plan,- Creating a temporary table with the required data(Approx 86 crore records).- Drop the constraints(15 constraints).- Truncate the original tabl...

In Oracle SQL Developer, 'Application Express' section, we can export DDL for an APEX app by context menu, 'Quick DDL' > 'Save to Worksheet' menu. I'd like to do the same by executing some SQL or PL/SQL code. I know we have DBMS_METADATA.GET_DDL, but that does...

Hi,I discovered the powerful, not officially documented, hint OPT_ESTIMATE about 12 years ago. Recently I discovered the following officiel document about it:Oracle Support Document 2402821.1 (How To Use Optimizer Hints To Specify Cardinality For Join Operatio...

Hello,I'd like to write a query based on dba_source/all_identifiers/all_dependencies to find all the queries/cursors that have more than x joins involved. Can you help me ?RegardsHans...

Hi,Could you please have a look at below table structure<code>drop table child purge/drop table parent purge/create table parent (id number(11,0) ,dt date ,constraint pk_parent primary key (id))partition by range (dt) (par...

Hi Tom,In one of the interview, interviewer asked me about the scenario where bitmap index is useful in OLTP system.and I was totally clueless.....I wasn't able to think any of the scenario.Later I googled every possible combination to get some info about the ...