Tablespace management alert script

############################################################################ ## This scripts checks for tablespace usage. If tablespace is 10 percent free, it will send an alert e-mail ## Script to identify %used on tablespace ############################################################################ #!/bin/ksh sqlplus -s <<! oracle/$1@$2 set feed off set linesize 100 set pagesize 200 spool tablespace.alert SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED (MB)”, TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE (MB)”, TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL (MB)”, TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER WHERE NAME = ‘db_block_size’)/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP … Continue reading Tablespace management alert script

Undo tablespace management

–Undo tablespace management select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB – USAGEMB) FREEMB from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = ‘UNDO’ group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB from DBA_UNDO_EXTENTS c where status <> ‘EXPIRED’ group by c.tablespace_name) b where a.tablespace_name = b.tablespace_name; –show you at any point in time how much UNDO is currently being used by actively running transactions SELECT (used_ublk * (SELECT block_size FROM dba_tablespaces WHERE contents = ‘UNDO’))/1024/1024 MB FROM v$transaction; ##The query above looks at the total amount of … Continue reading Undo tablespace management

Why & How do we create index on Foreign key

–When are index on foreign key required 1. You see many of “TABLE ACCESS FULL” operations against the child table when you perform DML against the parent table. 2. You update/delete against the primary key in the parent table –Importance of index on foreign key 1. Table-level locks are avoided & Proper/less IO consuming joint operations could be established 2. Without a foreign key index, SQL could not perform a nested loops join, and a more expensive sort merge join might be required –Script to detect all foreign key constraints that are unindexed set pagesize 5000 set linesize 350 column status format a10 column … Continue reading Why & How do we create index on Foreign key

ORACLE TRANSPARENT DATA ENCRYPTION – TDE

Transperent data encryption TDE by Anthony B We can encrypt columns on a table as well as encrypt the entire tablespace and backups. There is a performance overhead associated with the encryption/decryption process as encrypted columns cannot be used in Index scans. TDE works by setting wallet and generating a key/password to access data as it will be needed to de-crypt the data. Hackers will therefore need this key in order to de-crypt and as such the data is useless to them if hacked. In this exercise, we will encrypt a column on a table as well as tablespaces in … Continue reading ORACLE TRANSPARENT DATA ENCRYPTION – TDE

Change SQL server database file location without taking the database offline

Changing SQL server database file location is not a frequent practice, but what happens when you require to change file location to different directory? To avoid any database interruption DBCC SHRINKFILE command with the EMPTYFILE option, will allow to perform this migration operation while the database is online. Step01: First check the database files size and available space SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0-CAST(FILEPROPERTY (name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files; Step02: Go to database property you intend to move location, add file with new Path. Property – files – add Step03: Now use … Continue reading Change SQL server database file location without taking the database offline

Change SMTP server name

                   Simple Mail Transfer Protocol or SMTP is what allows you to receive email alerts from your Instance to either yourself, your alerting system or group of people. Now, in order for SMTP to work, it must be configured with a server. Some of our Instances were still using old deprecated SMTP servers so I needed to update all the SMTP servers to the current one. I used the following script to resolve the issue across my multiple servers. FYI this script was executed via my CMS (central management server). If you have CMS  in your environment then great, if you do … Continue reading Change SMTP server name

Long Running Query in SQL server

While SQL server slow performance  the most common source are long running process, which can distress our entire instances performance  like others process, CPU, memory, disk and etc. In order to demonstrate how to capture a long running query for your database instance, with appropriate execution plan for the related process. This query will give you most expensive processes list with all possible details. SELECT r.session_id , r.start_time ,  TotalElapsedTime_ms = r.total_elapsed_time ,      r.[status] ,   r.command ,   DatabaseName = DB_Name(r.database_id) ,   r.wait_type ,      r.last_wait_type ,   r.wait_resource ,    r.cpu_time ,    r.reads ,      r.writes ,   … Continue reading Long Running Query in SQL server