2013年4月27日 星期六
10:37
1、tablespace 功能:从逻辑上简化数据库的管理
2、tablespace 概述
一个database 对应多个tablespace ，一个tablespace 可以对应一个datafile ，也可以多个Oracle 10g，最少有两个系统表空间：system 和sysaux 用来存放数据字典信息system和sysaux 作为系统表空间system: 存放数据字典，在建库时建立sysaux：辅助表空间，存放从system分离一部分信息如对数据库的监控、运行状态等信息temp：临时表空间，用于数据排序，不存放永久对象undo：存放undo数据块（数据块旧的镜像）
system表空间不能 offline 和drop。
查看与表空间有关的视图
V$TABLESPACE
V$DBA_DATA_FILES
V$DATABASE_PROPERTIES
表空间的管理
1、创建表空间
查看 表空间
SYS@ prod>select tablespace_name,contents,logging from dba_tablespaces;
TABLESPACE_NAME CONTENTS LOGGING
------------------------------ --------- ------------------ ------------------ ---------
SYSTEM PERMANENT LOGGING
UNDOTBS1 UNDO LOGGING
SYSAUX PERMANENT LOGGING
TEMP TEMPORARY NOLOGGING LOGGING 说明该表空间上的操作都会产生rodo日志
USERS PERMANENT LOGGING
EXAMPLE PERMANENT NOLOGGING
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM
7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP
6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX
5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS
创建表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/lx02/test01.dbf' size 10m;
Tablespace created.
SQL> select file_id,file_name,tablespace_name ,bytes/1024/1024 "Size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME Size
---------- -------------------------------------------------- ------------------------------ ----------
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM 300
7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP 48
6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX 40
5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE 400
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS 48
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX 100
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS 100
8 /u01/app/oracle/oradata/cuug/test01.dbf TEST 10
2、表空间对free extents（空闲区）的管理
1）9i 以前通过dictionary管理，性能不好，容易产生碎片，通过uet$ 和 fet$ 管理
2）9i以后采用local 管理，在表空间上建立bitmap ，表空间自行管理 用0,1来表示free extents，oracle 10g 默认本地管理
dictionary管理 通过uet$ 和 fet$ 管理
SYS@ prod>desc uet$
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
SEGFILE# NOT NULL NUMBER
SEGBLOCK# NOT NULL NUMBER
EXT# NOT NULL NUMBER
TS# NOT NULL NUMBER
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
SYS@ prod>desc fet$
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
TS# NOT NULL NUMBER
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
SYS @ prod > select tablespace_name,status,extent_management from dba_tablespaces;
TABLESPACE_NAME STATUS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM ONLINE LOCAL
UNDOTBS1 ONLINE LOCAL
SYSAUX ONLINE LOCAL
TEMP ONLINE LOCAL
USERS ONLINE LOCAL
EXAMPLE ONLINE LOCAL
【当system表空间为 local管理方式时 其他表空间必须是 local ；表空间为dictionary 管理方式时其他表空间可以是数据字典 也可以是local 只是为了兼容以前版本而已】
SQL> create tablespace cuug
datafile '/u01/app/oracle/oradata/cuug/cuug01.dbf' size 10m
extent management dictionary; 【extent management 指定管理方式 数据字典 】
Default storage (initial 1M next 1M pctincrease o ) 【指定 第一个数据扩展 1M 第二个数据扩展 1M 从第三个数据扩展开始 每个数据扩展在原先基础上增加 0 】
create tablespace cuug
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace 报错 ：不能创建数据字典管理的表空间
3、数据字典管理通过fet$(记录空闲extent）和uet$(记录已使用extent）
SQL> select a.ts#,a.name,b.file#,b.name "File_name" ,c.block#,d.extent_management from v$tablespace a,v$datafile b,fet$ c,dba_tablespaces d
where a.ts#=b.ts# and a.ts#=c.ts# and a.name=d.tablespace_name;
TS# NAME FILE# File_name BLOCK# EXTENT_MAN
---------- ------------------------------ ---------- -------------------------------------------------- ---------- ----------
0 SYSTEM 1 /u01/app/oracle/oradata/cuug/system01.dbf 21509 DICTIONARY
9 CUUG 9 /u01/app/oracle/oradata/cuug/cuug01.dbf 2 DICTIONARY
SQL> select ts#,file#,block# from uet$；
4、local manager 对extent size 分配有两种管理方式：autoallocate （自动分配，默认），uniform size 统一大小
采用uniform 建立表空间
SQL> create tablespace lx01
datafile '/u01/app/oracle/oradata/cuug/lx01.dbf' size 10m
extent management local
uniform size 1m;
Tablespace created.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MANAGEMENT
-------------------------------------------------- ------------------- ---------------- ------------------------------
SYSTEM 64 LOCAL（自动分配，默认）
RTBS 64 LOCAL
SYSAUX 64 LOCAL
TEMP 1024 1024 LOCAL
USERS 64 LOCAL
TEXT 64 LOCAL
LX01 1024 1024 LOCAL（统一大小）
11 rows selected.
————inittial_extent 第一个extent 的大小
————next_extent 第二个extent的大小
5、数据字典管理表空间
SQL> create tablespace cuug
datafile '/u01/app/oracle/oradata/cuug/cuug.dbf' size 10m
extent management dictionary
default storage (initial 1m next 1m pctincrease 50);
Tablespace created.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT,pct_increase from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MAN PCT_INCREASE
------------------------------ ------------------- ---------------- ---------- ------------
SYSTEM 16 16 DICTIONARY 50
RTBS 64 LOCAL
SYSAUX 64 LOCAL
TEMP 1024 1024 LOCAL 0
USERS 4096 4096 LOCAL 0
EXAMPLE 1024 1024 LOCAL 0
INDX 64 LOCAL
OLTP 2048 2048 LOCAL 0
TEST 64 LOCAL
CUUG 40 40 DICTIONARY 50
LX01 1024 1024 LOCAL 0
CUUG 1024 1024 DICTIONARY 50
-----------initial 初始化区（第一个extent），
next 第二个extent ，
pctincrease 从第三个extent 开始，比上一个extent增加的百分比。
UNDO 表空间
undo tablespace 存放undo数据块，可以建立多个undo 表空间，但处于active状态只有一个，active 状态的undo tablespace是不能offline 和drop。
如果未建立undo tablespace 。 oracle 使用system undo segment。（如果没有建立undo表空间，就默认用system表空间作为undo表空间，这是很不好的）
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string rtbs
SQL> select tablespace_name,status,contents,logging from dba_tablespaces;
TABLESPACE STATUS CONTENTS
---------- --------------------------- ---------------------------
SYSTEM ONLINE PERMANENT
RTBS ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
TEXT ONLINE PERMANENT
LX01 ONLINE PERMANENT
7 rows selected.
创建表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/lx02/test01.dbf' size 10m;
Tablespace created.
SQL> select tablespace_name,status,contents,logging from dba_tablespaces;
TABLESPACE STATUS CONTENTS
---------- --------------------------- ---------------------------
SYSTEM ONLINE PERMANENT
RTBS ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
TEXT ONLINE PERMANENT
LX01 ONLINE PERMANENT
UNDOTBS ONLINE UNDO
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string rtbs
SQL> select TABLESPACE_NAME,STATUS,CONTENTS,logging from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
RTBS ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
EXAMPLE ONLINE PERMANENT
INDX ONLINE PERMANENT
OLTP ONLINE PERMANENT
TEST ONLINE PERMANENT
CUUG ONLINE PERMANENT
LX01 ONLINE PERMANENT
CUUG ONLINE PERMANENT
UNDO_TBS01 ONLINE UNDO
修改默认undo表空间
SQL> alter system set undo_tablespace=undo_tbs01;
System altered.
------------切换undo 表空间
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_TBS01
临时表空间
temporary tablespace 用于排序，可以建立多个临时表空间，但默认的临时表空间也只能有一个，default temporary tablespace不能offline和drop。如果未指定默认的临时表空间oracle 将会使用system作为临时表空间。 不可以存放永久对象
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/cuug/temp01.dbf' size 100m reuse;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name from dba_temp_files;
TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/app/oracle/oradata/cuug/temp01.dbf TEMP
SQL> select file#,name ,bytes/1024/1024 from v$tempfile;
FILE# NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
1 /u01/app/oracle/oradata/cuug/temp01.dbf 100
建立临时表空间
SQL> create temporary tablespace tmp01
tempfile '/u01/app/oracle/oradata/lx02/tmp01.dbf' size 10m
extent management local uniform size 128k;
Tablespace created.
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/cuug/temp01.dbf
2 /u01/app/oracle/oradata/cuug/tmp01.dbf
------------查看默认的临时表空间
SQL> select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME CUUG Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
27 rows selected.
-----------用户指定临时表空间
SQL> alter user scott temporary tablespace tmp01;
User altered.
-----切换默认的临时表空间
SQL> alter database default temporary tablespace tmp01;
Database altered.
SQL> select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TMP01 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME CUUG Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
--------------建立临时表空间组
SQL> alter tablespace temp tablespace group tmpgrp;
Tablespace altered.
SQL> alter tablespace tmp01 tablespace group tmpgrp;
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP
TMPGRP TMP01
------------将临时表空间组设置为默认临时表空间，可以实现负载均衡
SQL> alter database default temporary tablespace tmpgrp;
Database altered.
SQL> select * from database_properties;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TMPGRP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DBTIMEZONE -04:00 DB time zone
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME CUUG Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
------------查看临时表空间信息
SQL> select file#,name,bytes from v$tempfile
FILE# NAME BYTES
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/prod/temp01.dbf 20971520
2 /u01/app/oracle/oradata/prod/tmp01.dbf 10485760
3 /u01/app/oracle/oradata/prod/tmp02.dbf 10485760
【查看用户临时表空间 和 默认表空间】
SYS@ prod>select username,temporary_tablespace ,default_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
DBSNMP TEMP SYSAUX
SYSMAN TEMP SYSAUX
SCOTT TEMP USERS
CUUG TEMP USERS
SQL> desc dba_temp_files;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS CHAR(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
表空间处于read only 状态，不可以做dml 操作，但可以删除对象。
SQL> create table t01 (id int) tablespace cuug;
Table created.
【Read only 状态的表空间 不可以执行 DML 操作 但是可以执行 DDL 操作 ,因为 DML 修改的是表上的数据而表空间处于只读状态所以无法操作 DDL 数据定义语言修改的是表的结构 表结构存在数据字典而数据字典存在system 表空间】。
SQL> alter tablesapce cuug read only; 把表空间置为 read only 状态 系统生成一个检查点
Tablespace altered.
SQL> insert into t01 values (1); DML语言不可以操作
insert into t01 values (1)
*
ERROR at line 1:
ORA-00372: file 9 cannot be modified at this time
ORA-01110: data file 9: '/u01/app/oracle/oradata/lx02/cuug01.dbf'
09:26:03 SQL> drop table t01 purge; DDL 语言可以
Table dropped.
SQL> alter tablespace cuug read write;
Tablespace altered.
表空间脱机offline（在表空间被破坏，需要recover）
1）system表空间不能offline ，正在使用的undo 不能offline，默认temporary tablespace不能offline
2）表空间脱机时会在tablespace上生成检查点，下次online 不需要做恢复操作。
表空间 offline 数据文件 offline
表空间 生成检查点 脏数据写入数据文件 所以online不需要recover
数据文件 不会生成检查点, online时需要做 recover
SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary 【 system表空间不可以offline】
SQL> alter tablespace undo_tbs01 offline;
alter tablespace undo_tbs01 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
SYS @ orcl >alter tablespace users offline;
Tablespace altered.
SYS @ orcl >alter tablespace users online;
Tablespace altered.
表空间 offline 生成检查点不需要 recover
SQL> alter database datafile 4 offline;
Database altered.
SQL> select file#,name ,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf 175912
2 /u01/app/oracle/oradata/orcl/undotbs01.dbf 175912
3 /u01/app/oracle/oradata/orcl/sysaux01.dbf 175912
4 /u01/app/oracle/oradata/orcl/user01.dbf 176806
SQL> alter database datafile 4 online;
alter database datafile 4 recover datafile 4; online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/prod/users01.dbf'
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
数据文件offline 不会生成检查点需要recover
——offline immediate 立刻offline 不生成检查点，下次online需要recover
SYS @ orcl >alter tablespace users offline;
Tablespace altered.
SYS @ orcl >select file#,name ,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf 175912
2 /u01/app/oracle/oradata/orcl/undotbs01.dbf 175912
3 /u01/app/oracle/oradata/orcl/sysaux01.dbf 175912
SYS @ orcl >alter tablespace users online;
Tablespace altered.
表空间 不需要
SQL> alter tablespace tests online;
alter tablespace tests online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/test01.dbf'
SQL> recover tablespace tests;
Media recovery complete.
SQL> alter tablespace tests online;
Tablespace altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/arch/prod
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
-------对于非归档模式脱机：alter tablespace xxx offline drop；
调整表空间的尺寸（表空间的大小和它的数据文件对应）
1）自动 autoextend 扩展
2）resize datafile 大小
3）增加表空间数据文件
1）自动扩展配置
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT
---------- -------------------------------------------------- ------------------------------ ---
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO
9 /u01/app/oracle/oradata/cuug/cuug01.dbf CUUG NO
11 /u01/app/oracle/oradata/cuug/cuug.dbf CUUG NO
7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP NO
6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX NO
5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE YES
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS NO
8 /u01/app/oracle/oradata/cuug/test01.dbf TEST NO
10 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO
12 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDO_TBS01 NO
12 rows selected.
数据文件 改变大小
----------自动扩展
SQL> alter database datafile '/u01/app/oracle/oradata/lx02/test01.dbf' autoextend on next 10m maxsize 500m;
Database altered.
----------resize
SQL> alter database datafile '/u01/app/oracle/oradata/lx02/test01.dbf' resize 50m;
Database altered.
-----------增加datafile
SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/lx02/test02.dbf' size 10m;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size
---------- -------------------------------------------------- ------------------------------ --- ----------
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 300
9 /u01/app/oracle/oradata/cuug/cuug01.dbf CUUG NO 10
11 /u01/app/oracle/oradata/cuug/cuug.dbf CUUG NO 10
7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP NO 48
6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX NO 40
5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE YES 400
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 48
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 100
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS NO 100
8 /u01/app/oracle/oradata/cuug/test01.dbf TEST YES 50
10 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
12 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDO_TBS01 NO 10
13 /u01/app/oracle/oradata/cuug/test02.dbf TEST NO 10
13 rows selected.
迁移表空间数据文件：
1） open 状态（将表空间脱机——alter tablespace 表空间名 rename datafile ‘’to ‘’——将表空间online）
SQL> select file_id,file_name,tablespace_name,autoextensible,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUTOEXTEN size
---------- -------------------------------------------------- --------------- --------- ----------
5 /u01/app/oracle/oradata/cuug/text01.dbf TEXT NO 100
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 200
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 325
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS YES 200
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 325
6 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
6 rows selected.
SQL> alter tablespace text offline;
Tablespace altered.
SQL> !
[oracle@solaris10 ~]$cp /u01/app/oracle/oradata/cuug/text01.dbf /disk1/oradata/cuug
SQL> alter tablespace text rename
datafile '/u01/app/oracle/oradata/cuug/text01.dbf' to '/disk1/oradata/cuug/text01.dbf';
Tablespace altered.
SQL> alter tablespace text online;
Tablespace altered.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUTOEXTEN size
---------- -------------------------------------------------- --------------- --------- ----------
5 /disk1/oradata/cuug/text01.dbf TEXT NO 100
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 200
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 325
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS YES 200
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 325
6 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
2）mount 状态（正常关库——启动到mount状态——执行改名语句——起库到open状态）
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
SQL> !
[oracle@solaris10 ~]$cp /disk1/oradata/cuug/text01.dbf /u01/app/oracle/oradata/cuug/text01.dbf
[oracle@solaris10 ~]$exit
exit
SQL> alter database rename file '/disk1/oradata/cuug/text01.dbf' to '/u01/app/oracle/oradata/cuug/text01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUTOEXTEN size
---------- -------------------------------------------------- --------------- --------- ----------
5 /u01/app/oracle/oradata/cuug/text01.dbf TEXT NO 100
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 200
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 325
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS YES 200
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 325
6 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
删除表空间
【不可删除的表空间】 ：
1）system
2）active undo tablespace 有活动事务 的表空间
3） default temporary tablespace 默认的临时表空间
4）默认的永久表空间
SQL> DROP TABLESPACE SYSTEM;
DROP TABLESPACE SYSTEM
*
ERROR at line 1:
ORA-01550: cannot drop system tablespace
SQL> drop tablespace cuug including contents and datafiles; 删除表空间 级联 表空间下的对象 和 数据文件
Tablespace dropped.
SQL> select file_id,file_name,tablespace_name,AUTOEXTENSIBLE ,bytes/1024/1024 "size" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME AUT size
---------- -------------------------------------------------- ------------------------------ --- ----------
1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM NO 300
11 /u01/app/oracle/oradata/cuug/cuug.dbf CUUG NO 10
7 /u01/app/oracle/oradata/cuug/OLTP01.DBF OLTP NO 48
6 /u01/app/oracle/oradata/cuug/indx01.dbf INDX NO 40
5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE YES 400
4 /u01/app/oracle/oradata/cuug/user01.dbf USERS NO 48
3 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX NO 100
2 /u01/app/oracle/oradata/cuug/rtbs01.dbf RTBS NO 100
8 /u01/app/oracle/oradata/cuug/test01.dbf TEST YES 50
10 /u01/app/oracle/oradata/cuug/lx01.dbf LX01 NO 10
12 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDO_TBS01 NO 10
13 /u01/app/oracle/oradata/cuug/test02.dbf TEST NO 10
查看表空间空闲大小
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX 51.0625
LX01 9
UNDO_TBS01 8.6875
CUUG 9.9921875
RTBS 18.6875
USERS 16
OLTP 46
TEST 59.875
SYSTEM 131.890625
EXAMPLE 399
INDX 39.9375
建立非标准块表空间
SQL> alter system set db_16k_cache_size=12m; 给非标准块添加缓存
System altered.
SQL> create tablespace tbs_16k
datafile '/u01/app/oracle/oradata/tbs16k01.dbf' size 10m
blocksize 16k;
Tablespace created.
SQL> select TABLESPACE_NAME,block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
RTBS 8192
SYSAUX 8192
TEMP 8192
USERS 8192
EXAMPLE 8192
INDX 8192
OLTP 8192
TEST 8192
LX01 8192
CUUG 8192
UNDO_TBS01 8192
TMP01 8192
TBS_16K 16384
大文件（bigfile）表空间（默认small file）
1）small file，在一个表空间可以建立多个数据文件（8k的block，最多到32G）
2）bigfile：在一个表空间只能建立一个数据文件（缺点）（8k的block，datafile maxsize 可以32T），可以简化对数据文件管理
SQL> create bigfile tablespace big_tbs datafile '/u01/app/oracle/oradata/bigtbs01.dbf' size 100m;
Tablespace created.
给大文件表空间增加多一个数据文件，会被拒绝，因为大文件表空间只能建立一个数据文件
SQL> alter tablespace big_tbs add datafile '/u01/app/oracle/oradata/bigtbs02.dbf' size 100m;
alter tablespace big_tbs
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
SQL> select name,bigfile from v$tablespace;
NAME BIG
-----------------------------------------------------
SYSTEM NO
RTBS NO
SYSAUX NO
USERS NO
EXAMPLE NO
INDX NO
OLTP NO
TEMP NO
TEST NO
TBS_16K NO
LX01 NO
CUUG NO
UNDO_TBS01 NO
TMP01 NO
BIG_TBS YES
转载于:https://www.cnblogs.com/iyoume2008/p/7525170.html
相关资源：Oracle Database 9i10g11g编程艺术：深入数据库体系结构（第2版）--详细书签版