表空间查询和管理
红色是*指定的~~
--查询表空间
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)",
MAX_SPACE,
ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) "USED_RATE_MAX_SIZE(%)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
ROUND(SUM(DECODE(SIGN(MAXBYTES - BYTES), 1, MAXBYTES, BYTES)) / 1024 / 1024,
2) MAX_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)",
MAX_SPACE,
ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) "USED_RATE_MAX_SIZE(%)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
ROUND(SUM(DECODE(SIGN(MAXBYTES - BYTES), 1, MAXBYTES, BYTES)) / 1024 / 1024,
2) MAX_SPACE
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
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(+)
------------------------------------------
--查看表空间属性--
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME and T.TABLESPACE_NAME='DATA'
ORDER BY TABLESPACE_NAME,FILE_NAME;
--添加数据文件
ALTER TABLESPACE DATA ADD DATAFILE '/data/data01.dbf' SIZE 8192M;
--修改数据文件大小（尽量避免改小，貌似有HWM的问题，要修改的话，貌似先整理表空间碎片？）
ALTER DATABASE DATAFILE '/data/data_01.dbf' RESIZE 8192M;
--为了防止把硬盘撑爆，关闭自动拓展比较好。
ALTER DATABASE DATAFILE '/data/data_01.dbf' AUTOEXTEND OFF;
-------------------------------------
----释放磁盘空间-----
--首先用语句查询容量大于1G的数据段
select segment_name,sum(bytes)/1024/1024/1024 as GB from dba_segments group by segment_name having sum(bytes)/1024/1024/1024>1
/*得到如下结果：
SYS_LOB0000136091C00003$$ 255332M
SYS_LOB0000136441C00004$$ 7170M
SYS_C0082042 1305M
SYS_C0080433 1340M*/
--根据LOB段查询该该lob段属于哪个表
select table_name,segment_name from dba_lobs where segment_name='SYS_LOB0000136091C00003$$'
/*经查得知是ADU_*表占了很多容量
3.用Dbvisulizer/plsql连到数据库，删除重复的数据行，但删除这些重复的数据后，并不会释放出磁盘空间
4.然后释放lob类型数据占据的空间*/
alter table adu_* move tablespace BFPICK lob(content) store as (tablespace bfpick)
--之后就释放了重复数据所占的空间了，然后在对该表重建索引
alter index ***** rebuild;
----------------------------------------
--查询进程数/操作用户--
select count(*) from v$process
select value from v$parameter where name = 'processes'
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc
-----------------------------------------
--清理分区表
ALTER TABLE table_1 truncate PARTITION PART_201303;
ALTER TABLE table_1 DROP PARTITION PART_201303;
ALTER TABLE table_1 truncate PARTITION PART_201304;
ALTER TABLE table_1 DROP PARTITION PART_201304;