oracle 表空间管理

1、涉及到表空间的数据字典

 v$tablespace  dba_data_files

2、创建数据表空间

create tablespace user_data 
logging 
datafile '/home/oracle/app/oradata/orcl/user_data.dbf' 
size 50m 
autoextend on 
next 50m maxsize 2048m 
extent management local; 

3、创建临时表空间

create temporary tablespace user_temp 
tempfile '/home/oracle/app/oradata/orcl/user_temp.dbf' 
size 50m 
autoextend on 
next 50m maxsize 2048m 
extent management local; 

临时表空间存放的是临时数据或者排序等数据

4、创建undo表空间

create undo tablespace undotbs2 
datafile '/home/oracle/app/oradata/orcl/undotbs02.dbf' 
size 10m 
autoextend on next 100m;

5、查看表空间数据文件的位置

SQL> col tablespace_name for a10;
SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE FILE_NAME
---------- ----------------------------------------
USERS	   /home/oracle/app/oradata/orcl/users01.db
	   f

UNDOTBS1   /home/oracle/app/oradata/orcl/undotbs01.
	   dbf

SYSAUX	   /home/oracle/app/oradata/orcl/sysaux01.d
	   bf

SYSTEM	   /home/oracle/app/oradata/orcl/system01.d
	   bf

TABLESPACE FILE_NAME
---------- ----------------------------------------

USER_DATA  /home/oracle/app/oradata/orcl/user_data.
	

6、表空间扩容

 1）增大数据文件

alter database 
datafile '/home/oracle/app/oradata/orcl/user_data.dbf' 
RESIZE 1900M; 

如果创建表空间的时候没有加说明bigfile，那么最大可增加到32g

2）增加数据文件个数

alter tablespace user_data
 add datafile '/home/oracle/app/oradata/orcl/user_data02.dbf'
 size 100m

同样道理，如果创建表空间的时候说明是bigfile，那么不可以增加数据文件，这种方式不可使用

7、查看表空间的使用率

    SELECT /* + RULE */ df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024) "Size (MB)",
    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs,
    (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,df.bytes
    UNION ALL
    SELECT /* + RULE */ df.tablespace_name tspace,
    fs.bytes / (1024 * 1024),
    SUM(df.bytes_free) / (1024 * 1024),
    Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
    Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
    FROM dba_temp_files fs,
    (SELECT tablespace_name,bytes_free,bytes_used
    FROM v$temp_space_header
    GROUP BY tablespace_name,bytes_free,bytes_used) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
    ORDER BY 4 DESC;

8、表空间数据文件的迁移

1）先脱机

alter tablespace TEMPPMS offline;

2）移动数据文件

alter tablespace temppms rename datafile
 '/home/oracle/app/oradata/wtdb/gsdbdatafile.dbf' to
 '/home/oracle/app/oradata/wtdb/temp/gsdbdatafile.dbf';

3、使表空间在线

 alter tablespace TEMPPMS  online;