Forum Stats

  • 3,734,411 Users
  • 2,246,967 Discussions
  • 7,857,271 Comments

Discussions

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

TLawrence
TLawrence Member Posts: 8 Blue Ribbon
edited Nov 14, 2018 9:26AM in SQL Developer

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.

pastedImage_1.png

thatJeffSmith-OracleApexBine

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Oct 2, 2018 3:38PM
  • TLawrence
    TLawrence Member Posts: 8 Blue Ribbon
    edited Oct 2, 2018 3:58PM

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

  • TLawrence
    TLawrence Member Posts: 8 Blue Ribbon
    edited Oct 3, 2018 9:25AM

    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;

    ApexBine
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Oct 3, 2018 10:29AM

    Going back to the SR route.

  • TLawrence
    TLawrence Member Posts: 8 Blue Ribbon
    edited Oct 3, 2018 11:01AM

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

    thatJeffSmith-OracleApexBine
  • TLawrence
    TLawrence Member Posts: 8 Blue Ribbon
    edited Nov 14, 2018 9:26AM

    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
    matty c Member Posts: 97 Bronze Badge

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee

    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
    user9540031 Member Posts: 89 Silver Badge

    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,

Sign In or Register to comment.