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.

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

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.