how to compress all the tables inside a tablespace

1-calculate all the tablespace tables sizes before compression:
select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name='TIM_UAT' and segment_type='TABLE' group by segment_name,owner order by 1;
2-generate a script to compress all the tables inside the tablespace given that the table was not compressed before:

 select 'Alter table '||owner||'.'|| table_name||' move compress;' from dba_tables where TABLESPACE_NAME='TIM_EA3' and compression='DISABLED';

3-rebuild all the indexes that was unusable due to the compression:

  select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name='TIM_UAT' and segment_type='TABLE' group by segment_name,owner order by 1;

4-calculate the sizes of all the tables inside the tablespace after compression:

select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name='TIM_UAT' and segment_type='TABLE' group by segment_name,owner order by 1;

Don't  forget to rebuild all unusable indexes after finishing this process:

select 'Alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE'

Kindly note that compression is useful when you have alot of redundant data available and will be so beneficial.

trigger_to_catch_failed_login_attempts.sql

-- sample trigger to write diagnostic info to alert.log
-- for failed login attempts (ora-1017)

create or replace trigger logon_denied_to_alert
after servererror on database
 declare
  message varchar2(120);
  IP varchar2(15);
  v_db_user varchar2(30);
  v_os_user varchar2(80);
  v_module varchar2(50);
  v_action varchar2(50);
  v_pid varchar2(10);
  v_sid number;
  v_machine varchar2(100);
begin
   IF (ora_is_servererror(1017)) THEN

   -- get IP for remote or ospid for local connections:
      if sys_context('userenv','network_protocol') = 'TCP' then
        IP := sys_context('userenv','ip_address');
      else
        select distinct sid into v_sid from sys.v_$mystat;
        SELECT p.SPID into v_pid FROM V$PROCESS p, V$SESSION v
             WHERE p.ADDR = v.PADDR AND v.sid = v_sid;
        select machine into v_machine from v$session where sid = v_sid;
      end if;    

      v_db_user := sys_context('userenv','AUTHENTICATED_IDENTITY');
      v_os_user := sys_context('userenv','os_user');
     
      dbms_application_info.READ_MODULE(v_module,v_action);
       
         message:= to_char(sysdate,'Dy Mon dd HH24:MI:SS YYYY');
         sys.dbms_system.ksdwrt(2,message);

         message:= 'logon denied connecting as ' || v_db_user || ' from '|| v_machine || ' '||nvl(IP,v_pid)||' '||v_os_user||
         ' with '||v_module||' '||v_action;

         sys.dbms_system.ksdwrt(2,message);

-- remove comments from next line to let it hang for 5 minutes
-- to be able to do more diagnostics on the operating system:
--         sys.dbms_lock.sleep(300);
    end if;
end;
/
-- end trigger

ORA-08104: this index object 75350 is being online built or rebuilt

SQL> alter index S.SYS_C007740 rebuild online tablespace s_data01;
alter index S.SYS_C007740 rebuild online tablespace s_data01
*
ERROR at line 1:
ORA-08104: this index object 75350 is being online built or rebuilt

SQL> conn / as sysdba
Connected.
SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2563

declare

isclean boolean;

begin

isclean :=false;

while isclean=false

loop

isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);

dbms_lock.sleep(10);

end loop;

end;

/

PL/SQL procedure successfully completed.

SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2051

SQL> alter index S.SYS_C007740 rebuild online tablespace s_data01;

Index altered.

ORA-10631: SHRINK clause should not be specified for this object

When you try to shrink a table and get this error this is due to the table has functional based index.
How to find:

SELECT dt.owner, dt.table_name,
(CASE
WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
ELSE 'N'
END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type LIKE 'FUNCTION-BASED%'
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = 'CSS_DATA'
ORDER BY 1, 2;

OWNER                          TABLE_NAME                     C
------------------------------ ------------------------------ -
CSS_DATA                       ACTION                         Y
CSS_DATA                       ACTIONLOG                      N
CSS_DATA                       ACTIONLOG_ARCH1                Y
CSS_DATA                       ACTIONLOG_OLD                  Y
CSS_DATA                       ACTIONNAME                     Y
CSS_DATA                       ACTIONPARAMETER                Y
CSS_DATA                       ACTIONPARAMETER_ARCH1          Y
CSS_DATA                       ACTIONPARAMETER_OLD            Y
CSS_DATA                       ACTIONTYPE                     Y
CSS_DATA                       ACTION_PARAMS                  Y
CSS_DATA                       ACTION_PARAMS_ARCH1            Y

OWNER                          TABLE_NAME                     C
------------------------------ ------------------------------ -
CSS_DATA                       ADJUSTMENTREASONS              Y
CSS_DATA                       BATCH                          Y
CSS_DATA                       BILLING_BILL_CYCLE             Y
CSS_DATA                       BILLING_PRICE_GROUP            Y
CSS_DATA                       BILLING_SERVICES               Y
CSS_DATA                       BILLING_SERVICES_PARAMS        Y
CSS_DATA                       BILLING_SERVICES_PARAM_VALUES  Y
CSS_DATA                       BILLING_SERVICES_SMS           Y
CSS_DATA                       BILL_SERV_PRM_CALC_TYP         Y
CSS_DATA                       BUNDLED_MIGRATION              Y
CSS_DATA                       BUNDLED_MIGRATION_BUNDLES      Y

 select INDEX_NAME, INDEX_TYPE

         from dba_indexes

         where table_owner = 'CSS_DATA' and

         table_name = 'ORDER_INSTANCE';  2    3    4

INDEX_NAME                     INDEX_TYPE

------------------------------ ---------------------------

PK_ORDER_INSTANCE              NORMAL

ORDER_INSTANCE_MSISDN_IDX      NORMAL

OINSTANCE_JOB_ID               NORMAL

ORDER_INSTANCE_IDX             FUNCTION-BASED NORMAL

Solution:

1-Drop the function-based index.

3- Shrink the table.
4- Recreate the index again on the table.

ORA-00600: internal error code, arguments: [kdiblFlushStream:iStreamEndRange], [], [], [], [], [], [], []

Most probably this is due to index corruption:
To solve this:
analyze table schema.tablename validate structure cascade;
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
You need to drop this index and re create it.
go to the trace file and find the index object id the drop it and recreate it.