DBA Blogs

Synchronous refresh in mview ORA-31922: Foreign key must contain partition key in table

Tom Kyte - Tue, 2020-02-18 06:11
Team, Here is my testcase which got failed during Synchronous refresh in mview. <code>create table products as select rownum as prod_id, object_name as prod_name, object_type as prod_category, object_id as prod_category_id, data_object...
Categories: DBA Blogs

Oracle 19C database issue with table types and pipelining

Tom Kyte - Tue, 2020-02-18 06:11
I have a package working fine in 11g version. But when I deploy the same package in 19c version, the behavior is different. PFB the description. Package specification has an cursor and created a table type with cursor%rowtype. Having a pipel...
Categories: DBA Blogs

Sqoop ojdbc8.jar throws error ORA-06502:PL/SQL:: numeric or value error

Tom Kyte - Tue, 2020-02-18 06:11
Hi Tom, We are trying to do a sqoop import to hive from Oracle and struck with a weird error below: WARN[main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.io.IOException: java.sql.SQLExcepion: ORA-00606:error occurred ...
Categories: DBA Blogs

Scheduling Compilation or Execution of Stored Procedures

Tom Kyte - Tue, 2020-02-18 06:11
Hello We have a Oracle 11g Release 2 database with five identical working Schemas being accessed by a VB Client Server business application The db server, with 32GB RAM just hosts this one database Copies of Client applications are installed a...
Categories: DBA Blogs

Finding when someone dropped an object

Tom Kyte - Tue, 2020-02-18 06:11
Hi Team, I have a DB, where a table is dropped from the schema accidentally. We are trying to find whether it got dropped due to manual execution of DROP query and by whom? Is there any way that we can find that the DROP query which is executed ...
Categories: DBA Blogs

How to fetch part of a string for LONG datatype

Tom Kyte - Tue, 2020-02-18 06:11
HI, I am writing a query to find missing table partitions for next year using all_tab_partitions table, I am able to fetch the records with the help of column partition positions, but I have to extract the last partition date (YYYY-MM-DD) from HIG...
Categories: DBA Blogs

Average of 0 and Value - gives incorrect output. Is there a way to ignore the 0 during the average function.

Tom Kyte - Fri, 2020-02-14 21:11
Hi Tom, I am having a SQL output as follows. <code>A B C D E ---------------- ---------- ---------- ------------ ----------- 2020-02-12 221 68677 99.6...
Categories: DBA Blogs

Indexing strategy for dates in a query

Tom Kyte - Fri, 2020-02-14 21:11
Hello, Ask Tom team. I have the following query: <code>SELECT guid, sender_id, doc, status, arrived_date, register_date, last_updated_date FROM user1.table1 WHERE (sender_id=:SENDER OR :SENDER IS NULL ) AND (status=:STATUS OR :status IS NU...
Categories: DBA Blogs

Left padded String based on sub-string length

Tom Kyte - Fri, 2020-02-14 21:11
Hi Chris, I want to restrict the length of the input string to 8 characters by adjusting all the digits (after 'MFT' in below example) from Input string. Means, want to accommodate all the digits in string. Ex.1. Input String is 'MFT123456' ...
Categories: DBA Blogs

AWR records top 30 SQLs by default

Bobby Durrett's DBA Blog - Fri, 2020-02-14 16:22

I forget that Oracle’s AWR only records the top 30 SQL statements in each snapshot by default. I am not sure how long this link will last but here is a 19c manual page describing the default: 19c manual – see the topnsql setting. A lot of my query tuning assumes that the problem query is in the AWR but for very efficient queries on active systems they may mysteriously disappear or be absent from the AWR. It sometimes takes me a while to remember that the snapshots only include a fixed number of SQLs.

I use my sqlstat3.sql query to look at a history of a particular sql_id’s executions. Often it shows the query running faster on one plan_hash_value than another. Then I look at why the sql_id changed plans. But what about when the good plan does not show up at all? Several times I have looked at sqlstat3.sql output and thought that a query had not run in the past with an efficient query even though it had. It had run so efficiently that it was not on the report, so it looked like the query was a new, slow, SQL statement.

Often I will fix a query’s plan with a SQL Profile and rerun sqlstat3.sql on a busy system after manually running dbms_workload_repository.create_snapshot to capture the most recent activity and the problem query with the new plan will not show up. Usually I remember that it is not in the top 30 queries and that is why it is missing but sometimes I forget. Here is a partial sqlstat3.sql output showing a long running SQL disappearing after I fixed its plan on Wednesday:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Ave ms
------------- --------------- --------------------- ---------------- ------------------
acn0557p77na2      3049654342 12-FEB-20 05.00.01 AM                1          16733.256
acn0557p77na2      3049654342 12-FEB-20 06.00.03 AM                2           49694.32
acn0557p77na2      3049654342 12-FEB-20 07.00.53 AM                6          47694.527
acn0557p77na2      3049654342 12-FEB-20 08.00.54 AM               11         50732.0651
acn0557p77na2      3049654342 12-FEB-20 09.00.33 AM               15         53416.5183
acn0557p77na2      3049654342 12-FEB-20 10.00.43 AM               21         86904.4385
acn0557p77na2      3049654342 12-FEB-20 11.00.02 AM               27          84249.859
acn0557p77na2      3049654342 12-FEB-20 12.00.20 PM               27         125287.757
acn0557p77na2      3049654342 12-FEB-20 01.00.36 PM               69         156138.176

Sometimes I query the V$ tables to verify it is currently running a good plan. Here is example output from vsqlarea.sql showing the good plan running today.

LAST_ACTIVE         SQL_ID        PLAN_HASH_VALUE Avg Elapsed ms
------------------- ------------- --------------- --------------
2020-02-14 16:11:40 acn0557p77na2       867392646             14

This is just a quick note to me as much as anyone else. A query that is missing from an AWR report or my sqlstat3.sql report may not have run at all, or it may have run so well that it is not a top 30 query.

Bobby

Categories: DBA Blogs

Why commit/rollback or any DDL command not allowed in trigger or function?

Tom Kyte - Thu, 2020-02-13 21:10
Hi Tom, Theoretically I know that commit/rollback/DDL or anything that causes transaction to end are not allowed in a trigger and function if calling function in SQL statement. To use any of those in trigger/function we can use PRAGMA AUTONOMOUS T...
Categories: DBA Blogs

Want to replace a particular string with a null value

Tom Kyte - Thu, 2020-02-13 21:10
We have 2 types of record_data format in table speedwing table 1st type --> <code>..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20' ..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2...
Categories: DBA Blogs

Roles granted to other roles

Tom Kyte - Thu, 2020-02-13 21:10
Is it true that roles can not be granted to other roles anymore? I am unable to find documentation of this, but was informed that this was taken away in 12c. If this is true, will you please post the document?
Categories: DBA Blogs

DBMS_UTILITY.FORMAT_CALL_STACK Change in 12.2 and later

Bobby Durrett's DBA Blog - Thu, 2020-02-13 15:47

Quick note. During my 11.2.0.4 to 19c upgrade that I have been writing about we found a difference in behavior of DBMS_UTILITY.FORMAT_CALL_STACK. I tested it on several versions, and it switched in 12.2. Now it puts the procedure name within the package in the stack.

Old output:

----- PL/SQL CALL STACK -----
  OBJECT      LINE  OBJECT
  HANDLE    NUMBER  NAME
0X15BFA6930         9  PACKAGE BODY MYUSER.MYPKG
0X10C988058         1  ANONYMOUS BLOCK

New output:

----- PL/SQL CALL STACK -----
  OBJECT      LINE  OBJECT
  HANDLE    NUMBER  NAME
0XA796DF28         9  PACKAGE BODY MYUSER.MYPKG.MYPROC
0X7ADFEEB8         1  ANONYMOUS BLOCK

Test code:

select * from v$version;

CREATE OR REPLACE PACKAGE MYPKG
AS

PROCEDURE MYPROC;

END MYPKG;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY MYPKG
AS

PROCEDURE MYPROC
IS

BEGIN

DBMS_OUTPUT.PUT_LINE(UPPER(dbms_utility.format_call_stack));

END MYPROC;

END MYPKG;
/
SHOW ERRORS;

execute mypkg.myproc;
show errors;

Might be useful to someone else. We had some code that depended on the package name being the last thing on its line, but the new version includes the name of the procedure after the package name.

Bobby

Categories: DBA Blogs

Unable to load jar using dbms_java.loadjava in Oracle

Tom Kyte - Thu, 2020-02-13 03:10
I need to upload a jar file in Oracle RDBMS using dbms_java.loadjava method. I have granted all the required permission and able to run below function successfully. <code>create or replace function get_java_property(prop in varchar2) return varch...
Categories: DBA Blogs

LINESIZE and displaying data on a screen : the biggest part of execution time?

Tom Kyte - Thu, 2020-02-13 03:10
Hello Masters, I have one big question about the SQL*Plus parameter LINESIZE and the display of datas. I read in documentation Oracle 19 SQL*Plus : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/sqlplus-users-guide-and-refe...
Categories: DBA Blogs

Transform IF ELSE END IF TO CASE WHEN

Tom Kyte - Thu, 2020-02-13 03:10
I need to use PL-SQL or SQL to do my query. First: I want to transform all the IF..END IF used in the body of function F_CALCUL_TAUX to CASE..WHEN..END, and use the result inside my query. Secundo: It's possible to transform that and use it inside ...
Categories: DBA Blogs

Function for alphabetical sequence like a spreadsheet

Tom Kyte - Thu, 2020-02-13 03:10
I need function which convert numeric to alphabet like when I input 1 then it will return 'A', when i input 2 then it will return 'B' please help me on this.
Categories: DBA Blogs

Index rebuild is taking long time before partition exchange

Tom Kyte - Wed, 2020-02-12 06:04
Hi Tom, I have a work table where I will do all the calculations and do partition exchange to main table. This work table is truncate & load. This process has couple of steps. 1. I will copy few records from main table to work table and ca...
Categories: DBA Blogs

Autotrace traceonly

Tom Kyte - Wed, 2020-02-12 06:04
Hi, Could you please help to understand: How can I get the execution plan with all details like A rows E rows, etc when trace only enabled. I always get basic plan details like rows bytes and cpu% only. It would be helpful if you could share an...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs