查看数据库表空间
select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
EXAMPLE PERMANENT
6 rows selected.
Elapsed: 00:00:00.01
一、永久表空间(PERMANENT):存储永久对象(表,索引)
创建永久表空间
create tablespace data01 datafile size 10m;
select tablespace_name,contents from dba_tablespaces order by contents;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
EXAMPLE PERMANENT
DATA01 PERMANENT
USERS PERMANENT
TEMP TEMPORARY
UNDOTBS1 UNDO
7 rows selected.
Elapsed: 00:00:00.01
查看表空间所对应的物理文件
select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
USERS +DATA/orcl/datafile/users.259.974086707
UNDOTBS1 +DATA/orcl/datafile/undotbs1.258.974086707
SYSAUX +DATA/orcl/datafile/sysaux.257.974086707
SYSTEM +DATA/orcl/datafile/system.256.974086707
EXAMPLE +DATA/orcl/datafile/example.265.974086801
DATA01 +DATA/orcl/datafile/data01.275.975123385
在表空间下创建表
create table scott.t01 tablespace data01 as select * from scott.emp;
查看表所在的表空间信息
select tablespace_name,table_name from dba_tables where table_name='T01';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
DATA01 T01
查看表空间状态
select tablespace_name,contents,status from dba_tablespaces order by contents;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
EXAMPLE PERMANENT ONLINE
DATA01 PERMANENT ONLINE
USERS PERMANENT ONLINE
TEMP TEMPORARY ONLINE
UNDOTBS1 UNDO ONLINE
7 rows selected.
Elapsed: 00:00:00.00
修改表空间的状态
alter tablespace data01 read only;
在read only状态下是不能对表空间内的对象做改写的,查询、drop表是没有问题的
alter tablespace data01 read write;
系统表空间、系统辅助表空间、UNDO表空间、TEMP表空间是不能被设置为只读的
alter tablespace data01 offline;
select tablespace_name,contents,status from dba_tablespaces order by contents;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
EXAMPLE PERMANENT ONLINE
DATA01 PERMANENT OFFLINE
USERS PERMANENT ONLINE
TEMP TEMPORARY ONLINE
UNDOTBS1 UNDO ONLINE
7 rows selected.
Elapsed: 00:00:00.01
select * from scott.t01;
select * from scott.t01
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '+DATA/sundb/datafile/data01.275.975123385'
Elapsed: 00:00:00.03
表空间在OFFLINE状态下是不能进行查询修改操作的,但是可以进行DROP操作
alter tablespace data01 online;
表空间的空间监控
select tablespace_name,sum(blocks)/128 curr_mb from dba_data_files group by tablespace_name;
TABLESPACE_NAME CURR_MB
------------------------------ ----------
UNDOTBS1 545
SYSAUX 500
USERS 190
SYSTEM 680
EXAMPLE 100
DATA01 10
6 rows selected.
Elapsed: 00:00:00.04
select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;
TABLESPACE_NAME CURR_MB MAX_MB
------------------------------ ---------- ----------
UNDOTBS1 545 32768
SYSAUX 500 32768
USERS 190 32768
SYSTEM 680 32768
EXAMPLE 100 32768
DATA01 10 0
6 rows selected.
Elapsed: 00:00:00.02
查看表空间空闲大小
select tablespace_name,round(sum(blocks)/128,0) free_mb from dba_free_space group by tablespace_name;
TABLESPACE_NAME FREE_MB
------------------------------ ----------
SYSAUX 31
UNDOTBS1 142
USERS 10
SYSTEM 7
EXAMPLE 22
DATA01 9
6 rows selected.
Elapsed: 00:00:00.02
如果表空间已经没有剩余空间了,那么它将从dba_free_space中消失
表空间扩容
1.修改现有数据文件大小
select file_name from dba_data_files where tablespace_name='DATA01';
alter database datafile '+DATA/sundb/datafile/data01.275.975123385' resize 20m;
select tablespace_name,round(sum(blocks)/128,0) free_mb from dba_free_space group by tablespace_name;
TABLESPACE_NAME FREE_MB
------------------------------ ----------
SYSAUX 31
UNDOTBS1 142
USERS 10
SYSTEM 7
EXAMPLE 22
DATA01 19
6 rows selected.
Elapsed: 00:00:00.03
select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;
TABLESPACE_NAME CURR_MB MAX_MB
------------------------------ ---------- ----------
UNDOTBS1 545 32768
SYSAUX 500 32768
USERS 190 32768
SYSTEM 680 32768
EXAMPLE 100 32768
DATA01 20 0
6 rows selected.
Elapsed: 00:00:00.02
2.打开数据文件的自动增长属性
alter database datafile '+DATA/sundb/datafile/data01.275.975123385' autoextend on;
select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;
TABLESPACE_NAME CURR_MB MAX_MB
------------------------------ ---------- ----------
UNDOTBS1 545 32768
SYSAUX 500 32768
USERS 190 32768
SYSTEM 680 32768
EXAMPLE 100 32768
DATA01 20 32768
6 rows selected.
Elapsed: 00:00:00.06
3.向表空间增加新的数据文件
alter tablespace data01 add datafile size 10m;
select tablespace_name,sum(blocks)/128 curr_mb,round(sum(maxblocks)/128,0) max_mb from dba_data_files group by tablespace_name;
TABLESPACE_NAME CURR_MB MAX_MB
------------------------------ ---------- ----------
UNDOTBS1 545 32768
SYSAUX 500 32768
USERS 190 32768
SYSTEM 680 32768
EXAMPLE 100 32768
DATA01 30 32768
6 rows selected.
Elapsed: 00:00:00.03
数据库默认永久表空间
select * from database_properties where rownum<4;
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
USER表空间现在属于默认永久表空间,当我们创建了一个用户,而且用户没有指明使用哪个表空间,那么这个用户所创建的所有对象都放到了USERS这个默认永久表空间里面,默认永久表空间是不允许被删除的
drop tablespace data01 including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:00.75
drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
Elapsed: 00:00:00.01
二、UNDO表空间:不保存任何对象,这里面存储ROLLBACK SEGMENT回滚段(为事物提供回退、恢复、读一致性、闪回)
创建UNDO表空间
create undo tablespace undo02 datafile size 10m;
select tablespace_name,contents,status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS1 UNDO ONLINE
TEMP TEMPORARY ONLINE
USERS PERMANENT ONLINE
EXAMPLE PERMANENT ONLINE
UNDO02 UNDO ONLINE
7 rows selected.
Elapsed: 00:00:00.00
刚创建的UNDO表空间是没有用的,因为数据库在同一时刻只能使用一个UNDO表空间,
show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
undo_tablespace参数显示了数据库当前用的UNDO表空间
alter system set undo_tablespace=undo02;
show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO02
三、临时表空间:不能保存永久对象,保存排序的中间结果,临时表的数据
创建临时表空间
create temporary tablespace temp02 tempfile size 10m;
select tablespace_name,contents,status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS1 UNDO ONLINE
TEMP TEMPORARY ONLINE
USERS PERMANENT ONLINE
EXAMPLE PERMANENT ONLINE
UNDO02 UNDO ONLINE
TEMP02 TEMPORARY ONLINE
8 rows selected.
Elapsed: 00:00:00.00