获取表空间DDL

通过sys_connect_by_path可以获取某个用户用到的表空间的简单建立DDL。

SQL> DECLARE
2 l_sql VARCHAR2(32767) := '';
3 l_len INT := 255;
4 i INT := 1;
5 BEGIN
6 dbms_output.enable(1000000);
7 FOR c_tablespace IN (SELECT DISTINCT tablespace_name
8 FROM dba_segments
9 WHERE owner = 'SYS') LOOP
10 i := 1;
11 SELECT 'create tablespace ' || c_tablespace.tablespace_name ||
12 ' datafile ' ||
13 MAX(substr(sys_connect_by_path('''' || file_name || ''' size ' ||
14 fsize || 'm', ','), 2)) || ';'
15 INTO l_sql
16 FROM (SELECT file_name,
17 BYTES / 1024 / 1024 FSIZE,
18 tablespace_name,
19 row_number() over(PARTITION BY tablespace_name ORDER BY file_name) rn
20 FROM DBA_DATA_FILES
21 WHERE tablespace_name = c_tablespace.tablespace_name) T1
22 START WITH rn = 1
23 CONNECT BY rn = rownum;
24 WHILE i <= ceil(length(l_sql) / l_len) LOOP
25 dbms_output.put_line(substr(l_sql, l_len * (i - 1) + 1, l_len));
26 i := i + 1;
27 END LOOP;
28 END LOOP;
29 END;
30 /

之所以用一个游标是因为避免由于数据文件过多，导致ora-01489错误。

稍微修改这个块可以得到更详细的建立表空间的DDL。