生成不带dbms_metadata.get_ddl的"create tablespace“脚本

Question

我想从我们的生产数据库中为创建一个"create tablespace"-script。我已经尝试了以下方法，这将导致ORA-31603：

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces where tablespace_name != 'SYSTEM'

在谷歌之后，我发现我需要SELECT_CATALOG_ROLE来使用dbms_metadata.get_ddl。我所拥有的是访问DBA_TABLESPACES的权限。

问:有没有办法在没有只使用DBA_TABLESPACES的SELECT_CATALOG_ROLE的情况下生成"create tablespace"-script？

我会自己写脚本，但我真的不知道如何解释select * from DBA_TABLESPACES给出的信息( AUOTEXTEND是开还是关，初始大小是多少，等等)。

Answer 1

可以在DBA_DATA_FILES表中找到自动扩展和大小的详细信息。如果一个表空间不是BIGFILE表空间，那么它可以有多个大小和属性不同的文件。

一个简单的create脚本示例(在11g中)，没有考虑大多数特殊特性(所以请检查您的表空间，了解压缩和加密等细节，并相应地将它们添加到脚本中。请参阅完整的CREATE TABLESPACE参考here) -并一如既往地进行彻底测试。排除UNDO和TEMP表空间：

  SELECT    'CREATE '
         || DECODE (ts.bigfile, 'YES', 'BIGFILE ') --assuming smallfile is the default table space
         || 'TABLESPACE "'
         || ts.tablespace_name
         || '" DATAFILE' --assuming OMF (Oracle-Managed File names) is used
         || CHR (13)
         || CHR (10)
         || LISTAGG (
                  '  SIZE '
               || df.bytes
               || DECODE (
                     df.autoextensible,
                     'YES',    CHR (13)
                            || CHR (10)
                            || '  AUTOEXTEND ON NEXT '
                            || df.increment_by * ts.block_size
                            || ' MAXSIZE '
                            || CASE
                                  WHEN maxbytes < POWER (1024, 3) * 2
                                  THEN
                                     TO_CHAR (maxbytes)
                                  ELSE
                                        TO_CHAR (
                                           FLOOR (maxbytes / POWER (1024, 2)))
                                     || 'M'
                               END),
               ',' || CHR (13) || CHR (10))
            WITHIN GROUP (ORDER BY df.file_id)
         || CHR (13)
         || CHR (10)
         || '  '
         || ts.logging
         || ' '
         || ts.status
         || ' BLOCKSIZE '
         || ts.block_size
            ddl
    FROM    dba_tablespaces ts
         INNER JOIN
            dba_data_files df
         ON ts.tablespace_name = df.tablespace_name
   WHERE ts.contents = 'PERMANENT' --excludes UNDO and TEMP
GROUP BY ts.tablespace_name,
         ts.bigfile,
         ts.logging,
         ts.status,
         ts.block_size
ORDER BY ts.tablespace_name;

如果表空间中有多个数据文件，请考虑将其更改为BIGFILE表空间。

Answer 2

如果您知道数据文件的路径，并为它们提供与其表空间相同的名称，这将非常有用。如果需要，它还会生成一些表空间。

SELECT    'CREATE '
         || DECODE (ts.bigfile, 'YES', 'BIGFILE ') --assuming smallfile is the default table space
         || 'TABLESPACE "'
         || ts.tablespace_name
         || '" DATAFILE ''C:\Oracle\oradata\yourDATABASEname\'|| ts.tablespace_name || '.DBF'''
         || CHR (13)
         || CHR (10)
         || LISTAGG (
                   '  SIZE '
                || df.bytes
                || DECODE (
                       df.autoextensible,
                       'YES',    CHR (13)
                              || CHR (10)
                              || '  AUTOEXTEND ON NEXT '
                              || df.increment_by * ts.block_size
                              || ' MAXSIZE '
                              || CASE
                                     WHEN maxbytes < POWER (1024, 3) * 2
                                     THEN
                                         TO_CHAR (maxbytes)
                                     ELSE
                                            TO_CHAR (
                                                FLOOR (
                                                    maxbytes / POWER (1024, 2)))
                                         || 'M'
                                 END),
                ',' || CHR (13) || CHR (10))
            WITHIN GROUP (ORDER BY df.file_id)
         || CHR (13)
         || CHR (10)
         || '  '
         || ts.logging
         || ' '
         || ts.status
         || ' BLOCKSIZE '
         || ts.block_size
             ddl
    FROM dba_tablespaces ts
         INNER JOIN dba_data_files df
             ON ts.tablespace_name = df.tablespace_name
   WHERE     ts.contents = 'PERMANENT'                --excludes UNDO and TEMP
         AND ts.tablespace_name IN ('YOUR_TABLESPACE LIST')
GROUP BY ts.tablespace_name,
         ts.bigfile,
         ts.logging,
         ts.status,
         ts.block_size
ORDER BY ts.tablespace_name;