Excel Unmerge Cells and copy the values to each cells

This macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range

Sub UnMergeFill()
Dim cell As Range, joinedCells As Range
Application.ScreenUpdating = False
For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next

Application.ScreenUpdating = True
End Sub

Resizing or Recreating Online Redo Log Files

Online redo logs are sized too small causing database performance problems.

I had 11MB logfiles in each group.

I decided to increase the redo log file size to 100MB

The redo logs must be dropped and recreated for changing the redo log size. It can be done without shutdown the database. Redo group status must be INACTIVE before dropping.

There is a basic requirement in Oracle database that there should always be a minimum 2 redo log groups available with the database. So we can't drop any redo groups if there  are only 2 groups.

First we will see the current redo log files status
$sqlplus / as sysdba

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 CURRENT
         3   10485760 ACTIVE

To convert an group status to inactive , execute the checkpoint

SQL> alter system checkpoint;

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 CURRENT
         3   10485760 INACTIVE

The group 3 is inactive
Drop the 3rd group file using the following command

SQL> alter database drop logfile group 3;
Database altered

Re-Create dropped online redo log group

SQL> alter database add logfile group 3 (
'/u01/app/oracle/PROD/db/app_sts/PROD/log3a.dbf','/u01/app/oracle/PROD/db/app_sts/PROD/log3b.dbf''/u01/app/oracle/PROD/db/app_sts/PROD/log3c.dbf') size 512M
SQL> select group#,bytes,status from v$log;

 GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 ACTIVE
         3   104857600 UNUSED

After adding the new log file, it status should become current. using the following command
SQL> alter system switch logfile;

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 ACTIVE
         3   104857600 CURRENT



Repeat the same steps for other 2 log files.

Revoke DBMS_JAVA permissions

Revoke dbms_java permission

Query the dba_java_policy table

$sqlplus / as sysdba
SQL>select * from  dba_java_policy

KING      GRANTEE TYPE SCHEMA TYPE NAME                NAME ACTION ENABLED    SEQ

GRANT XXPMS    SYS                java.io.FilePermission   *   read ENABLED 178
GRANT XXPMS   SYS                java.io.FilePermission   /-   read,write  ENABLED 181
GRANT XXPMS   SYS                java.io.FilePermission       /bin/ls   execute ENABLED 180



Using the SEQ value, you can run the following statement to revoke the granted java permissions

eg: suppose you want to revoke SEQ 178

begin
  DBMS_JAVA.disable_permission(178);
  DBMS_JAVA.delete_permission(178);
end;

ERROR: While GATHER_TABLE_STATS: ***ORA-20005: object statistics are locked (stattype = ALL)***

ERROR: While GATHER_TABLE_STATS: ***ORA-20005: object statistics are locked (stattype = ALL)***

Connect as a dba user and use the following statement to unlock the statistics:

sqlplus / as sysdba or sqlplus system/****

SQL>EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('SCOTT');

SQL>exec dbms_stats.gather_schema_stats('SCOTT',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Error APP-FF-33980: Internal error attempting to generate package

Error "APP-FF-33980: Internal error attempting to generate package". While saving the fast formula.

How to diagnosis:
Compile the fast formula using FFXBCP command
go to
$cd $FF_TOP/bin
$FFXBCP apps/apps 0 Y -k %% 'ENTER_THE_NAME_OF_THE_FAST_FORMUALA'

it will generate 2 files one .log and .out

Please check the log file to see the package name
Connect using
$sqlplus apps/apps
SQL> alter package packagename compile body;
SQL> show error

fix the error and compile the fast formula

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

Select ppos.person_id,
trunc(months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start )/12) ||' Years '||
       TO_CHAR (FLOOR(MOD (months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start ), 12)) ) ||' Months '||
       (To_Date (coalesce(ppos.projected_termination_date,sysdate))-Add_Months (ppos.date_start,Trunc (Months_Between (coalesce(ppos.projected_termination_date,sysdate), ppos.date_start)) ) )      ||' Days ' yearOfService
      
From   per_periods_of_service ppos

Flashback version Between query in oracle

Flashback version between query used to track a table row in a specified time period

Syntax: VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound];

eg:
SELECT * FROM wf_notifications
VERSIONS  BETWEEN TIMESTAMP TO_TIMESTAMP('15-AUG-2012 08:13:07')
                                      AND TO_TIMESTAMP('15-AUG-2012 12:01:17')

        Where message_type = 'XXXX'