修改表空间-阿里云开发者社区

检查自己的DBA身份：select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

1. 查出现在表空间的使用情况

desc dba_data_files;查总量
desc dba_free_space; 查剩余情况

联合查询找出表空间的使用情况：

select  a.TABLESPACE_NAME,
  a.BYTES bytes_used,
  b.BYTES bytes_free,
  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;

查询结果：

TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED
-------------- ----- ---------- ---------- ---------- ------------
BOPSINDXTS                                            537919488    125829120     59768832                76.61
APPDATA1M                                            1.0743E+10 3233808384    629145600                 69.9
PERFSTAT                                                157286400     48431104     48168960                69.21
MEMBER_TS                                            1075838976    356515840    226492416                66.86
BOPSDATATS                                         4833935360 2252341248 2034237440                53.41
PRODUCTTS                                            3.8656E+10 2.1326E+10 2.1305E+10                44.83
APP_DATA1K                                         4568645632 2569011200 2083520512                43.77
USERS                                                     537919488    358612992    239075328                33.33
APPINDX1K                                            6445596672 4364173312 3602907136                32.29
APPINDX1M                                            6177161216 4265607168 3745513472                30.95
SYSTEM                                                 1074790400    838008832    837746688                22.03
MCSHADOWTS                                         1074790400 1070596096    797966336                    .39
UNDOTBS1                                             8593080320 8566145024 4143972352                    .31
TOOLS                                                    1680867328 1677721600 1073741824                    .19
APPINDX4M                                            4833935360 4831838208 4294967296                    .04

15 rows selected.

查出表空间对应的数据文件(dbf)的情况

select file_name, tablespace_name from dba_data_files order by tablespace_name;

查询结果：

FILE_NAME   TABLESPACE_NAME
--------------------------------------------- ------------------------------
/home/oracle/oradata/moree/APPDATA1M01.dbf        APPDATA1M
/home/oracle/oradata/moree/APPDATA1M02.dbf        APPDATA1M
/home/oracle/oradata/moree/APPDATA1M04.dbf        APPDATA1M
/home/oracle/oradata/moree/APPDATA1M05.dbf        APPDATA1M
/home/oracle/oradata/moree/APPDATA1M03.dbf        APPDATA1M
/home/oracle/oradata/moree/APPINDX1K01.dbf        APPINDX1K
/home/oracle/oradata/moree/APPINDX1K02.dbf        APPINDX1K
/home/oracle/oradata/moree/APPINDX1K03.dbf        APPINDX1K
/home/oracle/oradata/moree/APPINDX1M01.dbf        APPINDX1M
/home/oracle/oradata/moree/APPINDX1M03.dbf        APPINDX1M
/home/oracle/oradata/moree/APPINDX1M02.dbf        APPINDX1M
/home/oracle/oradata/moree/APPINDX4M01.dbf        APPINDX4M
/home/oracle/oradata/moree/APPINDX4M02.dbf        APPINDX4M
/home/oracle/oradata/moree/APP_DATA1K01.dbf     APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K02.dbf     APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K05.dbf     APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K04.dbf     APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K03.dbf     APP_DATA1K
/home/oracle/oradata/moree/BOPSDATATS01.dbf     BOPSDATATS
/home/oracle/oradata/moree/bopsdatats02.dbf     BOPSDATATS
/home/oracle/oradata/moree/BOPSDATATS02.dbf     BOPSDATATS
/home/oracle/oradata/moree/BOPSDATATS03.dbf     BOPSDATATS
/home/oracle/oradata/moree/BOPSINDXTS01.dbf     BOPSINDXTS
/home/oracle/oradata/moree/MCSHADOWTS01.dbf     MCSHADOWTS
/home/oracle/oradata/moree/MEMBER_TS01.dbf        MEMBER_TS
/home/oracle/oradata/moree/MEMBER_TS02.dbf        MEMBER_TS
/home/oracle/oradata/moree/perfstat.dbf             PERFSTAT
/home/oracle/oradata/moree/PRODUCTTS01.dbf        PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS06.dbf        PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS05.dbf        PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS02.dbf        PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS03.dbf        PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS04.dbf        PRODUCTTS
/home/oracle/oradata/moree/system01.dbf             SYSTEM
/home/oracle/oradata/moree/tools01.dbf                TOOLS
/home/oracle/oradata/moree/tools02.dbf                TOOLS
/home/oracle/oradata/moree/tools03.dbf                TOOLS
/home/oracle/oradata/moree/undotbs01.dbf            UNDOTBS1
/home/oracle/oradata/moree/undotbs02.dbf            UNDOTBS1
/home/oracle/oradata/moree/undotbs03.dbf            UNDOTBS1
/home/oracle/oradata/moree/users01.dbf                USERS

41 rows selected.

查出产品表的dbf的文件大小：

@>!ls -lh /home/oracle/oradata/moree/PRODUCTTS*

结果为：

-rw-r -----    1 oracle oinstall 513M Jan    3 09:30 /home/oracle/oradata/moree/PRODUCTTS01.dbf
-rw-r -----    1 oracle oinstall 513M Jan    3 09:30 /home/oracle/oradata/moree/PRODUCTTS02.dbf
-rw-r -----    1 oracle oinstall 513M Jan    3 09:30 /home/oracle/oradata/moree/PRODUCTTS03.dbf
-rw-r -----    1 oracle oinstall 513M Jan    3 09:30 /home/oracle/oradata/moree/PRODUCTTS04.dbf
-rw-r -----    1 oracle oinstall 4.1G Jan    3 09:30 /home/oracle/oradata/moree/PRODUCTTS05.dbf
-rw-r -----    1 oracle oinstall    31G Jan    3 09:30 /home/oracle/oradata/moree/PRODUCTTS06.dbf

准确知道每个表空间(dbf)的使用情况：

select
d.username as "用户",
b. file_id as "文件ID号",
b.tablespace_name as "表空间名",
b. file_name as "表空间位置",
b.bytes/1024/1024|| 'M'"字节数",
(b.bytes- sum(nvl(a.bytes,0)))/1024/1024|| 'M' as "已使用",
sum(nvl(a.bytes,0))/1024/1024|| 'M' as "剩余空间",
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 as "占用百分比" ,
c.tablespace_name as "临时表空间名",
c. file_name as "临时表空间位置"
from dba_free_space a,dba_data_files b ,dba_temp_files c,dba_users d
where a. file_id=b. file_id and d.temporary_tablespace = c.tablespace_name and b.tablespace_name = d.default_tablespace
group by d.username, b.tablespace_name,b. file_name,b. file_id,b.bytes ,c.tablespace_name ,c. file_name
order by d.username ;

2. 修改表空间

（1）create database;在创建数据库的时候进行设置

(2) create tablespace

eg: create tablespace user_data datafile '/u01/oradata/userdata01.dbf' size 200M autoextend on[或 off] next 10M maxsize 500M;
create tablespace perfstat datafile '/home/oracle/oradata/moree/perfstat.dbf' size 100M autoextend on next 50M maxsize 1000M;

创建临时表空间

create temporary tablespace moree_temp tempfile '/home/oracle/base/oradata/otter/moree_temp.dbf' size 100M autoextend on next 20 maxsize 500m;

创建用户

CREATE USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE 默认表空间名 temporary
TABLESPACE 临时表空间名

为用户（如scott）授权

grant create session, create user , create table to scott;

(3) alter tablespace ... add datafile

eg: 为表空间增加新的数据文件：

alter tablespace wenchuan add datafile '/u01/oradata/wilson/wenchuan2.dbf' size 10M autoextend on next 10M maxsize 100M;

修改已经存在的表空间：

alter database datafile '/u01/oradata/wilson/wenchuan1.dbf' autoextend on next 10M maxsize 100M;

查大小：

select file_name, tablespace_name, bytes, autoextensible from dba_data_files;

修改大小（但前提是数据还未超过到修改到的量，犹如要缩小箱子的大小需要先检查里面的东西是否超过到要缩小到的量）：

alter database datafile '/u01/oradata/wilson/wenchuan1.dbf' resize 5M;

3. 移动表空间位置

方式一：

（1）必须先将tablespace offline:

alter tablespace wenchuan offline;

（2）再用mv或cp要修改的文件

mv /u01/oradata/wilson/wenchuan1.dbf /u01/oradata/wenchuanX.dbf(移动到上级目录且改为wenchuanX.dbf)

（3） rename

alter tablespace wenchuan rename datafile '/u01/oradata/wilson/wenchuan1.dbf' to '/u01/oradata/wenchuanX.dbf';

（4）最后启动tablespace:

alter table wenchuan online;

=》此时查询： select file_name, tablespace_name, bytes, autoextensible from dba_data_files; 可以发现tablespace已经做了改变。

方式二：

(1) 关闭数据库：

shotdown immediate

（2）移动数据文件：cp 或 mv

（3）启动数据库到mount状态（注意是mount，而不是直接启动）： startup mount

（4）rename

alter database rename file '旧的dbf文件全路径名' to '新路径dbf全名'

(5) 打开数据库： alter database open;

=》此时查询： select file_name, tablespace_name, bytes, autoextensible from dba_data_files; 可以发现tablespace已经做了改变。

4. Drop tablespace

下列两种tablespace是不能drop的，一是system tablespace, 二是有active segment的表空间

Drop表空间语法：

drop tablespace wenchuan including contents and datafiles; //数据和文件都被拿掉了，硬盘空间上有改变。

有时候会遇到一个问题，表空间文件删除之后，操作系统的的硬盘空间却没有空余的多出来，这是操作系统的一个问题。此时可以通过重启操作系统或者重启数据库来完成。（本人在以下环境发现此问题：

操作系统：Linux b2b_plat_13619 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:56:44 EST 2007 x86_64 x86_64 x86_64 GNU/Linux，Oracle：）

删除表空间(不包括对应的数据文件)
drop tablespace users including contents;

删除表空间(包括对应的数据文件)
drop tablespace users including contents and datafiles;

5. 查看修改

desc dba_data_files;
col file_name format a20;
select file_name, tablespace_name, bytes, autoextensible from dba_data_files;

-------------------------------------------------------------------------------------------------------------------------------------------------

数据文件丢失的处理办法：

描述：错误的删掉了一个数据文件，导致数据库在重启的时候出现问题，报错为数据文件无法找到。

ERROR at line 1:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/home/oracle/oradata/moree/users01.dbf'

本文转自 tianya23 51CTO博客，原文链接：http://blog.51cto.com/tianya23/249649，如需转载请自行联系原作者

修改表空间

方式一：

方式二：

热门文章

最新文章

相关电子书