ORACLE常用操作表空间的SQL

qmf

【ORACLE】常用操作表空间的SQL。
创建MCIP使用的表空间。
CREATE TABLESPACE MCIPTBS DATAFILE '/dev/raw/rlvora_mciptbs' SIZE 4000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

删除表空间：
drop tablespace MCIPTBS including contents and datafiles;

查询TEMP表空间的使用情况
set lin 200
set wrap off;
col segtype format a10;
col MB format 99;
col sql_text format a100;
select sess.SID, segtype, blocks * 8 / 1024 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sort.SQLADDR(+)
order by blocks desc


查询每个表空间的大小，已经使用大小和剩余大小。
set lin 200;
set pagesize 200;
COL TABLESPACE_NAME FORMAT A20;
COL FILE_NAME FORMAT A50;
SELECT
    df.tablespace_name "TABLESPACE_NAME",
    df.file_name "FILE_NAME",
    ROUND(df.bytes/(1024*1024)) "Total(MB)",
    ROUND(sum(fs.bytes)/(1024*1024)) "Free(MB)",
    ROUND((df.bytes-sum(fs.bytes))*100/df.bytes) "% Used"
FROM
    dba_free_space fs,
    (select  tablespace_name,file_name, sum(bytes) bytes from  dba_data_files group by tablespace_name,file_name ) df
WHERE
    fs.tablespace_name = df.tablespace_name
GROUP BY
    df.tablespace_name,
    df.file_name,
    df.bytes;


简化版查询每个表空间的大小，已经使用大小和剩余大小。
SELECT
    df.tablespace_name "Tablespace",
    ROUND(df.bytes/(1024*1024)) "Total(MB)",
    ROUND(sum(fs.bytes)/(1024*1024)) "Free(MB)",
    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;


修改数据库表空间大小
ALTER DATABASE DATAFILE '/dev/raw/rlvora_system' RESIZE 2000M;
ALTER DATABASE DATAFILE '/dev/raw/rlvora_sysaux' RESIZE 2000M;
ALTER DATABASE DATAFILE '/dev/raw/rlvora_users' RESIZE 1000M;
ALTER DATABASE DATAFILE '/dev/raw/rlvora_undotbs' RESIZE 2000M;

ALTER DATABASE DATAFILE '/dev/raw/rlvora_system' RESIZE 2000M;

alter database datafile '/dev/raw/rlv_ORAC_TBS_CAT_DEF' online;
alter database datafile '/dev/raw/rlv_ORAC_TBS_CAT_DEF' offline drop;
drop tablespace TBS_CAT_DEF including contents cascade constraints;

fengerapple

Thank you very much for sharing!The good man!The good life of peace!