查看原文
其他

一线运维 DBA 五年经验常用 SQL 大全(二)

JiekeXu JiekeXu DBA之路 2024-03-03

作者 | JiekeXu

来源 | JiekeXu之路(ID: JiekeXu_IT)

转载请联系授权 | (微信ID:JiekeXu_DBA)

大家好,我是 JiekeXu,分开这么久,很高兴又和大家见面了,祝大家新年快乐,牛气冲天发大财,欢迎点击上方蓝字关注我,标星或置顶,更多干货第一时间到达!

本文 SQL 及相关命令均是在运维工作中总结整理而成的,对于运维 DBA 来说可提高很大的工作效率,值得收藏。当然如果你全部能够背下来那就很牛逼了,如果不能,还是建议收藏下来慢慢看,每条 SQL 的使用频率都很高,肯定能够帮助到你。


当然,由于本编辑器原因以下 SQL 可能出现格式错误不能执行,导致出错,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号后台回复【SQL大全二】 即可获取,这里也有 2020 年的精华文章汇总,如有需要可点击此处查看【精华文章】


1、查看数据文件信息:

col file_name for a55select tablespace_name,file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='AM_DATA';alter tablespace AM_DATA add datafile '+DATA' size 30G;

2、查看 ASM 磁盘组信息:

su - grid sqlplus / as sysasmselect group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;---调整ASM磁盘均衡级别ALTER DISKGROUP DATA REBALANCE POWER 10; ---查看ASM磁盘均衡时间:select * from v$asm_operation;

3、查看 ASM 磁盘组磁盘的信息

set lin 1000 pagesize 999col PATH for a33col NAME for a15col FAILGROUP for a15select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE from v$asm_disk where GROUP_NUMBER='1';

4、查看表空间大小:

SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%"FROM (SELECT tablespace_name,SUM(bytes) free FROMDBA_FREE_SPACEGROUP BY tablespace_name ) a,(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILESGROUP BY tablespace_name) bWHERE a.tablespace_name=b.tablespace_nameORDER BY 4;

5、查某个表空间内的前五个大表

col TABLE_NAME for a30set pagesize 200set linesize 200col TABLE_NAME for a30set linesize 200select * from (select TABLESPACE_NAME,OWNER,SEGMENT_NAME "TABLE_NAME",to_number(decode(substr(BYTES/1024/1024,1,1),'.','0'||BYTES/1024/1024,BYTES/1024/1024)) total_MBfrom dba_segments where TABLESPACE_NAME ='&tablespacename' and SEGMENT_TYPE='TABLE' order by total_MB desc ) where rownum<=5;
SELECT * FROM (SELECT OWNER,SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME,OWNER  ORDER BY 3 DESC) WHERE ROWNUM <=5;OWNER SEGMENT_NAME MB------------------------------ --------------------------------------------------------------------------------- ----------SYS XX_RECNAME_RB_IX 11136SYS XXXXX_ORIGINNOTE_IX 9152SYS XXXXX_ABSTRACT_IX 6388SYS XXX_PAYDATE_NU_NC 5490
SELECT OWNER,SEGMENT_NAME ,segment_type FROM DBA_SEGMENTS WHERE segment_name in ('PAYMENTS_RECNAME_RB_IX','XXXXX_ORIGINNOTE_IX','XXXXX_ABSTRACT_IX','XXX_PAYDATE_NU_NC');
OWNER SEGMENT_NAME SEGMENT_TYPE------------------------------ --------------------------------------------------------------------------------- ------------------SYS XXX_PAYDATE_NU_NC INDEXSYS XXXXX_ORIGINNOTE_IX INDEXSYS XXXXX_ABSTRACT_IX INDEXSYS XXXMENTS_RECNAME_RB_IX INDEX

6、查看 shared_pool 的大小

select sum(bytes)/1024/1024/1024 from v$sgastat where pool='shared pool';--查看空闲的:select * from v$sgastat where name = 'free memory' and pool = 'shared pool';--统计活动的undoselect sum(bytes /(1024*1024*1024)) from dba_undo_extents where status='ACTIVE';

7、查看占用内存 100k 的 sql 语句:

select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem;

8、查看字符集

select userenv('language') from dual;select * from nls_database_parameters;

9、Oracle 查询 temp 表空间的名字和位置

select tablespace_name,file_name from dba_temp_files;col FILE_NAME for a55select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 total_gb,USER_BYTES/1021/1024/1024 gb from dba_temp_files;
create temporary tablespace temp1 tempfile '/rhzxdata/tempdata/pbc/temp01.dbf' size 20G autoextend on maxsize 30G
Oracle查询 temp 表空间的使用率select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2) "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent"from dba_temp_free_space;
注意:Primary 端涉及到的临时表空间创建维护、临时文件创建的操作是不会传导到 standby 端的。

10、查看数据库版本

set line 150 col ACTION_TIME for a30 col ACTION for a8 col NAMESPACE for a8 col VERSION for a10 col BUNDLE_SERIES for a5 col COMMENTS for a20 select * from dba_registry_history;

======================================================

本文 SQL 命令由于微信编辑器原因可能出现格式错误不能执行,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号【JiekeXu DBA之路】扫描最下方二维码后台回复 【SQL大全二】即可获取。

=======================================================

11、查看补丁版本:

JIEKED2:/app/product/11.2.0/db/OPatch$opatch lsinventory

12、查看锁表

SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_timeFROM v$locked_object l, all_objects o, v$session sWHERE l.object_id = o.object_idAND l.session_id = s.sidORDER BY sid, s.serial# ;
查出锁定表的 session 的 sid, serial#,os_user_name, machine name, terminal 和执行的语句:
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, l.os_user_name,s.machine, o.object_name,s.terminal,a.sql_text, a.action FROM v$sqlarea a,v$session s, v$locked_object l,all_objects o WHERE l.session_id = s.sid AND s.prev_sql_addr = a.address ORDER BY sid, s.serial#;13、查看视图对应的表:
select * from dba_dependencies where NAME='视图名' and TYPE='VIEW';

14、杀会话命令

alter system kill session 'sid,serial#';

15、查看表大小

select TABLESPACE_NAME,OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024 total_mb from dba_segments where TABLESPACE_NAME='NEWCAR02' group by TABLESPACE_NAME,OWNER,SEGMENT_NAME;

16、查看 GI 兼容版本

select name,compatibility,database_compatibility from v$asm_diskgroup;

17、查看aix操作系统的资源情况

prtconf|morelparstat -iortconfAIX 磁盘扫描命令root 执行cfgmgr -v

--查看那个进程占用磁盘空间fuser -dV /app

AIX 查看错误日志并输出到 messagesJIekeXuY1:/#errpt -dHIDENTIFIER TIMESTAMP T C RESOURCE_NAME DESCRIPTIONDE3B8540 1012214419 P H hdisk18 PATH HAS FAILED4B436A3D 1012214119 T H fscsi0 LINK ERROR4B436A3D 1012214119 T H fscsi0 LINK ERROR4B436A3D 1012214119 T H fscsi0 LINK ERROR
/bin/errpt -a > messages.out
AIX查看LUNID信息lscfg -vpl hdisk187 输出结果里的 serial number就是存储要的lunid# lscfg -vpl hdisk11  hdisk11          U9080.MME.680A6E8-V7-C2-T1-W500507680C25ADBA-LB000000000000  MPIO FC 2145 Manufacturer................IBM Machine Type and Model......2145 ROS Level and ID............0000 Device Specific.(Z0)........0000063268181002 Device Specific.(Z1)........0203202        Serial Number...............600507680C808570080000000000042B PLATFORM SPECIFIC
Name: disk Node: disk    Device Type:  block  

--- 检查磁盘大小(单位M)bootinfo -s hdisk12--- 查看磁盘的详细信息lsattr -El hdisk12HP:machinfo(diskinfo /dev/rdisk/disk*  查看磁盘大小)WIN:msinfo32SUSE:cat /proc/cpuinfo  (model name )---查看资源使用情况:HP:glance/topAIX:nmon/topas
--AIX查看版本号oslevel JIEKEXuR2:/app/product/11.2.0/grid/network/log$oslevel6.1.0.0---查看内存大小:HP:/usr/contrib/bin/machinfo | grep -i MemoryAIX: /usr/sbin/lsattr -E -l sys0 -a realmem---查看swap分区:HP:/usr/sbin/swapinfo -aAIX:/usr/sbin/lsps -s

18、用户相关操作

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;create user agriproduct identified by product default tablespace users;grant conncet,resource,create view to product;--解锁用户set linesize 500col USERNAME for a15col ACCOUNT_STATUS for a15select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,DEFAULT_TABLESPACE from dba_users where USERNAME ='SQLMON';alter user SQLMON identified by "SQLMON" account unlock;alter user SQLMON account unlock;alter user SQLMON identified by "SQLMON";conn SQLMON/SQLMON#查看用户具有的权限1、查询用户有哪些角色:select * from dba_role_privs where grantee='&username';2、查询角色包含哪些权限:select * from role_sys_privs where role='&role';3、查询用户系统权限:select * from dba_sys_privs where grantee='&username';select * from dba_tab_privs where grantee='&username';--创建 dblink 权限grant create database link to user ;grant create public database link to user ;
删除用户:drop user XXX cascade;解锁用户alter user muar_rb account unlock identified by &PASSWORD;
查看表空间下的用户select distinct s.owner from dba_segments s where s.tablespace_name ='&TBSNAME'

19、查看数据量:

select sum(bytes)/1024/1024/1024 Gb from dba_segments;

20、查看 REDOLOG 大小

select group#,members,bytes/1024/1024,status from v$log;

21、清理垃圾文件

cd &DIRfind ./ -ctime +3 |xargs rm查看大于1024M的文件find / -type f -size +1024M -print0 | xargs -0 du -h | sort -nr

22、AIX 操作系统下 ASM 磁盘相关操作

--- 检查磁盘大小(单位M)bootinfo -s hdisk0
--- 查看磁盘的详细信息lsattr -El hdisk0
JIEKEXu:/dev#lsattr -El /dev/hdisk322lsattr: 0514-519 The following device was not found in the customized device configuration database: /dev/hdisk322 JIEKEXu:/dev#su - grid JIEKEXu:/home/grid$kfed read /dev/rhdisk322 kfbh.endian: 0 ; 0x000: 0x00kfbh.hard: 130 ; 0x001: 0x82kfbh.type: 1 ; 0x002: KFBTYP_DISKHEADkfbh.datfmt: 1 ; 0x003: 0x01kfbh.block.blk: 0 ; 0x004: blk=0kfbh.block.obj: 2147483953 ; 0x008: disk=305kfbh.check: 3956950460 ; 0x00c: 0xebda45bckfbh.fcn.base: 2632 ; 0x010: 0x00000a48kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
--- 检查权限 hdisk是块设备,而rhdisk是字符设备。ls -l /dev/hdisk*【排序查看ls -ltr /dev |grep rhdisk】
运行命令lsattr -E -a rw_timeout -F value -l Name,查看rw_timeout的值。JIEKEY1:/app/grid/diag/asm/+asm/+ASM1/trace$lsattr -E -a rw_timeout -F value -l hdisk1660--- 检查PVIDlspv | grep hdiskn--- 检查保留策略lsattr -E -l hdisk5 | grep reserve_policy  --- 查看磁盘是否为共享磁盘lsattr -El hdisk0 比对两个主机对应的磁盘号是否一致:unique_id--- 查看磁盘是否可用lspv看PVID是否为none,若为none则数据库可用,再查看数据库当前有没有使用,若没有则可用来扩容ASM磁盘组

23、AIX 下按用户查看占用多少内存              

svmon -U grid -w |more svmon -U oracle -w | more

24、AIX 下查看用户下有多少进程

svmon -PO unit=GB |grep aioserver |wcsvmon -PO unit=GB |grep oracle |wc
lsvg |lsvg -i -plsdev -c disk

25、查看服务器底层用的存储类型:

lscfg -vpl hdisk40

26、生成AWR报告

-----某个节点的 AWR@?/rdbms/admin/awrrpti.sql-----AWR报告@?/rdbms/admin/awrrpt.sql-- AWR 两个时间段的比较报告@?/rdbms/admin/awrddrpt.sql-----ASH报告@$ORACLE_HOME/rdbms/admin/ashrpt.sql-----细粒度的 ASH (Enter value for slot_width:1)slot 槽为 1 的 ASH@$ORACLE_HOME/rdbms/admin/ashrpti.sql
关于physical reads ,db block gets 和 consistent gets 这三个参数之间有一个换算公式:数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。用以下语句可以查看数据缓冲区的命中率:SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
查询出来的结果 Buffer Cache 的命中率应该在 90% 以上,否则需要增加数据缓冲区的大小。

27、查看ASM磁盘挂载时间:

set lines 500 pages 2000col g_name format a10col g_n format 99col d_n format 999col m_status format a7col mo_status format a7col h_status format a11col name format a20col path format a20col failgroup format a15select g.group_number g_n,g.disk_number d_n,g.name name,g.failgroup,g.mount_status m_status,g.header_status h_status,g.mode_status mo_status,g.path ,to_char(g.mount_date, 'YYYY/MM/DD HH24:MI:SS') m_datefrom v$asm_disk gorder by g_n, d_n

28、查看某个用户所拥有的角色

select * from dba_role_privs where grantee='JKX_NEW_QRY ';

29、查看某个角色所拥有的权限

select * from dba_sys_privs where grantee='SKDATA';select * from DBA_TAB_PRIVS where grantee='SKDATA';

30、修改 LINUX 操作系统 /dev/shm 文件系统大小

开机自启动:tmpfs /dev/shm tmpfs defaults,size=20G 0 0立即生效:mount -t tmpfs shmfs -o size=20g /dev/shm

31、trace 追踪监听文件

$ ps aux |sort -rnk3 |more侦听器进程的Pstack / Truss / Strace / Tusc 注意110888.1如何跟踪Unix系统调用Pstack <PID_of_listener>
Sun:truss -aefdDo /tmp/truss-lsnr.log -p <PID_of_listener> Linux:strace -frT -o /tmp/strace-lsnr.log -p <PID_of_listener> HP:tusc -aef -o /tmp/tusc-lsnr.log -T“%H:%M:%S” -p <PID_of_listener>

32、删除当前目录下十天前 *.dmp 文件  

find ./ -name '*.dmp' -mtime +10 -exec rm -f {}

33、dds 同步软件

su - ddsdt 用户,vshmt -c 可以看到同步情况/ddssoftware/ddt/ddsdt/bin/dds_pput -xDDS同步软件 类似于OGG的同步软件10.10.19X.XXX localhost10.10.19X.XX JiekeADGJiekeADG:/app/oracle/diag/rdbms/picccash/picccash/trace$hostname:/#su - ddsdtJiekeADG:/ddssoftware/ddt/ddsdt/dt$vshmt -cSHM: SHM-VER : Ver2.13.009 type : T MULTI_SOURCE : 1 size : 34675744 SystemStat : Normal OracleLoginStr : ddsdt/ddsdt DB-Charset : 852 DB-N-Charset : 2000 Conv-Charset : 0 AuthStr : ddsdt/ddsdt DDS_DATA : /ddssoftware/ddt/ddsdt/dt Audit : No QueueCount : 2 comm_param : TCP:10.10.19X.XX:7915 Source : TCP:10.10.19X.XXX:7910 SourceWebport : 8303 SizeLogFile : 104857600 ReserveTableCNT: 50 put_retry_cnt : 3 alarm_wait_dtf : 300 has_split_rids : 0 large_table_blo: 1000 lob_piece_len : 8388608 RM_Blk_Count : 128 PutData : InQueue: 0:0 PutOK: 12510654:945115120084 HisData : InQueue: 0:0 PutOK: 8893:87198847301 ChkData : InQueue: 0:0 PutOK: 0:0 AudData : InQueue: 0:0 PutOK: 0:0 dbfInASM : 1 DELAY : 0 DML_MODE : RowID PackBak : 5 Modules : 0x87 DDS_RB_HOURS : 0 MachineTime : 20190904182630 inode-log : 294 inode-err : 281 DISK-REMAIN(MB): 512 restart_mem(MB): 256 DDS_LOBDIR : sem_count : 121 sem_pput : 8 sem_pput_h : 40 sem_pput_v : 72 sem_pput_cmt : 89 max_num_pput : 32 max_num_pput_h : 32 max_num_pput_v : 16 max_num_pput_a : 1 adjust_op : 0 index_retry_cou: 0 index_retry_del: 60 SyncTime0 : 2018/07/26 00:35:05 SCN-PUT00 : SCN-0x83d659c71 SUB:0 Time:2019/09/04 10:29:57 SCN-PUT01 : SCN-0x83d659c71 SUB:0 Time:2019/09/04 10:29:57 StopPutIncremen: Normal, Load Increments StopCreateIndex: Normal, Load Indexes Total : IRP-160361545 URP-1100746241 DRP-130759 DDL-7 TXN-593096960 DelayTime : MIN-28591 MAX-1353324 AVG-229 PUT-NO-CUR : 136533 put0 : 6296809/6296809 put1 : 6296809/6296809 his0 : 12033/12033 ATS001 WaitCommit : 0-N 1-N SRC#0 : SOURCE 10.10.19X.XXX:7910:8303JiekeADG:/ddssoftware/ddt/ddsdt/dt$

=======================================================

关于 SQL 命令还有很多,由于篇幅就先写这么多,有机会在分享。上述 SQL 命令由于微信编辑器原因断句不明显,可能出现格式错误不能执行,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号ID 为 JiekeXu_IT】后台回复 【SQL大全二】 即可获取。也可添加微信 ID:JiekeXu_DBA 一起学习数据库。

=======================================================

34、根据归档号从带库恢复归档日志

rman target /run{Allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';restore archivelog sequence between 63145 and 63149 thread 1;restore archivelog sequence between 62697 and 62700 thread 2;Release channel t1;}

35、JDBC 连接串

jdbc:Oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(HOST=10.1XX.XXX.XXX)(PROTOCOL=TCP)(PORT=1521))(ADDRESS=(HOST=10.1XX.XXX.XXX)(PROTOCOL=TCP)(PORT=1521)))(LOAD_BALANCE=yes)(FAILOVER=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Jieker)))

36、数据泵导入 

impdp user/password dumpfile=temp.dmp directory=dump remap_tablespace=[old_table_space]:[new_table_space] old是指server1机器上的tablespace_name,new是指server2上的

37、切换日志打检查点,关闭实例时使用

alter system switch logfile;alter system switch logfile;alter system checkpoint;

38、修改内存

alter system set memory_max_target=0 scope=spfile;alter system set memory_target =0 scope=spfile;alter system set sga_max_size=13G scope=spfile;alter system set sga_target=13G scope=spfile;alter system set pga_aggregate_target=6G scope=spfile;

###########################################################

安装数据库报错:

/bin/dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1

$GRID_HOME/crs/install/roothas.pl -deconfig -force -verbose //GI回退 root.sh 脚本

###########################################################


39、停 oem 杀会话

停 oem 服务再停数据库/app/oms/bin/emctl start/stop omsPs -ef | grep -i local=no | grep -v grep | awk '{print $2}'| xargs kill -9

40、ASM 掉盘重新挂盘重启 crsd 进程

su - grid crsctl query crs softwareversion --查看集群版本asmcmdlsdgmount ocrexitcrsctl start res ora.crsd -init

41、Linux 6 下查看数据库有哪些 IP 连接进来

netstat -anop | grep ESTABLISHED | awk '$4 ~/:1521/'
--解压 10g 的 cpio 类型安装软件cpio -idmvc <*.cpio ----cpio解压

42、清理垃圾文件

cd &DIRfind ./ -ctime +3 |xargs rm查看大于20M的文件find / -type f -size +20M -print0 | xargs -0 du -h | sort -nr

43、查看备库进程:

set pages 9999set lines 200select process,client_process,sequence#,thread#,status from v$managed_standby;---mrp0当前正在应用的日志序列select process,status,sequence# from v$managed_standby;---查询dg应用情况set linesize 150; set pagesize 20; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

44、查询数据库负载 dbtime

set pages 9999set lines 200alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; SELECT * FROM ( SELECT A.INSTANCE_NUMBER, A.SNAP_ID, B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME, B.END_INTERVAL_TIME + 0 END_TIME, ROUND(VALUE - LAG( VALUE, 1 , '0') OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME" FROM (SELECT B.SNAP_ID, INSTANCE_NUMBER, SUM(VALUE ) / 1000000 / 60 VALUE FROM DBA_HIST_SYS_TIME_MODEL B WHERE B.DBID = (SELECT DBID FROM V$DATABASE) AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' )) GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A, DBA_HIST_SNAPSHOT B WHERE A.SNAP_ID = B.SNAP_ID AND B.DBID = (SELECT DBID FROM V$DATABASE) AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER) WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD') ORDER BY BEGIN_TIME;

=======================================================

本文 SQL 命令由于微信编辑器原因可能出现格式错误不能执行,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号后台回复 【SQL大全二】即可获取,点击下方公众号即可回复。

=======================================================

45、连接数相关查看

show parameter process--查看最大连接数select value from v$parameter where name ='processes'; --查两个节点连接数select INST_ID,count(*) from gv$session group by inst_id;--查看起库以来最大连接数select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');--查看并发连接数Select INST_ID,count(*) from gv$session where status='ACTIVE' group by inst_id;--查看不同用户的连接数select username,count(username) from v$session where username is not null group by username;

46、查当前的等待事件

col wait_class for a20set lines 200 pages 200col event for a60select event,count(*),wait_class from v$session_wait group by event,wait_class order by 3;

47、查看归档是否有错误

select dest_name,error from v$archive_dest;

48、查看数据库运行时间

select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') 启动时间,TRUNC(sysdate-(startup_time))||'天'||TRUNC(24*((sysdate-startup_time)-TRUNC(sysdate-startup_time)))||'小时'||MOD(TRUNC(1440*((SYSDATE-startup_time)-TRUNC(sysdate-startup_time))),60)||'分'||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-TRUNC(SYSDATE-startup_time))),60)||'秒' 运行时间from gv$instance;

49、查询dg同步延迟

select ceil((sysdate-next_time)*24*60) "M" from v$archived_log where applied='YES' AND SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE applied='YES');

50、查询数据库安装时间

select a.name,to_char(a.created,'yyyy.mm.dd'),b.instance_name from gv$database a,gv$instance b where a.inst_id=b.inst_id;

51、查看 sys 用户使用情况

select b.username,h.machine,to_char(h.sample_time,'yyyymmdd hh24:mi:ss') time,count(*) from dba_hist_active_sess_history h ,dba_users bwhere h.user_id=b.user_id and b.username in('SYSTEM','SYS') and h.MACHINE not in (select host_name from gv$instance)and h.sample_time>to_date('20210208','yyyymmdd')group by b.username,h.machine,to_char(h.sample_time,'yyyymmdd hh24:mi:ss');

52、查询 redo 每天切换次数

select trunc(FIRST_TIME,'dd'),count(1)from v$log_historywhere trunc(FIRST_TIME,'dd')>sysdate-10group by trunc(FIRST_TIME,'dd')order by 1;

53、查看每天归档产生数据量 归档量

--按天计算select trunc(FIRST_TIME,'dd') Time,sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024 Total_GB from v$archived_log group by trunc(FIRST_TIME,'dd') order by 1; --按小时计算select trunc(FIRST_TIME,'hh') Time,sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024 Total_GB from v$archived_log group by trunc(FIRST_TIME,'hh') order by 1;

54、redo 日志产生频率

select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round((b.first_time-a.first_time)*24*60,2) minutesfrom v$log_history a,v$log_history bwhere b.recid = a.recid+1 and to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') >=to_char(sysdate-10,'yyyy-mm-dd hh24:mi:ss') ;desc dba_hist_active_sess_history; ----可以查询执行过的sql的主机

55、AIX 查看僵尸进程

ps -ef | grep defunct

56、清理 LOCAL=NO 连接

ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |xargs kill -9

57、AIX 系统查看错误

errpt | head -20

58、rman 查看归档备份

rman target /list backup of archivelog all;

59、查看 rman 备份进度

set line 200 pages 1000col MESSAGE for a60col TARGET for a20select sid,SERIAL#,TARGET,START_TIME,ELAPSED_SECONDS,TIME_REMAINING,MESSAGE,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"from v$session_longopswhere 1=1 --and sid=2983 and SERIAL#=5and TIME_REMAINING>0 ;

60、查询创建用户的日期

select t.username, t.account_status, t.created, t.default_tablespace from DBA_USERS t where username= 'ABASE' order by t.created desc;

61、rman全库备份

run { allocate channel c1 type disk ; allocate channel c2 type disk ; allocate channel c3 type disk ; allocate channel c4 type disk ; backup as compressed backupset database format '/backup/%d_%I_%s_%p.bak'; backup as compressed backupset archivelog all format '/backup/%d_%I_%s_%p.arc'; backup current controlfile for standby format '/backup/%d_%I_%s_%p.ctl'; release channel c1; release channel c2; release channel c3; release channel c4;}

--压缩全库备份backup database format='/u01/app/backup/rman/eweaverstb_%s.bak' filesperset 4;run{backup as compressed backupset database format='/u01/app/backup/ewverstb_%s.bak' plus archivelog delete input skip inaccessible;}

62、数据泵导入导出相关命令

--expdp导入导出select * from dba_directories where directory_name ='DATA_PUMP_DIR';如果目录过小或者不存在,则create directory expdp_dir as '/u01/app/backup/expdp_dir';grant read,write on directory expdp_dir to public;expdp \'/ as sysdba\' directory=expdp_dir dumpfile=full_bak.dmp full=y;
impdp \'/ as sysdba\' directory=expdp_dir dumpfile=full_bak.dmp full=y;
impdb \'/ as sysdba\' DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
--使用 par 文件全库导出more full_expdp_query.pardirectory=exp_dir1logfile=full_new_exclude.logdumpfile=full_new_exclude_%U.dmp exclude=table:"in('LOG2017','LOG20210115')"query=GO.POLICYATTACHMENTS:"where createtime>=to_date('20200901','YYYYMMDD')"exclude=statisticsfull=y parallel=8cluster=N
nohup expdp \"/ as sysdba\" parfile=full_expdp_query.par & 
--导入more full_impdp_query.pardirectory=imp_dir1logfile=impdp_full_query.logdumpfile=full_expdp_query_%U.dmp CLUSTER=Nparallel=8
nohup impdp \"/ as sysdba\" parfile=full_impdp_query.par & expdp \'/ as sysdba\' directory=expdp_dir dumpfile=tbs_bak.dmp tables=(XXX,XXX)expdp \'/ as sysdba\' directory=expdp_dir dumpfile=tbs_bak.dmp tables=\(PROD.SU_UALCOMPEMRESPONINFO,PROD.SU_BASICINFO,PROD.SU_SENDXML,PROD.SU_SENDXMLBATCH\) fromuser=xxx touser=xxx log=tab.logexpdp \'/ as sysdba\' directory=TIF_DP  dumpfile=TSS_20190808.DMP logfile=TSS_20190809.log  fromuser=TSS touser=TSexpdp \'/ as sysdba\' directory=expdp_dir dumpfile=bbruser.dmp logfile=bbruser.log fromuser=bbruser touser=bbinfo
--只导出全库索引expdp \' / as sysdba \' directory=exp_dir dumpfile=index.dmp logfile=out_index.log cluster=n include=INDEX full=yExport: Release 11.2.0.3.0 - Production on Wed Feb 3 14:45:50 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=exp_dir dumpfile=index.dmp logfile=out_index.log cluster=n include=INDEX full=y Estimate in progress using BLOCKS method...Total estimation using BLOCKS method: 0 KBProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICSMaster table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_FULL_01 is: /oracle/soft/index.dmpJob "SYS"."SYS_EXPORT_FULL_01" successfully completed at 14:47:59
SQLFILE参数该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为SQLFILE=[directory_object:]file_name注意事项:1.directory_object可以不和impdp的DIRECTORY参数不一致,如果是一样,directory_object可以省略2.SQLFILE文件必须写入到磁盘之上,不能写入到ASM中3.SQLFILE和QUERY参数冲突,不能同时使用
--获取创建索引的 SQL impdp \' / as sysdba \' directory=exp_dir sqlfile=cre_index.sql dumpfile=index.dmp logfile=in_index.log cluster=n include=INDEXImport: Release 11.2.0.3.0 - Production on Wed Feb 3 15:06:30 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloadedStarting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=exp_dir dumpfile=index.dmp logfile=index.log cluster=n sqlfile=cre_index.sql include=INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEXProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICSJob "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 15:07:22
--获取创建索引的 SQL 语句set pages 9999set line 9999set long 9999SELECT dbms_lob.substr(dbms_metadata.get_ddl('INDEX',INDEX_NAME,'SCOTT'))||';'from dba_indexeswhere owner='SCOTT'; --导出序列expdp \' / as sysdba \' directory=exp_dir dumpfile=sequence.dmp logfile=out_sequence.log cluster=n include=sequence full=y
--删除序列在导入set line 120 pages 9999spool drop_sequence.sqlSELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';' FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='SCOTT';spool off;@drop_sequnece.sqlimpdp \' / as sysdba \' network_link=db_seq include=sequence
--按条件导出expdp \'/ as sysdba\' directory=exp_dir1 logfile=full_query2021.log dumpfile=full_query_polic_%U.dmp TABLES=ECARGO.POLICYATTACHMENTS query=\"where createtime \>= to_date\(\'20200901\',\'YYYYMMDD\'\)\"
impdp bbrinfo/8t4V~p5=Y DIRECTORY=expdp_dir dumpfile=bbruser.dmp REMAP_SCHEMA=bbruser:bbrinfoImport: Release 10.2.0.1.0 - 64bit Production on Friday, 09 August, 2019 14:43:03Copyright (C) 2003, 2005, Oracle.? All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsORA-39001: invalid argument valueORA-39000: bad dump file specificationORA-39142: incompatible version number 3.1 in dump file "/home/oracle/PICCATSSIX_20190808.DMP"
expdp \'/ as sysdba\' directory=exp_dir1 dumpfile=full_query2021_%U.dmp full=y logfile=full2021.log qurey= \"where policyattachments.createtime>= to_date('20200901','YYYYMMDD')\"
impdp scott/tiger ?network_link=test1? TABLES=scott.a? directory=backup? REMAP_TABLE=a:a1 Query="'Where HIREDATE<= to_date(''31-05-1981'', ''dd-mm-yyyy'') and HIREDATE>to_date(''31-01-1981'', ''dd-mm-yyyy'') '"? logfile=test.log ;
query=bi_dw.DW_MID_CONTRACT:"where data_date in(to_date('2013-11-13','yyyy-mm-dd'),to_date('2013-11-14','yyyy-mm-dd'),to_date('2013-8-13','yyyy-mm-dd'),to_date('2013-8-14','yyyy-mm-dd'))"
expdp \'/ as sysdba\' directory=exp_dir1 logfile=full_query2021.log dumpfile=full_query_%U.dmp TABLES=GO.POLICYATTACHME query=\"where createtime>= to_date('20200901','YYYYMMDD') \" parallel=8 cluster=N
源端数据库版本11.2.0.4.0,目标端数据库版本10.2.0.4.0,源端数据库版本高于目标端数据库版本,源端导出数据时加上目标端version:set lines 300col OWNER_NAME for a10col OPERATION for a15col JOB_MODE for a20col STATE for a15select * from dba_datapump_jobs;
expdp wxlun/wxlun123 tables=wxlun.WXLUN_TAB01,wxlun.WXLUN_TAB02 directory=expdp dumpfile=wxlun1212.dmp logfile=wxlun1212.log version=10.2.0.4.0impdp scott/tiger directory=expdp dumpfile=wxlun1212.dmp logfile=wxlun1212.log remap_schema=wxlun:scott REMAP_TABLESPACE=wxlun:USERS--cp冷备数据文件 控制文件 参数文件 v$datafile,v$controlfile;

63、查看历史备份

set line 200col START_TIME for a30col END_TIME for a30col status for a10select SESSION_KEY,INPUT_TYPE,STATUS,to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,to_char(END_TIME, 'yyyy-mm-dd hh24:mi') END_TIME,ELAPSED_SECONDS / 3600from v$rman_backup_job_detailswhere to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-12,'yyyy-mm-dd hh24:mi')order by SESSION_KEY;

64、查询数据库增量增长

select sum(TABLESPACE_USEDSIZE)*8/1024/1024,substr(rtime,0,10)from DBA_HIST_TBSPC_SPACE_USAGEwhere snap_id in (select max(snap_id) from DBA_HIST_TBSPC_SPACE_USAGE group by substr(rtime,0,10) ) --and TABLESPACE_ID=7group by substr(rtime,0,10) order by 1 ;--清空SHARED_POOL缓冲ALTER SYSTEM FLUSH SHARED_POOL ;--删除已备份归档rman target /delete archivelog all backed up 1 times to device type 'sbt_tape';关于密码错误验证延迟特性:密码错误验证延迟,可以通过设置EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" alter system set events='28401 trace name context forever, level 1'; ---不需要重启

65、数据库开启归档

su - oraclesqlplus / as sysdbaarchive log list;
1、归档路径①如果没有归档存放存储,需要操作系统挂载存储再继续操作②如果归档存储存在,但并非所想存放的实际路径,可在相关存储下创建目录mkdir -p /app/oracle/arch修改归档目录权限chown -R oracle:oinstall /app/oracle/arch
③如果归档存储存在,且路径正确,权限正确则可以直接使用
2、备份spfile文件create pfile from spfile;3、修改归档路径到想存放的位置,如查询出的归档路径已经是正确的位置,则不需要修改。su - oraclesqlplus / as sysdbaalter system set log_archive_dest_1='location=/app/oracle/arch' scope=spfile sid='*';4、重启数据库,开启/关闭归档模式(rac两个节点均关闭,使用一个节点修改,修改完成后启动另一个节点)shutdown immediate;startup mount;5、归档模式修改:开启归档模式:alter database archivelog;关闭归档模式:alter database noarchivelog;alter database open;如果为集群:需要停止两个节点,在一个节点操作完成后,拉起另一个节点,操作步骤如上,rac集群启动另一个节点:startup
6、查看数据库归档路径su - oraclesqlplus / as sysdbaarchive log list;切换日志,查看归档目录下产生归档文件alter system switch logfile;7、关闭归档shutdown immediate;startup mount;--开启归档模式:--alter database archivelog;关闭归档模式:alter database noarchivelog;

66、 RMAN 全备脚本部署

①RMAN全备脚本部署vi /home/oracle/full_backup.shORACLE_BASE=/app/oracleORACLE_HOME=/app/product/11.2.0/dbORACLE_SID=JiekeXu1db_name=JiekeXuPATH=$PATH:$ORACLE_HOME/bin/
v_date=`date +%Y%m%d`[ -d /backup/$db_name/$v_date ] || mkdir -p /app/backup/$db_name/$v_date
rman target /<<EOF > /app/backup/$db_name/$v_date/full_$v_date.logrun{allocate channel c1 device type disk;backup database format '/app/backup/%d/%T/full_%d_%T_%s_%p.bak';sql 'alter system archive log current';sql 'alter system archive log current';sql 'alter system archive log current';backup archivelog all delete input format '/app/backup/%d/%T/full_arch_%d_%T_%s_%p.bak';backup current controlfile format '/app/backup/%d/%T/full_controlfile_%U';backup spfile format '/app/backup/%d/%T/full_spfile_%d_%T_%s_%p.bak';release channel c1;}EOF
②赋权chmod +x full_backup.sh

关于 SQL 命令还有很多,由于篇幅就先写这么多,66 条六六大顺,剩余的有机会在分享。上述 SQL 命令由于微信编辑器原因可能出现格式错误不能执行,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号后台回复 【SQL大全二】 即可获取。


未完待续!!!


以下地址均可找到我:

————————————————————————————
公众号:JiekeXu之路
墨天轮:https://www.modb.pro/u/4347
CSDN :https://blog.csdn.net/JiekeXu
腾讯云:https://cloud.tencent.com/developer/user/5645107
————————————————————————————



Oracle 12c 及以上版本补丁更新说明及下载方法(收藏版)

一线运维 DBA 五年经验常用 SQL 大全(一)

Oracle 21C 新特性:数据泵相关新特性汇总

使用数据泵导出时遇到 ORA-27054 错误解决办法

案例|RAC 添加表空间误将数据文件放本地处理办法

11g RAC 在线存储迁移实现 OCR 磁盘组完美替换

震惊:Oracle 11gR2 RAC ADG 并没有高可用

如何通过 Shell 监控异常等待事件和活跃会话 

我的 OCM 之路|书写无悔青春追梦永不止步

Oracle 19c 之多租户 PDB 连接与访问(三)

Oracle 12C 最新补丁下载与安装操作指北

DBA 常用的软件工具有哪些(分享篇)?

深入了解 Oracle Flex ASM 及其优点

Oracle 11g 临时表空间管理

Oracle 每日一题系列合集

继续滑动看下一个

一线运维 DBA 五年经验常用 SQL 大全(二)

JiekeXu JiekeXu DBA之路
向上滑动看下一个

您可能也对以下帖子感兴趣

达梦数据库写文件的方式探索
每天5分钟PG聊通透第12期,为什么有的SQL会自动并行?
每天5分钟PG聊通透第10期,为什么有的索引不支持like查询?
Room数据库使用一些坑
PostgreSQL 17 正式发布, 要不要升?

文章有问题?点此查看未经处理的缓存