Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Back Soon

Why can't I ask a question right now?

Thanks for all of your questions. Even 28 Sand Lizards can't keep up with the volume. We'll be taking new questions again soon

Questions

QuestionUpdatedAsked
Clob vs Binary XML storage
Hello Team,While doing poc for storing XML in ClOB storage and Binary XML storage ,I could see storing XML in Binary XML takes less table space as compared to CLOB .As far as I know both store XML in LOB storage.so why there is difference between space taken ...
3 days ago3.2 years ago
ora-24247 when making an https call
Hi,I have a problem when making an https call inside a package. It doesn't appear to recognise the privileges granted to access the acl.When I call utl_http.begin_request in an anonymous plsql block or in a procedure with authid defined as current_user I get a...
3 days ago3.7 years ago
How To FULL DB EXPORT/IMPORT
Hello Tom, How to do a Full DB exp and Import.I do say exp system/manager@xyz FULL=Y FILE=FULL.DMPThen If I want to do a full Import to a new freshly created DB which only has the default schemas sys , system , etc.1) Please correct me :- I would n...
3 days ago10.9 years ago
enable trace for specific schema
Hi,We are having slowness issues so, I would like to activate the trace for 1 hour in my database for an specific schema, I have reviewed in several websites and I have these steps:1- ALTER SESSION SET TRACEFILE_IDENTIFIER = "TEST_TRACE";2- ALTER SESSION SET E...
3 days ago2.8 years ago
ACL created but accessing gives ORA-29273 ORA-12541
I have created a ACL and assigned it to a host. When accessing I get the above erros.I did the following stepsSQL> exec dbms_network_acl_admin.create_acl(acl=>'testlitle.xml', description=> 'all hctra.net connections',principal=>'TAG_OWNER't=>true,privilege=>'...
3 days ago3.8 years ago
Unable to find EMP & Dept in Object Browser (APEX oracle)
i have recently made an account on APEX oracle, my trainer illustrated how to make the account on Apex and use it for training purpose PL/SQL, therefore i tried by myself when i make the account and go to object browser there i am unable to find any table, vie...
3 days ago1.7 years ago
Uncommitted transactions in Data Files and Redo Log files
Hi Tom First of all I want to inform you that, I love you very much and I have a big respect to youI have a question related uncommitted transactions. I know that while we issue COMMIT, LGWR writes committed and uncommitted transactions from redo log buffer in...
3 days ago10.6 years ago
HA and Failover in Oracle RAC
Hello, Ask Tom Team.I have some many questions about Oracle RAC HA and Failover.I was reading the info in below link and it help me a lot. But I still have some questions. https://asktom.oracle.com/pls/apex/asktom.search?tag=failover-in-racI have Oracle RAC 18...
3 days ago3 weeks ago
Complex Query
I have a large number of orders (200) involving around 2000 diferent products and need to group the in batches of 6 orders.The task is to identify the best possible groups of orders so performance (human performance) can be maximized.As a start I have a script...
4 days ago9 days ago
Can we call a procedure in select statement with any restriction?
hi tom plz tell me in simple example explanationCan we restrict the function invoke in select statement.Can we call a procedure in select statement with any restriction?...
5 days ago3.7 years ago
Highest salary from each department
Hi Tom, Can you please tell me the SQL query to get the details of employees having maximum salary from each department without using analytic functions?EMPLOYEE Table ColumnsEMP_IDEMP_NAMEEMP_SALARYDEPT_IDIt should display all the columns from employee table....
5 days ago3.6 years ago
Please validate the database design of ticket history
Please validate the database design and help is there any better way. I'm designing database for tracking history of a ticket. the ticket history will store the each change as entry in history table. Please ignore, I have not added created by, created date col...
5 days ago9 days ago
Will defining variables be faster than reparsing an update statement?
Good day "Toms",I am working on a project which performs bulk updates of millions of rows. Unfortunately each update is currently a separate update statement which updates 2 columns with unique values and uses a where clause with a unique ID to identify the ro...
5 days ago7 days ago
Subtract date between a given start date and end date
Hi Tom,we have leave management system where user can select a date range to schedule his leaves. Ex. User a inputs date as 05-May-2019 to 23-Jul-2019. Later on he can cancel few days from this date like, 30-May-2019 to 31-May-2019.How can I map his leave as 0...
6 days ago9 days ago
Excel file upload from client getting ORA-22288: file or LOB operation FILEOPEN failed Access is denied
Hi , It's working fine if excel file is in local systemusing this command :<code>( SELECT NUMBER_VAL from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DATA_EXCEL', 'SCALING.xlsx' ), '1:1', 'G2' ) );)</code> but when I shared my excel on the network ...
6 days ago9 days ago
Gather Stats while doing a CTAS
Can you please provide your opinion on the below point. This is what I have noticed. When we create a table using a CTAS, and then check the user_Tables, the last_analyzed and num_rows column is already populated with accurate data. If it is so, is there a nee...
6 days ago9 days ago
Password change of remote database user by dblink but showing error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another database &#x27;SYS_DBALINK"
HI,i created a form in Oracle Apex and when i click the submit button then Stored Procedure runs that changes the remote database user password over dblink but iam getting the error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another database ...
6 days ago2 weeks ago
How to detect if insert transactions in oracle db are really slow?
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation:Some production devices send data results from tests to Server A, this server is a important server and it replicates the info t...
9 days ago1.5 years ago
How can application control to explicitly call OCIStmtPrepare2 rather than OCIStmtPrepare when using pro*C
Our application got an ORA-25412: transaction replay disabled by call to OCIStmtPrepare.Oracle Version: 12.2.The Oracle runs in RAC mode.After searched on the internet, we found below explanation: <i>This call(OCIStmtPrepare) does not support Application Cont...
9 days ago2 weeks ago
'BEFORE CREATE ON SCHEMA' trigger apparently not firing before Create Table
In Oracle 8.1.7 instance set up with characterset US7ASCII<code>Connected to:Oracle8i Enterprise Edition Release 8.1.7.4.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - ProductionSQL> create table t1 (c1 varchar2(3));Table created.SQL> cr...
10 days ago2 weeks ago