--查询表空间使用率
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
col file_name for a60
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space_mb
FROM dba_data_files
ORDER BY tablespace_name;
加表空间:
SQL> alter tablespace SYSAUX add datafile '/u01/app/oracle/oradata/xxx/sysaux02.dbf' size 200m;
Tablespace altered.
SQL> alter tablespace SYSTEM add datafile '/u01/app/oracle/oradata/xxx/system02.dbf' size 200m;
Tablespace altered.
ALTER DATABASE DATAFILE 21 resize 25G;
ALTER DATABASE DATAFILE 24 resize 25G;
alter database datafile '+SSDDG/xxx/datafile/ssd_indextbs1.dbf' resize 24G;
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
col file_name for a60
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space_mb
FROM dba_data_files
ORDER BY tablespace_name;
加表空间:
SQL> alter tablespace SYSAUX add datafile '/u01/app/oracle/oradata/xxx/sysaux02.dbf' size 200m;
Tablespace altered.
SQL> alter tablespace SYSTEM add datafile '/u01/app/oracle/oradata/xxx/system02.dbf' size 200m;
ALTER DATABASE DATAFILE 21 resize 25G;
ALTER DATABASE DATAFILE 24 resize 25G;
alter database datafile '+SSDDG/xxx/datafile/ssd_indextbs1.dbf' resize 24G;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-2132584/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/91975/viewspace-2132584/