--cursor c_db is
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;
-- cursor c_ctl is
SELECT name FROM v$controlfile;
--cursor c_backup is
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;
--cursor c_redo is
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 is in use') log_status
FROM v$logfile lf, v$log l WHERE l.GROUP#=lf.GROUP# ORDER BY l.GROUP#;
--cursor c_archive_log is
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;
--cursor c_asm is
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;
--cursor c_tbs is
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;
--cursor c_schm is
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;
--cursor c_sgm is
SELECT segment_type, ROUND(SUM(bytes)/1024/1024/1024) Size_GB
FROM dba_segments
GROUP BY segment_type
ORDER BY 2 DESC;
--cursor c_table is
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;
cursor c_index is
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 IS 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;
cursor c_invalid_obj is
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 ('CDR_OWNER','KHUYENMAI','BAOCAO','BILL_CHECK')
order by owner, object_name;
cursor c_hit_ratio is
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;
--SQL CHIEM TAI
--cursor c_top_cpu is
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;
--cursor c_top_buffer is
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;
--cursor c_top_disk is
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;
--cursor c_top_exec is
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;
--Them
--Cost cao
SELECT * FROM
(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id,physical_read_bytes, physical_read_requests, optimizer_cost, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
where optimizer_cost is not null
ORDER BY optimizer_cost DESC)
WHERE rownum <=20;
select * from v$sqlarea;
Website không bao giờ chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- 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
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 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: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Đị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
=============================
[VIP5] Tìm câu lệnh SQL chiếm tải trong Oracle Database, oracle tutorial, 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,sql tutorial, khóa học pl/sql tutorial, 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 RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
