SQL Developer

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

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

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;

Going back to the SR route.

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

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.

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?

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.

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,

@thatjeffsmith-oracle
cc @matty-c
SR: 3-26888749851 : Oracle 19c 19.11.0.0.0 Throws Error in SQLDEVELOPER when accessing table Indexes
for this issue, as we cannot get past that select index details error in 19c and our developers cannot progress to migrating apps to use the new 19c pdb instances.
regards

Yeah, it's not a sql developer problem, it's a database problem. It looks like your Support case has been sent to the database team.

@User_LUW6A
[...} we cannot get past that select index details error in 19c and our developers cannot progress to migrating apps to use the new 19c pdb instances.
As confirmed by thatJeffSmith-Oracle , this looks more like a bug in the Oracle RDBMS than in SQL Developer itself. The "offending" query should work, regardless of version of the RDBMS. The fact that it fails for some clients running 19c tells that there are some situations (database configurations, etc.) where, unfortunately, a bug is hit and the exception is raised. The difficulty for Oracle Support (and you) is in finding those circumstances which make the issue reproducible. (Out of curiosity, I have made a couple of tests on 2 different 19c DBs, but I couldn't reproduce the issue so far.)
However, using an alternative query might work around it. The original query was written as it stands, using that awesome MAX(DECODE(...) || ... || MAX(DECODE(...) expression, in order to support older versions of the RDBMS which lacked the LISTAGG function—introduced in 11.2, and that everyone has been loving and using ever since. I can't tell for sure if that will work for you because I can't reproduce the issue in the first place, but if it does, then it would be easy to keep the old query for pre-11.2 DBs, and use the replacement query for 11.2 and newer versions.
And all it takes is already built-in in SQL Developer—e.g. you could write a simple user-defined XML extension, as follows:
Copy/paste the XML document below into a local file (e.g. C:\users\xxx\sqldev_table_indexes-alt.xml)
Register that file as an EDITOR in the "Database: User Defined Extensions" tab in SQL Developer's preferences
Restart SQL Developer.

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
    <display type="editor" style="Table" enable="true" objectType="TABLE">
        <name><![CDATA[Indexes (alt.)]]></name>
        <description><![CDATA[Indexes (alt. implementation)]]></description>
        <tooltip><![CDATA[]]></tooltip>
        <drillclass><![CDATA[]]></drillclass>
        <queries>
            <query maxversion="11.1">
                <sql>
                    <![CDATA[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           
                                            and di.table_name  =  :OBJECT_NAME           
                                            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]]>
                </sql>
            </query>
            <query minversion="11.2">
                <sql>
                    <![CDATA[select
                                a.index_owner,
                                a.index_name,
                                a.uniqueness,
                                a.status,
                                a.index_type,
                                a.temporary,
                                a.partitioned,
                                a.funcidx_status,
                                a.join_index,
                                b.columns,
                                a.index_name    as sdev_link_name,
                                'INDEX'         as sdev_link_type,
                                a.index_owner   as sdev_link_owner
                            from
                                (select
                                    di.owner           as index_owner,
                                    di.table_owner,
                                    di.index_name,
                                    di.uniqueness,
                                    di.status,
                                    di.index_type,
                                    di.temporary,
                                    di.partitioned,
                                    di.funcidx_status,
                                    di.join_index
                                from
                                    all_indexes di
                                where
                                    di.table_owner = :OBJECT_OWNER
                                    and di.table_name = :OBJECT_NAME
                                ) a,
                                (select
                                    dc.index_owner,
                                    dc.index_name,
                                    listagg(dc.column_name, ', ') 
                                            within group (order by dc.column_position asc)  as columns
                                from
                                    all_ind_columns dc
                                where
                                    dc.table_owner = :OBJECT_OWNER
                                    and dc.table_name = :OBJECT_NAME
                                group by
                                    dc.index_owner,
                                    dc.index_name
                                ) b
                            where
                                a.index_owner = b.index_owner
                                and a.index_name = b.index_name
                            order by
                                a.index_owner,
                                a.index_name]]>
                </sql>
            </query>
        </queries>
        <display id="null" type="" style="Table">
            <name><![CDATA[Index columns]]></name>
            <description><![CDATA[]]></description>
            <tooltip><![CDATA[]]></tooltip>
            <drillclass><![CDATA[]]></drillclass>
            <query>
                <sql>
                    <![CDATA[select ind.*, ie.column_expression 
                               from All_ind_columns ind,
                                    All_ind_expressions ie
                              where ind.index_name = :INDEX_NAME
                                and ind.table_name = :OBJECT_NAME
                                and ind.index_owner = :OBJECT_OWNER
                                and ind.index_name = ie.index_name(+)
                                and ind.table_owner = ie.table_owner(+)
                                and ind.index_owner = ie.index_owner(+)
                                and ind.column_position = ie.column_position(+)
                              order by ind.column_position asc]]>
                </sql>
            </query>
        </display>
    </display>
</displays>

This will create an additional tab in SQL Developer's table editor, named "Indexes (alt)", doing the same (hopefully) as the original "Indexes" tab, but now using LISTAGG if the target DB is in version 11.2 or higher. This could be an acceptable workaround, at least temporarily.
Regards,