AI 助理
备案控制台
开发者社区 数据库 文章 正文

修改表空间

2017-11-27 977
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
检查自己的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，如需转载请自行联系原作者
文章标签：
关系型数据库
Oracle
数据库
数据库管理
Linux
otter
技术小阿哥
目录
相关文章
ziheng
|
SQL Oracle 关系型数据库
Oracle 修改表空间数据文件存储位置
一、方法一 1、以表空间ZZH为例，offline 表空间ZZH SQL> alter tablespace ZZH offline; 2、复制数据文件到新的目录 cp /old_path/ZZH01.
ziheng
3046 0
小蓝枣
|
存储 Oracle 关系型数据库
Oracle 数据库表空间不足拓展方法实例演示，表空间剩余大小查看，通过新增表空间文件拓展表空间，表空间文件路径查看
Oracle 数据库表空间不足拓展方法实例演示，表空间剩余大小查看，通过新增表空间文件拓展表空间，表空间文件路径查看
小蓝枣
274 0
Oracle 数据库表空间不足拓展方法实例演示，表空间剩余大小查看，通过新增表空间文件拓展表空间，表空间文件路径查看
技术小胖子
查看表空间、表空间文件路径
技术小胖子
1398 0
技术小甜
|
数据库 数据库管理 SQL
表空间与数据文件管理
技术小甜
938 0
余二五
|
Oracle 关系型数据库
管理表空间和数据文件
余二五
897 0
科技小先锋
|
SQL Oracle 关系型数据库
如何查看Oracle数据库表空间大小(空闲、已使用),是否要增加表空间的数据文件
科技小先锋
7794 0
余二五
|
Oracle 关系型数据库 数据库
UNDO 表空间管理
余二五
1072 0
余二五
|
SQL
12c datagurad 创建临时表空间遇到的问题
余二五
1189 0
科技小先锋
|
Oracle 关系型数据库 测试技术
警惕数据IMP导入的表空间是EXP同名的表空间，而不是用户默认的表空间
科技小先锋
1220 0
科技小能手
表空间问题----收缩空间方法：表空间中表的查法：扩展表空间
科技小能手
1253 0

热门文章

最新文章

  • 1
    docker容器里安装ssh
  • 2
    优秀的代码都是如何分层的？
  • 3
    RDS入门——Excel文件转存到RDS数据库实践
  • 4
    3、Python与设计模式--建造者模式
  • 5
    PostgreSQL SQL HINT的使用
  • 6
    跟阿里云技术专家阙寒一起深度了解视频直播CDN技术
  • 7
    AjaxFileUploader上传插件 兼容性好
  • 8
    （转载） 数组a[]={3,5,2,4,1,8}，要求从a中找出所有“和”等于10的子集
  • 9
    SAP WM Movement Type Mail Control for Background processing
  • 10
    Ext 4 概述（七）之面板Panel & 杂项
  • 1
    自学记录HarmonyOS Next DRM API 13：构建安全的数字内容保护系统
    44
  • 2
    《MaxFrame：数据处理的卓越实践与提升》
    60
  • 3
    《DataWorks：数据处理的卓越实践与改进思考》
    52
  • 4
    三维球体空间中光线反射模拟与三维点云提取matlab仿真
    52
  • 5
    《多模态数据信息提取解决方案的体验与部署》
    49
  • 6
    基于PSO优化的PV光伏发电系统simulink建模与仿真
    58
  • 7
    数据链中常见电磁干扰matlab仿真,对比噪声调频,线性调频,噪声,扫频,灵巧五种干扰模型
    53
  • 8
    《破局之路：人工智能产业的健康发展》
    41
  • 9
    《强化学习算法在动态环境中的优化之路》
    45
  • 10
    《大模型训练成本高，如何在不牺牲性能的前提下破局》
    45

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • PolarDB MySQL从表回收站中恢复误删的表
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)