oracle增加表空间大小
第一步:查看表空间的名字及文件所在位置
SELECT TABLESPACE_NAME as "表空间",
FILE_ID as "表空间ID",
FILE_NAME as "文件",
ROUND(BYTES / (1024 * 1024), 0) || 'M' as "大小"
FROM SYS.DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
第二步:查询表空间使用情况
SELECT TABLESPACE_NAME "表空间",
To_char(Round(BYTES / 1024, 2), '99990.00')
|| '' "实有",
To_char(Round(FREE / 1024, 2), '99990.00')
|| 'G' "现有",
To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
|| 'G' "使用",
To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
|| '%' "比例"
FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
Floor(A.BYTES / ( 1024 * 1024 )) BYTES,
Floor(B.FREE / ( 1024 * 1024 )) FREE,
Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'name%'
ORDER BY Floor(10000 * USED / BYTES) DESC;
第三步:增大所需表空间大小
方法一:
alter database datafile '表空间位置’resize 新的尺寸
alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m
方法二:增加数据文件个数
alter tablespace 表空间名称add datafile ‘新的数据文件地址’ size 数据文件大小
alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m
方法三:设置表空间自动扩展。
alter database datafile ‘数据文件位置’ autoextend on next 自动扩展大小maxsize 最大扩展大小
alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m
附
某个表大小查询
第一种方式
SELECT SEGMENT_NAME TABLE_NAME
,SUM(BLOCKS) BLOCKS
,SUM(BYTES)/(1024*1024) "TABLE_SIZE[MB]"
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'
AND SEGMENT_NAME='你要查询的表名'
GROUP BY SEGMENT_NAME;
第二种方式
SELECT SEGMENT_NAME TABLE_NAME
,SUM(BLOCKS) BLOCKS
,SUM(BYTES)/(1024*1024) "TABLE_SIZE[MB]"
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'
AND SEGMENT_NAME='你要查询的表名'
GROUP BY SEGMENT_NAME;
第三种方式
ANALYZE TABLE 用户名.表名 COMPUTE STATISTICS; --收集表对象的统计信息
SELECT OWNER OWNER_NAME
,TABLE_NAME TABLE_NAME
,TABLESPACE_NAME TABLESPACE_NAME
,BLOCKS ACTUAL_BLOCKS
,BLOCKS + EMPTY_BLOCKS TOTAL_BLOCKS
,(BLOCKS + EMPTY_BLOCKS) * 8192/(1024*1024)
"TABLE_SIZE[MB]"
FROM DBA_TABLES
WHERE OWNER='用户名(大写)'
AND TABLE_NAME='表名(大写)'
第四种方式
--注,仅表数据的大小,不含索引、分区、LOB类型
SELECT SEGMENT_NAME "表名",sum(bytes)/1024/1024 "表大小(M)"
From User_Extents
Group By Segment_Name
having Segment_Name='你要查询的表名';
注意:
1、通过系统视图DBA_TABELS、USER_TABLES、USER_EXENTS统计空间大小有可能不准。
2、DBA_TABLES和DBA_SEGMENTS表中的block的区别:DBA_SEGMENTS中的blocks表示分配给表的存储空间,而DBA_TABLES中blocks表示表中数据实际占有的存储空间;所以这个是有细微差别的。如果要查表的实际占用大小,使用DBA_TABLES来查询是比较准确的,但是需要先收集一下表对象的统计信息。3、数据库的db_block_size有所不同,不一定是8192,使用下面sql可查询
select name,value from v$parameter where name='db_block_size';
查看某个用户下各个表所占的空间
SELECT DISTINCT
s.owner
FROM
dba_segments s
WHERE
s.tablespace_name = 'USERS';--表空间名称
查看某个用户下各个表所占的空间
select OWNER "用户名", t.segment_name "表名", t.segment_type "表类型", sum(t.bytes / 1024 / 1024) "表大小M"
from dba_segments t
where t.owner = '你要查询的用户'
and t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type
order by "表大小M" desc;
查看数据库所有用户下全部表所占的总空间
SELECT OWNER as "用户名", sum(BYTES) / 1024 / 1024 as "所有表的大小(MB)"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME in (select t2.OBJECT_NAME
from dba_objects t2
where t2.OBJECT_TYPE = 'TABLE')
group by OWNER order by 2 desc;
查询某用户下所有表的记录总数
SELECT SUM(num_rows) "记录总条数" FROM SYS.ALL_TABLES T WHERE T.OWNER = '你要查询的用户';
查看户下所有表的各自的记录条数
SELECT T.TABLE_NAME "表名",T.NUM_ROWS "记录条数" FROM USER_TABLES T;
查看当前用户下所有表的表名所属表空间
select table_name "表名",tablespace_name "所属表空间" from user_tables;
查看当前用户下所有表空间的使用情况
SELECT a.tablespace_name "表空间名",
total / (1024 * 1024) "表空间大小(M)",
free / (1024 * 1024) "表空间剩余大小(M)",
(total - free) / (1024 * 1024) "表空间使用大小(M)",
round((total - free) / total, 4) * 100 "使用率 %"
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;
会话删除
select a_s.owner,
a_s.object_name,
a_s.object_type,
vn.sid,
vn.serial#,
vs.spid "os_pid",
vn.process "client_pid",
vn.username,
vn.osuser,
vn.machine "hostname" ,
vn.terminal,
vn.program,
to_char(vn.logon_time,'yyyy-mm-dd hh24:mi:ss')"login_time",
'alter system kill session '''||vn.sid||','||vn.serial#||''';' "oracke_kill",
'kill -9 '|| vs.spid "os_kill"
from all_objects a_s,
v$locked_object v_t,
v$session vn,
v$process vs
where a_s.object_id=v_t.object_id
and v_t.session_id =vn.sid
and vs.addr=vn.paddr
and vn.username not in('SYSMAN','SYS');