oracle维护表空间和数据文件

最新推荐文章于 2021-04-10 15:18:45 发布

bird_cat

最新推荐文章于 2021-04-10 15:18:45 发布

阅读量2.5k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/topwqp/article/details/9153521

版权

oracle 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

1：重要参考 wiki

2： oracle doc 表空间参考

3：来自dba-oracle的参考

26,27,28,29

一： oracle 表空间概念

表空间是联系数据库的物理磁盘（数据文件）和逻辑组件的桥梁，表空间是用来存储oralce数据库对象例如：tables、indexes和 rollback segments。可以想象oracle物理磁盘和逻辑磁盘之间的关系，表空间的概念和这个一样。 PS：数据文件就相当于一个省的的范围（这个是实实在在存在的），而表空间相当于在省上建立的管理机构，行政机构等，这些是相当于逻辑的结构，这些表空间的信息在数据字典中存储。

一个表空间是由至少一个数据库 datafiles（数据文件）组成，在表空间的总大小是可变的，所有分配在表空间的数据文件的物理磁盘空间大小也是可变的。

当表空间被定义的时候，datafiles自动创建，在大多数情况下，所有的datafile是被提前分配的，当datafile创建的时候，空间即被设置，你，你能够定义datafile的初始大小。

tablespace 就是把一组data file 放在一起成为一个 tablespace；是一个逻辑概念。

关于oracle的一些概念：

先科普以下概念：

1：oracle block concept（块概念）

一个oracle块是一个oracle数据块的最小的存储单元，oracle数据库块的大小当创建数据的时候被指定，除非oracle数据进行重构，否则不能进行修改了，数据库块大小通常为2K,4K,8K,16KOR 32K。一旦定义了块的大小，就能够创建新的表空间了，表空间包含若干个块。

注意：oracle数据库instance也包含一个RAM buffer cache，组成RAM buffer cache的块适合和物理数据文件数据块相对应。

数据库包含有多个 tablespace 一个 tablespace包含一个或多个 datafiles ，一个表或者一个index 暂居一个segment

extent 由连续的oracle data block组成：逻辑上连续。

一个segment可以存储在不同的datafiles里面，跨越的概念；extent不能跨越datafiles。oralce data block 包含一个或多 os block

二：表空间的类型：

1 ：一个数据库至少包含一个tablespace 就是 SYSTEM;

system tablespace ；创建数据库的时候，创建包含数据字典包含SYSTEM undo segment

Non-SYSTEM tablespace

2：更科学的划分 tablespace的类型：

1：permanent 永久表空间 (SYSTEM 和　No-SYSTEM tablespace 就是这种类型)

2： undo 表空间

3：temporary 表空间

2，3是用于管理数据库的表空间，所有不永久存储数据，

PS：当一个数据被创建的时候，将包含以下表空间：

SYSTEM (the data dictionary)
SYSAUX (optional database components)
TEMP (temporary tablespace, see tablespace types below)
UNDOTBS1 (undo tablespace, see tablespace types below)
USERS (default users tablespace created)

3：创建表空间 create tablespace oracle doc 11:

创建表空间的简单语法：

SQL> CREATE TABLESPACE  peng DATAFILE 'D:\APP\TOPWQP\ORADATA\ORCL\peng01.DBF'  S
IZE 5M;

表空间已创建。

查找表空间信息

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 USER_TEMP                      NO  NO  YES
         8 MY_SPACE                       YES NO  YES
         9 TS_MYDB                        YES NO  YES
        10 PENG                           YES NO  YES

已选择10行。

如何知道一个表空间有哪些数据文件：

SQL> col file_name format a40
SQL>
SQL> select file_name ,tablespace_name from dba_data_files;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ------------------
D:\APP\TOPWQP\ORADATA\ORCL\USERS01.DBF   USERS
D:\APP\TOPWQP\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
D:\APP\TOPWQP\ORADATA\ORCL\SYSAUX01.DBF  SYSAUX
D:\APP\TOPWQP\ORADATA\ORCL\SYSTEM01.DBF  SYSTEM
D:\APP\TOPWQP\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE
D:\APP\TOPWQP\ORADATA\ORCL\MY_SPACE.DBF  MY_SPACE
D:\APP\TOPWQP\ORADATA\ORCL\TS_MYDB.DBF   TS_MYDB
D:\APP\TOPWQP\ORADATA\ORCL\PENG01.DBF    PENG

已选择8行。

4：两种管理数据字典的方式：

1： locally managed tablespace 自治式管理：

1：自己管理tablespace
2：数据字典中有个Bitmap 用于管理 tablespace （一个位图对应一个extent的意味1占用 0未占用）

在每一个数据文件的头，都会有一个bitmap 位图，来表示空间的占用情况。一个bit代表一个extent，占用1 未占用 0；locally-managed也是串行，不能并行，但是每个表空可以自己管理，所有就相当于并行。如果SYSTEM表空间用的是local-managed ，其他表空间就只能是 local-managed的了。以后就要用local-managed 方式管理表空间。

如果由于历史的原因 dictionary 管理的 SYSTEM tablespace 想变成locally managed：需要执行如下命令：

DBMS_SPACE_ADMIN.TABLESPACE_MIGRANTE_TO_LOCAL('SYSTEM');

注意：做这个命令前的准备工作：　1：全备份数据库　2：确保临时表空间不是SYSTEM

自己管理的tablespace 有两个表管理这些数据：
？疑问每个表空间口有自己的 DEBIT和CREDIT表吗？
DEBIT表：记录所有已经被分配的extent
CREDIT表：记录所有空闲的extent
当申请空间的时候，oracle就去查询这些表，然后分配空间，当向表中
插入数据的时候，就是在申请空间。

2： data-dictionary 的管理。

当自己的表中插入数据的时候，oracle要去查询数据字典（执行很多隐含sql）然后分配空间。因为数据字典只有一个：对数据字典的访问必须是序列化的，不能并发，如果通过数据字典，管理表空间，容易造成数据库的开销过大，所以这种方式有点过时，中央集群的管理方式是 data-dictionary，地方似的管理是新出的管理表空间的方式。

PS ：大事中央管，小事自己管。中央集权制放掉，搞自治

　3：undo tablespace：

主要是用来存储undo segment；就是再对一个表或者表空间做修改的时候，undo 就是做事先进行备份。undo一定是 loacally mananged　　以后有专门一章进行讲解；

5：temporary tablespaces：

就是oracle的临时存储用的，比如做一些大型的排序，在内存中做肯定不行，内存不够，这个时候就需要在临时表空间中进行排序操作。临时的中转站。有一个全局的临时表空间可以被大家共用，只用于存放临时的信息，可以 data-dictionary方式，但是推荐使用locally -mananged管理方式．

强烈建议，在创建数据库的时候额外指定临时表空间。如果没有指定，默认是 SYSTEM这样做事很危险的。所以要指定。

有两种方式指定 default temporary tablespace：
1：创建数据库的使用 create database的时候。

　DEFAULT TEMPORARY TABLESPACE temp　　TEMPFILE '路径/文件名' SIZE 400M
2：可以使用 alter database 的时候。

ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE default_temp2;（这个要首先创建表空间）

6：查询默认的临时表空间

desc  database_properties;

col property_name format a20;
col property_value format a20;

select property_name,property_value from  database_properties;

7 ：如何看一个表空间的类型：

SQL> select tablespace_name,contents,extent_management from dba_tablespaces;

TABLESPACE_NAME    CONTENTS  EXTENT_MAN
------------------ --------- ----------
SYSTEM             PERMANENT LOCAL
SYSAUX             PERMANENT LOCAL
UNDOTBS1           UNDO      LOCAL
TEMP               TEMPORARY LOCAL
USERS              PERMANENT LOCAL
EXAMPLE            PERMANENT LOCAL
USER_TEMP          TEMPORARY LOCAL
MY_SPACE           PERMANENT LOCAL
TS_MYDB            PERMANENT LOCAL
PENG               PERMANENT LOCAL

已选择10行。

8：修改默认表空间

下面进行新加一个表空间：

SQL> create temporary tablespace mytemp  tempfile 'D:\APP\TOPWQP\ORADATA\ORCL\my
temp.DBF'  size 100M extent  management local;

表空间已创建。

SQL>
SQL> ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp;
ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp
                       *
第 1 行出现错误:
ORA-02231: ALTER DATABASE 选项缺失或无效


SQL>
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mytemp;

数据库已更改。

SQL>

默认临时表空间就修改了，可以通过如下命令查询：

SQL> select  property_name,property_value  from  database_properties;

PROPERTY_NAME        PROPERTY_VALUE
-------------------- --------------------
DICT.BASE            2
DEFAULT_TEMP_TABLESP MYTEMP
ACE

DEFAULT_PERMANENT_TA USERS
BLESPACE

一个数据库可以有多个临时表空间，但是只有一个default的表空间。

9：表空间只读设置：

ALTER TABLESPACE mytemp READ ONLY;

执行这条语句：oracle将做如下操作：
1:引起　checkpoint;
2:数据变成只读操作
3：依然可以从表空间中删除一个object(table index 等)　

这个在实际中用的不多，我简单说一下方法，就不做实验了。

实验： 创建一个表空间：
create tablespace wang datafile '/文件名/' size 100M
extent management local uniform size 128k;

create user wang  identified by wang default tablespace wang;

然后用user/user登录
创建表 就在以上指定的表空间中了。

create table wang (id integer,name char(10));
insert into t values(0,'wang');

commit;

insert into t values(1,'qiupeng');
第二条不commit 
然后切换到sys用户；

然后执行
alter tablespace wang read only;

如果transcription没有commit以上语句耗在这里，commit后这个语句才执行。

drop table wang ;这个命令可以执行。

相当于 一个省的地域是实实在在的文件， 省上面的行政机构是相当于
tablespace，是一个逻辑的结构，
这个tablespace的逻辑结构是存储在数据字段中的，可以执行 drop table操作
相当于对数据字典进行访问。

10 ：让一个表空间 offline

以下表空间不能离线：
1：SYSTEM表空间
2：处于active的 undo segment 的表空间
3：default temporary 表空间不能离线 ,temporary 表空间如果不是default的就可以离线。

ALTER TABLESPACE mytemp  OFFLINE;

ALTER TABLESPACE mytemp  ONLINE;

实验：

create table tt(id integer,name char(10));
insert into tt values(0,'wang');
commit;
select * from  tt;
insert into tt values(1 ,'bbbbb');
select * from  tt;
这个时候第二条记录还没有commit；
这意味在undo segment 有一个 active的。
如果表空间离线，就不能对这个表空间内的表做操作。

11：变大和变小表空间；（这个很有现实意义）

有二种方式：
1：表空间可以自动变大创建表空间的时候可以设置 AUTOEXTEND ON实现。

2：可以通过在表空间中加入新的数据文件来增加表空间。

1：查询表空间的使用情况：
DBA_DATA_FILES 这个表记录了所有数据文件情况。

DBA_FREE_SPACE　能够知道还剩余多少表空间。（如果是离线状态不能查出。）

google 搜索： oracle tablespace usage sql
这个ｓｑｌ能够查询出数据库的所有表空间的使用情况；
注意这是一个很重要的sql：

 SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

在我数据库上的执行结果：

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
TS_MYDB                               500   499.9375        100          0
MY_SPACE                              500   499.9375        100          0
PENG                                    5     4.9375         99          1
MYTEMP                                100         99         99          1
UNDOTBS1                              365   335.6875         92          8
USER_TEMP                             500        338         68         32
EXAMPLE                               100    22.3125         22         78
USERS                           9707.0625   462.8125          5         95
SYSAUX                             821.25    41.9375          5         95
SYSTEM                                710      1.125          0        100
TEMP                                   27          0          0        100

12 ：三种方法扩展表空间

方法一：自动扩充表空间

三种方式：
1： create database 指定 datafile 的时候可以增加选项 AUTOEXTEND ON NEXT 10M MAXSIZE 500M 这样数据库文件就能自动增长。
2： create tablespace 这个AUTOEXTEND ON NEXT 10M MAXSIZE 500M 也可以用在create tablespace。
3：ALTER TABLESPACE ADD DATAFILE

下面用第三种方式做实验：（修改的表空间必须实现，这个my_）

SQL> alter tablespace MY_SPACE  add datafile  'D:\APP\TOPWQP\ORADATA\ORCL\mytemp
001.DBF'  size 20M autoextend on next 10M maxsize 100M;

表空间已更改。

增加以后可以到 dba_data_files;表中看是否为自动增加：

 desc dba_data_files;
    col file_name format a20;
    select file_name , tablespace_name,autoextensible from  dba_data_files;

SQL>     select file_name , tablespace_name,autoextensible from  dba_data_files;


FILE_NAME            TABLESPACE_NAME    AUT
-------------------- ------------------ ---
D:\APP\TOPWQP\ORADAT USERS              YES
A\ORCL\USERS01.DBF

D:\APP\TOPWQP\ORADAT UNDOTBS1           YES
A\ORCL\UNDOTBS01.DBF

D:\APP\TOPWQP\ORADAT SYSAUX             YES
A\ORCL\SYSAUX01.DBF

D:\APP\TOPWQP\ORADAT SYSTEM             YES
A\ORCL\SYSTEM01.DBF

D:\APP\TOPWQP\ORADAT EXAMPLE            YES
A\ORCL\EXAMPLE01.DBF

D:\APP\TOPWQP\ORADAT MY_SPACE           YES
A\ORCL\MY_SPACE.DBF

D:\APP\TOPWQP\ORADAT TS_MYDB            YES
A\ORCL\TS_MYDB.DBF

D:\APP\TOPWQP\ORADAT PENG               NO
A\ORCL\PENG01.DBF

D:\APP\TOPWQP\ORADAT MY_SPACE           YES
A\ORCL\MYTEMP001.DBF


已选择9行。

可以看到刚增加的数据文件是否为自动增长 autoextensible 这个属性显示；

如果不想新增加数据文件的方式扩展表空间，可以使用：如下命令对原来的数据文件设置为自动扩展来扩展表空间（道理一样）

alter database datafile  '数据文件位置+数据文件名'  autoextend on next 10M maxsize 100M

方法二：重新定义表空间中的数据文件的大小

手工的让数据文件变大变小：使已经存在的数据文件变大变小，

example：
ALTER DATABASE DATAFILE ' ' RESIZE 200M

这个需要考虑当前需要改变的数据文件的大小。
col file_name format a40;
col tablespace_name format a15;
select file_name, tablespace_name,byte  from  dba_data_files;

注意临时表空间在： DBA_TEMP_FILES 这个表中去找。

先查询出，然后执行，修改数据文件大小，然后再次进行查询。

如果修改的大小小于真实的数据文件的大小，这样oracle就会报错。

方法三：增加数据文件到表空间。这样表空间就增加了。

alter tablespace my_temp add datafile ' ' SIZE 200M

如何移动DATAFILE:

方法一：
使用alter tablespace 命令：
做之前必须做两个工作：
1：让tablespace offline
2：目标数据文件必须存在。
命令：
首先让这个文件离线：
alter tablespace tablespace_name offline;
然后移动文件
然后执行如下命令：

ALTER TABLESPACE tablespace_name RENAME DATAFILE ' ' TO ' ';
相当于指针重新进行了指向了；
然后把表空间上线：
alter tablespace tablespace_name online;

删除表空间：
1： SYSTEM 表空间不能删除
2：有一个 active segments 不能删除。

命令： DROP TABLESAPCE tablespace_name INCLUDING CONTENTS AND DATAFILES;
有三种可选项。

如上命令可以执行，相应的表空间中的 datafile也被干掉了。