Feed aggregator

Validate constraint recursively run a SELECT with an "ordered" hint

AskTom - Hot Questions - Mon, 11/27/2017 - 00:00
I have been looking into minimizing the time it takes to validate a referential constraint. We want it to be validated for the optimizer to utilize join elimination during query rewrite. However, for a big table it takes a lot of time to validate ? a...

Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ?

AskTom - Hot Questions - Wed, 11/22/2017 - 00:00
Hello, teams:-) Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ? There has an example that I have given in Oracle 11.2.0.4.0. <code> SYS@orcl28> select round(sum(user_bytes)/(1024*1024*1024),2) fro...

export issue

AskTom - Hot Questions - Fri, 11/10/2017 - 00:00
Hi team, We are taking daily export of schema with expdp But for a few days we are continuously getting error saying - snapshot too Old. Table is a partitioned table weekly base. And the script which we are using for expdp is - expdp us...

how SPM works with cursor_sharing=force?

AskTom - Hot Questions - Tue, 11/07/2017 - 00:00
<code>Hello Tom The post https://blogs.oracle.com/optimizer/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact explains the interaction between cursor sharing and SPM quite clear. But I met some unexpected results if I set the cursor_...

SQL to identify duplicates of data groups.

AskTom - Hot Questions - Mon, 11/06/2017 - 00:00
Hi, I have a peculiar requirement to identify duplicates of data groups. So here is a sample data set. <code> PI BU PR AC ---------- ---------- -------------------- ---------- 1001 100 PR1 ...

Multiple query question

AskTom - Hot Questions - Fri, 11/03/2017 - 00:00
I have an Argos report that takes a query(s) and output a report based on those entries. My problem is there are 5 possible querys. I trying to use and/or ( I already tried the CASE function) to pulls data base on their entry. The queries are; Recei...

ASSM and basicfile LOB

AskTom - Hot Questions - Sun, 10/29/2017 - 00:00
Hello Tom According to https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB46175 basicfile lob must be stored in a tablespace with segment space manually managed. However, it seems oracle does not prevent user from creating it in ...

Make a simple audit of table connections

AskTom - Hot Questions - Fri, 10/27/2017 - 00:00
Dear Tom. I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries. An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will w...

Data Model (Design) Question

AskTom - Hot Questions - Fri, 10/27/2017 - 00:00
I have a around 2 million assets that send 15 min interval data for a given day (i.e) total of 96 intervals for a given day. What is best database modeling approach. My target RDBMS is Oracle. I'm partitioning by week and for each interval I need ...

Materialized View Fast Refresh from heterogeneous database via oracle gateway

AskTom - Hot Questions - Mon, 10/23/2017 - 00:00
Ora Masters, We pull a huge volume of data from source database (MySQL) to Oracle database (11.2.0.3) using Oracle gateway (12.2.0) through a remote database link. But the issue is that we receive following error when the MV is tried to refresh ...

Optimize Query

AskTom - Hot Questions - Tue, 10/17/2017 - 00:00
I want to optimize the following query SELECT t.merchant_id, t.brand_id, t.transaction_type, t.trns_currency_code, <b>COUNT (*) AS total_no_tx, SUM (t.mv_is_trnx_approved) AS approved_no_tx, ...

SQL * Loader connection errors

AskTom - Hot Questions - Wed, 10/11/2017 - 00:00
To previous answer to MY SQL*Loader question I had asked, you said: Examples of SQL*Loader String <code>sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name con...

Using Analytical Functions to get Group Total

AskTom - Hot Questions - Wed, 10/11/2017 - 00:00
I need a GROUP TOTAL for EACH MONTH that SUMS up all the values in APPL column even when the query is for a particular APPL value. <code>create table test_grp_total (dttm_month date, appl varchar2(5), mins number) insert into test_grp_total va...

Calling Procedure Parallel

AskTom - Hot Questions - Wed, 09/27/2017 - 00:00
I have below procedure which in turn calls two other Procedures. It calls and works fine but the two procs runs serial. I want to run them parallel and get the results on the main procs cursor. How do I do that? I tried with dbms_job.submit but could...

Hierarchical query with count of leave attributes

AskTom - Hot Questions - Thu, 09/21/2017 - 00:00
Hello Experts. I want to calculate the sum of the count of the leaves' attributes in a hierarchical query <code> create table hq_test (parent_id NUMBER, child_id NUMBER); INSERT INTO hq_test (parent_id, child_id) VALUES (25,26); INSERT INTO hq...

Pages