ORACLE 表空间操作和表的删除(一)

 

最近在用IMP 命令导入dmp数据的时候,发现表空间无法扩展,一查,才发现表空间SYSTEM已经使用了31.2G。

经过查找资料,总结如下:

1.一般不在SYSTEM表空间存放业务数据;

2.SYSTEM表空间最大值是32G;-----所有表空间的物理文件最大都是32G

下面是关于ORACLE表空间的基本常识和操作基本语法。

        Oracle数据库开创性地提出了表空间的设计理念,这为Oracle数据库的高性能做出了不可磨灭的贡献,Oracle中很多优化都是基于表空间的设计理念而实现的。
        Oracle数据库被划分成称作为表空间的逻辑区域——形成Oracle数据库的逻辑结构。一个Oracle数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是Oracle数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。
        每个Oracle数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。所以,业务数据最好不要存放于SYSTEM表空间下。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间,这对数据库的使用带来更大的方便。
        例如:可以把oracle数据库看作一个实在房间,表空间可以看作这个房间的空间,是可以自由分配,在这空间里面可以堆放多个箱子(箱子可以看作数据库文件),箱子里面再装物件(物件看作表)。用户指定表空间也就是你希望把属于这个用户的表放在那个房间(表空间)里面。
        表空间是一个虚拟的概念可以无限大,但是需要由数据文件作为载体。表空间的大小,其实就是数据文件的大小。

 

Segment(段)

        段是指占用数据文件空间的通称,或数据库对象使用的空间的集合;段可以有表段、索引段、回滚段、临时段和高速缓存段等。

Extent(区间)

        分配给对象(如表)的任何连续块叫区间;区间也叫扩展,因为当它用完已经分配的区间后,再有新的记录插入就必须在分配新的区间(即扩展一些块);一旦区间分配给某个对象(表、索引及簇),则该区间就不能再分配给其它的对象。

如图所示:

 

 

 

 

        Tablespace与Datafile的关系是一对多的关系。一个表空间Tablespace可以对应多个Datafile,Datafile则是只能归属在一个Tablespace里。传统的Oracle管理概念中,倡导一个表空间中创建多个数据文件,特别是创建分布在多个存储磁盘上,以期分散I/O。但是,Oracle10g以后推出的BigFile Tablespace大文件表空间,将这个概念有所变化。smallfile tablespace和bigfile tablespace。过去一个表空间对应多个数据文件我们成为Smallfile Tablespace。
 
        所谓Bigfile Tablespace最显著的差别就是一个表空间只能对应一个数据文件。Bigfile Tablespace虽只对应一个数据文件,但数据文件对应的最大体积大大增加。

        传统的small datafile每个文件中最多包括4M个数据块,按照一个数据块8K的大小核算,最大文件大小为32G。每个Small Tablespace理论上能够包括1024个数据文件,这样计算理论的最大值为32TB大小。
       而Bigfile Datafile具有更强大的数据块block容纳能力,最多能够包括4G个数据块。同样按照数据块8K计算,Bigfile Datafile大小为32KG=32TB。

        理论上small tablespace和big tablespace总容量相同。

 

表空间的操作基本语法

1 创建表空间

create tablespace my_01  -----表空间名字
datafile 'D:\app\oracle12c\oradata\orcl\my_01.dbf'    -----物理文件路径
size 1024M   -----初始大小
autoextend on next 2000m    -------每次自动扩展多少
maxsize unlimited;   --------最大是多少,设置为unlimited,最大也只能是32G


用户创建其数据库实体时,必须给予表空间中具有相应的权力,所以对一个用户来说,其要操纵一个ORACLE数据库中的数据,应该:
        1.被授予关于一个或多个表空间中的RESOURCE特权;
        2.被指定缺省表空间;
        3.被分配指定表空间的存储空间使用份额;
        4.被指定缺省临时段表空间,建立不同的表空间,设置最大的存储容量。

临时表空间
        临时表空间并不包含真正的数据,恢复的方法是删除临时表空间并重建即可。
系统表空间
如果备份不可用,则只能采用重建数据库的方法
 
2.在表空间上创建表

create table t1 (id int) tablespace my_01;

 3.插入数据

insert into t1 values(10);

4.查询表空间

select * from dba_tablespaces
----或者
select * from v$tablespace;


5.查询数据库物理文件

select *  from dba_data_files

6.表空间扩容:
一是
通过扩容数据文件的大小——直接对数据文件操作

alter database datafile 'D:\app\oracle12c\oradata\orcl\my_02.dbf' resize 256M;

设置数据文件具有自动扩展能力

alter database datafile 'D:\app\oracle12c\oradata\orcl\my_02.dbf' 
resize 256M 
AUTOEXTEND ON NEXT 5M 
MAXSIZE 500M;   

 
二是通过添加数据库文件——为表空间添加数据文件

----增加物理文件
alter tablespace SYSTEM add datafile 
    'D:\app\oracle12c\oradata\orcl\my_02.dbf' size 128M;

----增加物理文件,自动扩展,最大值
alter tablespace SYSTEM add datafile 
    'D:\app\oracle12c\oradata\orcl\my_02.dbf' size 128M 
    AUTOEXTEND ON NEXT 5M MAXSIZE 500M; 

----增加物理文件,自动扩展,最大值无限(32G)
alter tablespace SYSTEM add datafile 
    'D:\app\oracle12c\oradata\orcl\my_02.dbf' size 128M 
    AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;


7.查询表空间的大小:(单位是M)

select sum(bytes)/1024/1024, tablespace_name from dba_data_files 
    where tablespace_name='SYSTEM' group by tablespace_name;
-----或者
select bytes/1024/1024, tablespace_name from dba_data_files 
    where tablespace_name='SYSTEM';

8.查看用户下面的所有的表 
 

select * from user_tables;

select * from dba_tables where owner='TE'

9.查看用户所属的表空间 

select default_tablespace from dba_users where username=' TE'

删除数据文件
注意:不能够删除表空间中的第一个数据文件
,如果将第一个数据文件删除的话,相当于删除了整个表空间。SYSTEM的表空间数据文件不要删除,因为无法把SYSTEM表空间脱机。
需要先将表空间脱机

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;

ALTER TABLESPACE tablespace_name DROP DATAFILE'D:\app\oracle12c\oradata\orcl\my_02.dbf';

 

查看Oracle数据库中表空间信息的工具方法:使用oracle enterprise manager console工具

表空间的数据库文件达到31.9G,通过删除表后,发现数据库文件大小还是31.9

删除表的SQL语句:

drop table  tablename;

删除表,而且不放入回收站:

drop table  tablename purge ;

但是,数据库文件大小依然没有改变——原来是需要缩小表空间,以便释放磁盘空间。

具体步骤:

1.查看表空间的所有数据库文件datafile,找到你要缩小的数据库文件。

select file#, name from v$datafile;-----id =2

2.获得要缩小的数据库文件id是2,查看此文件有多少个block。

select max(block_id) from dba_extents where file_id=4;----614000个block

3.查询block的大小,单位是 byte

select value from v$parameter where name='db_block_size'   ---8292 byte = 8 kb

4.计算这个数据库文件占用的物理空间

select 614000 * 8 / 1024 from dual;  ---4975mb

5.修改datafile大小

alter database datafile 'D:\APP\ORCL\my_001.DBF' resize 6000m;
——要大于所有的block的大小。
——发现如果block非常多的话,数据库文件依然比较大,就算一些表格已经drop和purge。

 

后来找到以下办法:

tables是存储某个tablespace(表空间)里面的,tablespaces可以包含多个dbf文件,表空间会随着包含的表内容增加而增大,但是不会随着table删除而减小。

---使用
truncate table  tablename DROP STORAGE;

解释: 直接删除表,并且释放存储空间。truncate的意思是清空表数据, “DROP STORAGE”是释放存储空间。

 

10. truncate table 的drop storage和reuse storage区别

        如果使用下面两个语句删除两个表中的数据

   truncate table t1 drop storage;
   truncate table t2 reuse storage;

   区别是: 
t1的data:   数据所在的extent空间被释放,释放的空间供其它segment使用;
t1的index:   数据删除,剩下第一个extent;

t1的hwm:    重新设置到第一个block的位置(hwm会改变)

 

t2的data:   数据所在的extent空间不会被回收(仅仅数据会被删除),数据删除之后的freespace空间只能供本表使用,不可以供其它segment使用;
t2的index:   数据删除,但是保留extent;

t2的hwm:   重新设置到第一个block的位置(hwm会改变);

 

 

 

 

 

 

展开阅读全文

没有更多推荐了,返回首页