永久表空间管理
又分为:
小文件表空间(一个表空间可以存放1023个文件 每个文件4M*block_size)
基本应用都是这类 默认创建也是它
大文件表空间(一个表空间可以存放1个文件 文件管理4G*block_size)
很少使用,不利于IO分散,容易产生争用,容易被FS限制
小文件表空间
查看
SQL> select tablespace_name,file_id,file_name,ceil(bytes/1048567)||'M' MB from dba_data_files;
TABLESPACE_NAME FILE_ID FILE_NAME MB
--------------- ---------- --------------------------------------------- --------------------
USERS 4 /u01/oracle/oradata/ora10g/users01.dbf 26M
SYSAUX 3 /u01/oracle/oradata/ora10g/sysaux01.dbf 241M
UNDOTBS1 2 /u01/oracle/oradata/ora10g/undotbs01.dbf 26M
SYSTEM 1 /u01/oracle/oradata/ora10g/system01.dbf 481M
SQL>
创建
文件系统:create tablespace mytbs datafile '/PATH/file.dbf' size 10m;
裸设备:create tablespace rawtbs datafile '/dev/raw/raw1' size 4095M
SQL> create tablespace mytbs datafile '/u01/oracle/oradata/ora10g/mytbs01.dbf' size 10M;
Tablespace created.
SQL> select tablespace_name,file_id,file_name,ceil(bytes/1048567)||'M' MB from dba_data_files;
TABLESPACE_NAME FILE_ID FILE_NAME MB
--------------- ---------- --------------------------------------------- --------------------
USERS 4 /u01/oracle/oradata/ora10g/users01.dbf 26M
SYSAUX 3 /u01/oracle/oradata/ora10g/sysaux01.dbf 241M
UNDOTBS1 2 /u01/oracle/oradata/ora10g/undotbs01.dbf 26M
SYSTEM 1 /u01/oracle/oradata/ora10g/system01.dbf 481M
MYTBS 5 /u01/oracle/oradata/ora10g/mytbs01.dbf 11M
SQL>
创建大文件表空间
SYS@beijing> create bigfile tablespace bigtbs datafile '/u01/oracle/oradata/beijing/bigtbs01.dbf' size 5M;
Tablespace created.
SYS@beijing> select tablespace_name,bigfile from dba_tablespaces;
TABLESPACE_NAME BIG
--------------- ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS NO
MYTBS NO
BIGTBS YES
7 rows selected.
修改表空间状态 (online readonly offline)
只读表空间 只能select查询和drop删除对象 不能修改(INTSERT update delete truncate)
system undotbs temp 包含活动事务的表空间都不能只读
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- ----------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
MYTBS ONLINE
6 rows selected.
SQL> create table t1 tablespace mytbs as select * from scott.emp;
Table created.
SQL> alter tablespace mytbs read only;
Tablespace altered.
SQL> update t1 set sal=sal+1 ;
update t1 set sal=sal+1
*
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/u01/oracle/oradata/ora10g/mytbs01.dbf'
SQL> truncate table t1;
truncate table t1
*
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/u01/oracle/oradata/ora10g/mytbs01.dbf'
SQL> drop table t1 purge;
Table dropped.
SQL>
只读表空间不可以修改表数据 但可以删除表结构 因为表结果存储在数据字典中 数据字典在system表空间
SQL> 无法做只读的表空间
SQL> alter tablespace system read only;
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read only
SQL> alter tablespace undotbs1 read only;
alter tablespace undotbs1 read only
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace
SQL> alter tablespace temp read only;
alter tablespace temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL>
恢复成online(读写)状态
SYS@beijing> alter tablespace mytbs read write;
Tablespace altered.
SYS@beijing> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- ----------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
MYTBS ONLINE
6 rows selected.
离线状态:
只可以删除对象(drop) 不可以select dml truncate
系统表空间+default临时+包含活动事务的表空间是不能离线的
离线状态DBA可以移动数据文件路径
SQL> create table t1 tablespace mytbs as select * from scott.emp;
Table created.
SQL> alter tablespace mytbs offline;
Tablespace altered.
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/oracle/oradata/ora10g/mytbs01.dbf'
SQL> update t1 set sal=sal+1;
update t1 set sal=sal+1
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/oracle/oradata/ora10g/mytbs01.dbf'
SQL> truncate table t1;
truncate table t1
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/oracle/oradata/ora10g/mytbs01.dbf'
SQL> drop table t1 purge;
Table dropped.
SQL>
SQL> alter tablespace mytbs online;
Tablespace altered.
SQL>
离线就是冻结 将SCN号锁定在某一时刻
SYS@beijing> select name,checkpoint_change# open_scn,last_change# stop_scn from v$datafile;
NAME OPEN_SCN STOP_SCN
-------------------------------------------------- ---------- ----------
/u01/oracle/oradata/ora10g/system01.dbf 475662
/u01/oracle/oradata/ora10g/undotbs01.dbf 475662
/u01/oracle/oradata/ora10g/sysaux01.dbf 475662
/u01/oracle/oradata/ora10g/users01.dbf 476689 476689
/u01/oracle/oradata/ora10g/mytbs01.dbf 476652
SYS@beijing>
SYS@beijing> alter tablespace users online;
Tablespace altered.
SYS@beijing> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- ----------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
MYTBS ONLINE
6 rows selected.
数据文件的移动改名(两种命令方法)
1. 使用alter tablespace TABLESPACE_NAME rename datafile 'OLD_file' to 'NEW_file';命令
前提条件
a.表空间要offline状态 alter tablespace users offline
b.目标文件要存在 就是先做mv操作
SQL> alter tablespace users offline;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/oracle/oradata/ora10g/system01.dbf
/u01/oracle/oradata/ora10g/undotbs01.dbf
/u01/oracle/oradata/ora10g/sysaux01.dbf
/u01/oracle/oradata/ora10g/users01.dbf
/u01/oracle/oradata/ora10g/mytbs01.dbf
SQL> ! mv /u01/oracle/oradata/ora10g/users01.dbf /home/oracle/
SQL> alter tablespace users rename datafile '/u01/oracle/oradata/ora10g/users01.dbf' to '/home/oracle/users01.dbf';
Database altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- ----------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
MYTBS ONLINE
6 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/oracle/oradata/ora10g/system01.dbf
/u01/oracle/oradata/ora10g/undotbs01.dbf
/u01/oracle/oradata/ora10g/sysaux01.dbf
/home/oracle/users01.dbf
/u01/oracle/oradata/ora10g/mytbs01.dbf
SQL>
2. 使用alter database rename file 'OLD_file' to 'NEW_file';命令
前提条件
a.数据库mount或表空间离线 shut immediate ==> startup mount
b.目标文件存在 mv src_file dst_file
SQL> shut immediate
startup mount
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 71304784 bytes
Database Buffers 209715200 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/oracle/oradata/ora10g/system01.dbf
/u01/oracle/oradata/ora10g/undotbs01.dbf
/u01/oracle/oradata/ora10g/sysaux01.dbf
/home/oracle/users01.dbf
/u01/oracle/oradata/ora10g/mytbs01.dbf
SQL> ! mv '/home/oracle/users01.dbf' '/u01/oracle/oradata/ora10g/'
SQL> alter database rename file '/home/oracle/users01.dbf' to '/u01/oracle/oradata/ora10g/users01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/oracle/oradata/ora10g/system01.dbf
/u01/oracle/oradata/ora10g/undotbs01.dbf
/u01/oracle/oradata/ora10g/sysaux01.dbf
/u01/oracle/oradata/ora10g/users01.dbf
/u01/oracle/oradata/ora10g/mytbs01.dbf
SQL>
练习
将表空间设置只读 然后备份控制文件创建脚本
一致停库 启动到nomount重新创建控制文件
启动数据库 根据控制文件备份脚本中的提示将只读表空间恢复正常(直到可以在这个表空间内创建表)
表空间只读和离线导致控制文件备份不完整
当表空间只读或离线时,控制文件备份中并不记录它
SQL> create tablespace test datafile '/u01/oracle/oradata/ora10g/mytest01.dbf' size 10M;
Tablespace created.
SQL>
SQL> alter tablespace test add datafile '/u01/oracle/oradata/ora10g/mytest02.dbf' size 10M;
Tablespace altered.
SQL> alter database backup controlfile to trace as '/u01/oracle/ctl_rw.sql';
Database altered.
SQL> alter tablespace test read only;
Tablespace altered.
SQL> alter database backup controlfile to trace as '/u01/oracle/ctl_ro.sql';
Database altered.
SQL>
SQL>
[oracle@dba oracle]$ grep -A 7 '^DATAFILE' ctl_rw.sql
DATAFILE
'/u01/oracle/oradata/ora10g/system01.dbf',
'/u01/oracle/oradata/ora10g/undotbs01.dbf',
'/u01/oracle/oradata/ora10g/sysaux01.dbf',
'/u01/oracle/oradata/ora10g/users01.dbf',
'/u01/oracle/oradata/ora10g/mytest01.dbf',
'/u01/oracle/oradata/ora10g/mytest02.dbf'
CHARACTER SET AL32UTF8
--
[oracle@dba oracle]$ grep -A 7 '^DATAFILE' ctl_ro.sql
DATAFILE
'/u01/oracle/oradata/ora10g/system01.dbf',
'/u01/oracle/oradata/ora10g/undotbs01.dbf',
'/u01/oracle/oradata/ora10g/sysaux01.dbf',
'/u01/oracle/oradata/ora10g/users01.dbf'
CHARACTER SET AL32UTF8
;
--
那如果使用read only转储的创建语句重建控制文件 是不是会缺少表空间呢?
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 62916176 bytes
Database Buffers 218103808 bytes
Redo Buffers 2973696 bytes
SQL>
移除原来的控制文件 用noresetlos建立控制文件
SQL> ! rm /u01/oracle/oradata/ora10g/control*
SQL>
CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oracle/oradata/ora10g/redo01.log' SIZE 50M,
GROUP 2 '/u01/oracle/oradata/ora10g/redo02.log' SIZE 50M,
GROUP 3 '/u01/oracle/oradata/ora10g/redo03.log' SIZE 50M
DATAFILE
'/u01/oracle/oradata/ora10g/system01.dbf',
'/u01/oracle/oradata/ora10g/undotbs01.dbf',
'/u01/oracle/oradata/ora10g/sysaux01.dbf',
'/u01/oracle/oradata/ora10g/users01.dbf'
CHARACTER SET AL32UTF8
17 ;
Control file created.
SQL> alter database open;
Database altered.
SQL>
SQL> select tablespace_name,FILE_NAME,online_status,ENABLED from dba_data_files ddf,v$datafile df where ddf.file_id=df.file#;
TABLESPACE FILE_NAME ONLINE_ ENABLED
---------- --------------------------------------------- ------- ----------
SYSTEM /u01/oracle/oradata/ora10g/system01.dbf SYSTEM READ WRITE
UNDOTBS1 /u01/oracle/oradata/ora10g/undotbs01.dbf ONLINE READ WRITE
SYSAUX /u01/oracle/oradata/ora10g/sysaux01.dbf ONLINE READ WRITE
USERS /u01/oracle/oradata/ora10g/users01.dbf ONLINE READ WRITE
TEST /u01/oracle/product/10.2.0/dbs/MISSING00005 OFFLINE READ ONLY
TEST /u01/oracle/product/10.2.0/dbs/MISSING00006 OFFLINE READ ONLY
6 rows selected.
SQL>
SQL> alter database rename file '/u01/oracle/product/10.2.0/dbs/MISSING00005' to '/u01/oracle/oradata/ora10g/mytest01.dbf';
Database altered.
SQL> alter database rename file '/u01/oracle/product/10.2.0/dbs/MISSING00006' to '/u01/oracle/oradata/ora10g/mytest02.dbf';
Database altered.
SQL>
SQL> alter tablespace test online;
Tablespace altered.
SQL>
SQL> alter tablespace test read write;
Tablespace altered.
SQL>
SQL> select tablespace_name,FILE_NAME,online_status,ENABLED from dba_data_files ddf,v$datafile df where ddf.file_id=df.file#;
TABLESPACE FILE_NAME ONLINE_ ENABLED
---------- --------------------------------------------- ------- ----------
SYSTEM /u01/oracle/oradata/ora10g/system01.dbf SYSTEM READ WRITE
UNDOTBS1 /u01/oracle/oradata/ora10g/undotbs01.dbf ONLINE READ WRITE
SYSAUX /u01/oracle/oradata/ora10g/sysaux01.dbf ONLINE READ WRITE
USERS /u01/oracle/oradata/ora10g/users01.dbf ONLINE READ WRITE
TEST /u01/oracle/oradata/ora10g/mytest02.dbf ONLINE READ WRITE
TEST /u01/oracle/oradata/ora10g/mytest01.dbf ONLINE READ WRITE
6 rows selected.
SQL>
表空间扩容
使文件自动增长
增加新文件
修改现有文件的大小
SQL> select tablespace_name,sum(bytes/1048576) curr_MB,sum(MAXBYTES/1048576) MAX_MB from dba_data_files group by TABLESPACE_NAME;
TABLESPACE_NAME CURR_MB MAX_MB
--------------- ---------- ----------
SYSAUX 240 32767.9844
UNDOTBS1 25 32767.9844
USERS 25 32767.9844
SYSTEM 480 32767.9844
MYTBS 10 10
SQL>
最大大小为空 说明不能增长
查询当前分配大小中的剩余尺寸
SQL> select TABLESPACE_NAME,sum(bytes/1048576) free_mb from dba_free_space group by TABLESPACE_NAME;
TABLESPACE_NAME FREE_MB
--------------- ----------
UNDOTBS1 4.0625
SYSAUX .875
USERS 24.5625
SYSTEM 6.875
MYTBS 10.875
SQL>
将上面两个视图综合 写出下面的输出SQL
select a.TABLESPACE_NAME,
a.curr_mb,
b.free_mb,
a.max_mb,
round((b.free_mb/a.curr_mb)*100,3)||'%' "free_pct"
from
(select tablespace_name,sum(bytes/1048576) curr_MB,sum(MAXBYTES/1048576) MAX_MB from dba_data_files group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,sum(bytes/1048576) free_mb from dba_free_space group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME
TABLESPACE_NAME CURR_MB FREE_MB MAX_MB free_pct
--------------- ---------- ---------- ---------- -----------------------------------------
UNDOTBS1 25 2.8125 32767.9844 11.25%
SYSAUX 240 4.9375 32767.9844 2.057%
USERS 5 4.5 32767.9844 90%
SYSTEM 480 9.25 32767.9844 1.927%
BIGTBS 5 4.875 0 97.5%
MYTBS 10 9.9375 0 99.375%
6 rows selected.
扩容的方法:
1.使文件自动增长
alter database datafile '' autoextend on next 10M maxsize 4G;
alter database datafile '' autoextend off;
2.增加新文件
alter tablespace uses add datafile '' size 5M;
alter tablespace uses drop datafile ''; /*10G才能删 有数据不能删 第一个文件不能删*/
3.修改现有文件的大小
alter database datafile '' resize 15M;
alter database datafile '' resize 10M;
删除表空间和数据文件
删除表空间及其内容一起删除
删除表空间及其文件一起删除
SQL> create table t1 tablespace mytbs as select * from scott.emp;
Table created.
SQL> drop tablespace mytbs;
drop tablespace mytbs
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> drop tablespace mytbs INCLUDING CONTENTS;
Tablespace dropped.
SQL> !ls /u01/oracle/oradata/db10/mytbs0*
/u01/oracle/oradata/db10/mytbs01.dbf /u01/oracle/oradata/db10/mytbs03.dbf
/u01/oracle/oradata/db10/mytbs02.dbf
SQL> !rm /u01/oracle/oradata/db10/mytbs0*
SQL> create tablespace mytbs datafile '/u01/oracle/oradata/db10/mytbs01.dbf' size 10M;
Tablespace created.
SQL> create table t1 tablespace mytbs as select * from scott.emp;
Table created.
SQL> drop tablespace mytbs INCLUDING CONTENTS and datafiles;
Tablespace dropped.
SQL> !ls /u01/oracle/oradata/db10/mytbs0*
ls: /u01/oracle/oradata/db10/mytbs0*: 没有那个文件或目录
SQL>
创建索引或主键和唯一约束时将索引指定位置存放 生产库一定要把表和索引分开存放
创建索引和带索引的约束时,指定索引的存储位置
SQL> create index t1_ind on t1(empno) tablespace mytbs;
Index created.
SQL> ALTER TABLE T1 MODIFY(ENAME constraint T1_pk primary key using index tablespace mytbs);
Table altered.
SQL> select TABLESPACE_NAME,SEGMENT_NAME from dba_segments where SEGMENT_NAME in ('T1','T1_IND','T1_PK');
TABLESPACE_NAME SEGMENT_NAME
--------------- -------------------------
MYTBS T1
MYTBS T1_IND
MYTBS T1_PK
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24756465/viewspace-717781/ ，如需转载，请注明出处，否则将追究法律责任。
