Dear Readers, In this article, we will see the following Internal mechanism of Buffer Cache. Buffer cache management : 👉 Locating free blocks (Data to write) 👉 Finding data blocks (Data to read) 👉 Managing LRU lists (Flushing least recently used blocks) 👉 Cleaning dirty… Read More
Dear Readers, In this article, we will see the following Check Partition Summary More than N Days (60 Days). Script :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
cat parti_last_60days.sh #!/bin/bash #Script to Check Partition In last 60 Days #./Partition Report.sh DEMO ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` rm /tmp/part1.log /tmp/part.log touch /tmp/part1.log export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1' export ORACLE_SID='ORCL11' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexpert@ktexperts.com' export LOG='/tmp' _OWNER=$1 function sql_script { echo " " echo "Started To Run Partition Check Sql Query" echo " " sqlplus vinod/vinod <<EOF > /tmp/t.log SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0 spool /tmp/schema.log select table_name from dba_tables where owner='$_OWNER'; spool off; EOF sed -i '1d' /tmp/schema.log sed -i '$d' /tmp/schema.log wc=`wc -l /tmp/schema.log |awk '{ print $1 }'` for (( c=1; c<=$wc; c++ )) do _tbls=`sed ''$c'!d' /tmp/schema.log` _tbls1=` echo -n "${_tbls//[[:space:]]/}"` if [ "$_tbls1" ]; then echo "Checking Partition details on $_tbls1@$_OWNER" sqlplus vinod/vinod <<EOF > /tmp/t.log set line 2000 pages 2000 SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on col OBJECT_NAME for a40 col SUBOBJECT_NAME for a40 col OWNER for a30 col CREATED for a30 spool /tmp/part.log; SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4; spool off EOF fi sed -i '1d' /tmp/part.log sed -i '$d' /tmp/part.log #echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log if [ ! -s /tmp/part.log ]; then #echo " " >> /tmp/part1.log #echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log #echo " " >> /tmp/part1.log _var='FALSE' else echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log echo " " >> /tmp/part1.log echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log cat /tmp/part.log >> /tmp/part1.log fi done echo " " echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------" } function send_mail { cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST} } #MAIN sql_script send_mail |
Out Put :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
################ Partition Check on DEPART table of DEMO schema 60 Days Back ################# ------------------------------------------------------------------------------------------------------------------------------------------- TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE ------------------------------------------------------------------------------------------------------------------------------------------- DEPART SYS_P21641 DEMO 11-JUN-20 DEPART SYS_P13521 DEMO 27-JUL-20 DEPART SYS_P13530 DEMO 28-JUL-20 DEPART SYS_P13587 DEMO 29-JUL-20 DEPART SYS_P13631 DEMO 04-AUG-20 DEPART SYS_P13668 DEMO 05-AUG-20 DEPART SYS_P13768 DEMO 06-AUG-20 DEPART SYS_P13856 DEMO 19-AUG-20 DEPART SYS_P13894 DEMO 20-AUG-20 DEPART SYS_P13928 DEMO 11-SEP-20 DEPART SYS_P14004 DEMO 12-SEP-20 DEPART SYS_P14067 DEMO 14-SEP-20 DEPART SYS_P14072 DEMO 15-SEP-20 ################ Partition Check on EMPBKP table of DEMO schema 60 Days Back ################# ------------------------------------------------------------------------------------------------------------------------------------------- TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE ------------------------------------------------------------------------------------------------------------------------------------------- EMPBKP SYS_P21661 DEMO 11-JUN-20 EMPBKP SYS_P13561 DEMO 28-JUL-20 EMPBKP SYS_P13632 DEMO 04-AUG-20 EMPBKP SYS_P13672 DEMO 05-AUG-20 EMPBKP SYS_P13772 DEMO 06-AUG-20 EMPBKP SYS_P13857 DEMO 19-AUG-20 EMPBKP SYS_P13903 DEMO 20-AUG-20 EMPBKP SYS_P13949 DEMO 11-SEP-20 EMPBKP SYS_P14009 DEMO 12-SEP-20 |
Thank you for giving your valuable time to read the above information. Follow us on Website … Read More
Dear Readers, In this article, we will see the Daily Tips for Exadata. Tip-2 : Date: 12-Dec-20 Provided By (Linkedin): Ankush Chawla 👉 Exadata patches following components 👉 DB Home 👉 Grid Home 👉 DB Server (patchmgr)… Read More
Dear Readers, In this article, we will see the Daily Tips for GoldenGate. Tip-1 : Date: 12-Dec-20 Provided By (Linkedin): Ankush Chawla LogDump is a popular utility to troubleshoot GoldenGate issues: It allows to : 👉 Read… Read More
Dear Readers, In this article, we will see TableSpace Menu Bar in SecureCRT . TBS_FREE :
|
1 2 3 |
set linesize 130 set pagesize 1000 col tablespace format a30 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select tablespace_name tablespace, round(sum(a.total1)/1048576, 0) Total, round(sum(a.total1)/1048576, 0)-round(sum(a.sum1)/1048576, 0) used, round(sum(a.sum1)/1048576, 0) free, round(((sum(a.sum1)/1048576)/(sum(a.total1)/1048576))*100, 0) pct_free, round(sum(a.maxb)/1048576, 0) largest, max(a.cnt) fragment from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB, count(bytes) cnt from dba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by 5 desc; |
Out Put :
|
1 2 3 4 5 6 |
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENT ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- USERS 5 2 3 65 3 1 UNDOTBS1 60 24 36 59 25 13 SYSAUX 730 690 40 5 38 14 SYSTEM 800 792 8 1 8 2 |
TBS_FILE :
|
1 2 3 4 |
set linesize 130 set pagesize 1000 set echo on col file_name format a80 |
|
1 2 3 4 |
select file_id,file_name, bytes/1048576 mb,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAX_BYTES_GB from dba_data_files where tablespace_name='&tbs' order by substr(file_name,length(file_name)-5,6); |
SEGMENTBYNAME :
|
1 2 3 4 5 6 7 8 9 10 11 |
set ver off set trimspool on set lines 199 col owner for a15 col segment_name for a30 col segment_type for a25 col tablespace_name for a22 set pagesize 150 col part_name for a30 compu sum of size_mb on report break on report skip 2 |
|
1 2 3 4 5 |
select owner, segment_name,partition_name part_name, buffer_pool, segment_type, tablespace_name, extents, round(bytes/1024/1024) size_mb from dba_segments where segment_name like upper('&segname'); |
DBSIZE :
|
1 2 3 4 |
select sum(bytes)/1024/1024/1024 GB from dba_segments; GB ---------- 1.46954346 |
|
1 2 3 4 |
select sum(bytes)/1024/1024/1024 GB from dba_data_files; GB ---------- 1.55761719 |
|
1 2 3 4 |
select sum(bytes)/1024/1024/1024 GB from dba_temp_files; GB ---------- .05859375 |
Seg_With_Highest_IO_Reads :
|
1 2 3 4 |
set pagesize 200 set linesize 300 col segment_name format a40 col owner format a10 |
|
1 2 3 4 5 6 7 |
select segment_name,object_type,total_physical_reads from ( select owner||'.'||object_name as segment_name,object_type, value as total_physical_reads from v$segment_statistics where statistic_name in ('physical reads') order by total_physical_reads desc) where rownum <=10; |
Out Put :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SEGMENT_NAME OBJECT_TYPE TOTAL_PHYSICAL_READS ---------------------------------------- ------------------ -------------------- SYS.REFCON$ TABLE 6665 SYS.OBJ$ TABLE 3057 SYS.TYPE$ TABLE 2728 SYS.SEG$ TABLE 2694 SYS.IDL_UB1$ TABLE 1803 SYS.I_OBJ5 INDEX 962 SYS.HISTGRM$ TABLE 958 SYS.I_OBJ1 INDEX 615 SYS.IDL_UB2$ TABLE 542 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PARTITION 462 |
Seg_With_Highest_IO_Writes :… Read More
Dear Readers, In this article, we will see the Daily Tips for PostgreSQL. Tip-1 : Date: 07-Dec-20 Provided By (Linkedin): Ankush Chawla Public Role and Schema: 👉 This very interesting feature of PostgreSQL. 👉 When a new… Read More
Dear Readers, In this article, we will see the following Daily Tips for AWS. Tip-1 : Date: 07-Dec-20 Provided By (Linkedin): Bhagyasri History of AWS: 👉 AWS was first to market with a modern cloud infrastructure service… Read More
Dear Readers, In this article, we will see the following Daily Tips for DevOps. Tip-1 : Date: 09-Dec-20 Provided By (Linkedin): BhagyasriHistory of DevOps: 👉 The word “DevOps” was coined in 2009 by Patrick Debois, who became… Read More
Dear Readers, In this article, we will see the following Daily Tips for Data Science. Tip-1 : Date: 07-Dec-20 Provided By (Linkedin): Arjun Reddy 👉Data Science is doing reseach on past data to genereate the fact-based reports… Read More
Dear Readers, In this article, we will see the following Daily Tips for Python. Tip-1 : Date: 07-Dec-20 Provided By (Linkedin): Arjun Reddy Data Structures in Python: 👉 LIST 👉 TUPLE 👉 SET 👉 DICT Tip-2 : … Read More
