oracle-dba-表空间与数据文件管理

男娘i 2022-07-16 16:07 182阅读 0赞

表空间与数据文件管理  
ORACLE数据库应用系统ORACLE中间件DBMS数据文件  
逻辑结构：一个数据库有多个表空间，一个表空间有多个段，一个段有多个区段，一个区段有多个数据块，一个数据块有多个操作系统数据库块  
物理结构：一个表空间有多个数据库文件，一个数据文件物理上由操作系统块组成  
查看表空间的数据库块大小  
Select tablespace\_name,block\_size,contents from dba\_tablespaces;

数据文件物理结构  
数据文件:oracle格式的操作系统文件，如dbf。数据文件大小决定表空间大小，当表空间不足，需要增加新的数据文件或者重新设置数据文件的大小  
操作系统块：操作系统操作数据文件的最小单位。一个或多个构成数据库快

表空间分类以及创建表空间  
数据库必备的表空间,【System,临时，还原，默认】表空间。Oracle11g还有sysaux表空间，它是system表空间的扩展，包含各种oracle产品和功能部件使用的数据。  
表空间分为2类，  
系统表空间：数据库系统创建时需要的表空间，  
非系统表空间，还原表空间和临时表空间(默认系统表空间),数据表空间，索引表空间  
语法  
CREATE TABLESPACE tablespace\_name  
\[MINIMUM EXTENT integer\[K|M\]\]：定义该表空间最小区段的大小，这样该表空间中的区段大小为该最小值的整数倍  
\[BLOCKSIZE integer\[K\]\]:指出该表空间使用的非标准块尺寸的大小，单位K。设置前需要设置DB\_CACHE\_SIZE 和 DB\_Nk\_CACHE\_SIZE，且要与BLOCKSIZE相同。使用默认的话，DB\_Nk\_CACHE\_SIZE=0；  
\[LOGGING\[NOLOGGING\]\]：是否把该表空间中数据的变化记录在重做日志文件中，LOGGING记录，NOLOGGING不记录  
\[DEFAULT storage\_clause\]  
\[ONLINE|OFFLINE\]  
创建表空间  
Create tablespace user\_data Datafile ‘G:\\oracleDB\\temp\\userdata1.dbf’ size 100M ;  
查看表空间是否创建  
Select tablespace\_name,logging,status from dba\_tablespaces;  
查看表空间对应的数据文件  
Select file\_name ,tablespace\_name,status from dba\_data\_files where tablespace\_name=’USER\_DATA’;

表空间磁盘管理的2种方案  
数据字典管理，本地管理  
管理表空间的实质是为用户分配可用的区段以及回收空闲区段的过程。  
Oracle9I以上默认本地管理

数据字典管理的表空间磁盘管理  
将每个数据字典管理得表空间的使用情况记录在数据字典表中。当分配或者撤销表空间区段的分配时，则隐含使用sql语句对表操作以记录当前表空间区段的使用情况。并且在还原段中记录了当前变换前的区段使用情况。这种方式增加了数据字典的频繁操作。引入本地管理

本地管理的表空间磁盘管理  
让每一个表空间自己管理表空间区段的分配，记录区段的使用情况  
使用位图在数据文件头中记录数据文件的可用和已用的空间信息。将表空间中数据文件的可用和已用信息记录起来。位图使用一个数据为表示一个数据库块或者一组数据库块的使用情况，而表空间分配的最小单位是EXTENT，而一个区段由多个数据库块组成。当需要在表空间中增加新对象时，需要查找位图，看能否有一段连续的oracle数据库块空闲。  
局限:使用本地管理的表空间不能随意更改默认的存储参数。如初始区段的大小，最大区段数..  
创建表空间  
创建数据字典管理的表空间  
9i的第一个版本默认system表空间是字典管理的，9.2.0.1就默认是本地管理了。要改只能安装的时候选择  
该表空间有3文件，分别放在不同目录下，是为了保证io平衡，每个数据文件大小为100M，最小区段为20K，  
默认存储参数为，初始区段大小，和在次分配区段时，分配的区段大小为20k，所分配的最大磁盘空间为500区段。  
CREATE TABLESPACE Tianjin\_data  
Datafile  
‘G:\\oracleDB\\temp\\01.dbf’ size 100M,  
‘G:\\oracleDB\\temp\\02.dbf’ size 100M,  
‘G:\\oracleDB\\temp\\03.dbf’ size 100M  
minimum extent 20k  
extent management dictionary  
default storage(initial 20k next 20k maxextents 500 pctincrease 0);  
创建本地管理的表空间  
创建表空间  
CREATE TABLESPACE BEIJING\_DATA  
Datafile  
‘G:\\oracleDB\\temp\\01.dbf’ size 100M  
EXTENT management local  
Uniform size 1M; 设置统一的区段尺寸  
验证表空间  
Select tablespace\_name,block\_size,extent\_management,status,initial\_extent ,next\_extent,max\_extents,pct\_increase from dba\_tablespaces WHERE tablespace\_name=’BEIJING\_DATA’;  
Select file\_name ,tablespace\_name,status from dba\_data\_files where tablespace\_name=’BEIJING\_DATA’;

创建还原表空间  
存放还原段，用户1修改了数据，其他用户查看此数据，此时显示的应该不是用户1更改的数据，因为用户1还未提交。为了保证读一致性，oarcle设计了还原段。在还原段中存放更改前的数据。  
不能存放其他任何对象，创建还原表空间时，只能使用datafile,extent management子句。  
创建还原表空间  
CREATE undo tablespace user\_undo  
Datafile ‘G:\\oracleDB\\temp\\user\_undo.dbf’  
size 30M;

SELECT tablespace\_name,status,contents,logging,extent\_management from dba\_tablespaces;  
Contents为undo表示为还原表空间，logging说明该表空间的变化受重做日志的保护，区段管理为本地

创建临时表空间  
用于用户特定会话状态，如用户会话中的排序操作，排序中间结果需要存储在某个区域，这个区域就是临时表空间，临时表空间的排序段是在实例启动后有第一个排序操作时间时创建的。没有创建临时表空间，则默认system表空间。在oracle9I中会自动创建temp的临时表空间，oracle11g临时表空间会默认创建，名字为TEMP，文件名为$ORACLE\_HOME/ORADATA/TEMP01.DBF  
供多个用户使用，临时表空间中的区段在需要时按照创建临时表空间时的参数或管理方式进行扩展  
创建临时表空间  
Create temporary tablespace user\_temp  
Tempfile ‘G:\\oracleDB\\temp\\user\_temp.dbf’ size 20m  
extent management local uniform size 1m;

select tablespace\_name,status,contents,logging from dba\_tablespaces where tablespace\_name like  
CONTENTS为TEMPORARY，contents为 NOLOGGING，即不需要将临时表空间的变化记录到重做日志文件中。  
在初始化参数文件中，有sort\_area\_size参数，表示排序区尺寸大小。为了优化临时表空间中排序操作的性能，最好将Uniform size设置为ssort\_area\_size整数倍

默认临时表空间  
可以在创建数据库时创建，此时使用DEFAULT TEMPORARY TABLESPACE,  
也可以在创建数据库之后创建，此时需要建立临时表空间，在使用  
Alter database default temporary tablespace指令更改临时表  
如果在数据库创建时，没有建立表空间，数据库创建后也没有创建临时表空间且更改默认设置。Oarcle将system表空间设置为默认临时表空间，并且给出告警在alter.log文件中。  
查看当前数据库默认的临时表空间  
Select \* from database\_properties where property\_name like ‘DEFAULT’;  
切换临时表空间，当设置临时表空间为USER\_TEMP时出现临时表空间占用，不满足条件，使用下面即可  
Alter database default temporary tablespace user\_temp;  
可以发现当前数据库的临时表空间为USER\_TEMP。  
select \* from database\_properties where property\_name like ‘DEFAULT%’;  
删除当前使用的临时表空间。不能删除默认的临时表空间  
drop tablespace user\_temp;  
可以创建另外一个新的临时表空间，切换到新建立的表空间，然后删除  
默认临时表空间的类型不能改为PERMANENT永久表空间，不能置为脱机状态  
将默认临时表空间脱机  
Alter tablespace user\_temp offline ;  
创建大文件表空间  
大文件表空间(big File tablespace)简称BFT，不要求段空间管理的类型可以使用，如回滚表空间和临时表空间  
优势，大大减少数据文件数量，容量比普通表空间大，一个普通文件的表空间最多1024个数据文件，而大文件表空间只包含一个文件，但是容量可以达到1024倍数据文件的容量。  
创建大文件表空间3种方式  
创建数据库时，定义大文件表空间并把它作为默认表空间,  
一旦创建默认表空间为大文件表空间类型，以后创建的表空间都为大文件表空间。  
Create database set default bigfile tablespace tbs\_name datafile ‘d:\\temp\\tbs01.dbf’  
Size 2G;

使用create tablespace bigfile创建大文件表空间  
Create bigfile tablespace bigfiletbs datafile ‘G:\\oracleDB\\temp\\temp1.dbf’ size 2g;  
查询表空间的数据文件信息  
Select tablespace\_name ,file\_name,bytes/(1024\*1024\*1024) G from Dba\_data\_files;  
查询表空间的区段管理方式和段空间管理方式  
Select tablespace\_name ,initial\_extent,contents,extent\_management,segment\_s  
pace\_management from dba\_tablespaces where tablespace\_name like ‘BIG%’;  
初始字段(initial\_extent)：大小为64KB，区段管理方式(extent\_management)为local本地管理方式，段空间管理方式(EXTENT\_MANAGEMENT)为自动管理(AUTO)

通过改变默认表空间为大文件表空间，使得后面创建的表空间都为大文件表空间。  
Alter tablespace set default bigfile tablespace;

2种方式实现大文件表空间容量的修改  
1:直接更改  
Alter tablespace 表空间名 resize 4G;  
2:修改表空间为自动扩展  
Alter tablespace表空间名autoextend on next 1G;

Select tablespace\_name ,file\_name,bytes ,autoextensible from dba\_data\_files;  
Autoextensible:表示是否为自动扩展

查看当前数据库的默认表空间的类型  
Select \* from database\_properties where property\_name=’DEFAULT\_TBS\_TYPE’;  
SMALLFILE小文件表空间类型,也就是普通表空间

表空间管理  
脱机管理，脱机管理的表空间无法实现数据访问，  
有些情况需要将表处于脱机状态，情况如下  
1：允许用户访问数据库的一部分，而某些空间不允许用户访问  
2：执行脱机的表空间备份  
3：在数据库打开时，恢复表空间或表空间中的数据文件  
4：在数据库打开时，移动表空间中数据文件  
设置为脱机状态  
Alter tablespace USER\_UNDO offline;  
查看是否脱机，status  
Select status,contents,logging from dba\_tablespaces where tablespace\_name = ‘USER\_UNDO’;  
查看表空间中的数据状态  
Select file\#,name,status from v$datafile ;  
此时无法读取表信息.

只读管理  
只读的表空间不被重做日志保护，减少重做日志的大小。  
Alter tablespace bigfiletbs read only;  
查看表空间状态  
Select tablespace\_name,status from dba\_tablespaces;  
设置为正常状态  
Alter tablespace bigfiletbs read write;

表空间和数据文件管理  
修改表空间大小  
修改表空间4种方法  
 创建表空间时，使用autoextend on子句使得表空间在需要时可以自动扩展  
 创建表空间后使用alter database datafile file\_name autoextend on 修改。  
 表空间增加数据文件  
 修改数据文件大小，即重新设置表空间某个数据文件的大小

 创建数据文件自动扩展的表空间,每次扩展1m  
Create tablespace manager\_tbs1 datafile ‘G:\\oracleDB\\temp\\TBS1.DBF’ SIZE 100  
Uniform size 1M  
M AUTOEXTEND ON;

查看表空间的数据扩展方式  
SELECT FILE\_NAME,TABLESPACE\_NAME,BLOCKS,STATUS,AUTOEXTENSIBLE FROM DBA\_DATA\_FILES WHERE TABLESPACE\_NAME LIKE ‘MAN%’;

 使用alter database命令修改表空间中的数据文件为自动扩展  
Create tablespace manage\_tbs datafile  
查看表空间的是否为自动扩展  
select tablespace\_name ,file\_name ,autoextensible from dba\_data\_files;  
alter database datafile  
使用alter database 修改表空间数据文件为自动扩展,当该文件空间不足时可以自动扩展，每次扩展空间为1m;  
alter database datafile ‘G:\\oracleDB\\temp\\TEMP1.DBF’ autoextend on next 1m;

 向表空间增加数据文件以达到间接扩展表空间大小  
Alter tablespace manager\_tbs1 add datafile ‘G:\\oracleDB\\temp\\tbs02.DBF’ size 50M;  
查看表空间中的数据文件信息  
Select tablespace\_name,file\_name,status,autoextensible from dba\_data\_files  
where tablespace\_name=’MANAGER\_TBS1’;

修改表空间的存储参数  
9i支持，11g不支持  
只对数据字典管理的表空间有效  
修改表空间最小extent尺寸  
Alter tablespace Beijin\_data minimum extent 2m;  
修改表空间默认存储子句  
Alter tablespace BEIJIN\_DATA default storage(initial 2m next 2m maxextents 50);  
查看修改后的表空间的存储参数  
Select \* from dba\_tablespaces where tablespace\_name=’BEIJIN\_DATA’;  
删除表空间  
Drop tablespace tablespace\_name  
\[INCLUDING CONTENTS\[ADD DATAFILES\]\[CASCADE CONSTRAINTS\]\]  
INCLUDING CONTENTS:删除表空间中的所有区段(EXTENTS)  
ADD DATAFILES:删除表空间中的数据文件，文件格式为oracle格式  
CASCADE CONSTRAINTS:删除和该表空间中的表相关的引用完整性约束。  
删除表空间并且删除该表空间下的数据文件，不添加数据文件，默认删除该表文件指针，实际数据文件不会被删除  
Drop tablespace MANAGER\_TBS1 including contents and datafiles;

迁移数据文件  
把当前表空间中的数据文件迁移到其他磁盘空间。迁移数据文件分为2种  
 迁移系统表空间中的数据文件  
1:查看系统表数据信息  
Select tablespace\_name,file\_name,bytes,status from dba\_data\_files where tablespace\_name= ‘SYSTEM’;  
2:根据系统表路径复制一份到其他地方  
Host copy sysytem表路径 新路径  
3:打开到mount，然后迁移  
Startup mount;  
Alter database rename ‘system表路径’ to ‘新路径‘ ;  
Recover database;–介质回复(oracle11G需要) Alter database open;  
4:验证  
Select tablespace\_name,file\_name,bytes,status from dba\_data\_files where tablespace\_name=’SYSTEM’;  
 迁移非系统表  
要求没有活跃的还原段，临时段，排序段等，才可以迁移  
1:设置表空间脱机  
Alter tablespace USER\_UNDO offline;  
2:复制数据文件到新磁盘下  
host copy G:\\ORACLEDB\\TEMP\\USER\_UNDO.DBF G:\\ORACLEDB\\TEMP\\USER\_T.DBF  
3:迁移数据文件  
Alter tablespace USER\_UNDO rename datafile ‘G:\\ORACLEDB\\TEMP\\USER\_UNDO.DBF’ to ‘G:\\ORACLEDB\\TEMP\\USER\_T.DBF’;  
4:联机  
Alter tablespace USER\_UNDO online;  
5:查看  
Select tablespace\_name,file\_name,bytes,status from dba\_data\_files where ta  
blespace\_name=’USER\_UNDO’;

数据字典和本地管理的表空间  
ORACLE9I中切换为本地管理  
EXECUTE DBMS\_SPACE\_ADMIN.TABLESPACE\_MIGRATE\_TO\_LOCAL(tbs\_name);  
切换到数据字典管理  
EXECUTE DBMS\_SPACE\_ADMIN.TABLESPACE\_MIGRATE\_FROM\_LOCAL(tbs\_name)