管理表空间

oracle表空间管理总结，以后表空间问题有了这个材料基本上都可以解决，请保留

 

1、查看每个表空间剩余空间

select TABLESPACE_NAME, sum(bytes)/1024/1024,sum(blocks) from dba_free_space group by tablespace_name

 

 

2、查看每个表空间总的尺寸

select tablespace_name, sum(bytes) / 1048576 from dba_data_files group by tablespace_name

 

 

3、创建表空间

create tablespace "TBSLBIDW"

    nologging

    datafile '/lbiapp/dbs/TBSLBIDW.ora' size 1000M  reuse autoextend on next 100m maxsize 5000M

    extent management local segment space management  auto;

 

reuse表示如果该文件已经存在，则清除该文件并重新创建该文件

extent management local表示所创建的表空间为本地化管理表空间

segment space management atuo表示Oracle将使用位图来管理以用数据块和空闲数据块

 

 

创建临时表空间

create     temporary tablespace TBS_TEMP_01 tempfile

'/home/oracle/archive/lbidb/TBS_TEMP_01.ora' size 1000m reuse autoextend on next 10m maxsize 2000M;

 

 

注意：如果是裸设备，建立表空间的时候autoextend必须off，建立在文件系统上时可打开，可关闭

logging是只指表空间插入数据的时候必须将改变记录在ONLINE REDO LOG 里，这是系统默认的

 

-----创建表空间(裸设备)----临时表空间的方式一致

--使用root用户登录服务器，进入目录/dev/vx/rdsk/vgora/

--使用命令 ls 查看数据文件名称 lbi 的数据文件一般为 lbi后面加序号

 

4.表空间加文件

给表空间添加数据文件

alter tablespace service_core_dat add datafile

  '/dev/vgdata/rlv_dat010' size 4000M,

  '/dev/vgdata/rlv_dat011' size 4000M

 

临时表空间增加文件

alter tablespace TBS_TEMP_01 add tempfile

  '/ora/oracle/oradata/LBIDBS/TBS_TEMP_02.ora' size 100M;

 

5.修改表空间大小

ALTER DATABASE DATAFILE '/tellin/oracle/db/oradata/LBIDBS/TBS_KR_01.ora' RESIZE  500M

修改临时表空间大小

ALTER DATABASE tempfile  '/home/oracle/archive/lbidb/TBS_TEMP_01.ora ' RESIZE  5000M:

 

 

6.删除表空间及文件

 drop tablespace 表空间名 including contents and datafiles;

 

--删除表空间内指定的文件 必须为online

alter tablespace TBS_DW_SMS drop datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf';

alter tablespace users drop datafile '/opt/oracle/oradata/eygle/users02.dbf';

 

 --查看表空间

select * from dba_data_files where tablespace_name = 'TBS_DW_SMS'

 

--修改文件为offline

ALTER DATABASE datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf' offline drop;

 

--恢复文件 如果修改为online报错，需要及时恢复文件按

recover datafile  24;--不可以使用recover datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf';

 

--修改文件为online

ALTER DATABASE datafile '/opt/oracle/oradata/tbs_dw_sms_02.dbf' online;

 

--修改表空间的状态

alter tablespace TBS_DW_SMS online;

alter tablespace TBS_DW_SMS offline immediate;

 

--查看file id

select file#,status,name from v$datafile;

 

--将数据文件设置成自动扩展：alter database datafile '/oracle/app/oradata/ora11g/tbs_dw_maxis.dbf' autoextend on next 1M maxsize 10M;

取消自动扩展：alter database datafile '/oracle/app/oradata/ora11g/tbs_dw_maxis.dbf' autoextend off;

--移动一个表空间的数据文件：alter tablespace tas_dw_maxis offline;host move /oracle/app/oradata/ora11g/tbs_dw_maxis.dbf /oracle/tbs_dw_maxis.dbf;

                         alter tablespace tbs_dw_maxis rename datafile '/oracle/app/oradata/ora11g/tbs_dw_maxis.dbf'to '/oracle/tbs_dw_maxis.dbf';alter tablespace online;

 

--查询表空间中数据文件的位置，名称，大小，是否可以自动扩展，能够扩展到的最大大小：select tablespace_name,file_name,blocks,bytes,autoextensible, maxbyte from dba_data_files;

--查询表空间的空闲空间：select tablespace_name, sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

 

 

--查看用户表、索引、分区表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytes from user_segments group by segment_name;

1、表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;

2、索引占用空间

select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;

3、分区表TABLE PARTITION占用空间

select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;

 

--查看当前临时表使用空间大小与正在占用临时表空间的sql语句

select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess,v$sql sql
WHERE sort.SESSION_ADDR = sess.SADDR
AND sql.ADDRESS = sess.SQL_ADDRESS
ORDER by blocks desc;

select'the ' || name || ' temp tablespaces ' || tablespace_name ||
       ' idle ' ||
       round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
       '% at ' || to_char(sysdate, 'yyyymmddhh24miss')
from (select d.tablespace_name tablespace_name,
               nvl(sum(used_blocks), 0) tot_used_blocks,
               sum(blocks) total_blocks
          from v$sort_segment v, dba_temp_files d
         where d.tablespace_name = v.tablespace_name(+)
         GROUP by d.tablespace_name) s,
       v$database;

 

 

--查询在sort排序区使用的执行耗时的SQL

 

SELECT SE.USERNAME，SE.SID，SU.EXTENTS，SU.BLOCKS *
       TO_NUMBER（RTRIM（P.VALUE））AS SPACE，TABLESPACE，SEGTYPE，SQL_TEXT
  FROM V$SORT_USAGE SU，V$PARAMETER P，V$SESSION SE，V$SQL S
 WHERE P.NAME = 'db_block_size'
   AND SU.SESSION_ADDR = SE.SADDR
   AND S.HASH_VALUE = SU.SQLHASH
   AND S.ADDRESS = SU.SQLADDR
 ORDER BY SE.USERNAME，SE.SID

 

--查看表空间的大小：

SELECT a.tablespace_name as "表空间名称",
    to_char(b.total/1024/1024,999999.99)||'M'  as "总容量",
    to_char((b.total-a.free)/1024/1024,'9999990D99')||'M'  as "已使用容量",
    to_char(a.free/1024/1024,'9999990D99')||'M' as "剩余容量",
    to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as "已使用百分比"
   FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
    (SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
  WHERE a.tablespace_name=b.tablespace_name
  ORDER BY round((total-free)/total,4) DESC;

 

--查看某个表空间大小：

select aa.tablespace_name,

       trunc(aa.total_space / 1024 / 1024) || ' M' as total_space,

       trunc(bb.free_space / 1024 / 1024) || ' M' as free_space,

       trunc((1 - (bb.free_space / aa.total_space)) * 100, 2) as used_percent,

       trunc((aa.total_space - bb.free_space) / 1024 / 1024) || ' M' as used_space,

       sysdate as check_time,

       (select name from v$database) as db_name

  from (select a.tablespace_name, sum(a.bytes) as total_space

          from dba_data_files a

         where a.tablespace_name like upper('&tablespace_name') || '%'

         group by a.tablespace_name) aa,

       (select b.tablespace_name, sum(b.bytes) as free_space

          from dba_free_space b

         where b.tablespace_name like upper('&tablespace_name') || '%'

         group by b.tablespace_name) bb

 where aa.tablespace_name = bb.tablespace_name(+)

 order by used_percent desc;