一、查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
二、查看表空间对应的FILE_NAME和大小（单个文件最高32GB）
SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;
或者用如下的命令也是可以的
select df.BYTES/1024/1024/1024,df.* from dba_data_files df where df.TABLESPACE_NAME='NNC_DATA01'
三、扩展的动作
1、指定总容量的扩展
alter tablespace NNC_DATA01 add datafile 'D:\U01\APP\ORACLE\ORADATA\ORCL\NCDB\NNC_DATA06.DBF' size 32736m;
2、自动扩容的扩展
alter tablespace NNC_DATA03 add datafile '/data02/ORADATA/nnc_data03_1.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M;
###D:\U01\APP\ORACLE\ORADATA\ORCL\NCDB\NNC_DATA06.DBF 是对应的文件，路径一致用不同的名字就行了
执行完毕之后
1、可以在第二步里查询到文件已经附加到对应的表空间
2、另外在第一步里也可以看到表空间的容量变化
备注（如下生产实际使用）
alter tablespace nnc_data01 add datafile ‘路径/nnc_data01-06.dbf’ size 1G autoextend on;
#################################################################################
### 备注实际生产环境使用过的
### alter tablespace nnc_data01 add datafile ‘路径/nnc_data01-06.dbf’ size 1G autoextend on;
### 引号里面的路径 dbf文件 根据实际的自行更改（NNC_DATA01是需要的执行的，NNC_INDEX01是不需要执行的）
#################################################################################
另外参考推荐一篇文章写的比较好（https://www.cnblogs.com/yisheng163/p/16586744.html）
Oracle扩展表空间
Oracle扩展表空间
Oracle的表空间扩展，一般方法是，对已存在表空间数据文件设置新的大小。
在实际应用场景中，通常还会遇到更多问题，比如设置大小报错，文件超出32G了，需要新增表空间文件。
新增文件报错，文件数超出了预设值。修改预设值不生效，要重起数据库实例才生效。重起数据库实例主库生效后，备份库还需要重起同步生效。
等更多问题会遇到和需要解决，日常还需要巡检表空间的使用情况，表空间文件大小、文件数、文件路径，所以记录此篇。
查看表空间使用情况
--表空间巡查(按GB)
select a.tablespace_name as "表空间名","最大空间(GB)","占用空间(GB)",("占用空间(GB)"-"剩余空间(GB)") as "使用空间(GB)",round(("占用空间(GB)"-"剩余空间(GB)")/"占用空间(GB)"*100,2) as "使用率1(%)"
,round(("占用空间(GB)"-"剩余空间(GB)")/"最大空间(GB)"*100,2) as "使用率2(%)"
from (select tablespace_name,sum(decode(AUTOEXTENSIBLE,'NO',bytes,'YES',maxbytes))/1024/1024/1024 as "最大空间(GB)",sum(bytes)/1024/1024/1024 as "占用空间(GB)" from dba_data_files group by tablespace_name) a
,(select tablespace_name,sum(bytes)/1024/1024/1024 as "剩余空间(GB)" from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 6 desc;
查单个表空间情况
select df.BYTES/1024/1024/1024,df.* from dba_data_files df where df.TABLESPACE_NAME='CPOE_DATA'
表空间扩展
--表空间扩展方法一，对已存在表空间数据文件设置新的大小，单位M。示例：
alter database datafile '/vdb2/service/oracle/data/oracle/oradata/orcl/user04.dbf' resize 32736m
如果报错：ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks
那就是超出最大限制了，如果此时的表空间已经最大化了，建议新增一个对应表空间的数据文件并设定大小。
由于Oracle的Rowid中使用22位来代表Block号，这22位最多只能代表2^22-1(4194303)个数据块，而在我们一般情况下使用的数据块大小为8k，所以数据文件的理论大小最大为： 31.9999924G。
--查看表空间文件 隐藏参数，db_files 值为文件数。
show parameter file;
--查看表空间db_files值
show parameter db_files
新增数据文件
--新增表空间，并指定大小
alter tablespace '表空间名称' add datafile '表空间位置' size '容量大小';
--示例
alter tablespace mytable01 add datafile '/vdb2/service/oracle/data/oracle/oradata/orcl/users06.dbf' size 32736m;
--新增表空间，并指定大小，设置自动增长，并限定最大值。示例
alter tablespace CPOE_DATA add datafile '+DATA/xxxxxx/datafile/cpoe_data.11122.322233' size 2000m autoextend on maxsize 34359721984;
----给当前表空间添加数据文件并自动扩容,没有最大限制。缺省默认值最大扩展到32G.（不指定文件名，只指定路径，会自动生成）
alter tablespace CPOE_DATA add datafile '+DATA' size 1024M autoextend on next 1024M; --测试可用
alter tablespace CPOE_DATA add datafile size 2048M autoextend on next 1024M maxsize unlimited; --未测试
--添加数据文件后，检查一下。查看表空间文件列表名和创建时间
select vdf.name,vdf.CREATION_TIME from v$datafile vdf;
--查表空间文件创建时间和大小
select vdf.name,vdf.CREATION_TIME,vdf.BYTES/1024/1024/1024 as size_G,vdf.BLOCKS/1024/1024/1024 as BLOCKS_size_G,vdf.CREATE_BYTES/1024/1024/1024 as CREATEsize_G ,vdf.* from v$datafile vdf order by vdf.CREATION_TIME desc;
--表字段解释
BYTES：当前文件大小，0的话表示不可访问
BLOCKS ：当前文件块大小，0的话表示不可访问
CREATE_BYTES：创建时候的大小
BLOCK_SIZE：文件的块大小
NAME：文件的名字
--修改表空间 文件数量
alter system set db_files=3000 scope=spfile; --测试可用
如报错，ORA-02095: 无法修改指定的初始化参数，需要重起数据库才能生效。
--重起数据库
--登陆服务器
su - oracle 切换到oracle用户，前后都有空格
ps -ef |grep pmon 查找关健字pmon进程
export ORACLE_SID=abczzzjyyyyyhis1 --设置当前环境默认查看实例
sqlplus / as sysdba --运行sqlplus命令，进入sqlplus环境,--以系统管理员(sysdba)身份连接数据库
--查看状态gv$instance
select instance_name,status from gv$instance;
--数据库实例重起前的准备工作
--归档命令
alter system archive log current 是归档当前的重做日志文件，不管自动归档有没有打都归档。这样后就可以将所有的归档都备份出来了。这样做是为了保证数据的完整和一致。
--生成全局检查点命令
alter system checkpoint global; --生成全局检查点命令
crsctl stat res -t 查看CRSD管理的资源状态
crsctl stat res -t -init 查看OHASD管理的资源的状态
--数据库实例重起
--关闭数据库实例
srvctl stop database -d 数据库名 -o immediate --在操作系统下执行
如要你只是想关闭RAC某几个节点上的数据库,可用下面的命令:srvctl stop instance -d 数据库名 -i 节点1,节点2
在重起前，先归档并手工生成一个检查点，关闭后，再启动数据库时能加快速度并减少一些出错机率。归档命令(alter system archive all或alter system switch logfile) 生成检查点命令(alter system checkpoint)
RAC通常会有多个节点的事实，shutdown abort 关闭单台实际上只关掉了当前连接节点上的数据库，并没有关闭掉RAC上整个数据库.RAC下正确的关闭和启动数据库都应当通过srvctl命令来做。
--起动当前登陆的数据库实例
startup
--查看监听状态
!lsnrctl status
--手工强制将数据库实例注册到监听
alter system register;
--备库恢复
shutdown immediate
startup mount;
alter database recover managed standby database using current logfile disconnect from session;
附加重启实例
1、shutdown immediate
2、startup
参考链接
发表评论