Skip to Main Content

Can't see the Ask Question button?

Connor and Chris will be back taking questions again soon. Click Notify Me to be emailed when we're back taking new questions.

In the meantime, 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.

Questions

QuestionUpdatedAsked

Greetings,I've seen when "cpu_per_call" limit is reached. ORA-02393 is sent to the SQL Plus. Is there a view that a DBA can query to find out if "ORA-02393: exceeded call limit on CPU usage" occurs to applications using the database since it isn't written to...
5 hours ago40 hours ago

I would like to know how to access date column from a table and use it as date filter for another large volume table..I have the following query that currently uses a date in the filter criteria and it gets completed in about 10 to 15 minutes.<code>select a,b,...
6 hours ago48 hours ago

good evening,I have a sql statement with the following information in v$sqlarea<code>select sql_id, address, hash_value, plan_hash_value from v$sqlarea where sqltext=<string to identify my query>sql_id |address |hash_value|plan_hash_valuecv65zdur...
22 hours ago43 hours ago

Sorted input:<code>select regexp_replace( 'three0, three, two, two, two2, two3, three, three, three, one1, one1', '([^,]+)(,\1)+(,|$)', '\1\3') from dual;three0, three, two, two2, two3, three, one1</code>unSorted input:<code>select regexp_replace( 'three0, thr...
28 hours ago2.9 years ago

We have several existing Oracle Directories set up to allow reading CSV files that work fine, and a couple of them work OK to Write new files. I have been trying to add a new Directory definition pointing to a different path and cannot get it to work. I am i...
33 hours ago47 hours ago

Hi,I have a requirement where I need to export DB dumps using DBMS_DATAPUMP api to the Object storage on Oracle Cloud Infrastructure.I found below statement on official document to export dumps at object storage using expdp utility. <code>expdp admin/password@...
33 hours ago8 weeks ago

DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS ...
33 hours ago11.8 years ago

Hello,I created a PL/SQL function that returns a list of open balances as a table result, where all amounts are converted to the currency provided as an input parameter:<code>function my_pkg.my_func (pi_currency in NUMBER default NULL) return amount_tab pipeli...
2 days ago4 weeks ago

Hi!I'm trying to send a post request with json:<code>{ "id": 12344444, "email": "ppppoddddddppp@gmail.com", "first_name": "", "last_name": "", "billing": { "first_name": "22222", "last_name": "", ...
4 days ago8 days ago

I have created a MV on UAT server and my MV view using a query which has remote connectivity to PROD and select only rights to these tables which has millions of rows around 10 lakhs in each table but after calculation output of query is 139-150 rows only. que...
4 days ago9 days ago

Hello TOM and Happy New Year!We have a table with a VARCHAR2(200) column that stores `names` with European accents: Valérie, Véronique, and so on.We would like to perform a CI/AI (case-insensitive, accent-insensitive search) on those values, i.e. people search...
4 days ago9 days ago

We have around 20 Aggregate tables in our EDW and planning to convert all these into materiazed views.Currently all the AGG tables are partitioned(range).Some of the base tables which form the materialized view definition are partitioned on different key and s...
6 days ago8 days ago

Dear team,I hope you are all doing good!I wanted to ask for some hints to fix the following problem:We have a huge dataset (>60 mio) of items. Each item has an item number. The item number has the datatype varchar2(50). The format of the item number vary from ...
7 days ago8 days ago

Hi Chris, Connor,I have an application SQL written in below format <code>select /* Q_ORDERS_001 */ order_id from orders;</code>However, when I queried gv$sql to see the sql information, I couldn't find the excat match of the sql.In gv$sql I got an entry with ...
7 days ago8 days ago

Team:This below testcase was run on Oracle 19.9 database.q1) why the online stats gathering feature doesn't kick in post the direct path load on "twitter_data" table?q2) could you please help us to understand though we have Mview inplace, why the optimizer is ...
7 days ago8 days ago

Hello...I am new to using materialized views (MV) in Oracle, and I am having problems making this one work. I have two tables, SWO_LINE and SWLN_ADD where I insert and update records.I have created MV Logs for both tables as seen below:<code>CREATE MATERIALIZE...
8 days ago8 days ago

Hi ToM,i have the following external table definition<code>CREATE TABLE STAGE.EXT_TABLE1 (FIELD1 VARCHAR2(16), FIELD2 VARCHAR2(2), FIELD3 VARCHAR2(10), FIELD4 VARCHAR2(16), FIELD5 VARCHAR2(10), FIELD6 VARCHAR2(15), FIELD7 VARCHAR2(8), FIELD8 VARCHAR2(2...
8 days ago8 days ago

Is it possible to return a named constant in a SQL query? In other words, let's say we want to return the equivalent of:SELECT 'Business rule violation: '||to_char(bsnss_rule) FROM violation_table;But, we would like to pick up 'Business rule violation: ' f...
8 days ago17.6 years ago

Tom: We have a 6 millons rows table and we need to clean it. This process will delete 1,5 millons.My first approach was create a SP with this lines: SET TRANSACTION USE ROLLBACK SEGMENT Rbig; DELETE FROM CTDNOV WHERE CTDEVT IN (4,15); (1,5m rows) COMMIT;Then ...
8 days ago19 years ago

I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is available fo...
8 days ago4.4 years ago