项目实施中oracle数据库表空间问题

项目实施中oracle数据库表空间问题

项目实施中oracle数据库表空间问题 篇1

表空间和数据文件的管理实验

试验目的：全面掌握表空间和数据文件的创建、管理过程 试验内容：

1、创建表空间、增加数据文件

2、管理表空间和数据文件的的各种操作 试验步骤：

1、掌握表空间和数据文件相关的概念

1)oracle默认的表空间

系统默认的表空间:SYSTEM、SYSAUX、UNDOTBS1、EMP、EXAMPLE、USERS 2)系统表空间

SYSTEM、SYSAUX是系统表空间，SYSTEM用于存放oracle数据字典的结构和内部元数据。

SYSAUX是SYSTEM的辅助表空间用于存放个模式（用户）的对象元数据。

3)非系统表空间

由DBA根据需要创建，存放用户自己的数据，也可以包含临时数据以及UNDO数据。

2、创建表空间

1）创建表空间的语法

CREATE [smallfile|bigfile] TABLESPACE tablespace_name DATAFILE ‘/path/filename’ SIZE [k|m] REUSE

[‘/path/filename’ SIZE [k|m] REUSE]

[AUTOEXTEND [ON|OFF] NEXT [k|m] MAXSIZE [UNLIMITED|[K|M]]] [ONLINE|OFFLINE] [LOGGING|NOLOGGING] [PERMANENT|TEMPORARY] [DICTIONARY|LOCAL]

2）定义表空间名字及属性

创建两个表空间ts_test1、ts_test2，本地化管理(Local,默认)、永久表空间（permantent，默认）

3）定义数据文件名字、路径、初始大小、自动扩展等属性

数据文件data_test1、data_test2 路径：c:disk1data_test1.dbf c:disk2data_test2

4)创建脚本：

a、创建本地永久表空间

//创建本地永久表空间ts_test1和ts_test2，默认为永久和本地

SQL>create tablespace ts_test1 datafile ‘c:disk1data_file1.dbf’ size=50m reuse autoextend on next 10m;

SQL>create tablespace ts_test2 datafile ‘c:disk2data_file2.dbf’ size=50m reuse autoextend on next 10m maxsize unlimited;查看一下创建完成的结果

//查看dba_tablespaces结构

SQL>desc dba_tablespaces;

//查看字典中的所有表空间情况,extent_management值为local时为本地表空间 SQL>select tablespace_name,status,extent_management,content from dba_tablespaces;//查看创建表空间时创建的数据文件，首先查看dba_data_files字典结构 SQL>desc dba_data_files;//查看数据文件情况 SQL>set linesize 120 SQL>col file_name for a20 SQL>col file_id for a20 SQL>col tablespace_name a30 SQL>col online_status for a20 SQL>select file_name,file_id,tablespace_name,online_status from dba_data_files;

b、创建还原表空间和临时表空间

//创建还原表空间

SQL>create undo tablespace test_undo datafile ‘c:disk3test_undo’ size 20m;

//查看创建结果,contents为表空间内容类型，如undo

SQL>select tablespace_name,contents from dba_tablespaces;

c、创建临时表空间

//创建临时表空间

SQL>create temporary tablespace test_temp

tempfile ‘c:disk4test_temp.dbf’ size 20m extent management local;

//查询创建结果

SQL>select tablespace_name,extent_management,contents from dba_tablespaces;d、增加数据文件

//增加数据文件

SQL>alter tablespace ts_test1 add datafile ‘c:disk1ts_test11.dbf’ size 10m;

//查看增加数据文件的结果

SQL>col file_name for a30

SQL>col tablespace_name for a30 SQL>select file_name,tablespace_name from dba_data_files;

3、管理表空间的各种操作

1)改变表空间的读写状态

//将表空间设为只读

//这时表空间应为online状态，表空间不能包含回滚段，不能是归档模式

//查看数据库日志模式

SQL>archive log list;//查看表空间contents

SQL>select tablespace_name,contents from dba_tablespaces where tablespace_name like‘TS_%’;

//修改为只读

SQL>alter tablespace ts_test1 read only;2）表空间脱机离线、在线连接

SQL>alter tablespace ts_test1 offline;

SQL>alter tablespace ts_test1 online；

//查看表空间状态

SQL>select tablespace_name,status from dba_tablespaces;3)修改表空间名称 //查询当前表空间名称和所包含的数据文件

SQL>select file_name,tablespace_name from dba_data_files;//确认表空间处在online状态

SQL>select tablespace_name,status from dba_tablespaces;//表空间改名

SQL>alter tablespace ts_test2 rename to ts_test22;//查询修改结果

SQL>Col file_name for a30;SQL>col tablespace_name for a30;SQL>select file_name,tablespace_name from dba_data_files;4)设置默认表空间

SQL>alter database default tablespace users //设置默认临时表空间

SQL>alter database default temporary tablespace temp;5)删除表空间

SQL>drop tablespace ts_test2;//仅删除表空间信息不删除表空间数据文件 SQL>drop tablespace ts_test2 includng contents;//包括数据文件全部删除 查看删除后的结果情况

SQL>select file_name,tablespace_name from dba_data_files;6)切换undo表空间

SQL>alter system set undo_tablespace =undotbs02;7)删除undo表空间

要删除正在使用的undo表空间，首先切换undo表空间后在进行删除 例如删除undost01 SQL>alter system set undo_tablespace=undost02;SQL>drop tablespace undost01;8)查询undo表空间信息

SQL>show parameter undo_tablespace;

4、管理数据文件的各种操作 1)在表空间中新增一个数据文件

SQL>alter tablespace ts_test1 add datafile ‘c:dsk1ta_test111.dbf’ size 50m;2）删除表空间中无数据的数据文件 第一种语法

SQL>alter database datafile ‘c:disk1ts_test1.dbf’ offline;//数据文件脱机 SQL>alter tablespace ts_test1 drop datafile ‘c:disk1ts_test111.dbf’;第二种语法：

SQL>alter database datafile ‘mytb.dbf’ offline drop;3）数据文件自动扩展设置

SQL>alter tablespace ts_test1 datafile ‘c:disk1ts_test1.dbf’ autoextend on next 10m maxsize 500m;4）重置数据文件大小，收回或扩数据文件空间

SQL>alter tablespace ts_test1 datafile ‘c:disk1ts_test1.dbf’ resize 45m;

查询重置数据文件大小后的结果

SQL>col file_name for a30;

SQL>col tablespace_name for a30;

SQL>select file_name,tablespace_name,bytes/(1024*1024)from dba_data_files;5）移动数据文件

有两条语句可以实现对数据文件的移动：

a、alter tablespace tablespace_name rename datafile ‘/path/file_name1’

to ‘/path/file_name_new’;

a这条语句主要适合用户表空间，在执行这条语句时表空间必须脱机才能执行。SQL>alter tablesapace ts_test2 offline;//使表空间ts_test2脱机

SQL>host copy c:disk2ts_test2.dbf c:disk1 //将数据文件拷贝到新的位置

SQL>alter tablespace rename datafile ‘c:disk2ts_test2.dbf’ to ‘c:disk1ts_test1.dbf’;b、alter database db_name rename file ‘/path/file_name1’ to ‘/path/file_name_new’;

这条语句使用于系统表空间及不能置为脱机的表空间中的数据文件。在使用该语句时，数据库必须在加载(mount)的状态且目标数据文件必须存在，因为该语句只是修改文件中指向数据文件的指针(地址)。SQL>shutdown immediate;SQL>host copy c:oracleproduct10.2.0oradatasystem01.dbf c:disk1 SQL>startup mount;SQL>alter database rename file ‘c:oracleproduct10.2.0oradataorclsystem01.dbf’ to ‘c:disk1system01.dbf’;SQL>alter database open;查询移动后的结果

SQL>select file_name,tablespace_name from dba_data_files;