------------------ TABLE FRAGMENTATION--------------------

(I)   ------------------- TABLE FRAGMENTATION--------------------



When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

===TABLE SIZE (WITH FRAGMENTATION)===============

SQL> select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';

Example:

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb


========ACTUAL DATA IN TABLE======================

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables
where table_name = 'BIG1';

Example:
TABLE_NAME                     size
------------------------------ ------------------------------------------
BIG1                           30604.2kb


Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.


How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

#################################################################################################################################################################

2. JOBS RUNNING STATUS SCRIPTS

SQL>SELECT owner, job_name, enabled FROM dba_scheduler_jobs

SQL>select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name='ABC';