ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 永久表空间

永久表空间

原创 Linux操作系统 作者:yuecaibo 时间:2012-03-06 00:01:22 0 删除 编辑

永久表空间管理
 又分为:
  小文件表空间(一个表空间可以存放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.

SYS@beijing>

修改表空间状态  (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.

SYS@beijing>

 

离线状态:
 只可以删除对象(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>

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.

SYS@beijing>


数据文件的移动改名(两种命令方法)

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.

SYS@beijing>


扩容的方法:

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博客 ” ,链接:http://blog.itpub.net/24756465/viewspace-717781/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 临时表空间
请登录后发表评论 登录
全部评论

注册时间:2012-03-03

  • 博文量
    42
  • 访问量
    37666