Month: March 2020

Upgrading from Oracle 12cR1 to 12cR2 error ORA-40362: invalid object SYS.ODCIPARTINFOLIST

arcsdegeoLeave a comment

After upgrading a database from Oracle 12cR1 to 12cR2 (Non-CDB) environment, the following errors were thrown in the post-upgrade logs, and XDB component was not valid:

Oracle Database 12.2 Post-Upgrade Status Tool           02-24-2020 09:47:50

Component                               Current         Version  Elapsed Time

Name                                    Status          Number   HH:MM:SS

Oracle Server

   ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement

Oracle Server                           INVALID      12.2.0.1.0  00:06:34

Oracle Text

    ORA-06550: line 4, column 22: PLS-00905: object CTXSYS.TEXTINDEXMETHODS is i

nvalid

ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid ORA-06550: line 4, colu

mn 6:

PL/SQL: Statement ignored

ORA-06550: line 4, column 6: PL/SQL: Statement ignored

    PL/SQL: Statement ignored

    ORA-06552: PL/SQL: Declaration ignored ORA-06553: PLS-905: object CTXSYS.CTX

_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905:

object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA

-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declar

ation ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-0655

2: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is

invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSY

S.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-

905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignore

d

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: D

eclaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA

-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAI

NS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object

CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553:

PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration i

gnored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/S

QL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invali

d

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_C

ONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: ob

ject CTXSYS.CTX_CONTAINS is invalid

Solution:

sqlplus / as sysdba

SQL> GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC;

SQL> spool xdbreload.log

SQL> set echo on;

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> @?/rdbms/admin/xdbrelod.sql

SQL> shutdown immediate;

SQL> startup;

SQL> @?/rdbms/admin/utlrp.sql

SQL> spool off

*** check database components after that:

SQL> select comp_name,status from dba_registry;

if still NOT Valid…..then perform a “drastic measure” by removing Oracle Text component and re-install it again !

To Remove Oracle Text:

sqlplus / as sysdba

SQL>@?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL>@?/rdbms/admin/utlrp.sql

check database components

SQL> select name,status from dba_registry;

if everything is Ok ….then we can install oracle text using DBCA (Database Configuration Assistant)

export DISPLAY=PCXXXXX:0.0

cd $ORACLE_HOME/bin

dbca

 

 

 

Oracle 18c Private Temporary Tables

arcsdegeoLeave a comment

Oracle introduced a new feature in 18c release called “Private Temporary Tables”, This feature will essentially enable you to create a temporary memory based table, and this table will ONLY be viewed by the database session created this table. Moreover, here I will compare it with another old Oracle feature “Global Temporary Tables”.

Picture1

The parameter for the predefined naming is PRIVATE_TEMP_TABLE_PREFIX

SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX

Picture2

To simulate an example of creating a temporary private table:

SQL> alter session set container=ORCLPDB1;

Create a local database user in the pluggable database:

SQL> CREATE USER HR IDENTIFIED BY hr_123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

SQL> grant create session to hr;

SQL> grant resource to hr;

SQL> alter user hr quota unlimited on USERS;

SQL> alter user hr default role all;

Connect to the pluggable database as hr user and execute the following to create a private temporary table:

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_records

(time_id      DATE,

quantity_of_sale  NUMBER(10,2))

ON COMMIT PRESERVE DEFINITION;

Picture3

I will execute the following queries to insert a record in the temporary table:

SQL> insert into ORA$PTT_sales_records values(sysdate,1000);

SQL> commit;

Picture4

If I open another session with the same database account, I can’t query the newly created private temporary table as shown below:

Picture5

the following are the database views:

USER_PRIVATE_TEMP_TABLES

DBA_PRIVATE_TEMP_TABLES

CDB_PRIVATE_TEMP_TABLES

Its worth mentioning that if you attempt to create a private temporary table using SYS account, the following error will be thrown:

ORA-14451: unsupported feature with temporary table