How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases Lets now try to export tables from different schemas in Oracle 10g database on a Linux server. [oracle@localhost ~]$ sqlplus / as sysdba…

Oracle Tablespace High water mark Tablespace High water mark Tablespace HWM col tablespace_name format a15col file_size format 99999col file_name format a50col hwm format 99999col can_save format 99999 SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_saveFROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name…

Lets create a trigger that will log all the errors in the database:It is better to create this trigger under SYS or DBA user’s so that you don’t have to grant all permission if you have to grant permissions separately. ——————————————————–—  DDL for Trigger…

Find index skewed, rebuild                                                 Summary It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt. When an index is skewed, parts of an index are accessed more frequently than…

Which SQL are doing a lot of disk I/O                                                 Which SQL are doing a lot of disk I/O SELECT * FROM (SELECT SUBSTR(sql_text,1,500) SQL, ELAPSED_TIME, CPU_TIME, disk_reads, executions, disk_reads/executions “Reads/Exec”, hash_value,address FROM V$SQLAREA WHERE ( hash_value, address ) IN (…

Disk I/O Script Datafiles Disk I/O Tablespace Disk I/O Which segments have top Logical I/O & Physical I/O Which SQL are doing a lot of disk I/O

Which segments have top Logical I/O & Physical I/O                                                 Summary Do you know which segments in your Oracle Database have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and…

Tablespace Disk I/O                                                Summary The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O. How I/O is spread per Tablespace SELECT…

Datafiles Disk I/O                                             Summary The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O. How I/O is spread per datafile SELECT…