Oracle dba工作笔记

大家好！我是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 <sid><spid> --windows杀死进程，sid为实例名，spid为要杀死的线程

--查询死锁相关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

今天分享就到这里，希望大家能多多支持，你们的转发、关注、点赞、收藏，就是我持续更新的动力！