Feed aggregator

Hello I have a question on oracle redaction. Can oracle redaction be turned on based on a value in a column? please let me know Thanks subramanyam

Oracle is under the impression that 1/19 * 19 - 1 = -6 x 10^(-40). You can replace 19 with 19 multiplied by any positive power of 100 and the problem remains. (When you multiply 19 by an odd power of 10, the answer is zero. For example, 1/1900 * 1900 - 1 = -6x10^(-40), but 1/190 * 190 - 1 = 0.) I understand it has to do with the way numbers are stored internally, but is there a simple way around it? Note that this also happens in version 10.2.4

Sir, Greetings. I would just like to know if it is possible to truncate a sequence to reset back to its original starting number? Pls. help me. Thank you. Merry Christmass. - Vince Crismer C. Villena

Can you please give me an example for recursive function in plsql Which takes i_date as an input variable using this i_date it should perform operations like it should fetch data from table into cursor From this cursor I should insert data into a file .This file should be generated seperately for date that is passed in as input Let us take that my input date is 01012016 the function should recursively perform until it reaches 31122016 (for every last day of a month i.e total 12 times)

Hi Tom, 1) Is there or What is the relationship between number of CPUS the host has to the degree of Parallelism selected say while running DBMS_STATS. 2) Is there a relationship between DB sessions and Degree of Parallelism. If I have say 4 CPUS and select degree of 4 When I run DBMS_STATs will it show 4 DB sessions and consume 4 CPUS ? Or It will definitely show 4 DB sessions but no. of CPU depends upon availabilty from OS side. So even 2 CPUS will be used for 4 sessions ? 3) Is there always one to one relationship between a DB session ( from v$session) and OS process (seen with ps command ) 4) DBMS_SCHEDULER does it need to have job_queue_processes set like the old days with dbms_job Please answer with respect to 9208 and 11107 version.

Db Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production <code>desc Par_trials; Column Name Pk Null? Data Type PARTY_TRIAL_ID 1 N NUMBER (11) PARTY_ID N NUMBER (11) PARTY_ROLE_TYPE_ID N NUMBER (11) CLINICAL_TRIAL_ID N NUMBER (11) START_DT Y DATE END_DT Y DATE CORE_CREATE_DT N DATE CORE_LAST_UPDT Y DATE EDC_FLAG Y VARCHAR2 (1 Byte) CPAC_FLAG Y VARCHAR2 (1 Char) ELMS_FLAG Y VARCHAR2 (1 Char) CREATE MATERIALIZED VIEW LOG ON par_trials WITH ROWID, SEQUENCE (party_id,party_role_type_id,clinical_trial_id,start_dt,end_dt,core_create_dt,core_last_updt,edc_flag,cpac_flag,elms_flag), PRIMARY KEY INCLUDING NEW VALUES Insert into PAR_TRIALS (PARTY_TRIAL_ID, PARTY_ID, PARTY_ROLE_TYPE_ID, CLINICAL_TRIAL_ID, START_DT, CORE_CREATE_DT) Values (365352, 50858, 528, 1055, TO_DATE('10/30/2019 20:53:51', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/07/2019 12:03:59', 'MM/DD/YYYY HH24:MI:SS')); COMMIT; update par_trials set core_create_dt=sysdate where party_Trial_id=365352; select * from MLOG$_PAR_TRIALS</code> Question: 1) Would like to include a new column called "OPERATION$" in MLOG$_PAR_TRIALS table which should have below column values "UU" which indicates before update and should contain all its corresponding old values "UN" which indicates after update and should contain all its corresponding new values 2) In mlog creation script, have included "INCLUDING NEW VALUES" and i do have requirement to "exclude old values". Expectation here is just to retain only new values in mlog. Is that possible? If yes, kindly share example. 3) Can we do delete operation against MLOG$_PAR_TRIALS?If yes, kindly share example.

Dear Team We had recently upgraded our Application counter scheme ( Previously using oracle sequences) to our own created procedure to generate Next number/counter due to our requirement to generate seperate counters for our different Hospital Campuses. Every year we have to reset all sequences because our scheme is using year based counter values. Now the issue is locking problem on our Physician Encounter where we have to generate 7-8 different counters on the same time and our procedure sometimes get locked and does not return any counter value due to locking issue Our application user may loss data to No Counter return We have two options of Row update, wait 1 sec and Nowait, sample code is mentioned below. can you please guide us on method close to Oracle sequence so that we can update code and overcome this locking issue. Also Please guide do you recommend to use FOR UPDATE without wait or NOWAIT option in SQL ? <code> IF NVL(P_LOCK_WAIT, 'N') = 'Y' THEN SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE WAIT 1; ELSE SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE NOWAIT; END IF </code>

Hi Tom, We have a customer, who deployed multiple databases on the same supercluster zone and required a full segergation between each database. Here let's talk about one of zones which are having 2 databases and required a full network segergation between each other. We configured for each database a seprate network with different subent on the cluster and for sure different scan IPs and VIPs. Everything went smootly except the connectivity between the databases in the same zone with each others so we agreed to implment TCP.EXCLUDED_NODES to prevent each database to connect to the other but the problem it is not working and still database A can connect on B and viceversa, we did a test between databases in different zones and this parameter "TCP.EXCLUDED_NODES" is working properly. My Question is why it is not working with two databases in the same zone? - DB 12.1 - Zone O.S is Solaris Sparc : 11.4 - (2) Containers with (2) Different databases in the same Zone , we need to stop DB access from one DB to the other

Beginning of February 2020, Oracle launched a new family of Data Management services such as Data Catalog, Data Science, Data Flow, and a new Big Data service. All these services help to create new...

We share our skills to maximize your revenue!

…and how sparse tables can mess up your dynamic sampling.

A little lesson to self about paying attention to the detail, the obvious frickin’ detail. Getting older and slower.

We had one of those problems where a multitude of things were slow and yet there were no significant system-level problems (e.g. no general slow IO, no setting of the fast=false parameter, etc)

In such situations, if you check the high level system level metrics – AWR etc – and nothing is obvious then it makes sense to narrow the scope & drill into a specific problem, and distill that problem/statement as small as you can. If you can find the issue there then you might hope to find that that is the same as the wider problem and if not well you can just take that approach to every individual area,

I had a problem across a number of reporting modules, I picked on one module and from there I picked on the statement which was currently running and which was running for much longer than expected, I picked out one of the obvious problem joins within the problem statement and started looking at a simple two table join where the cardinality estimates were going wrong.

Most of the specific details of this plan and SQL can be ignored. It’s not important.

What is important is the cardinality difference.

And in fact, in this particular cut down example, the cardinality difference does not have the plan impact it does in the original setting.

 
set pages 999
explain plan for
SELECT /*+ parallel(4)*/
	   count(*)
FROM   psbal
LEFT OUTER JOIN
       prdct
ON    (prdct.d_product_id                        = psbal.d_product_id
AND    prdct.create_ts                           TO_TIMESTAMP('14.10.2020 08:16:34.822981', 'DD.MM.YYYY HH24:MI:SS.FF6'));

select * from table(dbms_xplan.display);

Plan hash value: 3885851338
--------------------------------------------------------------------------------------------------------------------------------------------------
|  Id | Operation                             | Name          | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  TQ   |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |    1 |    72 |   3430K (3)| 01:54:22 |       |       |       |      |            |
|   1 |  SORT AGGREGATE                       |               |    1 |    72 |            |          |       |       |       |      |            |
|   2 |   PX COORDINATOR                      |               |      |       |            |          |       |       |       |      |            |
|   3 |    PX SEND QC (RANDOM)                | :TQ10003      |    1 |    72 |            |          |       |       | Q1,03 | P->S |  QC (RAND) |
|   4 |     SORT AGGREGATE                    |               |    1 |    72 |            |          |       |       | Q1,03 | PCWP |            |
|*  5 |      HASH JOIN                        |               | 7466M|   500G|   3430K (3)| 01:54:22 |       |       | Q1,03 | PCWP |            |
|   6 |       PART JOIN FILTER CREATE         | :BF0000       |  286K|  1680K|     56  (0)| 00:00:01 |       |       | Q1,03 | PCWP |            |
|   7 |        PX RECEIVE                     |               |  286K|  1680K|     56  (0)| 00:00:01 |       |       | Q1,03 | PCWP |            |
|   8 |         PX SEND BROADCAST             | :TQ10000      |  286K|  1680K|     56  (0)| 00:00:01 |       |       | Q1,00 | P->P |  BROADCAST |
|   9 |          PX BLOCK ITERATOR            |               |  286K|  1680K|     56  (0)| 00:00:01 |       |       | Q1,00 | PCWC |            |
|  10 |           INDEX STORAGE FAST FULL SCAN| S_PK          |  286K|  1680K|     56  (0)| 00:00:01 |       |       | Q1,00 | PCWP |            |
|* 11 |       HASH JOIN RIGHT OUTER           |               | 7466M|   458G|   3425K (3)| 01:54:12 |       |       | Q1,03 | PCWP |            |
|  12 |        PX RECEIVE                     |               |    1 |    30 |    306  (0)| 00:00:01 |       |       | Q1,03 | PCWP |            |
|  13 |         PX SEND BROADCAST             | :TQ10001      |    1 |    30 |    306  (0)| 00:00:01 |       |       | Q1,01 | P->P |  BROADCAST |
|  14 |          PX BLOCK ITERATOR            |               |    1 |    30 |    306  (0)| 00:00:01 |     1 |     3 | Q1,01 | PCWC |            |
|* 15 |           TABLE ACCESS STORAGE FULL   | PRODUCT       |    1 |    30 |    306  (0)| 00:00:01 |     1 |     3 | Q1,01 | PCWP |            |
|* 16 |        HASH JOIN                      |               | 7466M|   250G|   3420K (3)| 01:54:01 |       |       | Q1,03 | PCWP |            |
|  17 |         PX RECEIVE                    |               | 6903K|   118M|  16379  (1)| 00:00:33 |       |       | Q1,03 | PCWP |            |
|  18 |          PX SEND BROADCAST            | :TQ10002      | 6903K|   118M|  16379  (1)| 00:00:33 |       |       | Q1,02 | P->P |  BROADCAST |
|  19 |           PX BLOCK ITERATOR           |               | 6903K|   118M|  16379  (1)| 00:00:33 |     1 |     3 | Q1,02 | PCWC |            |
|* 20 |            TABLE ACCESS STORAGE FULL  | BOOK          | 6903K|   118M|  16379  (1)| 00:00:33 |     1 |     3 | Q1,02 | PCWP |            |
|  21 |         PX PARTITION LIST JOIN-FILTER |               | 8064M|   135G|   3398K (3)| 01:53:17 |:BF0000|:BF0000| Q1,03 | PCWC |            |
|  22 |          TABLE ACCESS STORAGE FULL    | BALANCE       | 8064M|   135G|   3398K (3)| 01:53:17 |:BF0000|:BF0000| Q1,03 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  5 - access("FCT"."S_VID"="DNSP"."S_VID")
 11 - access("PRDCT"."PRODUCT_ID"(+)="FCT"."PRODUCT_ID")
 15 - storage(NVL("PRDCT"."MODIFY_TS"(+),TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 08:16:34.822981000' AND 
          "PRDCT"."CREATE_TS"(+)TIMESTAMP' 2020-10-14 08:16:34.822981000' AND 
         "PRDCT"."CREATE_TS"(+)<=TIMESTAMP' 2020-10-14 08:16:34.822981000')
 16 - access("BOOK_ID"="FCT"."BOOK_ID" AND "VERSION"="FCT"."BOOK_VERSION")
 20 - storage("COLUMN1" IS NULL OR "D_BOOK_VID"=(-2) OR "D_BOOK_VID"=(-1))
    filter("COLUMN1" IS NULL OR "D_BOOK_VID"=(-2) OR "D_BOOK_VID"=(-1))
 
Note
-----
  - dynamic sampling used for this statement (level=7)
  - Degree of Parallelism is 4 because of hint

and this:

set pages 999
explain plan for
select count(*)
FROM   prdct
WHERE  prdct.create_ts  <= TO_TIMESTAMP('14.10.2020 08:16:34.822981', 'DD.MM.YYYY HH24:MI:SS.FF6')
AND    NVL(prdct.modify_ts, DATE '9999-12-31')  > TO_TIMESTAMP('14.10.2020 08:16:34.822981', 'DD.MM.YYYY HH24:MI:SS.FF6');
   
select * from table(dbms_xplan.display);

Plan hash value: 2639949168
----------------------------------------------------------------------------------------------------
| Id | Operation                 | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |          |    1 |    14 |   1103  (1)| 00:00:14 |       |       |
|  1 | SORT AGGREGATE            |          |    1 |    14 |            |          |       |       |
|  2 |  PARTITION LIST ALL       |          |  399 |  5586 |   1103  (1)| 00:00:14 |     1 |     3 |
|* 3 |  TABLE ACCESS STORAGE FULL| PRODUCT  |  399 |  5586 |   1103  (1)| 00:00:14 |     1 |     3 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - storage(NVL("MODIFY_TS",TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 
       08:16:34.822981000' AND "CREATE_TS"<=TIMESTAMP' 2020-10-14 08:16:34.822981000')
    filter(NVL("MODIFY_TS",TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 
       08:16:34.822981000' AND "CREATE_TS"<=TIMESTAMP' 2020-10-14 08:16:34.822981000')

Once you see the problem, you can’t unsee it but for whatever reason, for ages, I was focusing on why there would be such a cardinality difference between A and B for what are basically the single table cardinality estimates for the same table without seeing the explanation right there in front of me.

Note
-----
  - dynamic sampling used for this statement (level=7)

I then got distracted and spent some time investigating why dynamic sampling was kicking in. Parallel query has different rules about when it can kick in. And I found reasons why it might.

But then I put that thought to one side.

Why is dynamic sampling saying this will return 1 row?

This is a small table and the estimate should be accurate and we like dynamic sampling so we want to show the feature to its full capability whenever and wherever it kicks in

Sure enough, as we might expect, if I hint dynamic sampling on the single table, estimate is one.

set pages 999
explain plan for
select /*+ dynamic_sampling(4) */ count(*)
FROM  prdct
WHERE prdct.create_ts                  <= TO_TIMESTAMP('14.10.2020 08:16:34.822981', 'DD.MM.YYYY HH24:MI:SS.FF6')
AND  NVL(prdct.modify_ts, DATE '9999-12-31')      > TO_TIMESTAMP('14.10.2020 08:16:34.822981', 'DD.MM.YYYY HH24:MI:SS.FF6');
   
select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------------
| Id | Operation                 | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |          |    1 |    14 |   1103  (1)| 00:00:14 |       |       |
|  1 | SORT AGGREGATE            |          |    1 |    14 |            |          |       |       |
|  2 |  PARTITION LIST ALL       |          |    1 |    14 |   1103  (1)| 00:00:14 |     1 |     3 |
|* 3 |  TABLE ACCESS STORAGE FULL| PRODUCT  |    1 |    14 |   1103  (1)| 00:00:14 |     1 |     3 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - storage(NVL("MODIFY_TS",TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 
       08:16:34.822981000' AND "CREATE_TS"<=TIMESTAMP' 2020-10-14 08:16:34.822981000')
    filter(NVL("MODIFY_TS",TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 
       08:16:34.822981000' AND "CREATE_TS"<=TIMESTAMP' 2020-10-14 08:16:34.822981000')
 
Note
-----
  - dynamic sampling used for this statement (level=4)

Without access to the sample statements, what suggestive evidence can we find?

select table_name, num_rows, blocks, avg_row_len
from  user_tables
where table_name = 'PRODUCT';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
PRODUCT                               462       4064         193

So that’s not a lot of rows in a lot more blocks than should be necessary.

At this point, we should go off and find out what the reasons might be.

For what it’s worth, for this application, my suspicions are historic direct path maintenance code coupled with table partitioning on status (LATEST/SUPERSEDED) and therefore row movement means an update is a delete + insert, coupled with periods of very low rate of change and small row sizes.

So we end up with old rows moving partition – using a new block for the updated version and leaving behind an empty block where the version was before the update and then relatively few new version rows per run which are using new blocks, and so lots of empty blocks.

i.e. sparsely populated table.

And then dynamic sampling kicks in and samples a bunch of blocks, and finds few actual rows from those samples and comes up with a low estimate and then the whole problem starts. And these were shared tables across the reporting modules so it had a reasonable wide impact.

It would be nice to back this up with some trace files detailing any of the dynamic sampling numbers but I don’t have access by default and mocking this all up outside of work is always a worthwhile exercise but just takes up so much time.

Anyway… move some partitions, rebuild some indexes and 4000 blocks becomes just over 40.

Now our dynamic sampling is looking much better:

----------------------------------------------------------------------------------------------------
| Id | Operation                 | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |          |   1 |     14 |   1103  (1)| 00:00:14 |       |       |
|  1 | SORT AGGREGATE            |          |   1 |     14 |            |          |       |       |
|  2 |  PARTITION LIST ALL       |          | 297 |     14 |   1103  (1)| 00:00:14 |     1 |     3 |
|* 3 |  TABLE ACCESS STORAGE FULL| PRODUCT  | 297 |     14 |   1103  (1)| 00:00:14 |     1 |     3 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - storage(NVL("MODIFY_TS",TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 
       08:16:34.822981000' AND "CREATE_TS"<=TIMESTAMP' 2020-10-14 08:16:34.822981000')
    filter(NVL("MODIFY_TS",TIMESTAMP' 9999-12-31 00:00:00')>TIMESTAMP' 2020-10-14 
       08:16:34.822981000' AND "CREATE_TS"<=TIMESTAMP' 2020-10-14 08:16:34.822981000')
 
Note
-----
  - dynamic sampling used for this statement (level=4)

Exercise repeated for a whole bunch of tables in a similar situation and everything across the board is a lot healthier.

Because of doing many project in the cloud, terraform is the tool which i use regularly. And to get better, i decided to read this book:

If you are working with a cloud of one of the hyperscalers, the you should take a look at terraform and perhaps you should read this book ;-)

If you are interested, take a look at my review at amazon.de (like all my reviews: written in german ;-).

Since a previous post with screenshots was very popular

I have created a  Video Demo on my Youtube Channel

I find it quite surprising that ‘people’ related skills only feature in 1 of the top 10 skills for 2025. That’s contrary to what I had believed would be the case. Interesting stats published this week by World Economic Forum for Top 10 skills of 2025 as below. 1 Analytical thinking/innovation 2 Active learning and […]

Please click on link to open document:

Validate And Perform 19c Data Guard Switchover

What’s up Doc!

So there I was, reading documentation and planning upgrade but still not perfect.

DBUA Command-Line Syntax for Active and Silent Mode

changeUserTablespacesReadOnly does not show from help but exists in documenation.

$ which dbua
/app/product/19.3.0.0/bin/dbua

$ dbua -help
Usage: dbua [<flag>] [<option>]
Following are the possible flags:
-createPartialBackup – Flag to create a new offline partial RMAN backup by setting the user tablespaces in R/O mode.
-backupLocation
-disableParallelUpgrade – Flag to disable the parallel execution of database upgrade.
-executePreReqs – Flag to execute the pre-upgrade checks alone for the specified database.
-sid | -dbName
-sid
-dbName
-help – Shows this usage help.
-ignorePreReqs – Ignore error conditions in pre-upgrade checks.
-keepEvents – Flag to keep the configured database events during upgrade.
-silent – This flag allows you to carry on configuration in silent mode.
-sid | -dbName
-sid
-dbName
-skipListenersMigration – Flag to skip the listener migration process as part of the database upgrade.

Following are the possible options:
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-backupLocation – <Specify directory to backup your database before starting the upgrade>]
[-createGRP – <true | false> To create a guaranteed restore point when database is in archive log and flashback mode.]
[-createListener – <true | false> To create a listener in newer release Oracle home specify listenrName:lsnrPort.]
[-dbName – <Specify Database Name>]
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-disableUpgradeScriptLogging – <true | false> This command disables the detailed log generation for running SQL scripts during the upgrade process. By default this is enabled. To enable the log generation, don’t specify this command.]
[-emConfiguration – <DBEXPRESS | CENTRAL | BOTH | NONE>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-emPassword – <Specify EM admin user password>]
[-emUser – <Specify EM admin username to add or modify targets>]
[-emExpressPort – <Specify the port where EM Express will be configured>]
[-omsHost – <Specify EM management server host name>]
[-omsPort – <Specify EM management server port number>]
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-ignoreScriptErrors – <true | false> Specify this flag for ignoring ORA errors during custom scripts.]
[-initParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-excludeInitParams – <Specify a comma separated list of initialization parameters to be excluded.>]
[-keepDeprecatedParams – <true | false> To retain deprecated parameters during database upgrade.]
[-localListenerWithoutAlias – To set LOCAL_LISTENER without TNS Alias.]
[-listeners – <To register the database with existing listeners, specify listeners by comma separated listenerName:Oracle Home. Listeners from lower release home are migrated to newer release home. Specifying -listeners lsnrName1,lsnrName2 or -listeners lsnrName1:<Oracle home path>,-listeners lsnrName2:<Oracle home path>, DBUA searches specified listeners from GI home (if configured), target home and source home>]
[-localRacSid – <Specify the local System Identifier of the cluster database if the cluster database is not registered in OCR>]
[-logDir – <Specify the path to a custom log directory>]
[-newGlobalDbName – <Specify New Global Database Name. This option can only be used for Oracle Express Edition upgrade>]
[-newSid – <Specify New System Identifier. This option can only be used for Oracle Express Edition upgrades>]
[-newInitParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value. Use this option to specify parameters that are allowed only on the target Oracle home>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-oracleHomeUserPassword – <Specify Oracle Home user password>]
[-pdbs – <Specify a comma separated list with the names of the pluggable databases (PDB) that will be upgraded. Specify ALL to select all or NONE to select none of the pluggable databases for upgrade>]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-pdbsWithPriority – <Specify a comma separated list of pluggable databases (PDB) to be upgraded along with its corresponding priorities (being 1 the top priority) of the format <pdb name>:<upgrade priority>,<pdb name>:<upgrade priority> >]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-performFixUp – <true | false> Enable or disable fix ups for the silent upgrade mode.]
[-postUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed at the end of the upgrade>]
[-preUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed before the upgrade>]
[-recompile_invalid_objects – <true | false> Recompile invalid objects as part of the upgrade.]
[-upgrade_parallelism – <Specify number of CPU’s to be used for parallel upgrade>]
[-upgradeTimezone – <true | false> Upgrade the timezone files of the database.]
[-upgradeXML – <Specify the path to the existing pre-upgrade XML file> This option only applies to in-place database upgrades.]
[-useExistingBackup – <true | false> To restore database using existing RMAN backup.]
[-useGRP – <Specify the name of the existing guaranteed restore point> To restore the database using a specified guaranteed restore point.]

Even when -createListener show as valid syntax, using -createListener is not recognized.
$ cat run_dbua.sh

 
date
/app/product/19.3.0.0/bin/dbua -silent \
-sid db01 \
-oracleHome /app/product/11.2.0.4 \
-useGRP upgrade19c \
-recompile_invalid_objects TRUE \
-upgradeTimezone TRUE \
-emConfiguration NONE \
-skipListenersMigration \
-createListener false \  --- failed
-upgrade_parallelism 8
date
exit

./run_dbua.sh: line 10: -createListener: command not found
This works.
/app/product/19.3.0.0/bin/dbua -silent -sid db01 -skipListenersMigration -oracleHome /app/product/11.2.0.4 -recompile_invalid_objects true -upgradeTimezone true -emConfiguration NONE -upgrade_parallelism 4 -createListener false

What am i missing?

Hopefully, you will have better luck than I did.

Update: there as a space from line above thanks to https://twitter.com/VincePoore

$ grep -r '[[:blank:]]$' run_dbua.sh
-skipListenersMigration \

$ grep -r '[[:blank:]]$' run_dbua.sh | wc -l
1

Here’s an odd little detail about the statistics of column groups. At first glance it’s counter-intuitive but it’s actually an “obvious” (once you’ve thought about it for a bit) consequence of the approximate_ndv() algorithm for gathering stats.

I’ll present it as a question:

I have a table with two columns: flag and v1. Although the column are not declared as non-null neither holds any nulls. If there are 26 distinct values for flag, and 1,000,000 distinct values for v1, what’s the smallest number of distinct values I should see if I create the column group (flag, v1) ?

The question is, of course, a little ambiguous – there’s the number of distinct values that the column (group) holds and the number that a fresh gather of statistics reports it as holding. Here are the stats from a test run of a simple script that creates, populates and gathers stats on my table:

select  column_name, num_distinct 
from    user_tab_cols 
where   table_name = 'T1'
/

COLUMN_NAME                      NUM_DISTINCT
-------------------------------- ------------
FLAG                                       26
ID                                    1000000
V1                                     999040
SYS_STUQ#TO6BT1REX3P1BKO0ULVR9         989120

There are actually 1,000,000 distinct values for v1 (it’s a varchar2() representation of the id column), but the approximate_ndv() mechanism can have an error of (I believe) up to roughly 1.3%, so Oracle’s estimate here is a little bit off.

The column group (represented by the internal column defonition SYS_STUQ#TO6BT1REX3P1BKO0ULVR9) must hold (at least) 1,000,000 distinct values – but the error in this case is a little larger than the error in v1, with the effect that the number of combinations appears to be less than the number of distinct values for v1!

There’s not much difference in this case between actual and estimate, but there test demonstrates the potential for a significant difference between the estimate and the arithmetic that Oracle would do if the column group didn’t exist. Nominally the optimizer would assume there were 26 million distinct values (though in this case I had only created 1M rows in the table and the optimizer would sanity check that 26M).

So, although the difference between actual and estimate is small, we have to ask the question – are there any cases where the optimizer will ignore the column group stats because of a sanity check that “proves” the estimate is “wrong” – after all it must be wrong if the num_distinct is less than the num_distinct of one of the components. Then again maybe there’s a sanity check that only ignores the column group if the estimate is “wrong enough”, but allows for small variations.

I mention this only because an odd optimizer estimate has shown up recently on the Oracle-L mailing list, and the only significant difference I can see (at present) is that a bad plan appears for a partition where this column group anomaly shows up in the stats, but a good plan appears when the column group anomaly isn’t present.

Footnote:

If you want to recreate the results above, here’s the model I’ve used (tested on 19.3.0.0 and 11.2.0.4):

rem
rem     Script:         column_group_stats_5.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             11.2.0.4
rem 

execute dbms_random.seed(0)

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        chr(65 + mod(rownum,26))        flag,
        rownum                          id,
        lpad(rownum,10,'0')             v1
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6   -- > comment to avoid WordPress format issue
order by
        dbms_random.value
/


select  column_name, num_distinct 
from    user_tab_cols 
where   table_name = 'T1'
/

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1 for columns(v1, flag) size 1'
        );
end;
/
 
select  column_name, num_distinct 
from    user_tab_cols 
where   table_name = 'T1'
/

Footnote 2:

As an interesting little statistical quirk, if I defined the column group as (flag, v1) rather than (v1, flag) the estimate for the column group num_distinct was 1,000,000.

Cloud has changed customer expectations and software buying preferences causing partners to adapt their deployment, development and customer support models. Increasingly customers consider &...

We share our skills to maximize your revenue!

I have a simple stored procedure, that I would like to have a passed in string(varchar2) for used in select from where col1 in (var1) in a stored procedure. I've tried everything but doesn't work. Followed is my proc. Thanks CREATE OR REPLACE PROCEDURE WSREVSECT_5 pSectNos varchar2, pRetCode OUT varchar2 ) AS nCount number; BEGIN SELECT count(fksrev) into nCount FROM SREVSECT WHERE sectno IN (pSectNos ) /* as in 'abc', 'xyz', '012' */ ; pRetCode:=to_char(ncount); End;

Hi Connor, Chris, I have a FOR UPDATE SQL used to lock certain rows in ORDERS table but it seems to be bit slow (takes around 1 min). I tried getting plan from dbms_xplan.display_awr. Could you please give me some lead from your past experience and I can look for the any SQL tuning stuff. <code> SELECT PT.ORDER_ID FROM STAGING_001 PN JOIN GTT_TAB IDS ON IDS.MSG_ID = PN.MSG_ID, XMLTABLE ( 'hsbcEnvelope/hsbcMessageBody/pymtTran' PASSING PN.XML_MSG COLUMNS REF_001 VARCHAR2 (50 CHAR) PATH 'REF_001', REF_002 VARCHAR2 (50) PATH 'REF_001', REF_003 VARCHAR2 (10 CHAR) PATH 'REF_001') PMT, ORDERS PT WHERE 1 = 1 AND ( ( PMT.REF_002 IS NOT NULL AND PMT.REF_001 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_002 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_001 IS NOT NULL AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) ) FOR UPDATE OF PT.ORDER_ID NOWAIT; </code> <code> ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27043 (100)| | | | | 1 | FOR UPDATE | | | | | | | | | 2 | BUFFER SORT | | | | | | | | | 3 | CONCATENATION | | | | | | | | | 4 | NESTED LOOPS | | 1003 | 1935K| 11972 (1)| 00:00:01 | | | | 5 | NESTED LOOPS | | 2940 | 3930K| 210 (1)| 00:00:01 | | | | 6 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 7 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 8 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 10 | XPATH EVALUATION | | | | | | | | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID | | 12 | INDEX RANGE SCAN | IDX_PT_REF_001 | 1 | | 3 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1011 | 1950K| 14172 (1)| 00:00:01 | | | | 14 | NESTED LOOPS ...

We have looked at dbms_flashback and dbms_wm but both don't deliver what we actually want: Is there a possibility to make an entire session transient? We would like to use this for our testing scenario's: 1. Start the session in "transient mode" 2. execute a lot of pl/sql with commits, rollbacks and savepoints 3. drop/stop the session ....and everything is magically back to before point 1. dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER is almost there by showing the data at a specified scn, but then you have to reverse every table that is hit by the -third party- code by hand. Not feasible in our environment I'm not very optimistic, but perhaps I missed a new capability of the DB. Is there a way?