12c 运行SQL中有ORDER BY 竟然没有数据

ALLSTARS_ORACLE

12c 运行SQL中有ORDER BY 竟然没有数据


SQL> select p.pdb_id, p.pdb_name from dba_pdbs p order by p.pdb_id;

PDB_ID PDB_NAME
------ --------
     2 PDB$SEED
     3 ORA12CPA
     4 ORA12CPC

SQL> select p.pdb_id, p.pdb_name, d.file_id, d.tablespace_name, d.file_name from dba_pdbs p, cdb_data_files d where p.pdb_id=d.con_id;

PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ---------------------------------------------
     2 PDB$SEED       2 SYSTEM     /oradata/ORA12CCB/pdbseed/system01.dbf
     2 PDB$SEED       4 SYSAUX     /oradata/ORA12CCB/pdbseed/sysaux01.dbf
     4 ORA12CPC      10 SYSTEM     /oradata/ORA12CCB/ORA12CPC/system01.dbf
     4 ORA12CPC      11 SYSAUX     /oradata/ORA12CCB/ORA12CPC/sysaux01.dbf
     4 ORA12CPC      12 USERS      /oradata/ORA12CCB/ORA12CPC/ORA12CPC_users01.d
                                   bf

     3 ORA12CPA       7 SYSTEM     /oradata/ORA12CCB/ORA12CPA/system01.dbf
     3 ORA12CPA       8 SYSAUX     /oradata/ORA12CCB/ORA12CPA/sysaux01.dbf
     3 ORA12CPA       9 USERS      /oradata/ORA12CCB/ORA12CPA/ORA12CPA_users01.d
                                   bf


8 rows selected.

SQL> select p.pdb_id, p.pdb_name, d.file_id, d.tablespace_name, d.file_name from dba_pdbs p, cdb_data_files d where p.pdb_id=d.con_id order by p.pdb_id;

no rows selected

SQL> select p.pdb_id, p.pdb_name, d.file_id, d.tablespace_name, d.file_name from dba_pdbs p, cdb_data_files d where p.pdb_id=d.con_id order by 1;

no rows selected

就是少一个order by
数据库版本为12.1.0.1

select p.pdb_id, p.pdb_name, d.file_id, d.tablespace_name, d.file_name from dba_pdbs p, cdb_data_files d where p.pdb_id=d.con_id;
有数据
select p.pdb_id, p.pdb_name, d.file_id, d.tablespace_name, d.file_name from dba_pdbs p, cdb_data_files d where p.pdb_id=d.con_id order by p.pdb_id;
没有数据



查到一个12.1.0.1上的Bug 17016479 : SQL QUERY RETURNS UNEXPECTED RESULT.

请试试下面的workaround是否可行？

set _rowsets_cdb_view_enabled to false.  Or disable rowsets by setting the parameter _rowsets_enabled to false.