4 Replies Latest reply on Nov 15, 2018 1:22 PM by sgeffe

    Extremely Long DDL Generation in SQL Tab of Any Table, on Specific Database

    sgeffe

      This probably isn't directly related to SQL Developer, but my luck with Google has been futile at best, so I'll start here, and hope that one of you Oracle Database gurus can point me in the right direction.  I'm running against a PDB running under Oracle 12.1.0.2 SE2 on Windows Server 2012 R2, with the latest build of SQL Developer 18.3 running on a Windows 10 Pro 64-Bit PC.  (I've had this same thing happen on the same database running under version 18.2 of SQL Developer.)

       

      On most of the Oracle Databases I work with, if I click on a Table in the Connections window, then click the SQL tab, the DDL will be generated in a couple seconds, as the following image of the Statements Log window shows:

      DEV.PNG

      However, on one of my test databases, the same operation takes up to 15 minutes, if not longer.  (And after the DDL finally appears, SQL Developer runs another query which seems to hang things up for about the same amount of time.)  Here's another screenshot from that database:

      TEST.PNG

      You can see that this is running quite a bit slower, as well as TWO queries running to generate the DDL!

       

      Again, I don't think this is a problem with SQL Developer itself!  Are there any system-level Oracle tables containing database metadata which might need attention (or more likely, the indexes on various tables)?  Is there any way to rebuild indexes on these types of tables, re-gather statistics, whatever, as sysdba, so that this DDL will generate in a reasonable length of time?