Oracle命令--表空间扩容

表空间操作
//表空间扩展
alter database datafile '/opt/oracle/oradata/ora11g/system01.dbf' resize 2g;
//表空间查询
select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name = 'SYSTEM' group by tablespace_name
//表空间剩余容量
select * from dba_free_space where tablespace_name ='TS_MDSP_DATA ';
//表空间的位置
select * from dba_data_files
//查询表空间剩余容量和总容量
select ef.tablespace_name, round(ef.used_space/(1024*1024)) used_space, round(fs.total_space/(1024*1024)) total_space, round(ef.used_space/fs.total_space*100,2) used_rate, round((fs.total_space-ef.used_space)/fs.total_space*100,2) free_rate from (select cf.tablespace_name,sum(df.bytes - cf.free_bytes) used_space from(select tablespace_name, file_id, sum(bytes) free_bytes from dba_free_space group by tablespace_name, file_id ) cf,dba_data_files df where cf.tablespace_name = df.tablespace_name and cf.file_id = df.file_id group by cf.tablespace_name) ef,( select tablespace_name, sum(case when autoextensible='YES' then maxbytes else bytes end) total_space from dba_data_files group by tablespace_name) fs where ef.tablespace_name = fs.tablespace_name;

-**************************************-
SELECT round(4194303*value/(1024*1024*1024),2) MaxFileSizeGByte from v$parameter where name='db_block_size'
SELECT round(4194303*value/(1024*1024*1024),2) MaxFileSizeGByte from v$parameter where name='db_block_size'

---增加数据文件
alter tablespace users add datafile '/ora/oracle/oradata/datafile.dbf' size 2048

---扩大数据文件
alter tablespace datafile '/ora/oracle/oradata/datafile.dbf' resize 2048M