mark

How to calculate DB time for a week

by

DB Time is a period demonstrate measurement that is the entirety of Oracle process CPU utilization and non-inert hold up time. While advancing Oracle frameworks we regularly center around decreasing “time”, however ordinarily database work is additionally part of the condition. SELECT sum(Round(NVL((e.value – s.value),-1)/60/1000000,2))/1440 ||’ days’ “DB Time” FROM DBA_HIST_SYS_TIME_MODEL s, DBA_HIST_SYS_TIME_MODEL e WHERE … Read more

How many log switches per hour

by

Calculate the log switches per hour over the last week. SET PAGESIZE 90 SET LINESIZE 150 set heading on column “00:00” format 9999 column “01:00” format 9999 column “02:00” format 9999 column “03:00” format 9999 column “04:00” format 9999 column “05:00” format 9999 column “06:00” format 9999 column “07:00” format 9999 column “08:00” format 9999 … Read more

Primary Keys without auto increment (MYSQL)

by

How do you check that all primary keys have an auto increment function? Well here is how to do it. SELECT distinct u.table_name, u.column_name, u.constraint_name, c.extra FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u, INFORMATION_SCHEMA.COLUMNS c WHERE u.TABLE_NAME=c.TABLE_NAME and u.COLUMN_NAME=c.COLUMN_NAME AND u.TABLE_SCHEMA=c.TABLE_SCHEMA AND u.CONSTRAINT_NAME = ‘PRIMARY’ AND u.TABLE_SCHEMA = ‘mytoll’ AND c.extra ‘auto_increment’;

How to Monitor an Undo Transaction

by

We can monitor the progress of an undo operation by running the query shown below: select session.username , substr(session.program, 1, 19) command , transaction.used_ublk , from v$session session , v$transaction transaction where session.saddr = transaction.ses_addr; From the first session connected as SH we issue a DELETE statement SQL> conn customer/customer123 Connected. SQL> delete * from … Read more

AWS Aurora Reader and Writer Endpoints

by

The cluster endpoint connects you to the primary instance for the DB cluster. You can perform both read and write operations using the cluster endpoint. The DB cluster can also have up to 15 Aurora Replicas that support read-only access to the data in the DB cluster. The primary instance and each Aurora Replica has … Read more

MySQL Deadlocks in InnoDB

by

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such as UPDATE … Read more

How to Shrink a Datafile

by

This is some code how to correctly shrink a datafile. set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by … Read more

Recover from missing UNDO Tablespace

by

Solution Kill user sessions in RAC 10g Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID, Kill_Session_User(p_username VARCHAR2) – This takes the schema/user name and will loop through all sessions for this user in gv$sessions — Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job Kill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id … Read more

Backup and Purge RDS Snapshots in AWS

by

Here is a script I’ve put together to backup both cluster and non clustered databases in AWS. (Still in progress). import boto3 import datetime import sys # Author: Mark Young # Date: 3rd October 2017 # Detail: Automatic database snapshots are restricted to 35 days # This script enables us to keep snapshots for extended … Read more