ORACLE扩展表空间

一、查询表空间使用情况

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