客服微信
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
学习目标：掌握表空间的操作，理解表空间的作用
表空间是个逻辑概念(可以理解为文件夹),下面包含数据文件(可以理解为文件夹下的文件)。
一般情况下数据库至少包含这四个表空间：SYSTEM、SYSAUX、UNDO及TEMP。
表空间三种分类： PERMANENT 永久表空间、 UNDO 撤销表空间、 TEMPORARY 临时表空间
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
5 USERS YES NO YES 3
6 TBS_T2 YES NO YES 3
6 rows selected.
注意：之后的段管理方式和区管理方式是由表空间创建时确定的。
SQL> select tablespace_name,contents,extent_management,segment_space_management
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN SEGMEN
------------------------------ --------------------- ---------- ------
SYSTEM PERMANENT LOCAL MANUAL
SYSAUX PERMANENT LOCAL AUTO
UNDOTBS1 UNDO LOCAL MANUAL
TEMP TEMPORARY LOCAL MANUAL
USERS PERMANENT LOCAL AUTO
TBS_T2 PERMANENT LOCAL AUTO
6 rows selected
set linesize 1000
col file_name for a30
col tbs_name for a70
select b.name n1, a.name n2, sum(c.bytes) / 1024 / 1024 as free_mb
from v$datafile a
join v$tablespace b
on a.ts# = b.ts#
left join dba_free_space c
on b.name = c.tablespace_name
group by b.name, a.name;
select name from v$datafile; select name from dba_data_files; --表空间信息 • CDB_TABLESPACES • DBA_TABLESPACES • V$TABLESPACE --数据文件 • CDB_DATA_FILES • DBA_DATA_FILES • V$DATAFILE --临时数据文件 • CDB_TEMP_FILES • DBA_TEMP_FILES • V$TEMPFILE --表空间中的表 • ALL_TABLES
--创建默认属性表空间
create tablespace tbs_a datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa01.dbf' size 10M;
提问：一个dbf最大能创建多大？
--查看db_block
set linesize 1000
show parameter block_size
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_block_size integer 8192
--创建一个32G数据文件的表空间
create tablespace tbs_32g datafile size 32g
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks
这里提示数据文件超出了最大限制
select 4194303*8192/1024/1024/1024 from dual;
4194303*8192/1024/1024/1024
---------------------------
31.9999924
总结：8K块的小文件表空间，单个DBF文件大小不能超出32GB。那16K，32K咧？
--查看表空间默认属性
set long 999
set pages 999
select dbms_metadata.get_ddl('TABLESPACE','TBS_A') from dual;
----
CREATE TABLESPACE "TBS_A" DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
---
注意两个重要信息：区本地管理且自动分配空间；段自动管理。
--创建段手工管理的表空间
create tablespace tbs_b datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsb01.dbf' size 10M extent management local uniform size 1m segment space management manual;
--查看手工段管理的表空间详细定义
set long 999
set pages 999
select dbms_metadata.get_ddl('TABLESPACE','TBS_B') from dual;
--------------------------------------------------------------------------------
CREATE TABLESPACE "TBS_B" DATAFILE
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsb01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL;
最后一行信息：区本地管理且统一每次分配1M, 段手动管理。如果在建表时使用缺省说明，则该表将服从其表空间的这些定义
在段区块知识点中，用实验加强验证，每次分配的区为1M
--创建压缩属性的表空间
1.下面的语句表示在表空间中创建的所有表和分区都将使用高级行压缩 create tablespace tbs_c1 datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_c1.dbf' size 20M default row store compress advanced; ## 在该表空间中创建表或分区时，可以覆盖默认的表空间压缩规范。 2.下面的语句表明在表空间中创建的所有索引都将使用高水平的高级索引压缩 ccreate tablespace tbs_i1 datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_i1.dbf' size 20M default index compress advanced high;
SQL> alter tablespace TBS_A add datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa02.dbf' size 10m; Tablespace altered.
当我们创建了一个表空间，同时指定了默认大小，并且没有开启自动扩展。随着数据插入，表空间容量被用满，除了为表空间添加数据文件之外，还可以重新指定数据文件大小
SQL> alter database datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa02.dbf' resize 20m; Database altered.
Select a.tablespace_name, to_char(a.bytes/1024/1024,'99,999.999') total_mb, to_char(b.bytes/1024/1024,'99,999.999') free_mb, to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_mb, to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name;
语法： DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND
DATAFILES [CASCADE CONSTRAINT]
drop tablespace test including contents and datafiles;
注意事项：
1）直接使用DROP TABLESPACE tablespace_name，如果表空间上仍然存在对象，是无法删除的。
SQL> drop tablespace tbs_data; drop tablespace tbs_data * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option SQL> drop tablespace tbs_data INCLUDING CONTENTS ; Tablespace dropped.
2) 如果不加including contents (and datafiles)，表空间下的dbf文件仍然存在,需要手工删除
SQL> drop tablespace TBS_B; Tablespace dropped. [oracle@database ORCLPDB1]$ ls -alt /opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsb01.dbf -rw-r----- 1 oracle oinstall 10493952 Jun 28 00:30 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsb01.dbf [oracle@database ORCLPDB1]$ rm /opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsb01.dbf
3) 数据库在 OPEN 状态下无法删除 SYSTEM、 ACTIVE UNDO、 DEFAULT TEMPORARY、 DEFAULT TABLESPACE 表空间。
示例：
SQL> drop tablespace system; drop tablespace system * ERROR at line 1: ORA-01550: cannot drop system tablespace
6、reuse表空间数据文件
该参数在drop tablespace时未加上including datafile时，可以重用dbf文件
1）删除空间
SQL> drop tablespace tbs_a including contents; Tablespace dropped.
2）创建表空间不添加reuse参数
SQL> create tablespace tbs_a datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa02.dbf' size 10m ; create tablespace tbs_a datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa02.dbf' size 10m * ERROR at line 1: ORA-01119: error in creating database file '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa02.dbf' ORA-27038: created file already exists Additional information: 1
3）创建表空间添加reuse参数
SQL> create tablespace tbs_a datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsa02.dbf' size 10m reuse; Tablespace created.
alter database move datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/test1.dbf' to '/opt/oracle/oradata/ORCLCDB/test1.dbf';
alter tablespace temp2 rename to temp22;
alter tablespace tbs_i1 read only; alter tablespace tbs_i1 read write;
设置表空间读写过程中，如果有活动事务，该操作会被阻塞
--窗口1：在会话464上执行dml，不提交
SYS@pdb2> create table tab3 tablespace tbs_i2 as select * from dba_objects;
Table created.
SYS@pdb2> update tab3 set object_name ='AAA' where object_id=2;
1 row updated.
SYS@pdb2> select sid from v$mystat where rownum=1;
SID
----------
464
--会话2
SYS@pdb2> select sid from v$mystat where rownum=1;
SID
----------
19
SYS@pdb2> alter tablespace tbs_i2 read only;
...hang死
分析方法
oradebug setmypid
ORADEBUG hanganalyze 3
oradebug close_trace
oradebug tracefile_name
hains most likely to have caused the hang:
[a] Chain 1 Signature: 'unbound tx'
Chain 1 Signature Hash: 0x81b0bc5b
-------------------------------------------------------------------------------
instance: 1 (orclcdb.orclcdb)
os id: 115204
process id: 60, oracle@database
session id: 19
session serial #: 23817
module name: 3 (sqlplus@database (TNS V1-V3))
pdb id: 3 (ORCLPDB1)
}
is waiting for 'unbound tx' with wait info:
{
time in wait: 0.007504 sec
heur. time in wait: 2 min 54 sec
timeout after: 0.002496 sec
wait id: 32989
blocking: 0 sessions
current sql_id: 2602020482
current sql: alter tablespace tbs_i2 read only
short stack: ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-semtimedop()+14<-skgpwwait()+187<-ksliwat()+2218<-kslwaitctx()+188<-ktuccw()+1529<-ktucwt3()+5968<-atsdrv1()+19766<-atsdrv()+70<-opiexe()+25980<-opiosq0()+4599<-kpooprx()+387<-kpoal8()+830<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1895<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+243<-0x41D589495541F689 wait history: * time between current wait and wait #1: 0.000037 sec 1. event: 'unbound tx' time waited: 0.011376 sec wait id: 32988 * time between wait #1 and #2: 0.000065 sec 2. event: 'unbound tx' time waited: 0.010982 sec wait id: 32987
● 使数据库的一部分失效，同时允许对数据库剩余部分进行正常访问。
● 进行脱机表空间备份(尽管表空间在联机并正使用的情况下也能备份)
● 在升级或维护应用程序时，使应用程序和它的那组表临时不可用。
1）表空间脱机offline
alter tablespace TBS_A offline;
2）dbf文件offline
alter database datafile '/u01/app/oracle/oradata/ORCL/pdb/ORCL/A80FAD374FAE1806E0538114A8C0E972/datafile/o1_mf_tbs_c_l9rgx2x3_.dbf' offline;
3）表空间联机online
SQL> alter tablespace TBS_A online;
4）dbf文件online
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/pdb/ORCL/A80FAD374FAE1806E0538114A8C0E972/datafile/o1_mf_tbs_c_l9rgx2x3_.dbf' online; alter database datafile '/u01/app/oracle/oradata/ORCL/pdb/ORCL/A80FAD374FAE1806E0538114A8C0E972/datafile/o1_mf_tbs_c_l9rgx2x3_.dbf' online * ERROR at line 1: ORA-01113: file 31 needs media recovery ORA-01110: data file 31: '/u01/app/oracle/oradata/ORCL/pdb/ORCL/A80FAD374FAE1806E0538114A8C0E972/datafile/o1_mf_tbs_c_l9rgx2x3_.dbf' SQL> recover datafile 31; Media recovery complete. SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/pdb/ORCL/A80FAD374FAE1806E0538114A8C0E972/datafile/o1_mf_tbs_c_l9rgx2x3_.dbf' online; Database altered.
1）创建共享临时表空间
create temporary tablespace tmp_a tempfile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tmp_a.dbf' size 10M;
查看共享表空间详细定义
set long 999
set pages 999
select dbms_metadata.get_ddl('TABLESPACE','TMP_A') from dual;
----
CREATE TEMPORARY TABLESPACE "TMP_A" TEMPFILE
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tmp_a.dbf' SIZE 10485760
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
---
注意两个重要信息：区本地管理且自动分配空间；段手工管理。
2）创建本地临时表空间
CREATE LOCAL TEMPORARY TABLESPACE FOR ALL local_temp
TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/local_temp01.dbf'
SIZE 5M AUTOEXTEND ON;
查看本地表空间详细定义
set long 999
set pages 999
select dbms_metadata.get_ddl('TABLESPACE','CNDBA_LOCAL_TEMP') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','LOCAL_TEMP')
--------------------------------------------------------------------------------
CREATE BIGFILE LOCAL TEMPORARY TABLESPACE FOR ALL "LOCAL_TEMP" TEMPFILE
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/local_temp01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 8192 MAXSIZE 2097152M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
提问1：临时表空间能创建表吗？？
SQL> create table ta1(id int) tablespace temp; create table ta1(id int) tablespace temp * ERROR at line 1: ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace
alter tablespace tmp_a add tempfile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tmp_a1.dbf' size 10m;
alter database tempfile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tmp_a.dbf' resize 20m;
select c.tablespace_name, to_char(c.bytes/1024/1024,'99,999.999') total_mb, to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_mb, to_char(d.bytes_used/1024/1024,'99,999.999') use_mb, to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use from (select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name,sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name;
drop tablespace tmp_a including contents and datafiles;
create temporary tablespace temp3
tempfile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tmp3_1.dbf' size 10m
autoextend on tablespace group temp_group ;
--将temp2添加到temp_group
alter tablespace temp2 tablespace group temp_group;
--将temp2从temp_group移除
alter tablespace temp2 tablespace group '';
--查看临时表空间组
select * from dba_tablespace_groups;
--修改系统默认的临时表空间
alter database default temporary tablespace temp_group ;
数据库执行的大规模的排序操作可能会导致临时表空间增长并占用大量磁盘空间。在排序操作完成后，额外的空间不会被释放;它只是被标记为空闲的和可重用的。因此，单个大型排序操作可能在排序操作完成后会导致大量已分配的临时空间仍未使用。出于这个原因，数据库允许您收缩本地管理的临时表空间并释放未使用的空间。
缩小一个临时表空间：
使用ALTER TABLESPACE语句的SHRINK SPACE子句。
收缩临时表空间的具体临时文件：
使用ALTER TABLESPACE语句的SHRINK TEMPFILE子句。
收缩可以在保持表空间或临时文件的其他属性的同时释放尽可能多的空间。可选的KEEP子句定义了表空间或临时文件的最小大小。
收缩是一种在线操作，这意味着如果需要，用户会话可以继续分配排序区，并且已经运行的查询不会受到影响。
1.下面的示例缩小本地管理的临时表空间temp，同时确保最小大小为18M。 alter tablespace temp shrink space keep 18M; # 2.下面的示例收缩本地管理的临时表空间temp的临时文件temp22.dbf。 # 由于省略了KEEP子句，数据库试图将临时文件缩小到尽可能小的大小。 col file_name for a40; select tablespace_name,file_name,bytes/(1024*1024) from dba_temp_files where tablespace_name = 'TEMP'; TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) ------------------------------ ---------------------------------------- ----------------- TEMP /opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_temp_lbsdx7tg_.tmp 18 # 收缩 alter tablespace temp shrink tempfile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_temp_lbsdx7tg_.tmp'; # 查看 SYS@orcl> select tablespace_name,file_name,bytes/(1024*1024) from dba_temp_files where tablespace_name = 'TEMP2'; TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) ------------------------------ ---------------------------------------- ----------------- TEMP /opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_temp_lbsdx7tg_.tmp 14.9921875
大文件表空间指的是 bigfile
创建语句： create bigfile tablespace
大文件表空间只能包含一个数据文件（这是区分大文件与小文件表空间的标准），文件最大可以达到 4GB 个数据块大小，按照 Oracle 通常支持的不同数据块大小（2KB、 4KB、8KB、 16KB 和 32KB），最小的大文件表空间单个文件大小可达 8TB，最大可以达到128TB，当然具体单个文件能够达到的大小还受到操作系统的限制。
数据库默认数据块大小为 8K，则意味着目前大文件表空间最大大小可为 32TB。
示例：
1）创建bigfile表空间
create bigfile tablespace tbs_big datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_big01.dbf' size 10M;
提问：可以添加bigfile表空间数据文件吗？
alter tablespace tbs_big add datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_big02.dbf' size 10M; ERROR at line 1: ORA-32771: cannot add file to bigfile tablespace
查看bigfile表空间
select name,bigfile from v$tablespace;
SQL> create tablespace tbs_c datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_c01.dbf' size 10m autoextend on maxsize 20m;-->设置上限为20m SQL> create tablespace tbs_c datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbs_c01.dbf' size 10m autoextend on unlimited; -->无限制也有上限32GB
提问：如果某个表空间未开启自动扩展，可以针对dbf文件开启扩展功能属性
alter database datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/tbsb01.dbf' autoextend on;
这个功能在RAC中是建议启用的,可以省去datafile命名
alter system set db_create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB1'; create tablespace tbs_d datafile size 10m autoextend on maxsize 20m;
查看OMF管理的DBF命名
select NAME from v$datafile; /opt/oracle/oradata/ORCLCDB/ORCLPDB1/ORCLCDB/FE8CF63D04B91F62E053814AA8C03B8A/datafile/o1_mf_tbs_d_l9qy2qvq_.dbf
SQL> create tablespace tbs_data datafile size 10m; Tablespace created. SQL> create tablespace tbs_idx datafile size 10m; Tablespace created. SQL> create table t1(id int) tablespace tbs_data; Table created. SQL> insert into t1 values(1); 1 row created. SQL> commit ; Commit complete. SQL> create index idx_t1_id on t1(id) tablespace tbs_idx; Index created.
在大表运算较多的环境，创建块大小为32KB的表空间
SQL> create tablespace tbs_16k datafile size 10m blocksize 32k; create tablespace tbs_16k datafile size 10m blocksize 32k * ERROR at line 1: ORA-29339: tablespace block size 32768 does not match configured block sizes SQL> alter system set db_32k_cache_size=10m; System altered SQL> create tablespace tbs_16k datafile size 10m blocksize 32k; Tablespace created.
