4 Replies Latest reply on Apr 20, 2015 8:02 PM by rp0428

    SQL Developer 4 - Schema Drop Downs Very Slow To Load

    oracle_code_monkey

      I'm using SQL Developer 4.0.3.16 on a number of databases with a large number of schemas ( > 200,000 ) and the schema drop downs in the DDL Export and Database Diff take several minutes to load or don't load at all. Is there any kind of work around for this, the schema drop down in the Schema Browser is slow but it still loads in less than a minute. In Schema Browser I can at least filter on only users with objects but there doesn't seem to be anyway to do that on the other screens.

       

      I'm using SQL Developer on Windows 7 x64 and Java 1.7.0_76.

       

      Thanks

        • 1. Re: SQL Developer 4 - Schema Drop Downs Very Slow To Load
          thatJeffSmith-Oracle

          I would expect that for a system with 200,000 schemas.

           

          Not sure what to do to help, other than to avoid the schema drop-downs - use filters for sure.

          • 2. Re: SQL Developer 4 - Schema Drop Downs Very Slow To Load
            I'm using SQL Developer 4.0.3.16 on a number of databases with a large number of schemas ( > 200,000 ) and the schema drop downs in the DDL Export and Database Diff take several minutes to load or don't load at all.

            If you really have that number of schemas there isn't a tool in existence that can extract that amount of info and display it in ANY useable form in a GUI application for a user to interact with.

             

            In 25+ years of working with Oracle, and some of the LARGEST companies in the world I have never run across any Oracle database with more than a few thousand schemas. And those were NOT in the production system but rather represented low volume/activity for dev environments.

             

            So I question the necessity of needing to do a DIFF operation for a non-production schema. I truly sympasize with you if you have to deal with such a system on a regular basis.

            • 3. Re: SQL Developer 4 - Schema Drop Downs Very Slow To Load
              oracle_code_monkey

              Well as far as the number of users, welcome to large old government projects. Our production systems have a user created for every single person who uses the system. In Toad I'd filter any users without objects but SQL Developer doesn't give me that option for Schema DIFF and DDL Exports.

               

              Thanks

              • 4. Re: SQL Developer 4 - Schema Drop Downs Very Slow To Load

                ok - I see three options:

                 

                1. submit an enhancement request to add such a filter

                2. don't use sql dev for the 'diff' project - do it manually

                3. use a workaround process

                 

                For options #2 or #3 you need to repost a related, but similar, question in the database general forum.

                General Database Discussions

                 

                This would be my recommendation:

                 

                1. Use EXPDP to export the metadata only for the databases you want to work with

                2. Use IMPDP to import that metadata on a server/DB dedicated to your DIFF needs

                3. Use a simple script/procedure to drop users that have no objects (easier than trying customize the import)

                4. Use Sql Dev on the resulting DIFF server/DB

                 

                You can easily export only the metadata. Then just import it somwhere where you can drop what you don't need. Sql Dev can then handle the rest for you.

                 

                Sure - it requires a second DB or two but those will be TINY since you won't have ANY data at all; just the code.

                 

                It also gives you a great sandbox to test any scriptss you might want to execute to make two schemas equal.

                 

                I won't follow up at all on the above in this forum. If you want more help with a workaround repost an appropriate question and provide a link to this thread in it.

                 

                I suggest

                With the requisite licensing you can use Oracle's DBMS_METADATA_DIFF package to do your own comparisons.

                http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metadiff.htm#CHEGJBCA