12c 运行SQL中有ORDER BY 竟然没有数据
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.
页:
[1]