目录
Oracle 表空间的日常维护与管理
1、创建数据表空间
查询有关表和视图：【使用版本Oracle 11gR2】
1.查看表空间信息
dba_tablespaces
v$tablespace
2.查看数据文件
dba_data_files
v$datafile
3.查看临时表空间
dba_temp_files
v$tempfile
当前的数据文件位置：
sql> col file_name format a60; sql> select file_name from dba_data_files; FILE_NAME ------------------------------------------------------------ +DGSYSTEM/kyeupdb/datafile/system.271.978829205 +DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265 +DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323 +DGSYSTEM/kyeupdb/datafile/users.275.978829391 +DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421 +DGSYSTEM/kyeupdb/datafile/kye01.dbf 6 rows selected.
- 创建单个数据文件的表空间
sql> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS TS_EXAMPLE KYE_TBS01 7 rows selected. sql> create tablespace kye_tbs02 datafile ‘+DGSYSTEM/kyeupdb/datafile/kye02.dbf‘ size 2M 2 autoextend off 3 segment space management auto; Tablespace created.
autoextend off —不自动扩展
segment space management auto —自动段管理 推荐
- 创建多个数据文件
sql> CREATE TABLESPACE kye_tbs03 LOGGING DATAFILE ‘+DGSYSTEM/kyeupdb/datafile/kye03_1.dbf‘ SIZE 2M AUTOEXTEND OFF,‘+DGSYSTEM/kyeupdb/datafile/kye03_2.dbf‘ SIZE 2M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. sql> create tablespace kye_tbs04 logging datafile 2 ‘+DGSYSTEM/kyeupdb/datafile/kye04_1.dbf‘ size 2M autoextend on next 1M maxsize 20M,3 ‘+DGSYSTEM/kyeupdb/datafile/kye04_2.dbf‘ size 2M autoextend on next 1M maxsize 20M 4 extent management local 5 segment space management auto; Tablespace created.
- 创建大表空间
sql> create bigfile tablespace kye_bigtbs1 datafile 2 ‘+DGSYSTEM/kyeupdb/datafile/kye_bigfile1.dbf‘ size 1G; Tablespace created.
2、创建临时表空间
sql> create temporary tablespace kye_tmptbs1 2 tempfile ‘+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf‘ 3 size 5M autoextend off; Tablespace created.
3、创建 UNDO 表空间
sql> create undo tablespace kye_undotbs1 2 datafile ‘+DGSYSTEM/kyeupdb/datafile/kye_undo1.dbf‘ 3 size 10m autoextend off; Tablespace created.
4、表空间的扩展与修改大小
- 表空间的扩展
--- 查看此时创建的表空间 sql> col name format a30; sql> select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 3 TEMP NO NO YES 4 USERS YES NO YES 5 TS_EXAMPLE YES NO YES 7 KYE_TBS01 YES NO YES 8 KYE_TBS02 YES NO YES 9 KYE_TBS03 YES NO YES 10 KYE_TBS04 YES NO YES 11 KYE_BIGTBS1 YES YES YES TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 12 KYE_TMPTBS1 NO NO YES 13 KYE_UNDOTBS1 YES NO YES 13 rows selected. sql> col file_name format a55; sql> select file_name from dba_data_files; FILE_NAME ------------------------------------------------------- +DGSYSTEM/kyeupdb/datafile/system.271.978829205 +DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265 +DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323 +DGSYSTEM/kyeupdb/datafile/users.275.978829391 +DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421 +DGSYSTEM/kyeupdb/datafile/kye01.dbf +DGSYSTEM/kyeupdb/datafile/kye02.dbf +DGSYSTEM/kyeupdb/datafile/kye03_1.dbf +DGSYSTEM/kyeupdb/datafile/kye03_2.dbf +DGSYSTEM/kyeupdb/datafile/kye04_1.dbf +DGSYSTEM/kyeupdb/datafile/kye04_2.dbf FILE_NAME ------------------------------------------------------- +DGSYSTEM/kyeupdb/datafile/kye_bigfile1.dbf +DGSYSTEM/kyeupdb/datafile/kye_undo1.dbf 13 rows selected. sql> alter tablespace kye_tbs01 add datafile ‘+DGSYSTEM/kyeupdb/datafile/kye01_1.dbf‘ size 2m autoextend off; Tablespace altered. sql> alter tablespace kye_tmptbs1 add tempfile ‘+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1_1.dbf‘ size 2m autoextend off; Tablespace altered.
- 修改大小
sql> col name format a55; sql> select name,bytes/1024/1024 from v$tempfile; NAME BYTES/1024/1024 ------------------------------------------------------- --------------- +DGSYSTEM/kyeupdb/tempfile/temp.274.978829379 1024 +DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf 5 +DGSYSTEM/kyeupdb/datafile/kye_tmptbs1_1.dbf 2 sql> alter database tempfile ‘+DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf‘ resize 6m; Database altered. sql> select name,bytes/1024/1024 from v$tempfile; NAME BYTES/1024/1024 ------------------------------------------------------- --------------- +DGSYSTEM/kyeupdb/tempfile/temp.274.978829379 1024 +DGSYSTEM/kyeupdb/datafile/kye_tmptbs1.dbf 6 +DGSYSTEM/kyeupdb/datafile/kye_tmptbs1_1.dbf 2
5、表空间重命名
sql> alter tablespace kye_bigtbs1 rename to kye_bigtbs; Tablespace altered.
6、表空间的删除
drop tablespace 表空间名字；--直接删除表空间，而不删除对应的数据文件
drop tablespace 表空间名字 including contents and datafiles; --加上该选项 则连同数据文件 一起删除了(常用）
sql> drop tablespace kye_tbs04 including contents and datafiles; Tablespace dropped. sql> drop tablespace kye_bigtbs including contents and datafiles cascade constraints; Tablespace dropped.
7、更改表空间的读写模式
sql> alter tablespace kye_tbs03 read only; Tablespace altered. sql> alter tablespace kye_tbs03 read write; Tablespace altered.
也可以修改表：
sql> create table kye001 (id int,name varchar2(22)); Table created. sql> alter table kye001 read only; Table altered. sql> alter table kye001 read write; Table altered.
8、更改表空间的在线模式
alter tablespace kye_tbs01 offline; Tablespace altered. sql> alter tablespace kye_tbs01 online; Tablespace altered.
在上面的测试中可以看出，直接可以设置表空间离线，但是在下面的操作中可以对数据文件直接设置成离线吗？
可以对数据文件进行操作：
alter database datafile 9 offline; alter database datafile ‘+DGSYSTEM/kyeupdb/datafile/kye03_2.dbf‘ online; alter database datafile 9 offline for drop;
在对数据文件操作的时候会出现错误： 除非启用了介质恢复,否则不允许立即脱机.在NOARCHIVELOG模式，不可以立即离线数据文件。
