1.oracle数据逻辑上存在表空间中，物理上存储在数据文件中
2.表空间：
–某一时刻只能属于一个数据库
–由一个或多个数据文件组成
–可进一步划分为逻辑存储单元
3.数据文件：
–只能属于一个表空间和一个数据库
–是方案对象数据的资料档案库
database>tablespace>datafiles
表空间类型：
1.SYSTEM表空间
–随数据库创建
–包含数据字典
–包含SYSTEM还原段
2.非SYSTEM表空间
–用于分开存储段
–易于空间管理
–控制分配给用户的空间量
创建表空间：
SQL> col file_name format a40
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/database/msp/system01.dbf SYSTEM
/database/msp/sysaux01.dbf SYSAUX
/database/msp/undotbs01.dbf UNDOTBS1
/database/msp/mspmytbs.dbf MYTBS
SQL> create tablespace msp datafile
2 '/database/msp/msp.dbf' size 100m;
Tablespace created.
[oracle@server01 msp]$ ll
total 1569732
-rw-r----- 1 oracle oinstall 16433152 Mar 28 16:09 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 28 16:09 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 28 16:09 msp.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 25 18:37 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:37 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:37 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:37 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:37 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 28 16:09 redo03a.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 28 16:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 28 16:05 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 28 16:05 undotbs01.dbf
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/database/msp/system01.dbf SYSTEM
/database/msp/sysaux01.dbf SYSAUX
/database/msp/undotbs01.dbf UNDOTBS1
/database/msp/mspmytbs.dbf MYTBS
/database/msp/msp.dbf MSP
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
3 TEMP NO NO YES
4 MYTBS YES NO YES
5 MSP YES NO YES
6 rows selected.
====================================================
表空间管理：
? 本地管理的表空间：(目前多用该模式管理)
–在表空间内管理空闲区
–使用位图来记录空闲区
–每一位与一个块或一组块相对应
–位的数值指明是空闲还是已占用
? 字典管理的表空间：
–由数据字典管理空闲区
–在分配或回收区时更新对应的表
还原(undo)表空间(对一个表进行操作前进行保存，以备回滚)
? 用于存储还原段(undo segment)
? 不能包含任何其它对象
? 其中的区要在本地管理
? 只能使用DATAFILE和EXTENT MANAGEMENT子句
创建方法：
CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;
=====================================================
临时表空间
? 用于排序操作
? 不能包含任何永久对象
? 建议在本地管理区
创建方法：
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
缺省临时表空间
? 指定数据库范围内的缺省临时表空间
? 避免使用SYSTEM表空间存储临时数据
? 可使用以下命令进行创建：
–CREATE DATABASE
–在本地管理
–ALTER DATABASE(如果没有在初始化创建数据库时候 没创建，用该方法创建)
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE temp;
缺省临时表空间的限制
不能对缺省临时表空间执行下列操作：
? 将其删除，除非已经有新的缺省临时表空间
? 使其脱机
? 更改为永久表空间
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
MYTBS PERMANENT LOCAL
MSP PERMANENT LOCAL
6 rows selected.
SQL> create temporary tablespace mytemp01
2 tempfile '/database/msp/mytemp01.dbf' size 100M
3 extent management local;
Tablespace created.
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
MYTBS PERMANENT LOCAL
MSP PERMANENT LOCAL
MYTEMP01 TEMPORARY LOCAL
7 rows selected.
SQL> alter database default temporary tablespace mytemp01;
Database altered.
SQL> desc database_properties;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)
SQL> col property_name format a20
SQL> col PROPERTY_VALUE format a20
SQL> col DESCRIPTION format a20
SQL> select * from database_properties;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
-------------------- -------------------- --------------------
DICT.BASE 2 dictionary base tabl
es version #
DEFAULT_TEMP_TABLESP MYTEMP01 Name of default temp
ACE orary tablespace
=============================================================
只读表空间
? 使用以下命令可将表空间置于只读模式(system表空间永远都是可读可写)
–导致检查点操作
–数据仅用于读操作
–可从表空间删除对象
ALTER TABLESPACE userdata READ ONLY;
SQL> create tablespace fuzhou
2 datafile '/database/msp/fuzhou.dbf' size 20M
3 extent management local uniform size 128k;
Tablespace created.
SQL> create user msp identified by msp default tablespace fuzhou; #新建用户用于该表空间fuzhou
User created.
SQL> grant connect,resource to msp; #将该用户进行授权
Grant succeeded.
[oracle@server01 ~]$ sqlplus msp/msp
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 28 18:42:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t (id integer,name char(10)); #创建一个表
Table created.
SQL> insert into t values(0,'fujian'); #对表插入数据
1 row created.
SQL> commit; #提交
Commit complete.
SQL> select * from t;
ID NAME
---------- ----------
0 fujian
SQL> insert into t values(1,'2222');
1 row created.
SQL> commit;
Commit complete.
[oracle@server01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 28 18:45:15 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace fuzhou read only; (当上文红色的commit未执行提交时候，执行alter指令会hang住)
Tablespace altered.
SQL> conn msp/msp
Connected.
SQL> insert into t values(2,'8888');
insert into t values(2,'8888')
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/database/msp/fuzhou.dbf'
SQL> select * from t;
ID NAME
---------- ----------
0 fujian
1 2222
SQL> delete from t where id=1;
delete from t where id=1
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/database/msp/fuzhou.dbf'
#因为为只读状态，只能查询，不能做其他插入和删除操作
SQL> drop table t; #在只读状态下仍旧可以把表删除
Table dropped.
SQL> conn /as sysdba
Connected.
SQL> alter tablespace fuzhou read write;
Tablespace altered.
提示:对于静态不怎么变化的表空间可以设置为只读状态，有利于备份恢复
==============================================================
SQL> conn msp/msp
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
解决方法：
SQL> conn system/123456
Connected.
SQL> @?/sqlplus/admin/pupbld.sql;
SQL> conn msp/msp
Connected. #没有警告信息了
===================================================================
使表空间脱机
? 无法访问数据
? 不能设为脱机的表空间：
–SYSTEM表空间
–具有活动的还原段的表空间
–缺省临时表空间
? 使用以下命令可使表空间脱机：
? 使用以下命令可使表空间联机：
ALTER TABLESPACE userdata OFFLINE;
ALTER TABLESPACE userdata ONLINE;
=======================================================
SQL> conn msp/msp
Connected.
SQL>
SQL>
SQL>
SQL> create table tt(id integer,name char(10));
Table created.
SQL>
SQL>
SQL> insert into tt values(0,'aaaaa');
1 row created.
SQL> insert into tt values(1,'bbbbb');
1 row created.
SQL>
SQL> insert into tt values(2,'ccccc');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt;
ID NAME
---------- ----------
0 aaaaa
1 bbbbb
2 ccccc
SQL> conn /as sysdba
Connected.
SQL> alter tablespace fuzhou offline; #将表空间脱机
Tablespace altered.
SQL> conn msp/msp
Connected.
SQL> select * from tt;
select * from tt
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/database/msp/fuzhou.dbf'
SQL> alter tablespace fuzhou online; #使表空间在线
Tablespace altered.
SQL>
SQL> select * from tt;
ID NAME
---------- ----------
0 aaaaa
1 bbbbb
2 ccccc
====================================================================
调整表空间大小
表空间大小可通过以下方法进行调整：
? 更改数据文件的大小：
–使用AUTOEXTEND自动调整
–使用ALTER TABLESPACE手动调整
? 使用ALTER TABLESPACE添加数据文件
SQL> alter tablespace fuzhou add datafile
2 '/database/msp/fuzhou02.dbf' size 20M
3 autoextend on next 10M maxsize 100M;
Tablespace altered.
SQL>
SQL>
SQL>
SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> col file_name format a20
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/system SYSTEM NO
01.dbf
/database/msp/sysaux SYSAUX NO
01.dbf
/database/msp/undotb UNDOTBS1 NO
s01.dbf
/database/msp/mspmyt MYTBS NO
bs.dbf
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/msp.db MSP NO
f
/database/msp/fuzhou FUZHOU NO
.dbf
/database/msp/fuzhou FUZHOU YES
02.dbf
7 rows selected.
SQL> alter database datafile
2 '/database/msp/fuzhou.dbf'
3 autoextend on next 10M maxsize 100M;
Database altered.
####也可以将原始的表空间设置为可自动扩展的#########################
SQL>
SQL>
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/system SYSTEM NO
01.dbf
/database/msp/sysaux SYSAUX NO
01.dbf
/database/msp/undotb UNDOTBS1 NO
s01.dbf
/database/msp/mspmyt MYTBS NO
bs.dbf
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/msp.db MSP NO
f
/database/msp/fuzhou FUZHOU YES
.dbf
/database/msp/fuzhou FUZHOU YES
02.dbf
7 rows selected.
=================================================================
用resize进行扩展或缩小表空间大小：
注意缩小的时候，不能小于当前表空间使用的大小
SQL> alter database datafile '/database/msp/fuzhou.dbf' resize 50M;
Database altered.
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
/database/msp/sysaux SYSAUX 314572800
01.dbf
/database/msp/undotb UNDOTBS1 209715200
s01.dbf
/database/msp/mspmyt MYTBS 104857600
bs.dbf
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/msp.db MSP 104857600
f
/database/msp/fuzhou FUZHOU 52428800
.dbf
/database/msp/fuzhou FUZHOU 20971520
02.dbf
7 rows selected.
================================================================================
移动表空间：
方法一：
? ALTER TABLESPACE
–表空间必须脱机
–目标数据文件必须存在
? 重命名数据文件的步骤：
–使表空间脱机。
–使用操作系统命令移动或复制文件。
–执行ALTER TABLESPACE RENAME DATAFILE命令。
–使表空间联机。
–必要时使用操作系统命令删除该文件。
ALTER TABLESPACE userdata RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u02/oradata/userdata01.dbf';
--------------------------------------------------------------------------------------
SQL> alter tablespace fuzhou offline;
Tablespace altered.
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
/database/msp/sysaux SYSAUX 314572800
01.dbf
/database/msp/undotb UNDOTBS1 209715200
s01.dbf
/database/msp/mspmyt MYTBS 104857600
bs.dbf
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/msp.db MSP 104857600
f
/database/msp/fuzhou FUZHOU
.dbf
/database/msp/fuzhou FUZHOU #fuzhou的表空间大小都没了，说明离线操作成功
02.dbf
7 rows selected.
SQL> !
[oracle@server01 ~]$ cd /database/msp
[oracle@server01 msp]$ ll
total 1643208
-rw-r----- 1 oracle oinstall 16433152 Mar 29 17:53 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 29 17:53 control03.ctl
-rw-r----- 1 oracle oinstall 20979712 Mar 29 17:52 fuzhou02.dbf
-rw-r----- 1 oracle oinstall 52436992 Mar 29 17:52 fuzhou.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 28 19:04 msp.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 28 19:04 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 29 17:24 mytemp01.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 29 17:52 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 29 17:52 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 28 19:04 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 28 19:04 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 28 19:04 redo03a.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 29 17:44 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 29 17:44 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 29 17:49 undotbs01.dbf
[oracle@server01 msp]$ mv fuzhou.dbf .. #手动移动表空间物理文件
[oracle@server01 msp]$ exit
exit
SQL>
SQL> alter tablespace fuzhou rename datafile
2 '/database/msp/fuzhou.dbf' to '/database/fuzhou.dbf'; #重定向表空间位置路径
Tablespace altered.
SQL>
SQL>
SQL>
SQL> alter tablespace fuzhou online; #重置表空间为在线状态
Tablespace altered.
SQL>
SQL>
SQL>
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
/database/msp/sysaux SYSAUX 314572800
01.dbf
/database/msp/undotb UNDOTBS1 209715200
s01.dbf
/database/msp/mspmyt MYTBS 104857600
bs.dbf
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/msp.db MSP 104857600
f
/database/fuzhou.dbf FUZHOU 52428800 #可以发现位置改变了
/database/msp/fuzhou FUZHOU 20971520
02.dbf
7 rows selected.
========================================================================
方法二：
使用alter database进行移动表空间，我们把fuzhou这个表空间移回原处
注意：该操作需要再数据库mount状态进行操作
SQL> shutdown immediate; ############1.关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> !
[oracle@server01 ~]$ cd /database/
[oracle@server01 database]$ mv fuzhou.dbf msp/ ####2.手动移动物理文件
[oracle@server01 database]$
[oracle@server01 database]$
[oracle@server01 database]$
[oracle@server01 database]$ exit
exit
SQL> startup mount; #4.启动数据库到mount状态
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 641731968 bytes
Database Buffers 184549376 bytes
Redo Buffers 6590464 bytes
Database mounted.
SQL>
SQL>
SQL>
SQL>
SQL> alter database rename file
2 '/database/fuzhou.dbf' to
3 '/database/msp/fuzhou.dbf'; #5.重定向表空间路径
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL> alter database open; ########6.重置数据库为open状态
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
/database/msp/sysaux SYSAUX 314572800
01.dbf
/database/msp/undotb UNDOTBS1 209715200
s01.dbf
/database/msp/mspmyt MYTBS 104857600
bs.dbf
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/msp.db MSP 104857600
f
/database/msp/fuzhou FUZHOU 52428800
.dbf
/database/msp/fuzhou FUZHOU 20971520
02.dbf
7 rows selected.
该方法较前文alter tablespace需要关闭数据库，所以在生产环境下用方法一alter tablespace可以在线迁移更方便。
===============================================================================================
删除表空间：
? 不能删除下列表空间：
–SYSTEM表空间
–具有活动段的表空间
? INCLUDING CONTENTS将删除段
? INCLUDING CONTENTS AND DATAFILES将删除
数据文件
? CASCADE CONSTRAINTS将删除所有引用完整性约束
DROP TABLESPACE userdata
INCLUDING CONTENTS AND DATAFILES; #########该指令谨慎使用
---------------------------------------------------------------------------------------------
SQL> drop tablespace fuzhou including contents and datafiles;
Tablespace dropped.
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
/database/msp/sysaux SYSAUX 314572800
01.dbf
/database/msp/undotb UNDOTBS1 209715200
s01.dbf
/database/msp/mspmyt MYTBS 104857600
bs.dbf
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/msp.db MSP 104857600
f
SQL> !
[oracle@server01 ~]$ cd /database/msp/
[oracle@server01 msp]$ ll
total 1571512
-rw-r----- 1 oracle oinstall 16433152 Mar 29 18:13 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 29 18:13 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 29 18:04 msp.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 29 18:04 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 29 17:24 mytemp01.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 29 18:04 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 29 18:04 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 29 18:13 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 29 18:13 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 29 18:04 redo03a.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 29 18:09 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 29 18:09 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 29 18:09 undotbs01.dbf
===========================================================
获取表空间信息常用视图
通过以下查询可获取表空间和数据文件的信息：
? 表空间：
–DBA_TABLESPACES
–V$TABLESPACE
? 数据文件信息：
–DBA_DATA_FILES
–V$DATAFILE
? 临时文件信息：
–DBA_TEMP_FILES
–V$TEMPFILE
阅读(878) | 评论(0) | 转发(0) |
2489
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
