Indexes tab on Tables causing ORA-01467: sort key too long

TLawrence

When selecting a table from any schema and clicking on the Indexes tab, I get an ORA-01467: sort key too long error.  This happens in version 18.2 as well as earlier versions of SQL Developer (17.x).  See image below.

Is there any way to fix this?  I'm purposely showing a SYSTEM schema table so that it removes questions as to whether the problem exists only in my application database objects.  I am using SQL Developer 18.2 on a Windows 10, 64-bit OS connected to a 12.2 database.

thatJeffSmith-Oracle

i see support notes like this one https://support.oracle.com/knowledge/Oracle%20Database%20Products/1995276_1.html

you may want to open a SR

TLawrence

I didn't think to check one of my 11g databases.  It did NOT have the issue.  Appears to 12c related.  Thank you.

TLawrence

I captured the offending query being executed in SQL Developer (see below).  With that query I was able to narrow the scope of the issue to only occurring when connected to a PLUGGABLE database in 12c using the listener.  When I connect directly to the CONTAINER database or a standard (non-multi-tenant) 12c database the query executes successfully without the ORA-01467 error.

Oracle has published Document 2345091.1 that address the source of this issue and suggests a possible workaround - recoding your query.  This "recoding" suggestion has not been implemented in SQL Developer.  TOAD does not have the issue and must use a different query method in its tool.

SELECT ind.index_owner,

       ind.index_name,

       ind.uniqueness,

       ind.status,

       ind.index_type,

       ind.temporary,

       ind.partitioned,

       ind.funcidx_status,

       ind.join_index,

       ind.columns,

       ind.index_name sdev_link_name,

       'INDEX' sdev_link_type,

       ind.index_owner sdev_link_owner

  FROM (  SELECT index_owner,

                 table_owner,

                 index_name,

                 uniqueness,

                 status,

                 index_type,

                 temporary,

                 partitioned,

                 funcidx_status,

                 join_index,

                    MAX (DECODE (position, 1, column_name))

                 || MAX (DECODE (position, 2, ', ' || column_name))

                 || MAX (DECODE (position, 3, ', ' || column_name))

                 || MAX (DECODE (position, 4, ', ' || column_name))

                 || MAX (DECODE (position, 5, ', ' || column_name))

                 || MAX (DECODE (position, 6, ', ' || column_name))

                 || MAX (DECODE (position, 7, ', ' || column_name))

                 || MAX (DECODE (position, 8, ', ' || column_name))

                 || MAX (DECODE (position, 9, ', ' || column_name))

                 || MAX (DECODE (position, 10, ', ' || column_name))

                 || MAX (DECODE (position, 11, ', ' || column_name))

                 || MAX (DECODE (position, 12, ', ' || column_name))

                 || MAX (DECODE (position, 13, ', ' || column_name))

                 || MAX (DECODE (position, 14, ', ' || column_name))

                 || MAX (DECODE (position, 15, ', ' || column_name))

                 || MAX (DECODE (position, 16, ', ' || column_name))

                 || MAX (DECODE (position, 17, ', ' || column_name))

                 || MAX (DECODE (position, 18, ', ' || column_name))

                 || MAX (DECODE (position, 19, ', ' || column_name))

                 || MAX (DECODE (position, 20, ', ' || column_name))

                 || MAX (DECODE (position, 21, ', ' || column_name))

                 || MAX (DECODE (position, 22, ', ' || column_name))

                 || MAX (DECODE (position, 23, ', ' || column_name))

                 || MAX (DECODE (position, 24, ', ' || column_name))

                 || MAX (DECODE (position, 25, ', ' || column_name))

                 || MAX (DECODE (position, 26, ', ' || column_name))

                 || MAX (DECODE (position, 27, ', ' || column_name))

                 || MAX (DECODE (position, 28, ', ' || column_name))

                 || MAX (DECODE (position, 29, ', ' || column_name))

                 || MAX (DECODE (position, 30, ', ' || column_name))

                 || MAX (DECODE (position, 31, ', ' || column_name))

                 || MAX (DECODE (position, 32, ', ' || column_name))

                    columns

            FROM (SELECT di.owner index_owner,

                         dc.table_owner,

                         dc.index_name,

                         di.uniqueness,

                         di.status,

                         di.index_type,

                         di.temporary,

                         di.partitioned,

                         di.funcidx_status,

                         di.join_index,

                         dc.column_name,

                         dc.column_position position

                    FROM Dba_ind_columns dc, Dba_indexes di

                   WHERE     di.table_owner = :OBJECT_OWNER  -- example: 'MY_SCHEMA'

                         AND di.table_name = :OBJECT_NAME          -- example: 'MY_TABLE_1'

                         AND dc.index_name = di.index_name

                         AND dc.index_owner = di.owner)

        GROUP BY index_owner,

                 table_owner,

                 index_name,

                 uniqueness,

                 status,

                 index_type,

                 temporary,

                 partitioned,

                 funcidx_status,

                 join_index) ind;

thatJeffSmith-Oracle

Going back to the SR route.

TLawrence

SR 3-18454905931 : SQL Developer ORA-01467 : sort key too long error

TLawrence

This SR has been closed with no resolution as the issue is in SQL Developer's internal code used to report index information in a multi-tenant, pluggable database.

matty c

My organization is in the process of upgrading from 18c standalone to 19c multi-tenant architecture and we just came across this exact issue ourselves. The index tab is still broken/useless for multi-tenant PDB connections in the current version of SQL Developer.

I see from the above comment that the SR was closed as deemed to be an issue with SQL Developer so is there any plan to get this fixed? Is this lodged as an official bug with the SQL Developer dev team?

thatJeffSmith-Oracle

Re-Open the SR, mention my name.

I'm on a 19c database, that is a pluggable database (using multitenant architecture), and the queries (and screens in SQLDev) are working just fine.

user9540031

Hi,

@matty c

My organization is in the process of upgrading from 18c standalone to 19c multi-tenant architecture and we just came across this exact issue ourselves.

Interesting! As Jeff said, that query just works, in 19c PDBs just the same as in non-CDBs. There's absolutely no reason why it should fail, and if it does you should probably open a new SR.

Historically there has been a couple of bugs related to ORA-01467 exceptions, but judging by Doc ID 18870.1 (OERR: ORA-1467 "sort key too long" Reference Note) they are all supposed to be fixed in 19c. If you're getting that exception with that query (as mentioned in the original post), then something might be wrong in your database, not in SQL Developer—don't let Support tell you that just because the query originates from SQL Developer it is SQL Developer's problem. It is not.

The index tab is still broken/useless for multi-tenant PDB connections in the current version of SQL Developer.

Of course, if it raises an exception it becomes understandably useless! 😥 As a workaround, you might consider crafting a user-defined extension—your very own home-made index tab—and use that temporarily while your SR is being processed. It's easy to do (for examples, see: https://github.com/oracle/oracle-db-examples/tree/master/sqldeveloper/extension/xml/individual) given that SQL Developer shows all the queries it sends to the DB, so you can pick what you want and change/enhance the rest. But keep this as a temporary solution only: the issue is (most likely) in your DB, not in SQL Developer, so you should work on that now.

My two cents...

Regards,