5 Replies Latest reply on Jul 17, 2014 11:20 PM by rp0428

    SQL Developer 4.0.2.15 Build15.21 DBA view of tablespace details incorrect

    dave031

      SQL Developer 4.0.2.15 Build 15.21


      View -> DBA

      Storage -> Tablespaces -> Select a tablespace -> click Datafiles returns correct data


      Storage -> Tablespaces -> Select a tablespace -> click SQL

      Shows a confusion between tablespace DDL mixed with redo log file data.


      Database being queried is 11.2.0.2 using a dba account login

        • 1. Re: SQL Developer 4.0.2.15 Build15.21 DBA view of tablespace details incorrect

          No one here can possibly see any 'confusion' if you don't post a copy of what you are talking about.

           


          • 2. Re: SQL Developer 4.0.2.15 Build15.21 DBA view of tablespace details incorrect
            dave031

            Well, I thought it spoke for itself. I see ASM redo files incorporated into a tablespace ASM data files. The query is pulling more than the tablespace's objects into this SQL definition.

             

            Have you tried to view a tablespace SQL? Does it look right?

             

            This is a RAC database. Maybe the query is not RAC aware???

             

             

            datafiles

             

             

            +DATA_DG/oraDB1/datafile/tst_ahist_hist_05.dbf

            +DATA_DG/oraDB1/datafile/tst_ahist_hist_06.dbf

            +DATA_DG/oraDB1/datafile/tst_ahist_hist_03.dbf

            +DATA_DG/oraDB1/datafile/tst_ahist_hist_01.dbf

            +DATA_DG/tst_ahist_hist_02.dbf

            +DATA_DG/oraDB1/datafile/tst_ahist_hist_04.dbf

             

             

            SQL

             

             

             

             

              CREATE TABLESPACE "tst_ahist_HIST" DATAFILE

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_01.dbf' SIZE 26214400000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,

              SIZE 26214400000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,

              SIZE 26214400000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,

              '+DATA_DG/tst_ahist_hist_02.dbf' SIZE 33554432000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_03.dbf' SIZE 33554432000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_04.dbf' SIZE 10485760000

              AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M,

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_05.dbf' SIZE 33554432000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_06.dbf' SIZE 33554432000

              AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M

              LOGGING ONLINE PERMANENT BLOCKSIZE 8192

              EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

               ALTER DATABASE DATAFILE

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_01.dbf' RESIZE 34359721984;

               ALTER DATABASE DATAFILE

              '+RECO_DG/oraDB1/onlinelog/group_16.2576.782219947' RESIZE 34359721984;

               ALTER DATABASE DATAFILE

              '+DATA_DG/oraDB1/onlinelog/group_16.293.782219945' RESIZE 34359721984;

               ALTER DATABASE DATAFILE

              '+DATA_DG/tst_ahist_hist_02.dbf' RESIZE 34359721984;

               ALTER DATABASE DATAFILE

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_03.dbf' RESIZE 34358689792;

               ALTER DATABASE DATAFILE

              '+DATA_DG/oraDB1/datafile/tst_ahist_hist_04.dbf' RESIZE 34359721984;

            • 3. Re: SQL Developer 4.0.2.15 Build15.21 DBA view of tablespace details incorrect
              Gary Graham-Oracle

              What do you see when running

              select DBMS_METADATA.GET_DDL('TABLESPACE','tst_ahist_HIST')

              from  dual;            

              from a worksheet?  I believe that's all SQL Developer is doing, so if something is not correct, then it would be a DBMS problem.  Maybe it's a known issue and a patch already exists for it.

               

              Regards,
              Gary

              SQL Developer Team

              • 4. Re: SQL Developer 4.0.2.15 Build15.21 DBA view of tablespace details incorrect
                dave031

                The query shows the problem the same way.

                 

                OK, I will look for a patch to the database.

                • 5. Re: SQL Developer 4.0.2.15 Build15.21 DBA view of tablespace details incorrect

                  Thanks for posting the additional info

                  Well, I thought it spoke for itself.

                  Sorry - I don't know how you can think that. This is basically what you said:

                  Shows a confusion between tablespace DDL mixed with redo log file data.

                   

                  How does that show us what YOU are seeing in YOUR system? And without seeing that how can we know what 'confusion' you are referring to?

                  Have you tried to view a tablespace SQL? Does it look right?

                  Yes - right after I read your question.

                   

                  Yes - it looks just fine

                   

                  But YOU are the one with the question/issue. We need to know what YOU see. How can I possibly know if I am seeing the same thing as you?

                  This is a RAC database. Maybe the query is not RAC aware???

                  Good! - NOW you give us some useful information.

                   

                  But no - RAC has NOTHING to do with it.

                  I see ASM redo files incorporated into a tablespace ASM data files. The query is pulling more than the tablespace's objects into this SQL definition.

                  Even better! Now we can answer your question - even though, for some reason you have already marked another answer as correct.

                   

                  ASM  stands for Automatic Storage Managemet. Oracle manages storage - it creates the actual file names.

                   

                  What you posted is what was done to create the tablespace you are looking at.

                   

                  See the ASM Admin Guide sections starting with 'Creating Tablespaces in ASM'

                  http://docs.oracle.com/cd/B28359_01/server.111/b31107/asmbysql.htm#i1025250

                  Creating Tablespaces in ASM

                  When ASM creates a datafile for a permanent tablespace (or a temporary file for a temporary tablespace), the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size.

                   

                  ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in "Managing Disk Group Templates". You can also create and specify your own template.

                   

                  Files in a tablespace might be in both ASM files and non-ASM files as a result of the tablespace history. RMAN commands enable non-ASM files to be relocated to an ASM disk group and enable ASM files to be relocated as non-ASM files.

                   

                  For example, if there is a disk group named dgroup3, you can create a tablespace tblspace3 in that disk group with the following SQL statement:

                  CREATE TABLESPACE tblspace3 DATAFILE '+DGROUP3';  

                   

                  Note the first sentence in the third paragraph:

                  Files in a tablespace might be in both ASM files and non-ASM files as a result of the tablespace history.

                  The DDL to manually recreate an ASM tablespace has to include ALL statements needed to create that current history state. That state might have been created slowly over time by making changes to the tablespace. So in ASM you might have all kinds of things in there.

                   

                  That is particularly true if the person creating and/or managing the ASM tablespaces isn't really familiar with how ASM works.

                   

                  That 'DGROUP3' is a disk group. Right below that first section quoted above is this one

                  Adding New Redo Log Files: Example

                  The following example creates a log file with a member in each of the disk groups dgroup1 and dgroup2. The following parameter settings are included in the initialization parameter file:

                  DB_CREATE_ONLINE_LOG_DEST_1 = '+dgroup1'  DB_CREATE_ONLINE_LOG_DEST_2 = '+dgroup2'  

                  That shows pretty much what you describe; it adds REDO log files to disk groups 1 and 2; but it could just as easily have shown them added to disk group 3 that the first example showed.

                  CREATE TABLESPACE tblspace3 DATAFILE '+DGROUP3'; 

                  That statement creates 'tblspac3' in disk group 3 and the REDO example just above shows how you could have put REDO log files in that same disk group.

                   

                  ASM is VERY different from manual storage management.

                   

                  Using DBMS_METADATA to get the DDL for a normal tablespace and then using that DDL to recreate the tablespace is not a problem.

                   

                  But, generally speaking, you should NOT do that for tablespaces that are part of ASM. You need to let Oracle create and manage ALL ASM files or you are almost guaranteed to have problems.

                   

                  There is NO bug.