首页 1 2 3 4 5 6 7

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

一线运维 DBA 五年经验常用 SQL 大全（一）
1.查看表空间使用率。
set line 220
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct
from (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
1.1查询单个表空间使用率。
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space where tablespace_name=‘TBL_SPACE’ group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files where tablespace_name=‘TBL_SPACE’ group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
2.查看临时表空间数据文件位置，大小，及是否自动扩展。
select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_data_files where tablespace_name in (’’) order by tablespace_name;
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
–查看所有临时表空间大小
SELECT D.TABLESPACE_NAME,SPACE “SUM_SPACE(M)”,BLOCKS SUM_BLOCKS,
USED_SPACE “USED_SPACE(M)”,ROUND(NVL(USED_SPACE,0)/SPACE100,2) “USED_RATE(%)”,
NVL(FREE_SPACE,0) “FREE_SPACE(M)”
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(10241024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(10241024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(10241024),2) FREE_SPACE
FROM V $TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+); 3.查看ASM磁盘空间。 select name,state,type,free_mb,total_mb,usable_file_mb from v$

54.杀应用连接
ps -ef |grep LOCAL=NO |awk ‘{print $2}’|xargs kill -9

55.查询某个时间点的scn
select timestamp_to_scn(to_timestamp(‘2021-01-06 10:00:00’,‘yyyy-mm-dd hh24:mi:ss’)) from dual;
56.闪回到某个scn点：（闪回前请先进行备份）
alter table tb_flash1 enable row movement
flashback table tb_flash1 to 115398523;
57.查询数据库最近一次重启时间
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
select startup_time from v $instance; 58.查看日志每小时文件切换频率 column h0 format 999 column h1 format 999 column h2 format 999 column h3 format 999 column h4 format 999 column h5 format 999 column h6 format 999 column h7 format 999 column h8 format 999 column h9 format 999 column h10 format 999 column h11 format 999 column h12 format 999 column h13 format 999 column h14 format 999 column h15 format 999 column h16 format 999 column h17 format 999 column h18 format 999 column h19 format 999 column h20 format 999 column h21 format 999 column h22 format 999 column h23 format 999 column avg format 999.99 column day format a6 SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", to_char(ROUND (COUNT (1) / 24, 2),'fm99999999990.00') "Avg" FROM gv$

select ‘alter system kill session ‘’’||sid||’,’||serial#||’’’; 'from v $session where SID in (select BLOCKER_SID from v$

本文 SQL 命令由于编辑器原因可能出现格式错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号【JiekeXu之路】扫描最下方二维码后台回复【SQL大全】即可获取。
1, 清除 column 格式
clear columns
2, 创建永久性表空间
create tablespace myspace
datafile ‘文件路径’
size 文件大小
autoextend on next 自动增加大小
maxsize 文件最大值
语法说明：
1>，temporary|undo（创建撤销表空间）
2>，teblespace_name
3>，datafile|tempfile’file_name’
4>，size
5>，reuse 若存在，则删掉并重新创建
6>，autoextend off|on
7>, next number 自动扩展的大小
8>，maxsize unlimited|number 指定数据文件最大大小
9>，mininum extent number 盘区可以分配到的最小尺寸
10>，blocksize number 设置数据块大小
11>，online|offline
12>，logging|nologging
13>，force logging 强制表空间数据库对象任何操作都产生日志，否定 12
14，default storage storage 指定保存在表空间中的数据库对象默认存储参数
15>，compress|nocompress 是否压缩数据（消除列中的重复值）
16>，permanent|temporary 指定表空间中数据的保存形式
17>，extent management dictionary（数据字典形式管理）|local（本地化形式管理）
18>，autoallocate|uniform size number 右边为指定表中盘区大小
19>，segment space management auto |manual 指定表空间中段的管理方式
3，查看表空间属性
SELECT * FROM dba_tablespace wheretablespace_name = ‘表空间名’;
4, 修改表空间状态、名字
alter tablespace 表空间名表空间状态;
alter tablespace 表空间名 1 rename to 表空间名 2;
5，查看闪回区、快速恢复区空间使用率
select sum(percent_space_used)||’%’ “已使用空间比例”
from V $RECOVERY_AREA_USAGE; 或 select round(100(a.space_used/space_limit),2)||'%' "已使用空间比例",a. from v$

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

手工创建快照的语句,AWR设置每隔30分钟收集一次报告，保留40天的报告
exec dbms_workload_repository.create_snapshot;
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>402460, interval=>30);
select * from dba_hist_wr_control;
AWR基线查看和创建
select * from dba_hist_baseline;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>‘am_baseline’);
导出AWR报告的SQL语句
select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
导出最新ADDM的报告（需要sys用户）
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks
where task_id =(
select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where
t.task_id=l.task_id and t.advisor_name=‘ADDM’ and l.status=‘COMPLETED’ );
select task_id,task_name,description from dba_advisor_tasks order by 1 desc
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id =XX
查询Rman备份集详细信息（未过期的，过期并已删除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
‘’, DECODE (BACKUP_TYPE, ‘L’, ‘Archivelog’, ‘Full’),
1, ‘Incr-1级’,
0, ‘Incr-0级’,
B.INCREMENTAL_LEVEL)
“Type LV”,
B.CONTROLFILE_INCLUDED “包含CTL”,
DECODE (A.STATUS,
‘A’, ‘AVAILABLE’,
‘D’, ‘DELETED’,
‘X’, ‘EXPIRED’,
‘ERROR’)
“STATUS”,
A.DEVICE_TYPE “Device Type”,
A.START_TIME “Start Time”,
A.COMPLETION_TIME “Completion Time”,
A.ELAPSED_SECONDS “Elapsed Seconds”,
A.BYTES/1024/1024/1024 “Size(G)”,
A.COMPRESSED,
A.TAG “Tag”,
A.HANDLE “Path”
FROM GV $BACKUP_PIECE A, GV$

把XXX用户下面的某些YYY表赋权给user,XXX\YYY要大写
set serveroutput on
–XXX要大写
declare tablename varchar2(200);
begin
for x IN (SELECT * FROM dba_tables where owner=‘XXX’ and table_name like ‘%YYY%’) loop
tablename:=x.table_name;
dbms_output.put_line(‘GRANT SELECT ON XXX.’||tablename||’ to user’);
EXECUTE IMMEDIATE ‘GRANT SELECT ON XXX.’||tablename||’ TO user’;
end loop;
end;

一线运维 DBA 五年经验常用 SQL 大全（二）
1、查看数据文件信息：
col file_name for a55
select tablespace_name,file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE from dba_data_files where tablespace_name=‘AM_DATA’;
alter tablespace UNDOTBS1 add datafile ‘+DATA’ size 30G;

2、查看ASM磁盘组信息：
select 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 999
col PATH for a33
col NAME for a15
col FAILGROUP for a15
select 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 FROM
DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4;

5、查某个表空间内的前五个大表
col TABLE_NAME for a30
set pagesize 200
set linesize 200
col TABLE_NAME for a30
set linesize 200
select * 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_MB
from 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 11136
SYS XXXXX_ORIGINNOTE_IX 9152
SYS XXXXX_ABSTRACT_IX 6388
SYS 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 INDEX
SYS XXXXX_ORIGINNOTE_IX INDEX
SYS XXXXX_ABSTRACT_IX INDEX
SYS XXXMENTS_RECNAME_RB_IX INDEX

6、查看shared_pool的大小
select sum(bytes)/1024/1024/1024 from v

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 a55
select 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_time
FROM v $locked_object l, all_objects o, v$

查出锁定表的 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 $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|more
lparstat -i
ortconf
AIX 磁盘扫描命令
root 执行
cfgmgr -v

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

AIX 查看错误日志并输出到 messages
JIekeXuY1:/#errpt -dH
IDENTIFIER TIMESTAMP T C RESOURCE_NAME DESCRIPTION
DE3B8540 1012214419 P H hdisk18 PATH HAS FAILED
4B436A3D 1012214119 T H fscsi0 LINK ERROR
4B436A3D 1012214119 T H fscsi0 LINK ERROR
4B436A3D 1012214119 T H fscsi0 LINK ERROR

/bin/errpt -a > messages.out
你要查那些hdisk的?lunid
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 hdisk0

— 查看磁盘的详细信息
lsattr -El hdisk0

HP：machinfo
（diskinfo /dev/rdisk/disk* 查看磁盘大小）

WIN：msinfo32

SUSE：cat /proc/cpuinfo （model name ）

—查看资源使用情况：
HP：glance/top
AIX：nmon/topas

–AIX查看版本号
oslevel
JIEKEXuR2:/app/product/11.2.0/grid/network/log$oslevel
6.1.0.0

—查看内存大小：
HP： /usr/contrib/bin/machinfo | grep -i Memory
AIX: /usr/sbin/lsattr -E -l sys0 -a realmem

—查看swap分区：
HP：/usr/sbin/swapinfo -a
AIX：/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 500
col USERNAME for a15
col ACCOUNT_STATUS for a15
select 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 &DIR
find ./ -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/hdisk322
lsattr: 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: 0x00
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt: 1 ; 0x003: 0x01
kfbh.block.blk: 0 ; 0x004: blk=0
kfbh.block.obj: 2147483953 ; 0x008: disk=305
kfbh.check: 3956950460 ; 0x00c: 0xebda45bc
kfbh.fcn.base: 2632 ; 0x010: 0x00000a48
kfbh.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 hdisk16
60
— 检查PVID
lspv | 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 |wc
svmon -PO unit=GB |grep oracle |wc

lsvg |lsvg -i -p
lsdev -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 @$

关于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 2000
col g_name format a10
col g_n format 99
col d_n format 999
col m_status format a7
col mo_status format a7
col h_status format a11
col name format a20
col path format a20
col failgroup format a15
select 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_date
from v$asm_disk g
order by g_n, d_n

28、查看某个用户所拥有的角色
select * from dba_role_privs where grantee='JXJL_NEW_QRY ';

29、查看某个角色所拥有的权限
select * from dba_sys_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 -x
DDS同步软件类似于OGG的同步软件
10.10.19X.XXX localhost
10.10.19X.XX JiekeADG
JiekeADG:/app/oracle/diag/rdbms/picccash/picccash/trace$
hostname:/#su - ddsdt
JiekeADG:/ddssoftware/ddt/ddsdt/dt $KaTeX parse error: Expected 'EOF', got '#' at position 1963: \dots 0-N 1-N SRC#̲0 : SO\dots$

===================================================================================================
关于 SQL 命令还有很多，由于篇幅就先写这么多，有机会在分享。上述 SQL 命令由于编辑器原因可能出现格式
错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号 JiekeXu DBA之路
【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 oms
Ps -ef | grep -i local=no | grep -v grep | awk ‘{print $2}’| xargs kill -9

40、ASM 掉盘重新挂盘重启 crsd 进程
su - grid
crsctl query crs softwareversion --查看集群版本
asmcmd
lsdg
mount ocr
exit
crsctl start res ora.crsd -init

41、Linux 6 下查看数据库有哪些 IP 连接进来
netstat -anop | grep ESTABLISHED | awk ‘$4 ~/:1521/’
–解压 10g 的 cpio 类型安装软件
cpio -idmvc <*.cpio //cpio解压

42、清理垃圾文件
cd &DIR
find ./ -ctime +3 |xargs rm
查看大于20M的文件
find / -type f -size +20M -print0 | xargs -0 du -h | sort -nr

43、查看备库进程：
set pages 9999
set lines 200
select 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、查询数据库负载
set pages 9999
set lines 200
alter 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$

本文 SQL 命令由于微信编辑器原因可能出现格式错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，
如有需要的小伙伴们在本公众号【JiekeXu DBA之路】扫描最下方二维码后台回复【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 a20
set lines 200 pages 200
col event for a60
select 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)2460) “M” from v $KaTeX parse error: Expected 'EOF', got '#' at position 46: \dotsS' AND SEQUENCE#̲=(SELECT MAX(SE\dots$ ARCHIVED_LOG WHERE applied=‘YES’);

50、查询数据库安装时间
select a.name,to_char(a.created,‘yyyy.mm.dd’),b.instance_name from 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 b
where 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_history
where trunc(FIRST_TIME,‘dd’)>sysdate-10
group 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)2460,2) minutes
from v $log_history a,v$

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 1000
col MESSAGE for a60
col TARGET for a20
select sid,SERIAL#,TARGET,START_TIME,ELAPSED_SECONDS,TIME_REMAINING,MESSAGE,ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”
from v$session_longops
where 1=1 --and sid=2983 and SERIAL#=5
and 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.par
directory=exp_dir1
logfile=full_new_exclude.log
dumpfile=full_new_exclude_%U.dmp
exclude=table:“in(‘LOGSAVE2017’,‘LOGSAVE_20210115’)”
query=ECARGO.POLICYATTACHMENTS:“where createtime>=to_date(‘20200901’,‘YYYYMMDD’)”
exclude=statistics
full=y
parallel=8
cluster=N

nohup expdp “/ as sysdba” parfile=full_expdp_query.par &

–导入
more full_impdp_query.par
directory=imp_dir1
logfile=impdp_full_query.log
dumpfile=full_expdp_query_%U.dmp
CLUSTER=N
parallel=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=(YHZXPROD.SU_ACTUALCOMPEMRESPONINFO,YHZXPROD.SU_BASICINFO,YHZXPROD.SU_SENDXML,YHZXPROD.SU_SENDXMLBATCH) fromuser=xxx touser=xxx log=tab.log

expdp ‘/ as sysdba’ directory=ANTIF_DP dumpfile=PICCATSSIX_20190808.DMP logfile=PICCATSSIX_20190809.log fromuser=PICCATSSIX touser=ATS

expdp ‘/ 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=y
Export: Release 11.2.0.3.0 - Production on Wed Feb 3 14:45:50 2021

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYS”.“SYS_EXPORT_FULL_01”: “ FORCE_MATCHING_SIGNATURE from v $bh ORDER BY tch DESC) WHERE ROWNUM < 11) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk; 29、查询 7 天的 db time TH sysstat AS (select sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time, ss.stat_name stat_name, ss.value e_value, lag(ss.value, 1) over(order by ss.snap_id) b_value from dba_hist_sysstat ss, dba_hist_snapshot sn where trunc(sn.begin_interval_time) >= sysdate - 7 and ss.snap_id = sn.snap_id and ss.dbid = sn.dbid and ss.instance_number = sn.instance_number and ss.dbid = (select dbid from v$

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDO freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 --备注：
#freq=daily：每天收集；
#;byday=SUN：一周之内的星期，例如这里是星期日；
#byhour=22 ：每天的时间点时，这里是 22 时；
#byminute=0：每天的的时间点分，这里是 0 分，则第 1 分；
#bysecond=0：每天的时间点秒，这里是 0 秒，则第 1 秒；
#+000 20:00:00 ：表示收集信息的时间区间长，这里表示 20 小时。
—修改自动收集统计信息计划任务时间：
–首先停止原来计划;
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“THURSDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“MONDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“TUESDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“WEDNESDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“FRIDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“SATURDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“SUNDAY_WINDOW”’);
end;
/
–修改计划任务的执行时间：
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“THURSDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“MONDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“TUESDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“WEDNESDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“FRIDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“SATURDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“SUNDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“SATURDAY_WINDOW”’,
attribute => ‘DURATION’,
value =>
‘+000 04:00:00’);
end;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“SUNDAY_WINDOW”’,
attribute => ‘DURATION’,
value =>
‘+000 04:00:00’);
end;
/
–启用新的计划任务的执行时间：
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“THURSDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“MONDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“TUESDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“WEDNESDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“FRIDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“SATURDAY_WINDOW”’);
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“SUNDAY_WINDOW”’);
end;
/
禁用 SQL TUNING TASK
BEGIN
dbms_auto_task_admin.disable(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/
BEGIN
dbms_auto_task_admin.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/
32.非常详细的查看表空间使用率
SET PAGESIZE 9999 LINESIZE 180;
TTI ‘Tablespace Usage Status’
COL TABLESPACE_NAME FOR A20;
COL TBS_MAX_SIZE FOR 99999.99;
COL TABLESPACE_SIZE FOR 99999.99;
COL TBS_AVABLE_SIZE FOR 999999.99;
COL “USED_RATE(%)” FOR A16;
COL “ACT_USED_RATE(%)” FOR A16;
COL “FREE_SIZE(GB)” FOR 99999999.99;
SELECT UPPER(F.TABLESPACE_NAME)
AS “TABLESPACE_NAME”, ROUND(D.MAX_BYTES,2)
AS “TBS_MAX_SIZE” , ROUND(D.AVAILB_BYTES ,2)
AS “ACT_TABLESPACE_SIZE”, ROUND((D.AVAILB_BYTES - F.USED_BYTES),2)
AS “TBS_USED_SIZE”, ROUND(F.USED_BYTES, 2)
AS “FREE_SIZE(GB)”, TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100, 2),
‘999.99’)
AS “USED_RATE(%)”, TO_CHAR(ROUND((D.AVAILB_BYTES -
F.USED_BYTES)/D.MAX_BYTES100, 2),
‘999.99’)
AS “ACT_USED_RATE(%)”, ROUND(D.MAX_BYTES - D.AVAILB_BYTES
+USED_BYTES,2) AS “TBS_AVABLE_SIZE”
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 *
1024), 6) USED_BYTES, ROUND(MAX(BYTES) / (1024 * 1024 *
1024), 6) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024 *
1024), 6) AVAILB_BYTES, ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(10241024*1024),6)
MAX_BYTES
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY “ACT_USED_RATE(%)” DESC;
vim get_tablespace_used_v3.sql
set pagesize 1000 linesize 180
tti ‘Tablespace Usage Status’
col “TOTAL(GB)” for 99,999,999.999
col “USAGE(GB)” for 99,999,999.999
col “FREE(GB)” for 99,999,999.999
col “EXTENSIBLE(GB)” for 99,999,999.999
col “MAX_SIZE(GB)” for 99,999,999.999
col “FREE PCT %” for 999.99
col “USED PCT OF MAX %” for 999.99
col “NO_AXF_NUM” for 9999
col “AXF_NUM” for 999
select d.tablespace_name “TBS_NAME”
,d.contents “TYPE”
,nvl(a.bytes /1024/1024/1024,0) “TOTAL(GB)”
,nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024/1024
“USAGE(GB)”
,nvl(f.bytes,0)/1024/1024/1024 “FREE(GB)”
,nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) “FREE
PCT %”
,nvl(a.ARTACAK,0)/1024/1024/1024
“EXTENSIBLE(GB)”
,nvl(a.MAX_BYTES,0)/1024/1024/1024
“MAX_SIZE(GB)”
,nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes +
nvl(a.ARTACAK,0)) * 100,0) “USED PCT OF MAX %”
,a.NO_AXF_NUM
,a.AXF_NUM
from sys.dba_tablespaces d, (select tablespace_name
,sum(bytes) bytes
,sum(decode(autoextensible,‘YES’,maxbytes - bytes,0 ))
ARTACAK
,count(decode(autoextensible,‘NO’,0))
NO_AXF_NUM
,count(decode(autoextensible,‘YES’,0)) AXF_NUM
,sum(decode(maxbytes, 0, BYTES, maxbytes))
MAX_BYTES
from dba_data_files
group by tablespace_name
) a,
(select tablespace_name
,sum(bytes) bytes
from dba_free_space
group by tablespace_name
) f
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and not (d.extent_management like ‘LOCAL’and d.contents
like ‘TEMPORARY’)
union all
select d.tablespace_name “TBS_NAME”
,d.contents “TYPE”
,nvl(a.bytes /1024/1024/1024,0) “TOTAL(GB)”
,nvl(t.bytes,0)/1024/1024/1024 “USAGE(GB)”
,nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024/1024
“FREE(GB)”
,nvl(t.bytes/a.bytes * 100,0) “FREE PCT %”
,nvl(a.ARTACAK,0)/1024/1024/1024
“EXTENSIBLE(GB)”
,nvl(a.MAX_BYTES,0)/1024/1024/1024
“MAX_SIZE(GB)”
,nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0)
“USED PCT OF MAX %”
,a.NO_AXF_NUM
,a.AXF_NUM
from sys.dba_tablespaces d, (select tablespace_name
,sum(bytes) bytes
,sum(decode(autoextensible,‘YES’,MAXbytes - bytes,0 )) ARTACAK
,count(decode(autoextensible,‘NO’,0)) NO_AXF_NUM
,count(decode(autoextensible,‘YES’,0)) AXF_NUM
,sum(decode(maxbytes, 0, BYTES, maxbytes))
MAX_BYTES
from dba_temp_files
group by tablespace_name
) a, (select tablespace_name
, sum(bytes_used) bytes
from v $KaTeX parse error: Expected 'EOF', got '&' at position 519: \dotsNC (SYSDATE) - &̲days_back AND s\dots$ session a, v $a.sql_id = b.sql_id and a.sql_child_number = b.child_number and sql_text not like '%from v$