【总结】Oracle数据库查看表空间和增加表空间
⼀、Oracle查看表空间的名称及其⼤⼩
查看表空间的名称及其⼤⼩的SQL语句：
select t1.tablespace_name,round(sum(bytes/(1024*1024)),0) tablespace_Size_MB
from dba_tablespaces t1, dba_data_files t2
where t1.tablespace_name = t2.tablespace_name
group by t1.tablespace_name;
查询结果：
TABLESPACE_NAME                TABLESPACE_SIZE_MB
------------------------------ ------------------
DLOTTEY                                    276480
SYSAUX                                      48450
UNDOTBS1                                    20000
INDEXMT                                    10240
USERS                                        1041
SYSTEM                                      10240
UNDOTBS2                                    20000
MMLOTTERY                                  215040
8 rows selected.
⼆、Oracle查看表空间的具体使⽤情况
⽅法1：（未排序）
select a.tablespace_name "tablespace_name",
totalspace "totalspaceM",
freespace "freespaceM",
round((1-freespace/totalspace)*100,2) "round%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) a,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
查询结果：
tablespace_name          totalspaceM freespaceM    round%
------------------------ ----------- ---------- ----------
DLOTTEY                      276480    232415      15.94
SYSAUX                        48450      9683      80.01
UNDOTBS1                      20000      19741        1.3
INDEXMT                        10240      10024      2.11
USERS                          1041        138      86.74
SYSTEM                        10240      4344      57.58
UNDOTBS2                      20000      19601          2
MMLOTTERY                    215040      36279      83.13
8 rows selected.
⽅法⼆：（查询结果排序） 
select a.tablespace_name,
a.bytes/1024/1024 "Sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",
b.bytes/1024/1024 "free MB",
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            Sum MB    used MB    free MB percent_used
---------------------- ---------- ---------- ---------- ------------
USERS                    1041.25    903.375    137.875        86.76
MMLOTTERY                  215040    178761      36279        83.13
SYSAUX                      48450      38767      9683        80.01
SYSTEM                      10240  5896.125  4343.875        57.58
DLOTTEY                    276480 44065.4375 232414.563        15.94
INDEXMT                    10240    215.625  10024.375        2.11
UNDOTBS2                    20000  399.3125 19600.6875            2
UNDOTBS1                    20000      257.5    19742.5        1.29
8 rows selected.
三、Oracle查看表空间物理⽂件的名称及⼤⼩set lines 150;
col tablespace_name for a20;
col file_name for a60;
1、查询所有的表空间
SQL语句：
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space_MB
from dba_data_files
order by tablespace_name;
2、查询指定的表空间
SQL语句：
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space_MB
from dba_data_files
where tablespace_name = 'MMLOTTERY'
order by tablespace_name;
查询结果：
TABLESPACE_NAME        FILE_ID FILE_NAME                                  TOTAL_SPACE_MB
------------------- ---------- ------------------------------------------- --------------
MMLOTTERY                  18 +DATA/ora11g/datafile/mmlottery01.dbf                30720
MMLOTTERY                  19 +DATA/ora11g/datafile/mmlottery02.dbf                30720
MMLOTTERY                  20 +DATA/ora11g/datafile/mmlottery03.dbf                30720
MMLOTTERY                  22 +DATA/ora11g/datafile/mmlottery04.dbf                30720
oracle数据库怎么查询表
MMLOTTERY                  23 +DATA/ora11g/datafile/mmlottery05.dbf                30720
MMLOTTERY                  26 +DATA/ora11g/datafile/mmlottery06.dbf                30720
MMLOTTERY                  27 +DATA/ora11g/datafile/mmlottery07.dbf                30720
7 rows selected.
四、Oracle查看表真实占⽤的空间
SQL语句：
select t.owner,t.segment_name,t.segment_type,
sum(t.bytes/1024/1024) used_MB
from dba_segments t
where owner = 'MMLOTTERY'
group by owner,segment_name,segment_type
order by used_MB desc;
查询结果：
OWNER              SEGMENT_NAME                    SEGMENT_TYPE          USED_MB
------------------- -------------------------------- ------------------ ----------
MMLOTTERY          TB_ORIGINAL_ORDERDETAILS        TABLE                    1792
MMLOTTERY          TB_LOTTERY_SALEDETAIL            TABLE                    1472
MMLOTTERY          TB_LOTTERYSCHEMEINFO_ADD        TABLE                    1280
MMLOTTERY          TEST_007                        TABLE                    1152
MMLOTTERY          TB_ACCOUNT_OPERATE_DETAIL        TABLE                    808
MMLOTTERY          PK14                            INDEX                    377
MMLOTTERY          PK14_11                          INDEX                    312
MMLOTTERY          PK14_13                          INDEX                    200
MMLOTTERY          PK14_12                          INDEX                    160
MMLOTTERY          TB_BONUS_ORDERDETAILS            TABLE                    160
MMLOTTERY          TB_WINBONUS_DETAIL              TABLE                    144
11 rows selected.
五、Oracle 增加表空间
语法：
alter tablespace {表空间名字} add datafile '物理数据⽂件路径' SIZE 『初始⼤⼩M』 AUTOEXTEND ON NEXT 『⾃动扩展⼤⼩M』例⼦：
alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery08.dbf' size 30720m autoextend on next 200m;
注意：如果添加表空间的⽂件名重复，那么会报错，如下：
SQL> alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery08.dbf' size 30720m autoextend on next 200m;
alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery08.dbf' size 30720m autoextend on next 200m
*
ERROR at line 1:
ORA-01537: cannot add file'+DATA/ora11g/datafile/mmlottery08.dbf' - file already part of database
若 datafile 加错到表空间，则执⾏删除操作。
alter tablespace MMLOTTERY drop datafile '+DATA/ora11g/datafile/mmlottery08.dbf';
或者
alter database datafile '+DATA/ora11g/datafile/mmlottery08.dbf' offline drop;
六、Oracle 临时表空间的查看与增加
查看临时表空间
SQL语句：
select tablespace_name,file_id,file_name,round(bytes/1024/1024,0) temp_size_MB from dba_temp_files;
查询结果：
TABLESPACE_NAME      FILE_ID FILE_NAME                                    TEMP_SIZE_MB
----------------- ---------- --------------------------------------------- ------------
TEMP                      1 +DATA/ora11g/tempfile/temp.262.917714589            20000
增加临时表空间⽂件
SQL语句：
alter tablespace TEMP add tempfile '+DATA/ora11g/tempfile/temp01.dbf' size 20000m autoextend on next 2000m;
七、查看表空间是否为⾃增
表空间是有数据⽂件组成的，所以看表空间是否⾃增即看数据⽂件，如下查⾃增的表空间：　
select tablespace_name,file_name,autoextensible from dba_data_files where autoextensible='YES';
autoextensible: YES/NO 即表⽰是否⾃增。
⽰例：
SQL> select tablespace_name,file_name,autoextensible from dba_data_files where TABLESPACE_NAME='USERS';
TABLESPACE_NAME      FILE_NAME                                                    AUT
-------------------- ------------------------------------------------------------ ---
USERS                +DATA/ora11g/datafile/users.264.917714693                    YES
【实例】Oracle 表空间的查看与增加
步骤 1、查看表空间的具体使⽤情况
SQL语句，如下：
select a.tablespace_name,
a.bytes/1024/1024 "Sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",
b.bytes/1024/1024 "free MB",
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            Sum MB    used MB    free MB percent_used
---------------------- ---------- ---------- ---------- ------------
USERS                    1041.25    903.375    137.875        86.76
MMLOTTERY                  215040    178761      36279        83.13
SYSAUX                      48450      38767      9683        80.01
SYSTEM                      10240  5896.125  4343.875        57.58
DLOTTEY                    276480 44065.4375 232414.563        15.94
INDEXMT                    10240    215.625  10024.375        2.11
UNDOTBS2                    20000  399.3125 19600.6875            2
UNDOTBS1                    20000      257.5    19742.5        1.29
8 rows selected.
步骤2、查看指定表空间（MMLOTTERY）的物理⽂件名称及其⼤⼩set lines 150;
col tablespace_name for a20;
col file_name for a60;
SQL语句：
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space_MB
from dba_data_files
where tablespace_name = 'MMLOTTERY'
order by tablespace_name;
查询结果：
TABLESPACE_NAME        FILE_ID FILE_NAME                                  TOTAL_SPACE_MB
------------------- ---------- ------------------------------------------- --------------
MMLOTTERY                  18 +DATA/ora11g/datafile/mmlottery01.dbf                30720
MMLOTTERY                  19 +DATA/ora11g/datafile/mmlottery02.dbf                30720
MMLOTTERY                  20 +DATA/ora11g/datafile/mmlottery03.dbf                30720
MMLOTTERY                  22 +DATA/ora11g/datafile/mmlottery04.dbf                30720
MMLOTTERY                  23 +DATA/ora11g/datafile/mmlottery05.dbf                30720
MMLOTTERY                  26 +DATA/ora11g/datafile/mmlottery06.dbf                30720
MMLOTTERY                  27 +DATA/ora11g/datafile/mmlottery07.dbf                30720
7 rows selected.
步骤3、增加指定表空间（MMLOTTERY）的⼤⼩
增加指定表空间的⼤⼩，SQL语句：
SQL> alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery.dbf' size 30720m;
Tablespace altered.
根据步骤2的命令，查询增加后的结果：
TABLESPACE_NAME        FILE_ID FILE_NAME                                                    TOTAL_SPACE_MB
-------------------- ---------- ------------------------------------------------------------ --------------
MMLOTTERY                      18 +DATA/ora11g/datafile/mmlottery01.dbf                                  30720
MMLOTTERY                      19 +DATA/ora11g/datafile/mmlottery02.dbf                                  30720
MMLOTTERY                      20 +DATA/ora11g/datafile/mmlottery03.dbf                                  30720
MMLOTTERY                      22 +DATA/ora11g/datafile/mmlottery04.dbf                                  30720
MMLOTTERY                      23 +DATA/ora11g/datafile/mmlottery05.dbf                                  30720
MMLOTTERY                      26 +DATA/ora11g/datafile/mmlottery06.dbf                                  30720
MMLOTTERY                      27 +DATA/ora11g/datafile/mmlottery07.dbf                                  30720
MMLOTTERY                      28 +DATA/ora11g/datafile/mmlottery08.dbf                                  30720
8 rows selected.
步骤4、查看增加表空间后的具体情况
SQL语句：
select a.tablespace_name,
a.bytes/1024/1024 "Sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",
b.bytes/1024/1024 "free MB",
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
and a.tablespace_name='MMLOTTERY';
查询结果：
TABLESPACE_NAME          Sum MB    used MB    free MB percent_used
-------------------- ---------- ---------- ---------- ------------
MMLOTTERY                245760    179218      66542        72.92
【备注】
Oracle只查询⼀条记录的语句：
select * from tablename where rownum = 1;
修改⽤户默认表空间
alter user user_name default tablespace dev_tb;
alter user user_name temporary tablespace dev_temp;
查看数据⽂件是否有数据：
只需查看数据⽂件中是否包含extent段。如果有extent（索引段，数据段）段，则说明数据⽂件中有数据。
使⽤dba_extents视图和dba_data_files视图进⾏连接查询。
select t.file_name,t1.owner,t1.segment_name,t1.segment_type,t1.tablespace_name from dba_data_files t,dba_extents t1 where t.file_id=t1.file_id and file_name='你要查询的数据⽂件路径'; 

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。