oracle表空间

--创建表空间
CREATE TABLESPACE TBS_CCARE_D01 LOGGING DATAFILE '/dev/rlv_crmdata1' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED blocksize 8192 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
alter tablespace TBS_CCARE_D01 add datafile '/dev/rlv_crmdata2' size 7600M;

select * from dba_data_files;--数据文件
alter database datafile datafile_id resize 1000M;
--temp表空间
alter database tempfile 'lvname' resize 1000M;

alter user scu temporary tablespace temp_migrate;

--关闭自动扩展
alter database datafile '/dev/vx/rdsk/vgora/lv_user' autoextend off

--查看表空间的使用状态
select df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Total Size(MB)",
       sum(fs.bytes) / (1024 * 1024) "Free Size(MB)",
       round(sum(fs.bytes) * 100 / df.bytes) "% 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
 order by df.bytes desc;

 

--查看lv使用情况
select *
  from (select a.file_name, to_number(substr(a.file_name, 17)) t_num
          from dba_data_files a
         where a.file_name like '/dev/rlv_crmdata%') b
 order by b.t_num desc;

--查询temp表空间
select d.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       USED_SPACE "USED_SPACE(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
       NVL(FREE_SPACE, 0) "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 order by "USED_RATE(%)" desc;
 --查询被谁用了
 SELECT su.username,
        se.sid,
        se.serial#,
        se.sql_address,
        se.machine,
        se.program,
        su.tablespace,
        su.segtype,
        su.contents
   FROM v$session se, v$sort_usage su
  WHERE se.saddr = su.session_addr;
--temp表空间
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;
--undo 表空间
select (a.all_bytes - b.busy_bytes) / 1024 / 1024
  from (SELECT SUM(BYTES) all_bytes
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME = 'UNDOTBS3') a,
       (SELECT NVL(SUM(BYTES), 0) busy_bytes
          FROM DBA_UNDO_EXTENTS
         WHERE TABLESPACE_NAME = 'UNDOTBS3'
           AND STATUS IN ('ACTIVE', 'UNEXPIRED')) b;

 

 

--检查哪个占用的最大
select sum(mbytese) from (select segment_name, sum(bytes) / 1024 / 1024 Mbytese
  from user_segments
 where /*segment_type = 'TABLE'
   and*/ tablespace_name = 'TBS_ISAP_D01'
 group by segment_name);

 ----查询表空间包含的对象  上面那个sql查询不到索引信息
SELECT t.owner, t.segment_name, SUM(bytes) / 1024 / 1024 size_MB
  From dba_segments t
 WHERE t.tablespace_name = 'PERFDB_IDX'
 GROUP BY t.owner, t.segment_name
/* having SUM(bytes) >1024*1024*1024*/
 ORDER BY SUM(bytes) desc;

--LOBSEGMENT 类型
select * from user_segments a where a.segment_name='SYS_LOB0000045851C00005$$';

--查询是存储哪个表
select * from user_lobs a where a.segment_name='SYS_LOB0000045851C00005$$';--table:WFE_TBL_CUSTOM_VARIABLES column: MARSHALLED_DATA