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模式，不可以立即离线数据文件。