30.11.20

ORA-00907: missing right parenthesis ! while creating view

ORA-00907: missing right parenthesis

"ORA-00907: missing right parenthesis" can happen if you really missing it in your SQL, But if that is the case you will not search on google.com :).

One of Our oracle SQL developers reported that while creating a view on one of the oracle database were facing "ORA-00907: missing right parenthesis". We started troubleshooting and found that it's a bug from the oracle in 12.2 databases. The solution is just to apply the patch or change the view of the code.

CREATE OR REPLACE FORCE VIEW "TEST_V" ("TEST_COL1", "TEST_COL2") AS

SELECT

-----

FROM TEST_T A WHERE TEST_COL1 IS NOT NULL

GROUP BY

-----

REGEXP_REPLACE(LISTAGG(TEST_COL1,';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY TEST_COL1),'([^;]+)(;\1)*(;|$)', '\1\3') AS TEST,

ERROR at line 12:

ORA-00907: missing right parenthesis

This view we were able to create in 12.1 but not in 12.2 so we in the last asked developers to change the view. That is how we handle "ORA-00907: missing right parenthesis" issue. If you are facing the same issue and are not able to find any clue, just try to create it on the older version. The actual information regarding the bug is not available yet but we update soon we will find it.

Oracle Means company, not database :) Keep sharing...

26.11.20

Oracle Fail Safe Compatibility with 19c

Is oracle fail Safe is not compatible with the windows 2019?

The answer is yes.

Below is the compatibility Matrix.:-

Note that version 4.1.0 is already on extended support.

Also, check "Oracle Fail Safe is deprecated with Oracle Database 19c".

Oracle Mean company, not database :)

2.11.20

ORA-65114: space usage in container is too high

I was creating tablespace in a newly created PDB and was facing " ORA-65114: space usage in the container is too high "

ORA-65114: space usage in the container is too high

So it happens when you created PDB with a storage option less than you need. Below is the example, I used to create PDB:

create pluggable database TEST admin user admin identified by "oracle" default tablespace USERS

datafile '/u02/oradata/TEST/pdb1_users01.dbf' size 250m autoextend on

storage (maxsize 1g max_shared_temp_size 1g)

file_name_convert=('/u02/oradata/TEST/datafile/','/u02/oradata/TEST/TEST');

Want to know drawback of oracle ? check this

This mean oracle will not go beyond 1G. As I used MAX to 1G and below is the error.

Below is the error:-

SQL> CREATE TABLESPACE "TEST" LOGGING DATAFILE '/u02/oradata/TEST.DBF' SIZE 750M AUTOEXTEND ON NEXT 8M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "TEST" LOGGING DATAFILE '/u02/oradata/TEST.DBF' SIZE 750M AUTOEXTEND ON NEXT 8M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ERROR at line 1:

ORA-65114: space usage in container is too high

Solution:-

Just increase the storage limit of the pluggable database. Run the following SQL in the current PDB to limit the size of all datafiles of that PDB:

SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

OR if you want a limited SIZE, You can still define the size.

SQL> alter pluggable database storage (MAXSIZE 120G);

Note:- If not defined storage option by default of MAX_PDB_STORAGE is no limit.

How to check the max size of PLUGGABLE DATABASE STORAGE?

To check value in current PDB, Conenct to PDB and run below :

select PROPERTY_VALUE FROM database_properties WHERE property_name = 'MAX_PDB_STORAGE';

To check value in current CDB for all PDB'S, Conenct to CDB and run below :

select PROPERTY_NAME,PROPERTY_VALUE,DESCRIPTION,CON_ID FROM cdb_properties WHERE property_name = 'MAX_PDB_STORAGE';

Oracle Mean company, not database :)

If it helps. Please comment and let us know :)

OPatch failed with error code 73

I was upgrading 19.3 to 19.7 and applying the below patch and during the patch, I faced the error "OPatch failed with error code 73:-

30805684: OJVM RELEASE UPDATE 19.7.0.0.0

Note:- All instances were down.

Please check below opatch process :-

opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.2.0.7.0

Logfile location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-26-35PM_1.log

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active executables are not used by opatch process :

/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Following active executables are used by opatch process :

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-26-35PM_1.log

OPatch failed with error code 73

In Logs:-

Following active executables are not used by opatch process :/u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Following active executables are used by opatch process : Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active executables are not used by opatch process : /u01/app/oracle/product/19.0.0/dbhome_1/bin/oracle

Solution:-

This was happening because there was one session that was still connected to sqlplus binaries.

ps -ef|grep sqlplus

Killed the session and successfully completed it.

opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory from

: /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-33-39PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 30805684

Do you want to proceed? [y|n] y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')

Is the local system ready for patching? [y|n] y

User Responded with: Y

Backing up files...

Applying interim patch '30805684' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 30805684 successfully applied.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-19_16-33-39PM_1.log

OPatch succeeded.

Note:- Make sure there are no binaries used during applying the patch.

Keep sharing.

Oracle Mean company, not database :)