Oracle: Reclaim space / Defragmentation

This Blog I am trying to explain when and how to attempt deframentation :

1. Defrag when:
    a. Application is mostly doing FTS (mostly in Data-warehouse and hybrid databases,)
    b. Application drops & rebuilds Indexes. Defrag will cut down on Index rebuild time.

In theory, de-fragmentation is normal wear and tear for a active database tables and if indexes are rebuilt for very active database tables at a regular intervals, de-fragmentation of tables probably wouldn't be required ever. For such databases, if application allows downtime, once in a while rebuild of objects greater than 30%(or 20%) fragmented could be attempted, more of schedule maintenance than anything else. Remember priority is application up-time, performance  not de-fragmentation so decision not be taken lightly.

Note: FTS(Full Table Scan) should be avoided for big tables as much as possible. Creates pressure on I/O, CPU, TEMP, Memory(PGA) basically resources(all may not apply always). Recommend statistics monitoring  and relevant index utilization/creation.

Plenty of queries are available online to find out fragmentation: Here is one, filter as you see fit:

col Percentage for 999
col TOTAL_SIZE for a15
col ACTUAL_SIZE for a15
col FRAGMENTED_SPACE for a15

select /* Objects to identify defrag candidates */ owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
    round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
    round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
((round(((blocks*8/1024)),2) - round((num_rows*avg_row_len/1024/1024),2))/round(((blocks*8/1024)),2))*100 "Percentage"
    from all_tables  WHERE Owner NOT IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)>500 <==greater than 500MB
order by 9;


Pre-created: EMP1 is partitioned table with few million rows.

Experiment 1: Deallocate space.

alter table &TAB deallocate unused space;

***Begin
06:41:27 SQL> Alter table scott.emp1 enable ROW MOVEMENT;

Table altered.

06:42:16 SQL> select count(1) from scott.emp1 partition(sys_P775);

  COUNT(1)
----------
    552903

Elapsed: 00:00:00.06
06:46:07 SQL> delete from scott.emp1 partition(sys_P775) where rownum<400000;

399999 rows deleted.

Elapsed: 00:00:57.41
06:47:57 SQL> commit;
06:48:01 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';

SUM(BYTES)/POWER(1024,2)
------------------------
                     137

Elapsed: 00:00:00.29
06:48:10 SQL> Alter table scott.emp1  shrink space compact;

Table altered.

Elapsed: 00:01:10.52
06:49:31 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';

SUM(BYTES)/POWER(1024,2)   <=No change
------------------------
                     137


06:52:45 SQL> alter table scott.emp1 deallocate unused;

Table altered.

Elapsed: 00:00:00.19
06:55:24 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';

SUM(BYTES)/POWER(1024,2)   <====Worked
------------------------
                132.1875

Elapsed: 00:00:00.02

Experiment 2: Defrag using DBMS_REDEFINATION.

06:55:34 SQL> ----------------Redefination
07:25:17 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';

SUM(BYTES)/POWER(1024,2)
------------------------
                132.1875

Elapsed: 00:00:00.01
07:25:24 SQL> Select count(1) from scott.emp1;

  COUNT(1)
----------
   1535166

Elapsed: 00:00:04.60
07:27:18 SQL> delete from scott.emp1 partition(sys_P774) where rownum<400000;

276458 rows deleted.

Elapsed: 00:00:44.08
07:28:20 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
07:28:35 SQL> ---genrating ddl
07:29:34 SQL> set heading off;
set echo off;
Set pages 999;
set long 90000;07:29:38 SQL> 07:29:38 SQL> 07:29:38 SQL>
07:29:39 SQL> select dbms_metadata.get_ddl('TABLE','EMP1','SCOTT') from dual;


  CREATE TABLE "SCOTT"."EMP1"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY HASH ("EMPNO")
 (PARTITION "SYS_P771" SEGMENT CREATION DEFERRED
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P772" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P773" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P774" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P775" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P776" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P777" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS )  ENABLE ROW MOVEMENT


Elapsed: 00:00:08.59
07:30:47 SQL>  "SYS_P777" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS )  ENABLE ROW MOVEMENT

07:33:26 SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => 'SCOTT',TNAME => 'EMP1',OPTIONS_FLAG =>DBMS_REDEFINI                                         TION.CONS_USE_ROWID);


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
07:37:15 SQL> ----Creating interim table
07:40:47 SQL> -----creating interim table
07:40:59 SQL> CREATE TABLE SCOTT.EMP1_interim
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY HASH ("EMPNO")
 (PARTITION "SYS_P771" SEGMENT CREATION DEFERRED
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P772" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P773" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P774" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P775" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P776" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P777" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS ,
 PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
  TABLESPACE "USERS"
 NOCOMPRESS );07:41:33   2  07:41:33   3  07:41:33   4  07:41:33   5  07:41:33   6  07:41:33   7  07:41:33   8  07:41:33   9  07:41:33  10  07:41:33  11  07:41:33  12  07:41:33  13  07:41:33  14  07:41:33  15  07:41:33  16  07:41:33  17  07:41:33  18  07:41:33  19  07:41:33  20  07:41:33  21  07:41:33  22  07:41:33  23  07:41:33  24  07:41:33  25  07:41:33  26  07:41:33  27  07:41:33  28  07:41:33  29  07:41:33  30  07:41:33  31  07:41:33  32  07:41:33  33  07:41:33  34  07:41:33  35  07:41:33  36  07:41:33  37  07:41:33  38

Table created.

Elapsed: 00:00:00.90
07:41:36 SQL>
exec DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM',col_mapping  => NULL,options_flag =>DBMS_REDEFINITION.CONS_USE_ROWID);

07:41:54 SQL>

PL/SQL procedure successfully completed.

Elapsed: 00:00:48.06
07:42:42 SQL> 07:42:42 SQL> 07:42:42 SQL>
07:42:45 SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
07:43:48 SQL> 07:43:48 SQL>
07:43:52 SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.77
07:44:41 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';

                     128 <====Reduced from previous allocation

Elapsed: 00:00:00.25
07:48:23 SQL> ---Use abort if redef fails for any reason.
07:49:36 SQL> ---exec dbms_redefinition.abort_redef_table(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
07:49:42 SQL> ----------------Done Enjoy-----

Experiment 3: Alter .... Move
08:22:37 SQL> select 'alter table '||table_owner||'.'||table_name||' move partition '||PARTITION_NAME||' tablespace '||tablespace_name||' compress update indexes;'
from dba_tab_partitions where table_name='EMP1' and table_owner='SCOTT'; 08:23:01   2

alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;
......More there.

8:23:02 SQL> alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;

Table altered.

Elapsed: 00:00:00.18
08:23:09 SQL>

Table altered.

Elapsed: 00:00:01.21
08:23:10 SQL>
Table altered.

8:23:02 SQL> alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P773 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P774 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P775 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P776 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P777 tablespace USERS compress update indexes;

alter table SCOTT.EMP1 move partition SYS_P778 tablespace USERS compress update indexes;

Table altered.

Elapsed: 00:00:00.18

08:23:09 SQL>

Table altered.

Elapsed: 00:00:01.21

08:23:10 SQL>

Table altered.

Oracle: Save schema password before refresh or any other purpose

One can save password for a schema using method below:
select
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from dba_users where
username ='MONKEY';

See my experiment for clarity :
------------------------------------------------------------------------------------------------------------------------
12:04:13 SQL> 12:04:13 SQL> 12:04:13 SQL> conn / as sysdba
Connected.
----create the user
12:04:21 SQL> grant connect,resource to monkey identified by monkey123;

Grant succeeded.

Elapsed: 00:00:03.84
12:04:27 SQL> select
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from
   dba_users
where
username ='MONKEY';12:05:05   2  12:05:05   3  12:05:05   4  12:05:05   5  12:05:05   6

OLD_PASSWORD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter user "MONKEY" identified by values '41A09E4762B188D3';

Elapsed: 00:00:01.71
12:05:08 SQL> alter user monkey identified by man;

User altered.

Elapsed: 00:00:00.02
12:05:50 SQL> alter user "MONKEY" identified by values '41A09E4762B188D3';

User altered.

Elapsed: 00:00:00.00

12:06:29 SQL> conn / as sysdba
Connected.
12:06:42 SQL> drop user monkey;

User dropped.

Elapsed: 00:00:07.48
12:07:00 SQL> grant connect,resource to monkey identified by monkey;

Grant succeeded.

Elapsed: 00:00:00.05
12:07:12 SQL> alter user "MONKEY" identified by values '41A09E4762B188D3';

User altered.

Elapsed: 00:00:00.01
12:07:20 SQL> conn monkey/monkey123
Connected.
12:07:30 SQL> conn / as sysdba
Connected.
06:10:37 SQL> drop user monkey;

User dropped.

Elapsed: 00:00:01.04
06:10:50 SQL> grant connect, resource to chimp;
grant connect, resource to chimp
                           *
ERROR at line 1:
ORA-01917: user or role 'CHIMP' does not exist


Elapsed: 00:00:00.02
06:11:18 SQL> grant connect, resource to chimp identified by chimp;

Grant succeeded.

Elapsed: 00:00:00.11
06:11:38 SQL> alter user chimp identified by values '41A09E4762B188D3';

User altered.

Elapsed: 00:00:00.03
06:12:06 SQL> conn chimp/monkey123;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
-----Monkeys PASSWD doenot work for CHIMP.
06:12:18 SQL> conn / as sysdba
Connected.
06:12:31 SQL> drop user chimp;

User dropped.

Elapsed: 00:00:00.21

----------------------------Enjoy!!!----------------

Oracle who is locking schema / account ORA-01017

Attempting to login with wrong password may lock you after several retires depending on how dba's set you database.

Here is lightweight method( tried and tested):

-----Creating table to store access failure results and investigative details for diagnosis:

drop table sys.logon_trigger;
 CREATE TABLE sys.logon_trigger
(
USERNAME VARCHAR2(30),
USERHOST VARCHAR2(100),
USERIP VARCHAR2(100),
USEROS VARCHAR2(100),
TIMESTAMP DATE
);

-----Create trigger as SYS to capture login faulures:

CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'),SYS_CONTEXT('USERENV', 'IP_ADDRESS'),sys_context('USERENV', 'OS_USER'), SYSDATE);
    COMMIT;
  END IF;
END;
/

------Query to retrieve details --------
SET lines 200
col USERHOST FOR a30
SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;

Sample Result:
SET lines 200
col USERHOST FOR a30
col USERNAME for a10
col USERIP for a30
col USEROS for a30
SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;06:44:50 SQL> 06:44:50 SQL> 06:44:50 SQL> 06:44:50 SQL> 06:44:50 SQL>

USERNAME   USERHOST                       USERIP                         USEROS                         TIMESTAMP
---------- ------------------------------ ------------------------------ ------------------------------ --------------------
scott      localhost.localdomain          127.0.0.1                      oracle                         07-APR-2016 06:34:54
system     localhost.localdomain          127.0.0.1                      oracle                         07-APR-2016 06:33:20
system     localhost.localdomain          127.0.0.1                      oracle                         07-APR-2016 06:30:20
scott      localhost.localdomain                                         oracle                         07-APR-2016 06:25:59

Note: Bequeath connection IP is not captured.

Other Method could be via Auditing, also used it, will blog some time later.