先看一下关于表空间的数据字典与动态性能试图即:
dba_tablespaces
v$tablespace
- CREATE
TABLESPACE data01
-
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
-
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k
- create
tablespace data01
- datafile
'/oracle/oradata/db/DATA01.dbf' size 500m
- autoextend
on
- next 32m maxsize 2048m
- extent management local;
创建用户并指定表空间
- create
user username identified by password
- default
tablespace data01
- temporary
tablespace temp;(这行临时表空间不加也可以,默认就是temp)
给用户赋予权限
- grant
connect,resource to username;
以后以该用户登录,创建的任何数据库对象都属于temp 和data01表空间,这就不用在每创建一个对象给其指定表空间了
- DROP
TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
SQL> col file_name format a30
SQL> col tablespace_name format a30
SQL> col tablespace_name format a15
SQL> select file_name,tablespace_name,bytes from dba_data_files;
查看表空间文件大小。
查看临时表空间在dba_temp_files里查看。
移动表空间首先先将表空间offline(文件大小不显示了利用select语句查看时)然后利用mv或者copy命令将数据文件移动到新的地方,利用alter命令后,然后再将表空间online.
- alter
database datafile '/oracle/oradata/db/DATA01.db' resize 100M;
建立UNDO表空间
- CREATE
UNDO TABLESPACE UNDOTBS02
- DATAFILE
'/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
#注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:
- ALTER
SYSTEM SET undo_tablespace=UNDOTBS02;
建立临时表空间
- CREATE
TEMPORARY TABLESPACE temp_data
- TEMPFILE
'/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
改变表空间状态
1.使表空间脱机
- ALTER
TABLESPACE game OFFLINE;
- ALTER
TABLESPACE game OFFLINE FOR RECOVER;
2.使表空间联机
- ALTER
TABLESPACE game ONLINE;
- ALTER
DATABASE DATAFILE 3 OFFLINE;
4.使数据文件联机
- ALTER
DATABASE DATAFILE 3 ONLINE;
- ALTER
TABLESPACE game READ ONLY;
- ALTER
TABLESPACE game READ WRITE;
扩展表空间
首先查看表空间的名字和所属文件
- select
tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space - from
dba_data_files order by tablespace_name;
- ALTER
TABLESPACE game
- ADD
DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;
- ALTER
DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;
3.设定数据文件自动扩展
- ALTER
DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
-
AUTOEXTEND ON NEXT 100M
-
MAXSIZE 10000M;
设定后查看表空间信息
- SELECT
A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
-
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
-
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
-
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
----------------------------------------------------------------------------
也可以这样;
- select
a.tablespace_name, a.bytes bytes_used, b.largest, round(((a.bytes - b.bytes)/a.bytes)*100,2) percent_used
- from
- (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
- (select
tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by tablespace_name) b
- where
a.tablespace_name = b.tablespace_name
- order
by ((a.bytes - b.bytes) / a.bytes) desc