Oracle数据库修改表空间
[*]准备工作：
[*]
[*] 修改表空间大小有两种方法，一种是为表空间增加数据文件(适用于刚建立的表空间)
[*]
[*] alter tablespace users add datafile 'E:\APP\ADMINISTRATOR\ORADATA\AMBTS\CIC2.DBF' size 100M;
[*]
[*] 另一种方法是增加表空间原有数据文件尺寸：（适用于表空间中已经有数据文件的情况）
[*]
[*] alter database datafile 'E:\APP\ADMINISTRATOR\ORADATA\AMBTS\CIC.DBF' resize 200M;
-------------------------------------------------------------------------------------------------------------------------
[*]第一步：查看表空间使用情况
[*]SELECT d.Status "Status",
[*] d.Tablespace_Name "Name",
[*] d.Contents "Type",
[*] d.Extent_Management "Extent Management",
[*] To_Char(Nvl(a.Bytes/1024/1024, 0), '99,999,990.900') "Size (M)",
[*] To_Char(Nvl(a.Bytes - Nvl(f.Bytes, 0), 0)/1024/1024,'99999999.999') || '/' ||
[*] To_Char(Nvl(a.Bytes/1024/1024, 0), '99999999.999') "Used (M)",
[*] To_Char(Nvl((a.Bytes - Nvl(f.Bytes, 0))/a.Bytes * 100, 0),'990.00') "Used %"
[*]FROM Sys.Dba_Tablespaces d,
[*] (SELECT Tablespace_Name,SUM(Bytes) Bytes
[*] FROM Dba_Data_Files
[*] GROUP BY Tablespace_Name) a,
[*] (SELECT Tablespace_Name,SUM(Bytes) Bytes
[*] FROM Dba_Free_Space
[*] GROUP BY Tablespace_Name) f
[*] WHERE d.Tablespace_Name = a.Tablespace_Name(+)
[*] AND d.Tablespace_Name = f.Tablespace_Name(+)
[*] AND NOT (d.Extent_Management LIKE 'LOCAL' AND d.Contents LIKE 'TEMPORARY')
[*]UNION ALL
[*]SELECT d.Status "Status",
[*] d.Tablespace_Name "Name",
[*] d.Contents "Type",
[*] d.Extent_Management "Extent Management",
[*] To_Char(Nvl(a.Bytes/1024/1024, 0), '99,999,990.900') "Size (M)",
[*] To_Char(Nvl(t.Bytes, 0)/1024/1024, '99999999.999') || '/' ||
[*] To_Char(Nvl(a.Bytes/1024/1024, 0), '99999999.999') "Used (M)",
[*] To_Char(Nvl(t.Bytes/a.Bytes * 100, 0), '990.00') "Used %"
[*]FROM Sys.Dba_Tablespaces d,
[*] (SELECT Tablespace_Name,SUM(Bytes) Bytes
[*] FROM Dba_Temp_Files
[*] GROUP BY Tablespace_Name) a,
[*] (SELECT Tablespace_Name,SUM(Bytes_Cached) Bytes
[*] FROM V$temp_Extent_Pool
[*] GROUP BY Tablespace_Name) t
[*] WHERE d.Tablespace_Name = a.Tablespace_Name(+)
[*] AND d.Tablespace_Name = t.Tablespace_Name(+)
[*] AND d.Extent_Management LIKE 'LOCAL'
[*] AND d.Contents LIKE 'TEMPORARY'
[*]
[*]
[*]第二步：查看表空间所属文件（第三步中用到）
[*]select tablespace_name,
[*] file_id,
[*] file_name,
[*] round(bytes / (1024 * 1024), 0) total_space
[*]from dba_data_files
[*]order by tablespace_name;
[*]
[*]
[*]第三步、增加表空间
[*] 因为我们是属于表空间中已经有数据文件的情况，所以使用增加表空间原有数据文件尺寸的方法。
[*]
[*] alter database datafile 'E:\APP\ADMINISTRATOR\ORADATA\AMBTS\CIC.DBF' resize 200M;
[*]
[*] 注意：路径'E:\APP\ADMINISTRATOR\ORADATA\AMBTS\CIC.DBF'是从第二步查询出来的路径。
页:
[1]