oracle 表空间、用户名 相关语句
一、oracle查询表空间文件所在路径
select * from dba_data_files t where t.tablespace_name='FLW'
二、计算出表空间各相关数据
SELECT file_name 物理路径,t1 表空间,z 总表空间M,z-s 已用表空间,s 剩余表空间,ROUND((z-s)/z*100,2) "使用率%"
From （Select tablespace_name t1,round(SUM(bytes / (1024 * 1024)), 0) s
From DBA_FREE_SPACE Group by tablespace_name),
(Select file_name ,tablespace_name t2,round(SUM(bytes / (1024 * 1024)), 0) z
From DBA_DATA_FILES Group by file_name,tablespace_name) Where t1=t2;
--查询表空间使用情况（低版本也可以用）
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
三、修改表空间自动增长
ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON; --设置数据文件自动增长
四、修改表空间大小
alter database datafile '/home/db/..../tbs.dbf' resize 100M;
五、新增表空间
create tablespace data_config datafile 'D:\ORACLE\app\Administrator\oradata\orcl\data_config.dbf' size 50m autoextend on;
六、新增用户
create user data_config identified by dcjet default tablespace data_config temporary tablespace temp;
grant dba,resource,connect to data_config;
七、查询出一个用户用了几个表空间
SELECT owner, tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024, 2) "USED(M)"
FROM dba_segments
GROUP BY owner, tablespace_name
ORDER BY owner DESC;
八、查询表空间使用情况
--查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
八、表压缩
--使能行移动
alter table tablename enable row movement;
--表压缩
alter table tablename shrink space;
--如果有函数索引就会有如下错误：
ORA-10631: SHRINK clause should not be specified for this object
九、新建表空间
alter tablespace 表空间名 add datafile '数据文件路径' size 10000m autoextend on next 1000m;
alter tablespace 表空间名 add datafile '数据文件路径' size 10000m autoextend on next 1000m;
十、表空间使用情况
---查看前一天所有表空间的增长量 select C.tablespace_name, D."Total(MB)", D."Used(MB)" - C."Used(MB)" AS "Increment(MB)", to_char(trunc(sysdate - 1),'yyyy/mm/dd') "TIME" from (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(trunc(sysdate - 1), 'yyyymmdd hh24:mi')) C, (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(trunc(sysdate), 'yyyymmdd hh24:mi')) D where C.tablespace_name = D.tablespace_name; ---查看前一周所有表空间的增长量 select C.tablespace_name, D."Total(MB)", D."Used(MB)" - C."Used(MB)" AS "Increment(MB)", to_char(next_day(trunc(sysdate),2)-7,'yyyy/mm/dd')||'--'||to_char(next_day(trunc(sysdate),2)-7,'yyyy/mm/dd') "TIME" from (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(next_day(trunc(sysdate),2)-14,'yyyymmdd hh24:mi')) C, (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(next_day(trunc(sysdate),2)-7,'yyyymmdd hh24:mi')) D where C.tablespace_name = D.tablespace_name; ---每天使用情况 select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)总空间", round(A.tablespace_usedsize*8 / 1024) "Used(MB)使用空间", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# AND B.NAME='NJYTH' and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'hh24:mi') = to_char(trunc(sysdate), 'hh24:mi') ORDER BY to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss');
十一、查看oracle 分配了几个G的内存
select * from v$parameter where name like '%sga%';
十二、数据库读取情况排名前10统计分析
SELECT *
FROM (SELECT PARSING_USER_ID EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM < 10
SELECT S.SID, S.EVENT, S.WAIT_TIME, W.SEQ#, Q.SQL_TEXT
FROM V$SESSION_WAIT W, V$SESSION S, V$PROCESS P, V$SQLAREA Q
WHERE S.PADDR = P.ADDR
AND S.SID = '14'
AND S.SQL_ADDRESS = Q.ADDRESS;
--当前由Oracle引起的CPU100%
十三、--每个session占用的cpu
select ss.sid, se.command, ss.value CPU, se.username, se.program
from v$sesstat ss, v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid = ss.sid
and ss.sid > 6
order by ss.sid
十四、抓取当前最消耗CPU的sql代码，根据操作系统(os)的spid
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '10401'))
ORDER BY piece ASC
十五、oracle 10以上的高水位问题处理
alter table t_hxhs_entryhead_tmp_log enable row movement;
alter table t_hxhs_entryhead_tmp_log shrink space;
analyze table t_hxhs_entryhead_tmp_log compute statistics;
十六、-所有分区表TABLE PARTITION占用空间
select segment_name, sum(bytes) / 1024 / 1024 Mbytese
from user_segments
where segment_type = 'TABLE PARTITION'
group by segment_name
十七、表和索引占用空间情况
select a.segment_name,
a.Mbytese total_size,
b.segment_name table_name,
b.Mbytese table_size,
c.segment_name index_name,
c.Mbytese index_size
from ( --查看所有segment的大小(M)
select segment_name, sum(bytes) / 1024 / 1024 Mbytese
from user_segments
group by segment_name) a
left join ( --所有表占用空间(M)
select segment_name, sum(bytes) / 1024 / 1024 Mbytese
from user_segments
where segment_type = 'TABLE'
group by segment_name) b
on a.segment_name = b.segment_name
left join ( --所有索引占用空间(M)
select segment_name, sum(bytes) / 1024 / 1024 Mbytese
from user_segments
where segment_type = 'INDEX'
group by segment_name) c
on a.segment_name = c.segment_name
order by total_size desc
---查看前一天所有表空间的增长量select C.tablespace_name, D."Total(MB)", D."Used(MB)" - C."Used(MB)" AS "Increment(MB)", to_char(trunc(sysdate - 1),'yyyy/mm/dd') "TIME" from (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(trunc(sysdate - 1), 'yyyymmdd hh24:mi')) C, (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(trunc(sysdate), 'yyyymmdd hh24:mi')) D where C.tablespace_name = D.tablespace_name;---查看前一周所有表空间的增长量select C.tablespace_name, D."Total(MB)", D."Used(MB)" - C."Used(MB)" AS "Increment(MB)", to_char(next_day(trunc(sysdate),2)-7,'yyyy/mm/dd')||'--'||to_char(next_day(trunc(sysdate),2)-7,'yyyy/mm/dd') "TIME" from (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(next_day(trunc(sysdate),2)-14,'yyyymmdd hh24:mi')) C, (select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)", round(A.tablespace_usedsize*8 / 1024) "Used(MB)", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') = to_char(next_day(trunc(sysdate),2)-7,'yyyymmdd hh24:mi')) D where C.tablespace_name = D.tablespace_name;---每天使用情况select B.name tablespace_name, case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024) when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2) END as "Total(MB)总空间", round(A.tablespace_usedsize*8 / 1024) "Used(MB)使用空间", A.rtime from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B where A.tablespace_id = B.TS# AND B.NAME='NJYTH' and to_char(to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss'), 'hh24:mi') = to_char(trunc(sysdate), 'hh24:mi') ORDER BY to_date(replace(rtime, '/', null), 'mmddyyyy hh24:mi:ss');
