Three Approach finding out the different entries between two LOG files

I guess some guys and friends who have followed my social media on both Twitter and LinkedIn may have noticed that a couple of days ago I once announced A demo with secure copying RMAN EXP[DP] backup files to another server has been added into my Github repository. Especially full_and_incr_scp_oracle_rman_expdp_or_exp_3.sh is one of my most favorite SHELL script which is able to full and incremental scp backup files from local to another linux server.

Yes, the shell script previously mentioned has deployed to our linux server for synchronizing backup files. Here is the corresponding cron job setting.

You know, there will generate two temporary log files in the directopry “/tmp” when successfully running that shell script.

Perhaps you’re wondering how to generate those two logfiles in the prior screenshot. Here’s a bunch of critical code as follows.

As you can see from the previous image both $TMP_LOCAL_LOG_ALL and $TMP_REMOTE_LOG_ALL contain the location entries of all (properly speaking, 9 number) of production oracle database backup files, so at the end of them I add the keyword “ALL”. Due to so many location entires in those two log files so I just give 2 number of my oracle database “prodb1” and “prodb2” (the rest of oracle database “prodb3” … “prodb9” so on).

Here’s the entries on temporary log file “tmp_20210403033001_local_all.log” as well as the entries on log file “tmp_20210403033001_remote_all.log”. By the way I have downloaded them from my Github and saved them in the directory “/tmp” of my oracle 20c test database server.

Hence in this post I’ll introduce three approach finding out the different entries between these two log files “tmp_20210403033001_local_all.log” and “tmp_20210403033001_remote_all.log”.

The three approach is respectively as below for Table of Contents.

Table of Contents

Continue reading

ORA-10560: block type ‘KTU UNDO BLOCK’

In the middle of last month an oracle Data Guard standby database of our production system there had shown the very weird message in its ALERT log file.

......
Mon Mar 15 10:01:16 2021
Media Recovery Waiting for thread 1 sequence 2768 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 2768 Reading mem 0
  Mem# 0: /u01/oradata/DGstandby/standbyredo01.log
Mon Mar 15 11:01:06 2021
RFS[4]: Selected log 5 for thread 1 sequence 2769 dbid 298590189 branch 1059239666
Mon Mar 15 11:01:09 2021
Archived Log entry 58618 added for thread 1 sequence 2768 ID 0x1ab474d5 dest 1:
Mon Mar 15 11:21:04 2021
Errors in file /u01/app/oracle/diag/rdbms/DGstandby/DGstandby/trace/DGstandby_pr1e_28452.trc  (incident=494753):
ORA-00600: internal error code, arguments: [3020], [3], [1820], [12584732], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 1820, file offset is 14909440 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/u01/oradata/DGstandby/undotbs01.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
Incident details in: /u01/app/oracle/diag/rdbms/DGstandby/DGstandby/incident/incdir_494753/DGstandby_pr1e_28452_i494753.trc
Mon Mar 15 11:21:13 2021
Dumping diagnostic data in directory=[cdmp_20210315112113], requested by (instance=1, osid=28452 (PR1E)), summary=[incident=494753].
Mon Mar 15 11:21:19 2021
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception
Errors in file /u01/app/oracle/diag/rdbms/DGstandby/DGstandby/trace/DGstandby_pr1e_28452.trc:
ORA-00600: internal error code, arguments: [3020], [3], [1820], [12584732], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 1820, file offset is 14909440 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/u01/oradata/DGstandby/undotbs01.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
Mon Mar 15 11:21:20 2021
Errors in file /u01/app/oracle/diag/rdbms/DGstandby/DGstandby/trace/DGstandby_mrp0_28299.trc  (incident=494313):
ORA-00600: internal error code, arguments: [3020], [3], [1820], [12584732], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 1820, file offset is 14909440 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/u01/oradata/DGstandby/undotbs01.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
Incident details in: /u01/app/oracle/diag/rdbms/DGstandby/DGstandby/incident/incdir_494313/DGstandby_mrp0_28299_i494313.trc
Mon Mar 15 11:21:21 2021
Sweep [inc][494753]: completed
Sweep [inc][494313]: completed
Sweep [inc2][494753]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Recovery Slave PR1E previously exited with exception 600
Mon Mar 15 11:21:22 2021
MRP0: Background Media Recovery terminated with error 448
Errors in file /u01/app/oracle/diag/rdbms/DGstandby/DGstandby/trace/DGstandby_pr00_28328.trc:
ORA-00448: normal completion of background process
Managed Standby Recovery not using Real Time Apply
Dumping diagnostic data in directory=[cdmp_20210315112122], requested by (instance=1, osid=28299 (MRP0)), summary=[incident=494313].
Recovery interrupted!
Recovered data files to a consistent state at change 22382148475
MRP0: Background Media Recovery process shutdown (DGstandby)
Mon Mar 15 11:22:21 2021
Sweep [inc2][494313]: completed
......
Continue reading

Request a Workspace to start my APEX journey

I knew that there has an alternatively free APEX workspace to be requested on https://apex.oracle.com/en/learn/getting-started/. Take a looking at the following screenshot.

As you can seem from the prior image I click the button “Request a Free Workspace” with green color to start requesting a free workspace of Oracle APEX on another new tab page on my Chrome browser. Or you can firstly watch the right side YouTube video to learn it.

Continue reading

Getting my account of Oracle Cloud Free Tier in 2021

I have found the signup page of Oracle Cloud Free Tier has significantly changed in 2021 because I remembered that I was always stuck to that step of my credit card verification on May, 2020. At that moment I described my issue on Twitter, pretty luckily Jeff Smith retweeted my message to his boss Jenny Tsai-Smith afterwards she sincerely gived me a workaround. As well known as Oracle Corp particularly attachs importance to the users’ feedback opinion. Might many other guys and friends around the world like me also encountered the same problem. I guess it is the reason why oracle redesigns the enrollment page of cloud free tier in 2021.

And, What’s included with Oracle Cloud Free Tier? Totally two section of contents is as follows (about the introduction of more details you can read here).

Yes, I am very delighted that I successfully register my oracle cloud account and all the process only spends less than 5 minutes. Even each step more seems like a wizard to guide you to do the next operation.

Let’s get started with it.

Continue reading

SQL Quiz for Intersect

I noticed that there is a sql quiz of oracle database on the place where I seem like to not remember it a few days ago. But the stuff of this SQL Quiz should look like as below.

Which statement is true regarding the INTERSECT operator in the oracle database?

(1) By reversing the order of the intersected tables alter the result.

(2) It ignores the NULL values.

(3) The number of columns and the data types must be identical for all of the
    SELECT statements in the query.

(4) The names of all columns in the SELECT statements must be identical.

(5) None.

When ended up reading the prior SQL topic and each choice I know the topic has certainly only a correct answer. For being able to pick up the correct answer I built some demos to verify the description of each choice of this SQL Quiz is whether right or not. By the way my all of demos is separately tested on oracle 18.3.0.0 and 19.8.0.0 (LiveSQL).

Firstly, let me take a look at the choice 1 and my demo is as follows:

PROMPT  ==================================================================
PROMPT             Building a demo for verifying Choice 1:
PROMPT  ==================================================================
PROMPT  By reversing the order of the intersected tables alter the result.
PROMPT  ==================================================================

DROP TABLE t1 PURGE;

CREATE table t1(a VARCHAR2(1), b VARCHAR2(1));

INSERT INTO t1 VALUES ('a', 'b');
INSERT INTO t1 VALUES ('a', 'c');
INSERT INTO t1 VALUES ('b', 'c');

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a VARCHAR2(1), b VARCHAR2(1));

INSERT INTO t2 VALUES ('a', 'b');
INSERT INTO t2 VALUES ('c', 'd');
INSERT INTO t2 VALUES ('b', 'c');

COMMIT;

SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;

SELECT * FROM t2
INTERSECT
SELECT * FROM t1
;

When performing the previous SQL demo, you’ll get the same query result whatever the first SELECT table t1 OR the first SELECT table t2.

-- on 18.3 and LiveSQL:

A B
- -
a b
b c

As you can see the stuff of choice 1 is incorrect – “By reversing the order of the intersected tables DON’T alter the result” (note: only showing the first SELECT table’s column names).

Next, it’s my second demo for the choice 2.

PROMPT  =======================================
PROMPT  Building a demo for verifying Choice 2:
PROMPT  =======================================
PROMPT  It ignores the NULL values.
PROMPT  =======================================

DROP TABLE t1 PURGE;

CREATE table t1(a CHAR(1), b CHAR(1), c CHAR(1));

INSERT INTO t1 VALUES ('a', 'b', null);
INSERT INTO t1 VALUES ('a', null, 'c');
INSERT INTO t1 VALUES ('a', 'b', 'c');

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a CHAR(1), b CHAR(1), c CHAR(1));

INSERT INTO t2 VALUES ('a', 'b', null);
INSERT INTO t2 VALUES ('a', null, 'c');
INSERT INTO t2 VALUES ('d', 'e', 'f');

COMMIT;

SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;

Then the preceding last SQL QUERY result is as below respectively on 18.3 and LiveSQL:

-- on 18.3:

A B C
- - -
a b
a   c

-- on LiveSQL:

A B C
- - -
a b -
a - c

As you can see from the above outcome we found that the INTERSECT operation of the SELECT of two tables hasn’t ignored the NULL values (the choice 2 is absolutely wrong). For better showing the NULL values we can also use the SQL expression CASE WHEN … THEN … ELSE … END or the SQL function NVL() to return a pretty friendly result. Let me show you about them in the following two number of SQL snippets.

Case when … then … else … end

SELECT
           CASE WHEN a IS NULL THEN 'null'
           ELSE a
           END "A",
           CASE WHEN b IS NULL THEN 'null'
           else b
           end "B",
           CASE WHEN c IS NULL THEN 'null'
           else c
           end "C"
FROM
           t1
INTERSECT
SELECT
           CASE WHEN a IS NULL THEN 'null'
           ELSE a
           END "A",
           CASE WHEN b IS NULL THEN 'null'
           else b
           end "B",
           CASE WHEN c IS NULL THEN 'null'
           else c
           end "C"
FROM
           t2
;

Of course the prevous SQL statement shows the same query result on 18.3 and LiveSQL.

-- on 18.3:

A    B    C
---- ---- ----
a    b    null
a    null c

-- on LiveSQL:

A B    C
- ---- ----
a b    null
a null c

NVL()

SELECT
           NVL(a, 'null') a,
           NVL(b, 'null') b,
           NVL(c, 'null') c
FROM
           t1
INTERSECT
SELECT
           NVL(a, 'null') a,
           NVL(b, 'null') b,
           NVL(c, 'null') c
FROM
           t2
;

Due to using NVL() still shows the identical outcome with the CASE WHEN for the intersect operation hence I deliberately omit it.

Now it’s time to verify the choice 3 is whether correct or not. We should carefully read the stuff of this choice.

The number of columns and the data types must be identical for all of the
SELECT statements in the query.

As you can see from the previous info there are two number of key points in that choice – the number of columns AND the data types. So I’ll illustrate it from those two aspects.

The different number of columns

PROMPT  =========================================================================
PROMPT                  Building a demo for verifying Choice 3:
PROMPT  =========================================================================
PROMPT  The number of columns and the data types must be identical for all of the
PROMPT  SELECT statements in the query.
PROMPT  =========================================================================

-- The number of columns of the SQL query (for the INTERSECTION in two tables) is different. 

DROP TABLE t1 PURGE;

CREATE table t1(a NUMBER(1), b NUMBER(1));

INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (1, 3);

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a NUMBER(1), b NUMBER(1));

INSERT INTO t2 VALUES (1, 2);
INSERT INTO t2 VALUES (3, 4);
INSERT INTO t2 VALUES (2, 3);

COMMIT;

SELECT
           a, b
FROM       t1
INTERSECT
SELECT     a
FROM       t2
;

We can get the same error ORA-01789 both on oracle 18.3 and LiveSQL.

-- on 18.3 and LiveSQL:

ORA-01789: query block has incorrect number of result columns

The different data types

-- The data type of columns of the SQL query (for the INTERSECTION in two tables) is different.

DROP TABLE t1 PURGE;

CREATE table t1(a NUMBER(1), b NUMBER(1));

INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (1, 3);

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a CHAR(1), b CHAR(1));

INSERT INTO t2 VALUES ('1', '2');
INSERT INTO t2 VALUES ('3', '4');
INSERT INTO t2 VALUES ('2', '3');

COMMIT;

SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;

Now we return the same error of ORA-01790 separately on 18.3 and LiveSQL.

-- on 18.3 and LiveSQL:

ORA-01790: expression must have same datatype as corresponding expression

By contrary the description of the choice 3 is correct – must have the identical number of columns and the data types in the two number of SELECT statements for the intersect operation.

P.S. – We can also deem that the NUMBER and FLOAT are the same data type because the FLOAT data type is a subtype of NUMBER as you can see from this link of oracle official docs on 19c and the following screenshot.

Next let me verify the choice 4, here’s my fourth demo.

PROMPT  ====================================================================
PROMPT                Building a demo for verifying Choice 4:
PROMPT  ====================================================================
PROMPT  The names of all columns in the SELECT statements must be identical.
PROMPT  ====================================================================

DROP TABLE t1 PURGE;

CREATE table t1(a INTEGER);

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(b INTEGER);

INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);

COMMIT;

SELECT a FROM t1
INTERSECT
SELECT b FROM t2
;

Certainly the returned result is all okay on 18.3 and LiveSQL even if those two tables’ column name is different.

-- on 18.3:

         A
----------
         1
         3

-- on LiveSQL:

A
-
1
3

So, the choice 4 is wrong as well.

As s whole the SQL Quiz’s correct answer is the choice 3 (Of course it makes no sense that we read the choice 5). You can also see the entire demo codes from my Github repository.

Two skills for Changing Password of A User

Have you known how many methods for changing password of a user in the oracle database? Might the common method is using the “ALTER USER username IDENTIFIED BY password” in the SYS schema or your own schema to change it. Recently I know another approach when learning the course Oracle Database 19c: SQL Workshop in the Lesson 18 – Controlling User Access in the section of Changing Your Password (on my annual oracle database learning subscription from Oracle University), as you can see from the following screenshot.

So this post I will use the previous two skills to illustrate it.

  • ALTER USER … IDENTIFIED BY …;
  • PASSW[ORD] command in the SQL*Plus

Alter User … Identified By …

In this section I will use the following SQL code snippets to implement it.

Firstly, I create a user of qwz and then grant it with the role of CONNECT and RESOURCE in the SYS schema.

14:41:50 SYS@ora18c> create user qwz identified by qwz;

User created.

14:42:21 SYS@ora18c> grant connect, resource to qwz;

Grant succeeded.

Next connect to the user qwz and use “alter user …” to change its password.

14:42:30 SYS@ora18c> conn qwz/qwz

Connected.

14:42:33 QWZ@ora18c> alter user qwz identified by qwz123;

User altered.

or, I directly change the password of user qwz in the SYS schema.

14:44:41 SYS@ora18c> alter user qwz identified by qwz;

User altered.

Note: as you can see from the preceding demo I am able to quickly change the password of a user to becocme a new one instead of inputtng an OLD one. Of course, after you certainly input your password you are able to go into your own schema.

Passw[ord] in SQL*Plus

I need to take a look at the usage of PASSWORD in the SQL*Plus.

14:45:29 SYS@ora18c> help password

 PASSWORD
 --------

 Allows you to change a password without displaying it on an input device.

 PASSW[ORD] [username]


14:45:32 SYS@ora18c> 

Next I will change the password of user qwz using the command PASSW of the SQL*Plus in the SYS schema (with the username after the keyword password).

14:45:32 SYS@ora18c> passw qwz
Changing password for qwz
New password:         (qwz123 no echo)
Retype new password:  (qwz123 no echo)
Password changed

or, connect to the user qwz, and then change its password by inputting the keyword passw.

14:46:12 SYS@ora18c> 
14:47:20 SYS@ora18c> conn qwz/qwz123

Connected.
14:47:24 QWZ@ora18c> passw
Changing password for QWZ
Old password:          (qwz123 no echo)
New password:          (qwz no echo)
Retype new password:   (qwz no echo)
Password changed

As you can see I must firstly input the OLD password so that allowing me to change a new one because I certainly know the old password.

Addendum

There is one point to notice when you change the password of SYS schema in the schema qwz. Although oracle prompt that you can input a new password for SYS, actually you get an error of ORA-01031.

15:29:38 QWZ@ora18c> passw sys
Changing password for sys
New password:         (sys no echo)
Retype new password:  (sys no echo)
ERROR:
ORA-01031: insufficient privileges


Password unchanged

Trigger bug

There has a section “CALL Statements in Triggers” in the “Lesson 10: Creating Compound, DDL, and Event Database Triggers” in the Student Guide “Program with PL/SQL – Student Guide II” in this course “Program with PL/SQL” of ODDLS (Oracle Database Deveoper Learning Subscription) subscribed by me.

As you can see from the first screenshot about procedure and trigger code, actually there exists an invoking procedure “log_execution” clause when creating the trigger “log_employee”. And you should also notice that adding an interesting comment “– no semicolon needed” after that invoking statement in order to remind reader not to add an extra semicolon (there is also a conspicuous Note in the second screenshot).

For practicing that trigger code, I separately tested it on oracle 11.2, 12.1, 12.2, 18.3, 19.3 and LiveSQL (current 19.5). Unfortunately PL/SQL compiler alwayws reports the very weird error of PLS-00103 on all the oracle versions I mentioned previously. So yesterday morning I emailed to Steven Feuerstein (Oracle PL/SQL guru all over the world) to ask for him to help me, as a result, he replied to me it looks like a bug if removing the comment “– no semicolon needed” and then it works. He was going to report the issue. The following are three pictures about our emails back and forth.

After his help, today I wrote the following test SQL code snippet on Github.

REM
REM     Script:        invoke_procedure_in_trigger.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Jul 31, 2020
REM
REM     Last tested:
REM             11.2.0.4
REM             12.1.0.2
REM             12.2.0.1
REM             18.3.0.0
REM             19.3.0.0
REM             19.5.0.0 -- LiveSQL
REM
REM     Purpose:
REM       If adding a comment after the call procedure clause in a trigger, PL/SQL compiler
REM       will report the very weird error of PLS-00103. So I emailed to Steven Feuerstein,
REM       he suggests me remove that comment and then it will normally work, which seems to
REM       be a bug.
REM

CREATE TABLE emp (empno NUMBER, ename VARCHAR2(10));

INSERT INTO emp VALUES(7839, 'King');
INSERT INTO emp VALUES(7698, 'Blake');
INSERT INTO emp VALUES(7782, 'Clark');

COMMIT;

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE log_execution IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('log_execution: Emp table has inserted 1 row.');
END;
/

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution -- no semicolon needed
/

Warning: Trigger created with compilation errors.

show errors
Errors for TRIGGER LOG_EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/37     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         := . ( @ % ;
         The symbol ";" was substituted for "end-of-file" to continue.

-- if eliminating the comment '-- no semicolon needed' after the 'CALL log_execution' clause

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution
/

Trigger created.

INSERT INTO emp VALUES(7566, 'Jones');
log_execution: Emp table has inserted 1 row.

1 row created.

COMMIT;

And I have to say LiveSQL is a pretty nice tool runing Oracle SQL and PL/SQL code, hence I also wrote a SQL script to test my code (in other words, report a bug). Here‘s my LiveSQL script.

Finally I replied to Steven Feuerstein to let me know the bug number that when MoS has been going to finish reporting.

Addendum

Update Aug 01, 2020: Yesterday evening Steven Feuerstein also emailed to me the bug filed number #31698004 and he reported the issue to oracle and was still able to reproduce it on 20c (outside of LiveSQL).

Displaying and setting PL/SQL Initialization Parameters

There are the following four types of initialization parameters for PL/SQL compilation.

  • PLSQL_CODE_TYPE
  • PLSQL_OPTIMIZE_LEVEL
  • PLSQL_CCFLAGS
  • PLSQL_WARNINGS

In releases before Oracle Database 10g, the PL/SQL compiler translated your code to machine code without applying many changes for performance. Oracle Database 11g onwards, PL/SQL uses an optimizing compiler that can rearrange code for better performance. You do not need to do anything to get the benefits of this new optimizer; it is enabled by default.

The following table is specially describing those four number of initialization parameter of PL/SQL compilation.

+============================+=============================================================================+
|Compiler Option             |Description                                                                  |
+============================+=============================================================================+
|PLSQL_CODE_TYPE             |Specifies the compilation mode for PL/SQL library units.                     |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_OPTIMIZE_LEVEL        |Specifies the optimization level to be used to compile PL/SQL library units. |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_WARNINGS              |Enables or disables the reporting of warning messages by the PL/SQL compiler.|
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_CCFLAGS               |Controls conditional compilation of each PL/SQL library unit independently.  |
+----------------------------+-----------------------------------------------------------------------------+

The new compiler improves the performance of PL/SQL code and allows it to execute approximately two times faster than an Oracle 8i database and 1.5 times to 1.75 times as fast as Oracle 9i Database Release 2.

To get the fastest performance, the compiler setting must be:

PLSQL_CODE_TYPE = NATIVE
PLSQL_OPTIMIZE_LEVEL = 2

Hence this post I focus on displaying and setting the previous two initialization parameters for PL/SQL compilation.

Taking the schema “OE” for example in Oracle Database 11.2, firstly, we use the following SQL code snippet to check the current value of both PLSQL_CODE_TYPE and PLSQL_OPTIMIZE_LEVEL based on some oracle objects.

-- running on the schema of 'OE'.

SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15

SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ACTIONS_T                      TYPE         INTERPRETED              2
ACTION_T                       TYPE         INTERPRETED              2
ACTION_V                       TYPE         INTERPRETED              2
ADD_JOB_HISTORY                PROCEDURE    INTERPRETED              2
CATALOG_TYP                    TYPE         INTERPRETED              2
CATALOG_TYP                    TYPE BODY    INTERPRETED              2
CATEGORY_TYP                   TYPE         INTERPRETED              2
COMPOSITE_CATEGORY_TYP         TYPE         INTERPRETED              2
COMPOSITE_CATEGORY_TYP         TYPE BODY    INTERPRETED              2
CORPORATE_CUSTOMER_TYP         TYPE         INTERPRETED              2
CUSTOMER_TYP                   TYPE         INTERPRETED              2
CUST_ADDRESS_TYP               TYPE         INTERPRETED              2
CUST_DATA                      PACKAGE      INTERPRETED              2
CUST_DATA                      PACKAGE BODY INTERPRETED              2
CUST_LIST                      PROCEDURE    INTERPRETED              2
......

51 rows selected.

As you can see from the above vaule “INTERPRETED” based on PLSQL_CODE_TYPE, but we can set the compiler initialization parameter’s value using the ALTER SYSTEM or ALTER SESSION statements.

Secondly, we’ll use the ALTER SESSION statement to set PLSQL_CODE_TYPE to become “NATIVE” in order to get the fastest performance.

ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';

Thirdly, I create a procedure “add_job_history” on the schema “OE”.

CREATE OR REPLACE PROCEDURE add_job_history
  (p_emp_id             job_history.employee_id%TYPE,
   p_start_date         job_history.start_date%TYPE,
   p_end_date           job_history.end_date%TYPE,
   p_job_id             job_history.job_id%TYPE,
   p_department_id      job_history.department_id%TYPE
  )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  END add_job_history;
/

Abruptly oracle gives me the following warning message.

Warning: Procedure created with compilation errors.

Using a simple statement “SHOW ERRORS” to quickly find out which lines the compilation errors are located in.

oe@ORA11GR2> SHOW ERRORS
Errors for PROCEDURE ADD_JOB_HISTORY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
10/17    PL/SQL: ORA-01031: insufficient privileges

As you can see from the previous error ORA-01031 that has a bit of confusion. Next, I check the object_type of “job_history”.

COLUMN object_name FORMAT a12
COLUMN object_type FORMAT a12

SELECT object_name, object_type FROM user_objects WHERE object_name = 'JOB_HISTORY';

OBJECT_NAME  OBJECT_TYPE
------------ ------------
JOB_HISTORY  SYNONYM

Oh, my god! As you can see from the above object_type, it’s a synonym (not a table). A real table “JOB_HISTORY” should be from the schema “HR”. By the following SQL, you can prove my description whether it is right.

COLUMN synonym_name FORMAT a12
COLUMN table_owner  FORMAT a12
COLUMN table_name   FORMAT a12

SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'JOB_HISTORY';

SYNONYM_NAME TABLE_OWNER  TABLE_NAME
------------ ------------ ------------
JOB_HISTORY  HR           JOB_HISTORY

So, switching to the schema “HR”, rerunning all the previous code step by step.

SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15

SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL                        PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT                 PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT_NOEX            PROCEDURE    INTERPRETED              2
ADD_DEPT                       PROCEDURE    INTERPRETED              2
ADD_EMPLOYEE                   PROCEDURE    INTERPRETED              2
ADD_ROW                        PROCEDURE    INTERPRETED              2
......

81 rows selected.
ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';
CREATE OR REPLACE PROCEDURE add_job_history
  (p_emp_id             job_history.employee_id%TYPE,
   p_start_date         job_history.start_date%TYPE,
   p_end_date           job_history.end_date%TYPE,
   p_job_id             job_history.job_id%TYPE,
   p_department_id      job_history.department_id%TYPE
  )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  END add_job_history;
/

Procedure created.
SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL                        PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT                 PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT_NOEX            PROCEDURE    INTERPRETED              2
ADD_DEPT                       PROCEDURE    INTERPRETED              2
ADD_EMPLOYEE                   PROCEDURE    INTERPRETED              2
ADD_JOB_HISTORY                PROCEDURE    NATIVE                   2
ADD_ROW                        PROCEDURE    INTERPRETED              2
......

82 rows selected.

As you can see the last SQL query result, the PLSQL_CODE_TYPE of the procedure “ADD_JOB_HISTORY” being created just now has become NATIVE.

Potential Bug on Muti-tenant DB

Middle last month I found a very strange phenomenon on oracle 19.3 of Windows platform – querying “SELECT COUNT(*) FROM all_objects” is very rapid on SYS but pretty slow on a COMMON user.

So I decided to use a 10046 event to trace the SQL statement previously mentioned so that expected to find out some valuable clues. Take a look at my operation steps as follows.

  • running on SYS schema
  • running on C##QWZ schema
  • tracking SQL via 10046 event on C##QWZ schema

SYS

16:18:35 SYS@ora19c> select count(*) from all_objects;

  COUNT(*)
----------
     72352

16:18:46 SYS@ora19c> 

C##QWZ

16:28:29 C##QWZ@ora19c> select count(*) from all_objects;

  COUNT(*)
----------
     55672

16:40:01 C##QWZ@ora19c> 

10046 event

SET LINESIZE 150
SET PAGESIZE 150
SET SERVEROUTPUT OFF

ALTER SESSION SET max_dump_file_size = 'UNLIMITED';
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET events '10046 trace name context forever,level 12';

select count(*) from all_objects;

ALTER SESSION SET events '10046 trace name context off';
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

Next generating a trc file named “…10046.trc” such as, “C:\APP\ADMINISTRATOR\VIRTUAL\diag\rdbms\ora19c\ora19c\trace\ora19c_ora_20224_10046.trc”.

Continue reading

Wget

A few of days ago Jonathan Lewis sent the following message (quoting his original stuff) on Twitter.

EARLY WARNING: at the end of May my old (unchanged since September 2012) website jlcomp.demon.co.uk will be taken off the internet. I may update and post a very small number of the articles on my blog eventually, but if there’s anything you want to keep from it, copy it now.

04:40PM 2020-04-04 Twitter Web App

and,

WARNING: by end May the DEMON domain (with my old website jlcomp.demon.co.uk) will disappear. If there’s anything you want from the website copy it now.

wget -e robots=off -r -np http://www.jlcomp.demon.co.uk -o log.txt

05:53 PM 2020-04-06 Twitter Web App

I heard of this bad news and asked him what reason it is afterwards he replied to me – the license of sub-domain DEMON was about to expire. So it’s time to copy those stuff from his old website if you keep somthing.

Continue reading