大家好！我是RomMr，一个乐于分享的it男，今天和大家分享的是我在工作中总结的sql，希望对大家有帮助
1、表空间
--查询默认表空间
select default_tablespace from user_users/dba_users;
--更改默认表空间
alter user user_name default tablespace tablespace_name;
--更改表所在表空间
alter table xxx move tablespace xxxx;
--索引表空间迁移
alter index xxx rebuild tablespace xxxx；
--查询表空间下所有表名
select table_name,tablespace_name from dba_tables where tablespace_name='xxxx';
--查询表空间下表和索引
select * from
dba_segments
where
tablespace_name='xxx' and owner='xxx' and (segment_type='TABLE' or segment_type='INDEX');
--查看表空间大小
select tablespace_name,sum(bytes)/1024/1024||'M' from dba_data_files group by tablespace_name;
--查询已使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024||'M' from dba_free_space group by tablespace_name;
--查看表空间的使用率(现有)
select a.tablespace_name,a.bytes/1024/1024 "sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "used%"
from(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 5 desc;
--查看表空间的使用率（总大小）
select tablespace_name,tablespace_size*8/1024/1024 as totle,
used_space*8/1024/1024 as used,
(tablespace_size-used_space)*8/1024/1024 as free,
used_percent
from dba_tablespace_usage_metrics
--创建表空间
create tablespace xxx datafile 'xxx' size 10G autoextend on next 50M maxsize 30G;
--删除表空间
drop tablespace xxx including contents and datafiles cascade constraint;
--临时表空间大小
select a.tablespace_name,a.*,b.*
from(select tablespace_name ,sum(bytes)/1024/1024 ||'M' total,sum(maxbytes)/1024/1024||'M' maxsize from dba_temp_files group by tablespace_name)a,
(select tablespace_name,sum(free_space)/1024/1024||'M' free from dba_temp_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
--查看ASM磁盘可用空间
select name,total_mb,free_mb,usable_file_mb,state from v$asm_diskgroup;
2、锁相关
--查看锁表进程
select s.sid,
s.serial#,
l.oracle_username,
l.os_user_name,
a.object_name,
l.locked_mode
from v$locked_object l, dba_objects a, V$session s
where a.object_id = l.object_id
and l.session_id = s.sid
注：
locked_mode：
0：none
1：null 空
2：Row-s 行共享（RS），共享表锁 sub share
3：Row-x 行独占（RX），用于行的修改
4：share共享锁（S），阻止其他DDL操作
5：S/Row-x共享行独占（SRX）：阻止其他事物操作
6：exclusive独占（X）：独立访问
--查看锁状态
select * from v$lock
注：
id1：代指object_id
block:如果值是“1”,则代表阻塞了其他会话
--按用户查询锁的情况
select l.sid,
s.blocking_session blocker,
s.event,
l.type,
l.lmode,
o.object_name,
o.object_type,
l.request
from v$lock l, dba_objects o, v$session s
where l.sid=s.sid
and l.id1=o.object_id
and upper(s.username)=upper('xxxx')
order by sid,type
注：主要看 blocker 这一列
--查询锁的源头
with lk as
(select blocking_instance || ':' || blocking_session blocker,
inst_id || ':' || sid waiter
from gv$session
where blocking_instance is not null
and blocking_session is not null)
select lpad(' ', 2 * (level - 1)) || waiter lock_tree
from (select *
from lk
union all
select distinct 'root', blocker
from lk
where blocker not in (select waiter from lk))
connect by prior waiter = blocker
start with blocker = 'root';
注：查锁的树，子节点为被阻塞会话
--查有锁的进程
select b.session_id,
a.serial#,
p.spid,
a.machine,
a.program,
a.status,
c.object_name,
b.oracle_username,
b.os_user_name,
a.sql_id
from v$process p, v$session a, v$locked_object b, all_objects c
where p.addr = a.PADDR
and a.process = b.process
and c.object_id = b.object_id;
--杀死会话或线程
alter system kill session 'sid,serial#'; --SQL中执行
ps -ef| grep spid --查询相关进程
kill -9 spid --linux杀死相关线程
orakill
--查询死锁相关sql
select * from v$sql where sql_id='xxx';
--查看enq：TX-index contention
select event,wait_time_milli,wait_count,last_update_time from v$event_histogram where event like 'enq：TX-index contention'
--查看library cache lock
select * from v$event_histogram where event like 'library cache lock'
3、统计信息相关
analyze
dbms_stats.gather
--生成统计信息历史表
exec dbms_stats.create_stat_table('schame','stat_table');
--导出统计信息
exec dbms_stats.export_table_stats(ownname=>'schema',tabname=>'xxx',stattab=>'stat_table',statid=>'A');
statid:执行统计信息标识，即序号,可省略
--导入统计信息
exec dbms_stats.import_table_stats(ownname=>'schema',tabname=>'xxx',statab=>'stat_table',statid='A')
4、闪回数据
--根据SCN号闪回
--查询SCN号
select current_scn from V$database;
dbms_flashback.get_system_change_number from dual;
--根据scn号闪回查询
select * from table_name as of scn xxx;
--根据具体时间点进行闪回查询
select * from table_name as of timestamp to_timestamp('2020-11-11 14:32:20','yyyy-mm-dd hh24:mi:ss');
--闪回版本查询
select * from table_name versions {between{scn|timestamp} start and end}
注：start，end可以是scn也可以是时间
版本列说明：
versions_startscn：版本开始scn
versions_starttime：版本开始时间
versions_endscn：版本结束scn，有值则说明此行后面被更改过是旧版本，如果为null，则说明此版本是当前版本或行版本被删除（即versions_operation 值为D）
versions_endtime：版本结束时间 同结束scn
versions_xid：创建行版本的事务ID
versions_operation：在行上执行的操作（I=插入，D=删除，U=更新）
--闪回事务查询
select * from flashback_transaction_query
注：闪回事务查询需要打开追加日志，不然无法从
flashback_transaction_query 查出undo_sql
查询追加日志状态：show parameter undo，主要看事务查询中的undo_sql
--表数据闪回
flashback table xxx to scn xxx;
注：需要打开行迁移alter table xxx enable/disable row movement；
5、数据文件相关
--修改数据文件自动扩展
alter database datafile 'xxxxx' autoextend on next 10M maxsize 32G;
--增加数据文件
alter tablespace xxx add datafile 'xxxx' size 10G autoextend on next 10M maxsize unlimited;
--查看每个段的大小
select segment_name,sum(bytes)/1024/1024 ||'M' from user_extents group by segment_name;
--更改数据文件大小
alter database datafile 'xxx' resize 30G;
6、索引相关
--查看索引相关
select * from dba_indexes
--查看分区索引
select * from dba_ind_partitions
--查看选择度
select a.column_name,b.num_rows,round(a.num_distinct/b.num_rows*100,2) selectivity,a.num_distinct cardinality,a.histogram,a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner='xxx'
and a.table_name='xxxx'
--修改索引的名字
alter index xxx rename to xxx
--重建索引
alter /*+ Parallel(4) */ index xxx rebuild nologging online;
--hint 强制走索引
select /*+index(table_name index_name)*/ a.* from table_name a
7、连接数相关
--允许最大连接数
select value from v$parameter where name='processes'
--修改最大连接数
alter system set processes=xxx scope=spfile；
--当前连接数
select count(*) from v$session
--并发连接数
select count(*) from v$session where status='active'
--查询正在使用数据的用户
select a.sid,a.serial#,a.osuser,a.username,b.elapsed_time,b.cpu_time/1000000,b.executions||'s',b.sql_text,a.machine from v$session a,v$sqlarea b where a.sql_address=b.address order by b.cpu_time desc;
--查看对应几次建立的连接数
select machine，count(1) from v$session group by machine
8、日志相关
--查看归档日志大小
select sum(blocks*block_size) from v$archived_log
9、其他相关
--查询备份路径
select * from dba_directories；
--创建备份目录
create or replace directory xxx as xxxx
--查询ddl语句
select dbms_metadata.get_ddl('table','table_name') from dual;
--查询expdp/impdp作业状态
select * from dba_datapump_jobs;
--管理expd/impdp作业
impdp/expdp user_name/passwd attach=job_name
--查询命中率
select sum(decode(name, 'physical reads', value, 0)) "Physical Reads",
sum(decode(name, 'db block gets', value, 0)) "Db Block Gets",
sum(decode(name, 'consistent gets', value, 0)) "Consistent Gets",
(1 - (sum(decode(name, 'physical reads', value, 0))) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0)))) * 100 "Hitratio"
from V$sysstat
--查询sql运行时间
select s.sql_text,
s.sql_fulltext,
s.sql_id,
round(s.elapsed_time / 1000000 /
(decode(executions, 0, 1, null, 1, executions)),2) "平均执行时间（s）",
s.executions,
s.optimizer_cost,
s.sorts,
s.module "链接模式",
s.physical_read_bytes,
s.physical_write_requests,
s.rows_processed,
s.disk_reads,
s.direct_writes "直接路径写",
s.last_active_time,
s.parsing_schema_name
from v$sqlarea s
where to_char(s.last_load_time, 'yyyy-mm-dd') =
to_char(sysdate, 'yyyy-mm-dd')
and s.command_type in (2, 3, 5, 6, 189)
order by 4
今天分享就到这里，希望大家能多多支持，你们的转发、关注、点赞、收藏，就是我持续更新的动力！