Drop Hidden Columns in Oracle table

Extended Statistics feature enables Oracle to calculate statistics on group of columns to improve the row estimates.This will be when there is correlation between the values of two (or more) columns in a table.

How to identify Extended Stats ?
There will be system generated column in the table (can be found when queries in user_tab_cols table)


select column_name, data_default, hidden_column
from   user_tab_cols
where  table_name = 'EXT_ST_TAB';

COLUMN_NAME                    DATA_DEFAULT                   HID
-----------                ------------                   ---
SYS_STUYPW88OE302TFVBNC6$MMQXE SYS_OP_COMBINED_HASH("X","Y"   YES
Z                                                             NO
Y                                                             NO
X                                                             NO

The above SYS_ column was generated by system due to merging the arguments together to form a hash and it is hidden column.

And to make sure it is the column created by extended stats feature then DATA_DEFAULT value must be SYS_OP_COMBINED_HASH().


Find the stat_extensions details

select owner, table_name, extension_name, creator
       ,extension
       , cast(substr( extension,1,200) as varchar2(200) )
  from all_stat_extensions
 where table_name='EXT_ST_TAB'

Get the out put from below query and execute them in db for dropping those extended stats

select 'exec dbms_stats.drop_extended_stats(''' || owner || ''',''' || table_name || ''',''' || cast(substr( extension,1,200) as varchar2(200) ) || ''' );' 

from all_stat_extensions
where table_name='EXT_ST_TAB'

exec dbms_stats.drop_extended_stats('ADARLA', 'EXT_ST_TAB', '(x, y)');

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

Sometimes if you add additional column with DEFAULT value then also you can notice system generated column in the table. This Oracle optimization feature which will add this SYS_ column.

Note: When you set any column as UNUSED also you see hidden column 

Case 1: ADD column with DEFAULT value

CREATE TABLE DWADM.HIDDEN_COL_TST

(

  A1  NUMBER primary key,

  B1  NUMBER                   

);

Check column created in user_tab_cols or dba_tab_cols

select  table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'HIDDEN_COL_TST';

TABLE_NAME                    COL        HID         VIR

------------------                    -----        -----        -----
HIDDEN_COL_TST            A1           NO         NO
HIDDEN_COL_TST            B1           NO         NO

Now try adding new column with DEFAULT value

ALTER TABLE HIDDEN_COL_TST ADD (A2 NUMBER DEFAULT 0);

Again check for column in system metadata table

select  table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'HIDDEN_COL_TST';

TABLE_NAME                    COL                        HID         VIR

------------------                    -----                        -----        -----
HIDDEN_COL_TST            A1                           NO         NO
HIDDEN_COL_TST            B1                           NO         NO
HIDDEN_COL_TST            SYS_NC00003$  YES         NO
HIDDEN_COL_TST            A2                           NO         NO


We can notice Hidden column SYS_NC0003$ which was created by system.

If for any reason you don't want this Hidden Column on your table then you have to set below parameter to FALSE before adding new column to the table.

Alter session set "_add_col_optim_enabled"=false ;

• This parameter is set to true by default if Parameter COMPATIBLE >= 11 .

   1. Before 11g or if _ADD_COL_OPTIM_ENABLED = FALSE:

       The DDL will cause the column to be added to the table definition and every row of the table is updated with the new column and its default value. For large tables the update of all rows to add a column can take some time.

   2. From 11g Onward provided _ADD_COL_OPTIM_ENABLED = TRUE:

       The DDL adds the column to the table definition but there is no update of the actual table rows.
       Instead the dictionary stores a flag so that it knows that any row without the column value present should return the DEFAULT value instead.
       The setting of the parameter only affects new ADD COLUMN commands. Once a column has been added using the optimized method a flag in the dictionary indicates that specific column as being optimized.

       You can check which columns have been Added with the optimization active using SQL of the form below :

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
and object_id=obj#;

Case 2: Set Column UNUSED

ALTER TABLE HIDDEN_COL_TST SET UNUSED COLUMN B1;

select  table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'HIDDEN_COL_TST';

TABLE_NAME                    COL                                                        HID         VIR

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

HIDDEN_COL_TST            A1                                                           NO         NO

HIDDEN_COL_TST            SYS_C00002_19070200:44:53$    YES         NO

HIDDEN_COL_TST            SYS_NC00003$                                  YES         NO

HIDDEN_COL_TST            A2                                                           NO         NO

We can notice B2 column was removed and hidden column created in place of it.


Now our objective is to remove these hidden columns.
DROP column command will not work as DML and DDL operations are not directly allowed on guard-columns.

There is separate syntax for deleting these unused columns.

ALTER TABLE HIDDEN_COL_TST DROP UNUSED COLUMNS;

Unfortunately above command also not deleting those unused and hidden columns though command execution successful. This is due to table compression enabled  because it's expensive and requires a full table decompression. Any drop column will be converted to set unused internally.
The reason is that dropping a column requires a full table update and will result in full decompression .

Possible workaround for deleting these unused hidden column is by using dbms_redefinition.cons_use_pk and then add primary key.

Here is the example.

create table UNUSED_TAB

( 

TXN_DATE DATE,

TXN_FLG VARCHAR2(1),

TXN_LINE_NBR NUMBER PRIMARY KEY

) COMPRESS FOR ALL OPERATIONS;

alter table unused_tab add c1 number default 0;

alter table unused_tab set unused column c1;

select table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'UNUSED_TAB';

TABLE_NAME    COLUMN_NAM                           HID      VIR

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

UNUSED_TAB    TXN_DATE                                NO        NO

UNUSED_TAB    TXN_FLG                                  NO        NO

UNUSED_TAB    TXN_LINE_NBR                         NO        NO

UNUSED_TAB    SYS_C00004_19070201:17:03$   YES       NO

UNUSED_TAB    SYS_C00005_19070201:17:03$   YES       NO

DECLARE

p_option PLS_INTEGER := dbms_redefinition.cons_use_pk;

p_owner varchar2(30) := 'ADARLA';

p_tname varchar2(30) := 'UNUSED_TAB';

BEGIN

dbms_redefinition.can_redef_table(p_owner, p_tname, p_option);
dbms_output.put_line('Option returned : '||p_option);

END;

create table UNUSED_TAB_I

( 

TXN_DATE DATE,

TXN_FLG VARCHAR2(1),

TXN_LINE_NBR NUMBER PRIMARY KEY

);

DECLARE

num_errors pls_integer;

BEGIN

dbms_redefinition.start_redef_table(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’,

col_mapping => NULL,

options_flag => dbms_redefinition.cons_use_pk);

dbms_redefinition.copy_table_dependents(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’,

copy_indexes => dbms_redefinition.cons_orig_params,

copy_triggers => TRUE,

copy_constraints => TRUE,

copy_privileges => TRUE,

ignore_errors => TRUE,

num_errors => num_errors);

dbms_output.put_line('Number of Errors: '||num_errors);

dbms_redefinition.sync_interim_table(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’);

dbms_redefinition.finish_redef_table(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’);

END;

drop table UNUSED_TAB_I purge;

select table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'UNUSED_TAB';

TABLE_NAME    COLUMN_NAM                           HID      VIR

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

UNUSED_TAB    TXN_DATE                                NO        NO

UNUSED_TAB    TXN_FLG                                  NO        NO

UNUSED_TAB    TXN_LINE_NBR                         NO        NO

Oracle Object Types

Object Types are user-defined data types in Oracle.
These types can be created from
1.Built-in datatypes (NUMBER,VARCHAR,DATE etc)
   or
2.Previously defined object types,object references and collection types.

Let see a use case where address (Street,City,PINCODE,Country) is needed for both EMPL and CUSTR tables . So instead of defining all these attributes in both tables we can create one TYPE for address and this TYPE can be leveraged in both tables as user-defined datatype for address column.

CREATE TYPE ADDR_TYPE AS OBJECT
(
STREET_NO VARCHAR2(20),
CITY VARCHAR2(20),
PINCODE NUMBER(6),
COUNTRY VARCHAR2(20)
) ;

CREATE TABLE EMPL
(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(50),
SALARY NUMBER,
EMP_ADDR ADDR_TYPE --- ADDR_TYPE is Object Type
);

CREATE TABLE CUSTR
(
CUST_ID NUMBER,
CUST_NAME VARCHAR2(50),
CUST_CONTACT VARCHAR2(20),
CUST_ADDR ADDR_TYPE  --- ADDR_TYPE is Object Type
)

Let see how we can play around with these object types

1.INSERT data

INSERT INTO EMPL (1321,'Aravind',2789,ADDR_TYPE('17-1-383','Hyderabad',500059,'India'));

2.UPDATE data

UPDATE EMPL e set e.EMP_ADDR.STREET_NO='17-1-383/190') where  EMP_ID=1321 ;

3.SELECT data

SELECT e.EMP_ID,e.EMP_NAME,e.EMP_ADDR.STREET_NO,e.EMP_ADDR.CITY from EMPL e;

MEMBER FUNCTION within Object types.

CREATE TYPE MARKS_TYPE as OBJECT
(
SUB1 NUMBER,
SUB2 NUMBER,
MEMBER FUNCTION TOT_MARKS RETURN NUMBER,
MEMBER FUNCTION RESULT RESULT VARCHAR2
);

We have to define member functions code in object type body.

CREATE TYPE BODY MARKS_TYPE AS
(
MEMBER FUNCTION TOT_MARKS RETURN NUMBER IS
BEGIN
RETURN(SUB1+SUB2);
END;

MEMBER FUNCTION RESULT RETURN VARCHAR2 IS
BEGIN
IF SUB1<35 OR SUB2<35
THEN RETURN('FAIL');
ELSE RETURN('PASS');
END IF;
END;
);

Examples

CREATE TABLE STUDENT
(
H_T_NO NUMBER,
NAME VARCHAR2(50),
MARKS MARKS_TYPE
);

INSERT INTO STUDENT (0606256,'Aravind',MARKS_TYPE(52,91));

SELECT S.H_T_NO,S.NAME,S.MARKS.SUB1,S.MARKS.SUB2,S.MARKS.TOT_MARKS(),S.MARKS.RESULT
FROM STUDENT S;

Converting VARCHAR2 date string to DATE

Use below function to convert any of your String type date values into Date values.

create or replace function str_to_date(p_str varchar2) return date is       
 type format_masks is table of varchar2(20);      

 v_mask format_masks     := format_masks('dd/mm/yyyy',
                                         'dd-mon-yyyy',
                                         'dd/mon/rr',
                                         'dd@mm@yy',
                                         'dd-mon@yy',
                                         'ddmmyy',
                                         'ddmonyy');      

 v_date date;
 begin
      for i in 1 .. v_mask.count
      loop
            begin
                  v_date := to_date(p_str,v_mask(i));
                  return v_date;
            exception
            when others then null;
            end;
      end loop;
      return null;
 end;

examples

select str_to_date('01-jan-12') DATE_TEST from dual;

DATE_TEST
---------
01-JAN-12

select str_to_date('01-jan@12') DATE_TEST  from dual;

DATE_TEST
---------
01-JAN-12

SQL to get PO Number, Invoice Number and Receipt Number inline

Here is the SQL query to get the PO number,Invoice number and Receipt number inline.

 SELECT DISTINCT
    PHA.SEGMENT1 PO_NUMBER,
    PR.PO_RELEASE_NUM,  
    AIA.INVOICE_NUM INVOICE_NUMBER,
    RSH.RECEIPT_NUM RECEIPT_NUMBER   
 FROM   
    PO_HEADERS_ALL PHA,
    PO_DISTRIBUTIONS_ALL PDA,
    AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    AP_INVOICES_ALL AIA,
    RCV_SHIPMENT_LINES RSL,
    RCV_SHIPMENT_HEADERS RSH ,
    PO_RELEASES_ALL PR,
    RCV_TRANSACTIONS RT
 WHERE
    PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
    AND PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID
    AND AIDA.INVOICE_ID= AIA.INVOICE_ID
    AND PHA.PO_HEADER_ID = RSL.PO_HEADER_ID
    AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
    AND RSL.PO_RELEASE_ID = PR.PO_RELEASE_ID
    AND RSL.PO_HEADER_ID = PR.PO_HEADER_ID
    AND RSL.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
    AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
    AND RT.TRANSACTION_ID=AIDA.RCV_TRANSACTION_ID
    AND PHA.SEGMENT1=NVL(:PO_NUM,PHA.SEGMENT1)
    AND PR.RELEASE_NUM=NVL(:PO_REL_NUM,PR.RELEASE_NUM)
ORDER BY 3;

SQL to remove HTML Tags from the text

Here is the query to eliminate html tags and other entities like &amp ,&nbsp etc. 

SELECT trim(regexp_replace('<p style="margin-left:30px;">Replacing html tags &amp; entities with space using regular expresion&nbsp;.</p>',

'<.+?>|\&(nbsp;)|(amp;)|(quot;)|(lt;)|(gt;)', ' ')) as plain_col
FROM dual ;

Result will be 

PLAIN_COL
------------------------------------------------------------------------------------
Replacing html tags &  entities with space using regular expression.

UOM conversion logic - Oracle EBS

Using below query we can get the conversion rate for an Inventory Item.

SELECT * FROM

(SELECT MSIB.ORGANIZATION_ID,

                                MSIB.INVENTORY_ITEM_ID,

                                MUCC.TO_UOM_CODE

                                FROM_UOM_CODE,

                                MUCC.FROM_UOM_CODE TO_UOM_CODE,

                                MUCC.CONVERSION_RATE

FROM   MTL_UOM_CLASS_CONVERSIONS MUCC,

                                MTL_SYSTEM_ITEMS_B MSIB

WHERE MUCC.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID

UNION ALL

SELECT MSIB.ORGANIZATION_ID,

                                MSIB.INVENTORY_ITEM_ID,

                                MUM.UOM_CODE FROM_UOM_CODE,

                                MSIB.PRIMARY_UOM_CODE TO_UOM_CODE,

                                1/MUC.CONVERSION_RATE

FROM   MTL_SYSTEM_ITEMS_B MSIB,  

                                MTL_UOM_CONVERSIONS MUC,

                                MTL_UNITS_OF_MEASURE MUM

WHERE MSIB.PRIMARY_UOM_CODE=MUC.UOM_CODE

                                AND MUC.INVENTORY_ITEM_ID=0

                                AND MUC.UOM_CLASS=MUM.UOM_CLASS AND BASE_UOM_FLAG='Y'

) SQ_UOM