获取表空间定义

SELECT CASE
         WHEN (SELECT MIN(1)
                 FROM sys.dba_data_files
                WHERE tablespace_name = ddf.tablespace_name
                  AND file_id < ddf.file_id) = 1 THEN
          'ALTER  TABLESPACE  ' || d.tablespace_name || ' ADD DATAFILE '''
         ELSE
          'CREATE TABLESPACE  ' || d.tablespace_name || ' DATAFILE '''
       END || REPLACE(REPLACE(ddf.file_name,
                              substr(ddf.file_name,
instr(ddf.file_name,
                                           '/',
                                           -1) + 1),
lower(d.tablespace_name)) || lpad((SELECT COUNT(1) + 1
FROM sys.dba_data_files                           WHERE tablespace_name = ddf.tablespace_name                                                                   AND file_id < ddf.file_id), 2, '0') || '.dbf',
' /u01/oracle/oradata/appserv',
'+DATA_GAS') || '''' || '  SIZE ' || to_char(greatest((ddf.bytes / 1024 / 1024)+10,11),
      '999999') || 'M   ' || CASE
         WHEN (SELECT MIN(1)
                 FROM sys.dba_data_files
                WHERE tablespace_name = ddf.tablespace_name
                  AND file_id < ddf.file_id) = 1 THEN
          ';'
         ELSE
          ' EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ;'        END
  FROM sys.dba_tablespaces d,
       sys.sm$ts_avail     a,
       sys.sm$ts_free      f,
       sys.dba_data_files  ddf
 WHERE d.tablespace_name = a.tablespace_name
   AND f.tablespace_name(+) = d.tablespace_name
   AND ddf.tablespace_name = d.tablespace_name
   AND d.tablespace_name NOT IN ('SYSTEM',
                                 'SYSAUX',
                                 'UNDOTBS1',
                                 'TEMP',
                                 'USERS',
                                 'PSTEMP')
 ORDER BY ddf.file_id;