ORACLE PIVOT FUNCTION & ORA-00918

First we create a view v_emp

create or replace view scott.v_emp as
select a.empno,a.sal,b.dname from scott.emp a, scott.dept b
where a.deptno=b.deptno;

Case One:

We get this error because we are pivoting multiple columns without giving alias for each column, we should give alias at least for one column.

Case Two:

In the second case, we still get the same error even if we have added an alias of at least of one of pivoted column.

The length of the name of the last department was 28 , Oracle reconstruct the name for column name as below : 'name_of_department'_NAME_OF_PIVOTING_COLUMN' ==> This gives  

'S013456789013456789013456789'_SAL; it length is 34 . This will be truncated and get 'S013456789013456789013456789'_ with length 30 characters.

The max size of column is 30 characters.

Now try with department_name =S013456789013456789013456788

Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c

Massive Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c (Doc ID 2611336.1)

Last updated on MAY 01, 2020

BUG 27268249 - ENHANCE INDEX NDK STATISTICS GATHERINGW

 it's recommended by change the fix control to disable 27268249 either at the session level or the system level: 
alter session set "_fix_control"='27268249:0';
or
alter system set "_fix_control"='27268249:0';
+++delete existing statistics:
sql> exec DBMS_STATS.DELETE_TABLE_STATS ('<owner-name>','<table-name>', partname => '<part-name>');
++ regather state++++
begin
 DBMS_STATS.gather_table_stats(ownname => '<owner-name>'
 ,tabname => '<table-name>'
 ,partname => NULL
 ,DEGREE => 1
 ,CASCADE => TRUE
 ,no_invalidate =>FALSE
 );
 end;
 /
On Jul 1, 2022 oracle 
reference :  
Ref Doc ID 2611336.1
Patch 33427856: GATHERING STATISTICS FOR INDEXES ON LARGE TABLES MAY BE TIME-CONSUMING

Materialized View

Materialized View Refresh is very slow in Oracle Database 12.2

Bug 26338953 — MVIEW refresh slowed down because of select count(*) queries (Doc ID 26338953.8), 

This fix improves refresh performance when statistics-collection level, as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT, is TYPICAL. With this fix, Cardinality Collection is turned on only when STATISTICS_LEVEL = ADVANCED.   Rediscovery Notes  When stats collection, as set by as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT is TYPICAL, "select count(*)" queries are used to collect cardinalities of tables and MVs.  This causes refresh slowdown.

Same behavior since upgrading from 12.2 to 19c.

Regarding this MOS "12.2: Drop Materialized View Is Very Slow or Hangs (Doc ID 2401976.1)"
I had to disable the statistic collection and truncate the statistic tables. Now it works.

Move and improve your application on Oracle Autonomous Database and Oracle Cloud Infrastructure

About this workshop

Workshop Outline

• Generate SSH Keys
• Generate Auth Token
• Create Virtual Cloud Network
• Create Computes
• Create Autonomous Database
• Copy SSH Key and Wallet
• Install the Application and Migrate the Database
• Run the Application Workload
• Manage Storage Volumes and Images