1、列出表空间
SQL> select name from v$tablespace;
删除表空间，包括属于它的数据文件（物理上也同时删除）
SQL> drop tablespace qiu including contents and datafiles;
删除之后，查询表空间视图确认
select name from v$tablespace;
到ASM目录去验证，看物理文件是否已经删除
[[email protected] ~]$ export ORACLE_SID=+ASM1
[[email protected] ~]$ asmcmd
ASMCMD> ls
ASMCMD> cd DATA
ASMCMD> ls
ASMCMD> cd RACDB
ASMCMD> ls
ASMCMD> cd DATAFILE
ASMCMD> ls
在ASM上创建表空间，自定义目录及DATAFILE的名字
create tablespace qiu datafile ‘+DATA/RACDB/DATAFILE/qiu01.dbf’ size 40G autoextend on;
在qiu表空间中增加一个数据文件
alter tablespace qiu add datafile ‘+DATA/RACDB/DATAFILE/qiu02.dbf’ size 30G AUTOEXTEND ON;
(###alter tablespace qiu add datafile ‘+DATA/RACDB/DATAFILE/qiu02.dbf’ size 30G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;)
使一个已存在的数据文件自动增长：
alter tablespace datafile ‘+DATA/RACDB/DATAFILE/qiu02.dbf’ AUTOEXTEND ON;
查询
SQL> select name from v$datafile;
SQL> select name from v$tablespace;
–创建新的表空间
create tablespace njdata datafile ‘+WLW_DATA1’ size 28000m autoextend on next 100m maxsize 30000m extent management local segment space management auto;
–查看表空间数据文件
select file_name from dba_data_files where tablespace_name=’NJDATA’;
–扩展表空间
alter tablespace njdata add datafile ‘+WLW_DATA1’ size 28000m autoextend on next 100m maxsize 30000m;
–查询当前用户所属表空间
select username,default_tablespace from user_users;
–创建用户、赋予权限
create user sdj identified by sdj ACCOUNT UNLOCK
DEFAULT TABLESPACE njdata TEMPORARY TABLESPACE TEMP;
grant connect to sdj;
grant resource to sdj;
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO sdj;
select count(*) from sdj.historydata
select * from sdj.historydata order by rtime desc
— 查看所有表空间，总容量，剩余容量
SELECT tbs 表空间名,
sum(totalM) 总共大小M,
sum(usedM) 已使用空间M,
sum(remainedM) 剩余空间M,
sum(usedM)/sum(totalM)*100 已使用百分比,
sum(remainedM)/sum(totalM)*100 剩余百分比
FROM(
SELECT b.file_id ID,
b.tablespace_name tbs,
b.file_name name,
b.bytes/1024/1024 totalM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
sum(nvl(a.bytes,0)/1024/1024) remainedM,
sum(nvl(a.bytes,0)/(b.bytes)*100),
(100 – (sum(nvl(a.bytes,0))/(b.bytes)*100))
FROM dba_free_space a,dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY b.tablespace_name
)
GROUP BY tbs
— 查看所有数据文件路径以及对应的表空间名
select file_name , tablespace_name from dba_data_files;
SELECT
TABLE_NAME,
TABLESPACE_NAME
FROM
USER_TABLES
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 SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes “% USED “,
(c.bytes * 100) / a.bytes “% FREE “
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
ITB运维部落—http://www.itbcn.cn—ITB运维技术交流之家平台
MySQL数据库集群——PXC
CentOS7.8安装部署Oracle 21C Rac集群完整版
Mysql-Cluster-7.5搭建数据库集群
MySQL 高可用集群架构之MMM