(Alex note) Managing Tablespace & Data Files

Define the purpose of tablespaces and data files
#数据库中的block是操作系统的2的n 次幂倍，如0, 2, 4, 8 ....
#database 中的一个table 至少要有一个segment.
# SYSTEM tablespce created with database , contains the data dictionary, contains the system undo segment. 这个是必需的。

#Tablespace 3 types:
Permanent
undo
tempory

Create tablespaces
-----------------------------------------
select * from v$tablespace;
#查看哪个文件属于哪个tablespace.
desc dba_data_files;
select file_name, tablespace_name from dba_data_files;
create tablespace userdata datafile '/u01/oradata/wilson/userdata01.dbf' size 5M;
select * from v$tablespace;
select file_name, tablespace_name from dba_data_files;
-----------------------------------------

Manage tablespace
This article detail describe the space management :   http://www.orafaq.com/node/3.
1. Locally managed tablespace;
2. Dictionary-managed tablespace; (这种管理方式已经过时了)


Create and manage tablespaces using Oracle Managerd files (OMF)
......


#如果system 表空间是local 管理方式，那其它表空间也必须采用local management .
#建议使用local management 方式不要用dictionary management style.

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
#具体要作这一步的转换在有很多的前提条件，请查看相关文档。

#Temporary Tablespace
used for sort operations
can be shared
locally managed extents recommended

#如果system表空间使用locally managed style, 如果我们没有default temporary tablespace, 则系统会自动将system 表空间作为tempoary tablespace.
这样会严重影响系统性能。所以在建立db的时候，一定要指定default temporary tablespace.

#Default temporay TS
database-wide

#看default tablespace
select propery_name, property_value, description from database_properties
#查看所有的tablespace
select * from v$tablespace;

select * from tablespace_name, contents,extent_mangement from dba_tablespace;

#当创建临时表空间的时候非常快，还有可以虽然看到相关的临时表空间的datafile已经产生，但是它是不占空间的。
#default 是全局的，只有一个，我们不可以删除它。但可以指定到其它临时表空间，让其它表空间成为default.

#reference article about temporary tablespace: http://www.orafaq.com/node/2

#Read-Only Tablespace
alter tablespace userdata read only;
-causes a checkpoint
-objects can be dropped from tablespace
#system 表空间是不能设置为read only的
 
#在创建user 的时候指定表空间和临时表空间，以后用这个用户所做的一些操作默认都会在创建指定的表空间上。
#如果有transaction没有commit,此时把表空间设为read only,这个会hung 住，真到transation commit 或rollback才会继续。

#Taking a tablespace offline
-offline的时候做备份和恢复。
-system表空间是不能offline的。
-Tablespace with active undo segments can not taken offline
-default temporary tablespace
alter tablespace usedate offline;
alter tablespace usedate online;

#Resizing a tablespace
-automatically using AUTOEXTEND(只是扩大)
-manually useing ALTER DATABASE(其实是更改tablespace下的datafile 大小)
-adding a data file using ALTER TABLESPACE
#查看表空间的使用情况
The script for reference can refer to: http://www.dba-oracle.com/t_tablespace_script.htm
--------------------------------------
1. CREATE TABLESPACE user_data DATAFILE '..../...dbf' SIZE 200m AUTOEXTEND ON NEXT 10m MAXSIZE 500M;
2. ALTER TABLESPACE user_data ADD DATAFILE '..../...dbf/ size 20M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;
3. #Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled
4. ALTER DATABASE DATAFILE '..../....dbf' AUTOEXTEND ON NEXT 10m MAXSIZE 100m;(对原有的datafile进行扩展)
5. ALTER DATABASE DATAFILE '..../....dbf’RESIZE 200M;
--------------------------------------

#临时表空间大小在 DBA_TEMP_FILE中查找

--------------------------------------
#Method 1 for moving Datafile
alter tablespace userdata offline;
!
cd /u01/oradata/allan/
mv 01.dbf ../01new.dbf
cd ..
ls
Alter tablespace userdata RENAME DATAFILE '..../01.dbf' to '..../01new.dbf'
alter tablespace online;
select file_name, tablespace_name from dba_data_file;
--------------------------------------

--------------------------------------
#Method 2 for moving Datafile
shutdown immedate
cd /u01/oradata
mv 01new.dbf alex/
startup mount
alter database rename file 'u01/oradata/01new.dbf' to 'u01/oradata/alex/01new.dbf'
--------------------------------------

--------------------------------------
# Dropping tablespace
- can't drop SYSTEM tablespace
- can't drop the tablespce when has active segments(比如有undo和temporary 中有数据的时候)
INCLUDE CONTENTS
INCLUDE CONTENTS AND DATAFILES
CASCADE CONSTRAINTS

DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;
--------------------------------------

Get Tablespace Information
Tablespace Informaction
-DBA_TABLESPACE
-V$TABLESPACE

Datafile information
-DBA_DATA_FILES
-V$DATAFILE

Temp file information
-DBA_TEMP_FILES
-V$TEMPFILE

转载于:https://www.cnblogs.com/yifan268/archive/2008/07/03/1234934.html