1、查哪些需要关自动扩展(先写好,最后再执行)
datafile
set linesize 300
col file_name for a70
select file_name,tablespace_name,autoextensible from dba_data_files where autoextensible=‘YES’ order by tablespace_name;
tempfile
set linesize 300
col file_name for a70
select file_name,tablespace_name,autoextensible from dba_temp_files where autoextensible=‘YES’ order by tablespace_name;
关datafile的自动扩展
alter database datafile ‘/home/oracle/app/oradata/jxyth/system11.dbf’ autoextend off;
关tempfile的自动扩展
alter database tempfile ‘/home/oracle/app/oradata/jxyth/system11.dbf’ autoextend off;
2、查看哪些表空间需要新建数据文件
set linesize 300
col file_name for a70
select file_name,tablespace_name,autoextensible from dba_data_files order by tablespace_name;
set linesize 300
col file_name for a70
select file_name,tablespace_name,autoextensible from dba_temp_files order by tablespace_name;
3、新添加表空间数据文件
datafile
alter tablespace PORATAL_HIS add datafile ‘/newdata/data/poratal_his84.dbf’ size 1g autoextend on;
tempfile
alter tablespace TEMP add tempfile ‘/newdata/data/temp04.dbf’ size 1g autoextend on;
4、关老数据文件的自动扩展
用的1写好的语句
5、再检查一遍
datafile
set linesize 300
col file_name for a70
select file_name,tablespace_name,autoextensible from dba_data_files where autoextensible=‘YES’ order by tablespace_name;
tempfile
set linesize 300
col file_name for a70
select file_name,tablespace_name,autoextensible from dba_temp_files where autoextensible=‘YES’ order by tablespace_name;
6、查看表空间
col tablespace_name format a8
col status format a7
col extent_management format a5
col segment_space_management format a6
col contents format a9
select tpsname,status,mgr,maxsize,c_userd,max_used from
(
SELECT d.tablespace_name tpsname,d.status status,
d.segment_space_management mgr, d.contents type,
TO_CHAR(NVL(trunc(A.maxbytes / 1024 / 1024), 0),‘99G999G990’) maxsize,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),‘990D00’) c_userd,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0),‘990D00’) max_used
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,sum(bytes) bytes,SUM(case autoextensible when ‘NO’ then BYTES when ‘YES’ then MAXBYTES else null end ) maxbytes
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name,SUM(bytes) bytes, MAX(bytes) largest_free FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name AND d.tablespace_name = f.tablespace_name(+)
)
where max_used>0
order by max_used desc;