ORACLE常用操作表空间的SQL
【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,
(selecttablespace_name,file_name, sum(bytes) bytes fromdba_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,
(selecttablespace_name, sum(bytes) bytes fromdba_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; Thank you very much for sharing!The good man!The good life of peace!
页:
[1]
