一、Objectives
Define the purpose of tablespaces and data files(定义的表空间和数据文件的目的)
Create tablespaces
Manage tablespaces
Create and manage tablespaces using Oracle Managed Files(OMF)
Obtain tablespace information (获取表空间信息)
二、Tablespaces & Data Files
Oracle stores data logically in tablespaces and physically in data files
Tablespaces
--Can belong to only on database at a time
--
Data files
三、Storage Hierarchy summary存储层次结构总结
logical
Database->Tablespace->segment->extent->oracle data block
Database->Schema
Physical
Datafile->OS block
一个Tablespace 对应多个Data file
一个ORacle data block 对应多个 OS black 是os block的倍数
四、Types of Tablespaces
1、SYSTEM tablespace
--Create with the database
--Contains the data dicationary
--Contains the SYSTEM undo segment
2、Non-System tablespace
--Separate segments
--Eases space administration 简化空间管理
--Controls amount of space allocated to a user
另外三种划分(permanent,undo,temporary)
五、查看表空间相关信息
1、查看表空间
select * from v$tablespace;
2、查看表空间包含数据
select file_name,tablespace_name from dba_data_files;
六、Create Tablespaces
create tablespace paul datafile
'/u01/app/oracle/product/10.2.0/oradata/oamis/paul01.dbf' size 20m;
七、两种方式
dictionary-mangage tablespace 中央集中式
a locally managed 独立式 (9版本都采用这种方式,但还是兼容以前的方式)
9i以后如果system 表空间是 locally managed 管理,那新建的都是
将Dictionary-mangage方式转换为locally managed
DBMS_SPACE_ADMIN.TABLESPCAE_MIGRATE_TO_LOCAL('system')(共有7步工作)
八、Undo Tablespace(用于回滚操作)
1、Used to store undo segments
2、Cannot contain any other objects
3、Extents are locally managed
4、Can only use the DataFile and Extent management clauses
create undo tablespace undo1
datafile '/u01/app/oracle/oradata/undo01.dbf' size 40M;
九、Temporary Tablespace(最好单独指定)
1、used for sort operation
2、can be shared by multiple user
3、cannot contain any permanent objects(不能包含永久信息)
4、Locally managed extents recommended (推荐使用locally 方式)
5、
Create temporary tablespace temp
tempfile '/u01/app/oracle/oradata/temp01.dbf' size 20M
extent management local uniform size 4M
1、创建数据库的时候指定Temporary tablespace
2、创建临时表空间,然后修改
①、创建
create temporary tablespace mytemp1
tempfile '/u01/app/oracle/product/10.2.0/oradata/oamis/mytemp01.dbf' size 100M
extent management local;
②、修改
alter database default temporary tablespace mytemp1;
Temporary Tablespace Restriction
1、Dropped until after a new default is made available 不能删除
2、Taken offline 不能离线
3、Altered to a permanent tablespace 不能改变为永久表空间
十、Read-only Tablespace
1、Causes a checkpoint (导致了一个检查点)
2、Data available only for read operation
3、Object can be dropped from tablespace
(System Tablespace 不可能read-only,drop 的时候只是drop 数据字典的东西)
4、测试上面三点
①、创建表空间
create tablespace shanxi datafile
'/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi.dbf' size 20m
extent management local uniform size 128k;
②、创建用户,默认表空间是shanxi
create user fc identified by fc default tablespace shanxi
③、赋予权限
grant connect,resource to fc;
④、登录
conn fc/fc@oamis;
⑤、创建表并添加记录 提交
create table t (id integer,name char(10));
insert into t values(1,'yuncheng');
commit;
⑥、修改表空间只读
alter tablespace shanxi read only;
⑦、测试结果
不能增删改 只能drop
⑧、恢复表空间write
alter table shanxi read write;
十一、Taking a Tablespace Offline(使表空间离线)
表空间状态(read ,write,offline ,online)
1、Not avaliable for data access
2、Tablespace that cannot be taken offline
①、system tablespace
②、Tablespaces whit active undo segment ()
③、Default temporary tablespace (temporary tablespace 不是默认的可以离线)
2、测试表空间离线
①、创建表 添加数据
create table t2 (id integer,name char(10));
insert into t2 values(1,'aaa');
insert into t2 values(2,'bbb');
commit;
insert into t2 values(3,'ccc');
②、将表空间shanxi离线(sys用户)
alter tablespace shanxi offline;(表空间离线)
这个时候查询不行了
alter tablespace shanxi online; (表空间在线)
这个时候可以了
十二、Changing Storage Settingg(改变存储设置)
1、可以分为三个层次进行设置(数据库级,表空间级,segament级)
2、Storage setting for locally managed tablespaces cannot be altered;
十三、Resizing a TableSpace(调整表空间的大小)
A tablespace can be resized by:
1、Changing the size of a data file(改变数据文件的大小)
①、Automatically using autoextend (自动改变,一个步调,一个极限)
三种方式实现: create database ,create tablespace,alter tablespace ...add datafile
Example:
Create Tablespace user_data
datafile='/u01/oradata/userdata01.dbf' size=200M
autoextend on next 10m maxsize 500M; (可以应用于create database 或者 create tablespace)
修改表空间(增加一个)
alter tablespace shanxi add datafile
'/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi2.dbf' size 20m
autoextend on next 10M maxsize 100M;
修改表空间(修改原来的)
alter database datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi.dbf'
autoextend on next 10m maxsize 100M;
查询tablespace是否自动增长
select file_name,tablespace_name,autoextensible from dba_data_files;
②、Manually using Alter Database (手工改变方式)
(从小的往大变,如果从大的往小变则根据数据库文件实际的大小来定)
Alter database datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi.dbf' resize 20M;
2、Add a data file using Alter Tablespace (添加一个新的)
(这种方式好,例如一个100G跟10个10g,10个的这个会并发,速度快)
alter tablespace shanxi add datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf' size 5M;
3、查询表空间的使用情况(这个查询需要点时间)
主要是 DBA_DATA_FILES和dba_free_space
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used", round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used
from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name;
临时表空间在DBA_TEMP_FILES中查找
十四、Methods for moving data file(移动数据文件)
1、Alter Tablespace
前提
①、Tablespace must be offline
②、Targer data file must exist
步骤
①、alter tablespace shanxi offline;
②、离线状态下dba_data_files 中bytes是空的
③、将shanxi3.dbf更改到上一级
alter tablespace shanxi rename datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf'
to '/u01/app/oracle/product/10.2.0/oradata/shanxi3.dbf';
④、在通过dba_data_files查询发现路径变化了
2、Alter Database
前提:
①、database must be mounted
②、targer data file must exist;
步骤
①、关闭数据库
shutdown immediate;
②、移动数据文件或拷贝
mv shanxi3.dbf oamis/
③、打开数据库到mount状态
startup mount;
④、执行下面命令
alter database rename file '/u01/app/oracle/product/10.2.0/oradata/shanxi3.dbf' to
'/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf';
⑤、修改数据库为打开状态
alter database open;
⑥、查询dba_data_files文件看路径
十五、Dropping Tablespace
You cannot drop a tablespace if it:
-Is the System tablespace
-Has active segment
including contents drops the segments
including contexts and datafile deletes data files
cascade constraints drops all referential integrity constraints
命令:
drop tablespace userdata including contents and datafiles
删除表空间shanxi及文件
drop tablespace shanxi including contents and datafiles;
十六、Get Tablespace information
1、Tablespace information
--dba_tablespaces
--v$tablespace
2、Data file information
dba_data_files
v$datafile
3、Temp file information
dba_temp_files
v$tempfile
oracle 表空间 Managing Tablespace & Data Files
最新推荐文章于 2024-09-05 10:00:22 发布