oracle的查询命令
1.查询所有的普通表的大小，并按照大小倒叙排序;
select segment_name,bytes/1024/1024/1024 "Gb" from dba_segments where owner='xxx' and segment_type='TABLE' order by 2 ;
2. 查詢非分區表包含lob，排序
select SEGMENT_NAME,SEGMENT_TYPE, ceil(sum(bytes) / 1024 / 1024 / 1024) sum3
from user_extents
where segment_type not like 'INDEX%'
and segment_type not in ('ROLLBACK', 'CACHE', 'LOBINDEX', 'TYPE2 UNDO')
and segment_name not in ('FLEX_WO_OQC_2017',
'FLEX_STRETCH_RECORD_2016',
'FLEX_PRINT_SN_2016',
'FLEX_UPLOAD_IMAGE_2016',
'FLEX_SN_BADMARK_BAK2018',
'FLEX_ICT_RECORD_REWORK_BAK2018',
'G_SN_STATUS_2016',
'PM_ICTTEST_RECORD_BAK_2016',
'G_SN_STATUS',
'G_SN_AOI',
'G_SN_TRAVEL',
'FLEX_PRINT_SN',
'FLEX_ICT_RECORD',
'PM_ICTTEST_RECORD_BAK',
'FLEX_STRETCH_RECORD',
'FLEX_UPLOAD_IMAGE',
'FLEX_WO_OQC',
'FLEX_SN_BADMARK',
'FLEX_ICT_RECORD_REWORK',
'G_SN_AOI_2017',
'FLEX_ICT_RECORD_2016',
'FLEX_MACHINE_JOBRECORD',
'FLEX_LINENEEDLE_SN_RECORD',
'FLEX_SN_STATUS',
'FLEX_SN_TRAVEL',
'FLEX_OQCFR_RECODE',
'FLEX_OQCLED_RECODE',
'FLEX_OQCSTRETCH_RECODE',
'FLEX_OQCICT_RECODE',
'FLEX_OQCVSWR_RECODE',
'FLEX_LINENEEDLE_JOBRECORD',
'FLEX_RF_RECORD_BAK',
'FLEX_RF_RECORD')
group by SEGMENT_NAME,SEGMENT_TYPE
order by 3 desc
3.5 查询普通表的大小包含索引
select sum(bytes)/1024/1024/1024 from dba_segments where segment_type not in ('INDEX PARTITION','TABLE SUBPARTITION','TABLE PARTITION','INDEX SUBPARTITION');
3.oracle查看一整年的數據增量
select sum(bytes/1024/1024/1024) GB
from v$datafile
where CREATION_TIME >
to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and CREATION_TIME <
to_date('2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
4. 查看表哪些分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
5.查询数据库的慢语句
select to_char(sysdate-1/24,'yyyy-mm-dd hh24') as snapshot_time,--快照时间
v_1.sql_id,
v_1.elapsed_time,--一小时内累计耗时
v_1.cpu_time,--一小时内累计CPU时间
v_1.iowait_time,--一小时内累计io等待时间
v_1.gets,--一小时内累逻辑读
v_1.reads,--一小时内累计物理读
v_1.rws,--一小时内累计返回行数
v_1.clwait_time,--一小时内累计集群等待时间
v_1.execs,--一小时内累计执行次数
v_1.elpe,--平均每条SQL消耗时间
nvl(v_2.machine,'null') as machine,--客户服务器名称
nvl(v_2.username,'null') as username,--客户连接用户名
to_char(substr(v_1.sqt,1,3000)) as sql
from
(select s.sql_id,
round(elapsed_time / 1000000,2) elapsed_time,
round(cpu_time / 1000000,2) cpu_time,
round(iowait_time / 1000000,2) iowait_time,
gets,
reads,
rws,
round(clwait_time / 1000000,2) clwait_time,
execs,
st.sql_text sqt,
round(elapsed_time / 1000000 / decode(execs, 0, null, execs),2) elpe
from (select *
from (select sql_id,
sum(executions_delta) execs,
sum(buffer_gets_delta) gets,
sum(disk_reads_delta) reads,
sum(rows_processed_delta) rws,
sum(cpu_time_delta) cpu_time,
sum(elapsed_time_delta) elapsed_time,
sum(clwait_delta) clwait_time,
sum(iowait_delta) iowait_time
from dba_hist_sqlstat
where snap_id =(select max(snap_id) from dba_hist_snapshot)
group by sql_id
order by sum(elapsed_time_delta) desc)
where rownum <= 20) s,
dba_hist_sqltext st
where st.sql_id = s.sql_id) v_1
left join
(select distinct a.sql_id, a.machine, b.username
from dba_hist_active_sess_history a
left join dba_users b
on a.user_id = b.user_id
where a.snap_id = (select max(snap_id) from dba_hist_snapshot)) v_2
on v_1.sql_id = v_2.sql_id
where v_1.elpe >=1 --平均执行时间大于1s的sql过滤出来
order by elpe desc
;
6. 查询所有的schema（用户），及对应的表空间和临时表空间。
SQL> set line222
SQL> select username,default_tablespace,temporary_tablespace from dba_users;
7.查看库下有哪些表
select * from user_object
8.查看schema下有哪些大于10G的表，包含表，分区表，index，lob
select owner,segment_name,segment_type ,sum(bytes/1024/1024/1024) GB from dba_segments group by
owner,segment_name,segment_type having sum(bytes/1024/1024/1024)>10 order by 4 desc;
9.监控索引是否使用
--监控
alter index SAJET.FLEX_SCAN_COUNT_PIDX01 monitoring usage;
---如果取消监控就是
alter index SAJET.FLEX_SCAN_COUNT_PIDX01 nomonitoring usage;
conn sajet/tech
select *
from v$object_usage
where USED = 'NO'
and END_MONITORING is null;
可以先执行alter index 开启监控（可以sys用户执行）然后在相应的schema下执行开启监控隔24h再次查询，如果一直没有没使用used就为NO。否则为YES
添加指定用户并对指定表授权
create user mesac identified by mesac;
grant connect, resource to mesac;
grant select,insert,update,delete on sajet.FLEX_EMP to mesac;
grant select,insert,update,delete on sajet.SYS_EMP to mesac;
grant select,insert,update,delete on sajet.SYS_ROLE_EMP to mesac;
grant select,insert,update,delete on sajet.SYS_HT_EMP to mesac;
grant select,insert,update,delete on sajet.SYS_EMP to mesac;
查看有哪些分區表
SELECT distinct table_name,table_owner FROM dba_tab_partitions where table_owner='SAJET'
SELECT * FROM dba_tab_partitions where table_owner='SAJET'
查看所有的分区表
select distinct table_name from dba_tab_partitions where owner='SAJET';
查看所有分区表的大小
select segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name in (
'G_CCD_FORATION_DATA',
'G_CCD_TRANSFER',
'G_CCD_TRANSFER_DETAIL',
'G_EQ_DATA',
'G_EQ_DATA_183',
'G_EQ_DATA_DETAIL',
'G_EQ_DATA_DETAIL_183',
'G_FRAME_DETAIL',
'G_HT_FRAME_DETAIL',
'G_HT_SN_TRAVEL',
'G_SN_COUNT',
'G_SN_KEYPARTS',
'G_SN_STATUS',
'G_SN_TRAVEL',
'G_SOUNDCHECK_TEST_COUNT',
'G_TEST_ITEM',
'SYS_SOUNDCHECK',
'MERRY_CCD_ITEM_TEST')
group by segment_name order by 2;
统计二级分区的大小
select segment_name,substr(partition_name,1,19),sum(bytes/1024/1024/1024) from dba_segments where segment_name in (
'G_SN_TRAVEL')
group by segment_name,substr(partition_name,1,19) order by 2;
查看硬解析
create index SAJET.IDX$$_52BE60001 on
SAJET.FLEX_SN_BADMARK(SYS_OP_C2C(SERIAL_NUMBER),PROCESS_ID)
tablespace sysbsidx online;
select sql_text
from v$sql
where force_matching_signature = 17596571941187863513
查看rac架构session 分配
SQL> set line222
col file_name for a100SQL>
SQL>
