表空间和数据文件管理

表空间和数据库文件概念

数据库的逻辑结构和物理结构关系

oracle使用逻辑结构来管理数据存储

逻辑结构        物理结构

database   

    |

tablespace    --&gtdatafile (一个表空间最多有1023个数据文件)

    |

segment(table)

    |

extent

    |

db_block    --&gtos_block

 

 

 

表空间类型

1、永久表空间：保存永久对象

2、undo表空间：不能保存对象，只能存储rollback segment

3、临时表空间:保存临时对象的数据和排序的中间结果，会自动分配，重启数据库的时候会

自动重新分配,不产生日志

1、system 表空间

数据库创建时建立；

里面包含的内容有：数据字典、系统undo segment ；

system表空间一般不放数据

默认的表空间是放在system中 

2、非system表空间

存储着用户的数据；

 

大部分数据字典表是复数的，动态性能视图是单数的；

 

数据文件的相关概念

数据文件是数据的存放载体

数据文件存在操作系统上，也可以裸设备

数据文件不能单独存在，得有组织

数据文件的逻辑组织形式为表空间tablespace

一个表空间可以含有多个数据文件

一个数据文件只能属于一个表空间

数据库内可有多个表空间

常用命令:

示例：在指定的表空间上创建表

SQL> create table scott.demo tablespace demo as select * from scott.emp;

 

示例：创建用户并指定永久表空间和临时表空间，用户创建表

sys@TEST> create user apps identified by apps default tablespace etcapp01 temporary tablespace user_temp;

sys@TEST> grant resource,connect to apps;

sys@TEST> conn apps/apps

apps@TEST> create table apps_user(name varchar2(20));

sys@TEST> select username,default_tablespace,temporary_tablespace from dba_users where username='APPS';

sys@TEST> select table_name,tablespace_name,temporary from dba_tables where table_name='APPS_USER';

  

示例：如何查询一个表空间上有哪些对象

select owner,segment_name,segment_type,tablespace_name from dba_segments where tablespace_name='USERS';

 

select owner,tablespace_name,owner,segment_name,segment_type,sum(bytes)/1024/1025 mb from dba_segments  where tablespace_name='USERS' group by owner,tablespace_name,owner,segment_name,segment_type order by mb; 

 

示例：查看数据库中所有类型为表的大小

select segment_name, sum(bytes)/1024/1024 mb from user_segments where SEGMENT_TYPE='TABLE'

group by segment_name order by 2;

 

示例：查看数据库中所有对象的大小

select segment_name, sum(bytes)/1024/1024 mb from user_segments group by segment_name order by 2;

示例：我们给数据库创建一个用户表空间paul，用来存储用户的数据。

SQL> create tablespace pual datafile  '/u01/oradata/lx92/pual01.dbf' size 20m; 

我们查询确认下是否已经创建了pual表空间

SQL> select * from v$tablespace;

查看pual表空间的数据文件

SQL> select file_name,tablespace_name from dba_data_files;

查看数据文件是否已经在操作系统上是否已经创建

SQL> !

$ ll/u01/oradata/lx92/ | grep pual

-rw-r-----  1 oracle oinstall  20979712 Dec  1 19:49 pual01.dbf

示例:查看表空间大小

select tablespace_name ,file_name ,ceil(bytes/1024/1024) mb from dba_data_files order by 1; 

//查看用户所在的表空间

select username,default_tablespace,temporary_tablespace from dba_users;

//查看默认表空间

select PROPERTY_VALUE  from database_properties where 

property_name ='DEFAULT_PERMANENT_TABLESPACE';  

//查看表空间剩余

select * from dba_free_space;

//修改默认表空间

alter database default tablespace users; 

//修改用户默认表空间

alter user dhy default tablespace users;

查看表空间类型

sys@TEST> select tablespace_name,block_size,contents, bigfile  from dba_tablespaces;

 

TABLESPACE_NAME      BLOCK_SIZE CONTENTS

-------------------- ---------- ---------

SYSTEM                     8192 PERMANENT

SYSAUX                     8192 PERMANENT

UNDOTBS1                   8192 UNDO

TEMP                       8192 TEMPORARY

USERS                      8192 PERMANENT

TS1                        8192 PERMANENT

 

PERMANENT  ：永久表空间类型

UNDO              ：回退表空间类型

TEMPORARY   ：临时表空间类型

 

查看逻辑结构和物理结构的对应关系(表空间上的数据文件路径)

SQL> select tablespace_name,file_name from dba_data_files;

 

查看表和表空间的对应关系

       1、表空间上有哪些表

SQL> select table_name,tablespace_name from dba_tables where tablespace_name='DEMO';

       2、查看表在哪个表空间上

sys@ERDB> select table_name,tablespace_name from dba_tables where table_name='EMP';

 

 

//查看表空间中数据文件的位置，大小和名称:

sys@TEST> select tablespace_name,file_name,bytes/1048576 mb from dba_data_files;

sys@TEST> select tablespace_name,file_name from dba_data_files;

//查看表空间信息

sys@TEST> select * from v$tablespace;

查看表空间是否是大文件表空间

sys@TEST> select tablespace_name,bigfile from dba_tablespaces;

//查看表空间的类型和区管理方式

SQL> select tablespace_name,contents,extent_management from dba_tablespaces;

//查看表空间及数据文件及状态

sys@TEST> select file_name,tablespace_name,status from dba_data_files;

//查看指定用户所拥有的表空间及临时表空间

sys@TEST> select default_tablespace,temporary_tablespace from dba_users where username='APPS';

//查看表空间的段管理方式（手动还是自动）

sys@TEST> select tablespace_name,segment_space_management from dba_tablespaces;

//查看是否使用logging

select tablespace_name,logging,force_logging from dba_tablespaces; 

表空间Force logging的选项后，表级设定了nologging后无效

//修改表空间为logging

alter tablespace test logging;

//查看段区的管理方式

select tablespace_name,contents,extent_management , segment_space_management from dba_tablespaces;

//删除表空间（没有将数据文件删除）

SQL> drop tablespace demo;

 

//级联删除表空间及数据文件(需要注意的是SYSTEM表空间以及具有active segments的表空间是不可以删除的)

SQL>drop tablespace testraw including contents and datafiles;

 

补充语法：

drop tablespace ‘表空间名',但是还有3个选项需要注意：

INCLUDING CONTENTS                                    指删除表空间中的segments；

INCLUDING CONTENTS AND DATAFILES    指删除segments和datafiles；--&gt常用

CASCADE CONSTRAINTS                                 删除所有与该空间相关的完整性约束条件。

例：

drop tablespace fesco including contents and datafiles cascade constraints;

 

//只删除表空间中的某一个数据文件：

oracle 10g中,可以删除指定的数据文件，前提该数据文件中没有数据，而且不能是该表空间的第一个数据文件

sys@ERDB>alter tablespace users drop datafile '/u01/beijing/users02.dbf';

sys@ERDB> alter tablespace ts2 drop datafile '/home/oracle/ts02.dbf';

alter tablespace ts2 drop datafile '/home/oracle/ts02.dbf'

*

ERROR at line 1:

ORA-03261: the tablespace TS2 has only one file

 

//不能删除offline的数据文件，但可以级联删除表空间及数据文件

sys@GD> alter tablespace example drop datafile '/u01/app/oracle/oradata/gd/example02.dbf';

alter tablespace example drop datafile '/u01/app/oracle/oradata/gd/example02.dbf'

*

ERROR at line 1:

ORA-03264: cannot drop offline datafile of locally managed tablespace

SQL> drop tablespace test04 including contents and datafiles;

Tablespace dropped.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30024909/viewspace-1343715/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30024909/viewspace-1343715/