Mục đích: Các câu lệnh kiểm tra tổng thể DB 1 cách nhanh chóng, cùng các câu lệnh cao tải (bình thường lấy trong AWR)
-- db
SELECT DBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,
PLATFORM_NAME,CURRENT_SCN,FLASHBACK_ON,DB_UNIQUE_NAME, (select version from v$instance) version FROM v$database;
-- ctl
SELECT name FROM v$controlfile;
-- backup
select start_time start_time1, to_char(start_time,'DAY dd/mm/yyyy hh24:mi:ss') start_time, to_char(end_time,'DAY dd/mm/yyyy hh24:mi:ss') end_time, output_device_type, status, input_type, round(compression_ratio,2) compression_ratio,
input_bytes_display, output_bytes_display, time_taken_display
from v$rman_backup_job_details
where trunc(end_time)>=trunc(sysdate-7)
order by start_time1 desc;
-- redo
SELECT l.GROUP# group_no,l.thread# thread_no,l.sequence# sequence_no,l.BYTES/1024/1024 size_in_mb,l.MEMBERS logfile_member,l.status logfile_status,lf.member Log_member,NVL(lf.STATUS,'File in use') log_status
FROM v$logfile lf, v$log l WHERE l.GROUP#=lf.GROUP# ORDER BY l.GROUP#;
-- archive_log
select trunc(completion_time) completion_time, round(sum(blocks*block_size)/1024/1024/1024,2) archive_log_in_gb from V$ARCHIVED_LOG
where trunc(completion_time) >= sysdate - 7
--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
and dest_id=1
group by trunc(completion_time)
order by trunc(completion_time) desc;
-- asm
select name, sector_size, block_size, state, type, round(total_mb/1024,2) total_gb , round(free_mb/1024,2) free_gb,
round(hot_used_mb/1024,2) hot_used_gb, round(cold_used_mb/1024,2) cold_used_gb, round(required_mirror_free_mb/1024,2) required_mirror_free_gb,
round(usable_file_mb/1024,2) usable_file_gb, voting_files
from gv$asm_diskgroup where inst_id = 1 and state not in ('CLOSED','DISMOUNTED') order by name;
-- tbs
select
a.tablespace_name,
round( a.bytes_alloc/(1024*1024*1024)) Total_in_GB,
round(a.physical_bytes/(1024*1024*1024)) Max_total_in_GB,
round(nvl(b.tot_used,0)/(1024*1024*1024)) Used_in_GB,
round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) used_percentage,
(select status from dba_tablespaces where tablespace_name = a.tablespace_name) status
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
(select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%'
order by 5 desc;
-- schm
SELECT owner, size_in_gb FROM
(SELECT owner, ROUND(SUM(bytes)/1024/1024/1024) size_in_gb FROM dba_segments
WHERE owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX',
'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM',
'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')
GROUP BY owner
ORDER BY 2 DESC)
WHERE size_in_gb > 50;
-- sgm
SELECT segment_type, ROUND(SUM(bytes)/1024/1024/1024) Size_GB
FROM dba_segments
GROUP BY segment_type
ORDER BY 2 DESC;
-- table
SELECT owner, table_name,table_type, num_part, num_rows, table_size, tablespace_name,last_analyzed FROM
(SELECT owner, table_name,table_type, num_part, num_rows, table_size, tablespace_name,last_analyzed FROM
(
SELECT owner,table_name,'NON PARTITIONED' table_type, 1 num_part, nvl(num_rows,0) num_rows,
nvl(ROUND(NVL(blocks,0)*(select to_number(block_size) from dba_tablespaces where tablespace_name = a.tablespace_name)/1024/1024/1024),0) table_Size,
tablespace_name, last_analyzed
FROM dba_tables a
WHERE partitioned = 'NO' and
owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW',
'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')
union
select table_owner, table_name, 'PARTITIONED' table_type, count(1) num_part, sum(num_rows) num_rows, round(sum(part_size)/1024/1024/1024,2) table_size, '' tablespace_name,
max(last_analyzed) last_analyzed
from
(select table_owner, table_name, nvl(num_rows,0) num_Rows,
nvl(NVL(blocks,0)*(select to_number(block_size) from dba_tablespaces where tablespace_name = b.tablespace_name),0) part_size, last_analyzed
from dba_tab_Partitions b where table_owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW',
'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB'))
group by table_owner, table_name
)
ORDER BY table_Size DESC)
WHERE rownum <=20;
-- index
SELECT index_size,Ind_name,Tab_name,tablespace_name, Owner,Status,Analyzed FROM
(SELECT ROUND(sum(s.bytes)/1024/1024/1024,2) index_size, i.owner Owner, i.index_name Ind_name, i.tablespace_name, i.table_name Tab_name, i.status Status, max(i.last_analyzed) Analyzed
FROM dba_indexes i, dba_segments s
WHERE
i.owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')
AND
i.last_analyzed NOT NULL
AND
i.index_name=s.segment_name
AND
i.owner=s.owner
group by i.owner, i.index_name, i.tablespace_name, i.table_name, status
ORDER BY 1 DESC)
WHERE rownum<=20;
-- invalid_obj
select owner, object_name, owner || '.' ||object_name full_name, object_type, to_char(last_ddl_time,'dd/mm/yyyy hh24:mi:ss') last_ddl_time,status
From dba_objects
where status <> 'VALID' and owner in ('TEST_OWNER')
order by owner, object_name;
-- hit_ratio
SELECT a.inst_id, ROUND((1-(a.physical_reads)/((a.db_block_gets+a.consistent_gets)))*100,3) p_pool_hit_ratio,
ROUND((1-(sum(b.getmisses)/(sum(b.gets)+sum(b.getmisses)))) * 100,3) p_Dictionary_cache_hit_ratio,
ROUND((SUM(c.pins)/(SUM(c.pins)+SUM(c.reloads)))*100,3) p_Lib_Cache_Hit_Ratio , ROUND((sum(c.pinhits)/sum(c.pins))*100,3) p_Lib_Cache_Pin_Hit_Ratio
FROM gv$buffer_pool_statistics a, gv$rowcache b, gv$librarycache c
WHERE a.db_block_gets + a.consistent_gets > 0 and b.gets + b.getmisses <> 0 and a.inst_id = b.inst_id and a.inst_id = c.inst_id
group by a.inst_id,ROUND((1-(a.physical_reads)/((a.db_block_gets+a.consistent_gets)))*100,3) order by 1;
-- top_cpu
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY cpu_time DESC)
WHERE rownum <=20;
-- top_buffer
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY buffer_gets DESC)
WHERE rownum <=20;
-- top_disk
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum <=20;
-- top_exec
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY executions DESC)
WHERE rownum <=20;
Hy vọng hữu ích cho bạn.
=============================* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty
