ORACLE表空间操作实例
本文主要介绍oracle表空间常见的操作实例，包括创建、查询、增加、删除、修改。表空间和数据文件常用的数据字典和动态性能视图包括v$dbfile、v$datafile、v$tempfile、dba_segments、user_segments、dba_data_files、v$tablespace、dba_tablespaces、user_tablespaces。
创建表空间
1、创建数据表空间
CREATE TABLESPACE DATATBS DATAFILE '/opt/oracle/oradata/datatbs.dbf' SIZE 1024M; #默认扩张属性为off CREATE TABLESPACE DATATBS DATAFILE '/opt/oracle/oradata/datatbs.dbf' SIZE 1024M AUTOEXTEND ON NEXT 64K MAXSIZE 3G; #设置表空间最大值
2、创建undo表空间
CREATE UNDO TABLESPACE undotbs DATAFILE '/opt/oracle/oradata/undotbs.dbf' SIZE 1024M;
3、创建临时表空间
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/oracle/oradata/temp.dbf' SIZE 1024M;
查询表空间
1、查询oracle系统用户的默认表空间和临时表空间
select default_tablespace,temporary_tablespace from dba_users where username = 'USER';
2、查看系统当前默认的数据表空间和临时表空间
select * from dba_properties where property_name like 'DEFAULT%' ; #查询 alter database default tablespace DATATBS2; #修改默认数据表空间 alter database default temporary tablespace TEMP2; #修改默认临时表空间
3、查看所有表空间、数据文件及表空间的数据文件
select * from v$datafile; #查看数据文件 select * from v$tempfile; #查看临时文件 select * from v$tablespace; #查询所有表空间 select file_name,tablespace_name from dba_data_files; #查看表空间对应的数据文件 select sum(d.bytes)/1024/1024/1024 ||'G' as total_bytes,d.tablespace_name from dba_datafiles d where d.tablespace_name = 'UNDOTBS' group by d.tablespace_name; #查看表空间文件大小 select bytes/1024/1024/1024 as "Size(G)",name from v$tempfile order by bytes; #查看临时文件大小
4、查看用户表占用空间大小
#USER_SEGMENTS SELECT SEGMENT_NAME TABLE_NAME,SUM(BYTES)/(1024*1024) "TABLE_SIZE[MB]" FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='TABLE_NAME' GROUP BY SEGMENT_NAME; #DBA_SEGMENTS,shell编程中常用 SEL
5、查询单张表占用空间大小
select segment_name,bytes from dba_segments where segment_name = 'TABLE_NAME' and owner = 'USER';
6、查询所有用户表占用空间的前十名
select * from (select segment_name,bytes from dba_segments where owner = 'USER' order by bytes desc ) where rownum <= 10;
7、查询表空间使用情况
select total.tablespace_name, round(total.MB, 2) as Total_MB, round(total.MB - free.MB, 2) as Used_MB, round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name;
8、查询表空间总大小
select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;
9、查询表空间剩余大小
select tablespace_name, count(*) as extends, round(sum(bytes) / 1024 / 1024, 2) as MB, sum(blocks) as blocks from dba_free_space group by tablespace_name;
10、查看临时表空间使用情况
SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent" FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total WHERE temp_used.tablespace_name = temp_total.tablespace_name;
11、查看当前临时表空间使用大小与正在占用临时表空间的sql
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text from v$sort_usage sort, v$session sess, v$sql sql where sort.SESSION_ADDR = sess.SADDR and sql.ADDRESS = sess.SQL_ADDRESS order by blocks desc;
修改表空间状态
SELECT TABLESPACE_NAME,BLOCK_SIEZE,STATUS FROM DBA_TABLESPACE; #查看表空间状态 SLEECT NAME,BLOKC_SIZE,STATUS FROM V$DATAFILE; #查看数据文件状态 ALTER TABLESPACE DATATBS OFFLINE; #使表空间脱机 ALTER TABLESPACE DATATBS OFFLINE FOR RECOVER; #如果是意外删除了数据文件，则必须带有RECOVER选项 ALTER TABLESPACE DATATBS ONLINE; #使表空间联机 ALTER DATABASE DATAFILE 3 OFFLINE; #使数据文件脱机 ALTER DATABASE DATAFILE 3 ONLINE; #使数据文件联机 ALTER TABLESPACE DATATBS READ WRITE; #使表空间可读写
扩展表空间
SQL> alter tablespace tablespacename add datafile '/home/oracle/add_tablespacename.dbf' size 1024M; #增加数据文件方式，不允许自动增长 SQL> alter tablespace tablespacename add datafile '/home/oracle/add_tablespacename.dbf' size 1024M autoextend on next 5M maxsize 2048M; #增加数据文件方式且允许自动增长 SQL> alter database datafile '/home/oracle/tablespace.dbf' autoextend on next 5M maxsize 2048M; #允许已存在的数据文件自动扩展 SQL> alter database datafile '/home/oracle/tablespace.dbf' resize 2048M; # 改变当前数据文件大小 #调整临时表空间文件 SQL> alter tablespace temp add tempfile '/home/oracle/temp2.dbf' size 2048M; #扩展临时表空间大小，默认autoextend off SQL> alter tablespace temp add tempfile '/home/oracle/temp2.dbf' size 1024M antoextend on next 128M maxsize 2048M; #扩展临时表空间大小 SQL> alter database tempfile '/home/oracle/temp.dbf' resize 2048M;
删除表空间
DROP TABLESPACE DATATBS INCLUDING CONTENTS AND DATAFILES;
说明：including contents 字句用来删除段，and datafiles 字句用来删除数据文件，cascade constraints 字句用来删除所有的引用完整性约束
相关实例
1、移动表空间数据文件
SQL> select tablespace_name,file_name,online_status from dba_data_files where tablespace_name='DATATBS'; SQL> alter tablespace datatbs offline; SQL> host move /opt/oracle/oradata/datatbs.dbf /home/oracle/datatbs.dbf; SQL> alter tablespace datatbs rename datafile '/opt/oracle/oradata/datatbs.dbf' to '/home/oracle/datatbs.dbf'; SQL> alter tablespace datatbs online;
说明：该方法也适用于SYSAUX表空间。对于系统表空间（SYSTEM）和TEMP表空间需要先将关闭实例再将实例启动到mount状态再执行上述步骤。
适用场景：oracle当前数据文件所在的文件系统空间不足的情况，将数据文件移动到其他文件系统。
2、重建undo表空间
create undo tablespace undotbs2 datafile '/home/oracle/oradata/undotbs2.dbf' size 1024M; #创建新的回滚表空间 alter system set undo_tablespace='undotbs2'; #修改使用新建的回滚表空间 alter tablespace undotbs1 offline； #先offline旧的回滚表空间udotbs1 drop tablespace undotbs1 including contents and datafiles；#删除旧的undo表空间
3、重建temp表空间
#创建临时表空间TEMP1 CREATE TEMPOARY TABLESPACE TEMP1 TEMPFILE '/home/oracle/oradata/TEMP1.dbf' SIZE 256M; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1; ALTER DATABASE TEMPFILE '/home/oracle/oradata/TEMP.dbf' OFFLINE; DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; #建TEMP表空间并修改 CREATE TEMPOARY TABLESPACE TEMP TEMPFILE '/home/oracle/oradata/TEMP.dbf' SIZE 1024M; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; ALTER DATABASE TEMPFILE '/home/oracle/oradata/TEMP1.dbf' OFFLINE; DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;优质内容筛选与推荐>>
1、记一次PG错误------Failed to start the database server.
2、笔记
3、hyperledger 入门 demo
4、HDU 2112 HDU Today（最短路径+map)
5、织梦搬家