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

How to Move schema data from USERS tablespace to their own tablespace

#TASK – – Move schema (ABC) data from USERS tablespace to their own tablespace It is very common for objects to be created in wrong tablespaces especially the USERS tablespace. Part of the DBA’s task is to move these objects to the right tablespace. First. you want to note all objects belonging to the schema. –Create new tablespaces to host schema data CREATE SMALLFILE TABLESPACE XXXX_ts DATAFILE ‘XXXX_01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 512M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; — Add enough datafiles on the tablespace (This depends on the size of the data to … Continue reading How to Move schema data from USERS tablespace to their own tablespace

CRONTAB FOR SCHEDULING JOBS

Before scheduling a job on crontab, the DBA must have created and tested the script for a successful execution. You can view the current cron setting using the below command; cat /etc/crontab * * * * * Command_to_execute | | | | | | | | | +?? Day of week (0?6) (Sunday=0) or Sun, Mon, Tue,… | | | +???- Month (1?12) or Jan, Feb,… | | +????-? Day of month (1?31) | +??????? Hour (0?23) +????????- Minute (0?59) Below are some examples of cron jobs; 0 6,18 * * /home/oracle/dba/scripts/rm_listener.sh >> /home/oracle/rmlistener.log 0 1 * * * /home/oracle/dba/scripts/expdp_orcl.sh … Continue reading CRONTAB FOR SCHEDULING JOBS