ORA-30036_Oracle数据库表空间扩容

查看表空间使用率
select a.tablespace_name,a.total "total_size(MB)",
round((a.total-b.free),2) "used_size(MB)",round(b.free,2) "free_size(MB)",
round(((a.total-b.free)/a.total)*100,2) "percent_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(+) 
order by ((total-free)/total) desc;

查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

查看表空间所含的数据文件
col file_name for a70
select file_name,tablespace_name,bytes/1024/1024 from dba_data_files 
where tablespace_name='TBS_HFFCLOG_DATA';

暂停oracle DG日志传输
show parameter archive 确定备库的log_archive_dest_state
alter system set log_archive_dest_state_2 = 'defer';

表空间增加数据文件
ALTER TABLESPACE TBS_HFFCLOG_DATA ADD DATAFILE '+DATA/orcl/datafile/tbs_hffclog_data14.dbf' SIZE 10G AUTOEXTEND ON NEXT 128M MAXSIZE 20G;

ALTER TABLESPACE TBS_CLOUND ADD DATAFILE 'F:\oradata\TBS_CLOUND01.DBF' SIZE 5120M;

查看表空间使用率
select a.tablespace_name,a.total "total_size(MB)",
round((a.total-b.free),2) "used_size(MB)",round(b.free,2) "free_size(MB)",
round(((a.total-b.free)/a.total)*100,2) "percent_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(+) 
order by ((total-free)/total) desc;

查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

查看表空间所含的数据文件
select file_name,tablespace_name,bytes/1024/1024 from dba_data_files order by tablespace_name;

启动oracle DG日志传输
alter system set log_archive_dest_state_2 = 'enable';

备库上查看表空间的数据文件是否创建
select file_name,tablespace_name,bytes/1024/1024 from dba_data_files order by file_id;

主库上查看日志序列号
archive log list;

备库上查看日志同步是否正常
select pid,process,status,sequence# from v$managed_standby;