Category: Databases

Last week I had a requirement to create a trigger on a table that had to perform some logic and then run additional updates based on the outcome of the logic. The logic ran into multiple IF statements resulting in long messy code. Additionally, there was second requirement to execute this chunk of code from two places within the trigger functionality. I moved the code to a subprogram within the trigger’s PL/SQL logic resulting in a modular structure. This is a simplified example of the trigger code to demonstrate the technique.

The below lines declare a subprogram with some logic that creates an update statement. The EXECUTE IMMEDIATE is commented out for testing and demonstration:

  PROCEDURE perform_calculations(cust_num number) IS
    BEGIN 

        v_upd_sql :='';

		IF  :OLD.CUST_ZIP <> :NEW.CUST_ZIP THEN  
		    v_upd_sql := ' UPDATE DEAN_CUSTOMER SET CUST_ZIP_OLD = ' || :OLD.CUST_ZIP || ' WHERE CUST_NUM = ''' || CUST_NUM || '''';
   	    END IF;  

		--EXECUTE IMMEDIATE v_upd_sql;
		dbms_output.put_line('SQL --->' || v_upd_sql);  
    END;

The below line of code calls the subprogram:

        perform_calculations(:old.cust_num);

In the real-world scenario, I was working on, the code issued a BULK COLLECT similar to below and ran the subprogram for each row collected:

        v_main_sql := ' SELECT ROWID FROM ' || v_table_name || 
                      ' WHERE COL01 = ' :OLD.COL01;

        EXECUTE IMMEDIATE v_main_sql BULK COLLECT INTO var01;
        FOR i IN 1..var01.COUNT
        LOOP
            perform_calculations(var01(i));
        END LOOP;

“var01” was defined as shown below:

TYPE var01_type IS TABLE OF ROWID;
Var01 var01_type;

Code

create or replace TRIGGER UPDATE_TRIGGER AFTER UPDATE ON DEAN_CUSTOMER FOR EACH ROW 
DECLARE 
  v_upd_sql         VARCHAR2(8000); 

  table_not_found EXCEPTION;  
  PRAGMA EXCEPTION_INIT (table_not_found, -942);  

  PROCEDURE perform_calculations(cust_num number) IS
    BEGIN 

        v_upd_sql :='';

		IF  :OLD.CUST_ZIP <> :NEW.CUST_ZIP THEN  
		    v_upd_sql := ' UPDATE DEAN_CUSTOMER SET CUST_ZIP_OLD = ' || :OLD.CUST_ZIP || ' WHERE CUST_NUM = ''' || CUST_NUM || '''';
   	    END IF;  

		--EXECUTE IMMEDIATE v_upd_sql;
		dbms_output.put_line('SQL --->' || v_upd_sql);  
    END;

    BEGIN 
        dbms_output.put_line(' ');  
        dbms_output.put_line('********** Trigger DC invoked **********');  
        dbms_output.put_line(' ');  
        perform_calculations(:old.cust_num);
END; 

Table definition

CREATE TABLE DEAN_CUSTOMER
(
  “CUST_NUM"     NUMBER(8,0),
  "CUST_NAME"    VARCHAR2(10 BYTE), 
  "CUST_ZIP"     NUMBER(12,0),
  "CUST_ZIP_OLD" NUMBER(12,0)
) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  TABLESPACE "USERS"
;

I created the below SQL to identify the largest LOB objects and associate them back to the respective table and column:

set pages 99
set lines 200
col owner           format a12
col table_name      format a25
col column_name     format a25
col segment_name    format a30
col partition_name  format a15
SELECT      tc.owner,
            tc.table_name,
            tc.column_name,
            lb.segment_name,
            sg.partition_name,
            Round(( sg.bytes / (1024*1024*1024)),2) AS size_gb
FROM        dba_tab_cols tc
inner join  dba_lobs lb
        ON  lb.owner        =  tc.owner
       AND  lb.table_name   =  tc.table_name
       AND  lb.column_name  =  tc.column_name
inner join  dba_segments sg
        ON  sg.segment_name =  lb.segment_name
WHERE       tc.owner        = 'schema owner here'
  AND       tc.data_type LIKE '%LOB%'
  and       Round(( sg.bytes / (1024*1024*1024)),2) > 50
ORDER  BY 
            size_gb DESC,
            tc.owner,
            tc.table_name,
            tc.column_name,
            lb.segment_name
;          

I got paged over the weekend for an Enterprise Edition Release 19.0.0.0.0 RAC database running out of space. When I attempted to add a new data file with the commands:

alter tablespace USERS add datafile '+DATA' size 31G;

the SQL failed with the response

Attempted to add a datafile but encountered
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

I checked the DB_FILES parameter with the command:

SQL> show parameter db_files;
 
NAME           TYPE        VALUE
-------------- ----------- -----
db_files       integer     100

Check the number of data files with the below SQL:

SQL> select count(*) from dba_data_files;
 
  COUNT(*)
----------
       100

This parameter is not dynamically modifiable, so I issued the command:

alter system set db_files=125 scope=spfile sid='*';

and attempted to do a rolling bounce with:

srvctl stop instance -d PRODDB -i PRODDB4 -o immediate

This resulted in the error

CRS-2974: unable to act on resource 'ora.PRODDB.db' on server 'prodbox87' because that would require stopping or relocating resource 'ora.PRODDB.PRODDB_svc.svc' but the appropriate force flag was not specified

I checked the status of the services with:

srvctl status service -d PRODDB
Service PRODDB_svc is running on instance(s) PRODDB1,PRODDB2,PRODDB3,PRODDB4

Since the service was configured to run on the other nodes, I stopped the service on node 4 with:

srvctl stop service -d PRODDB -i PRODDB4

and retried the rolling bounce. The instance shutdown successfully but did not come back up because of the below error:

srvctl start instance -d PRODDB -i PRODDB4
PRCR-1013 : Failed to start resource ora.PRODDB.db
PRCR-1064 : Failed to start resource ora.PRODDB.db on node prodbox87
CRS-5017: The resource action "ora.PRODDB.db start" encountered the following error:
ORA-01105: mount is incompatible with mounts by other instances
ORA-01174: DB_FILES is 125 buts needs to be 100 to be compatible
. For details refer to "(:CLSN00107:)" in "/oracle/product/diag/crs/prodbox87/trace/crsd_oraagent_oracle.trc".
 
CRS-2674: Start of 'ora.PRODDB.db' on 'prodbox87' failed

At this point I was forced to perform a bounce of all nodes of the RAC after which I was able to add a new data file.

I had a requirement to move a table from one user (i.e. schema) to another. As this is not possible with an ALTER, the usual approach is to do CREATE TABLE AS or an export-import. Depending on the volume of data, this may take a long time. I found this interesting approach on google and decided to try it

Can you move a table between schemas ?

Many thanks to the person that identified this approach.

My test was as follows:

As sysdba, I created the below users. The assumption is user JIM needs to be dropped but his table named JIMS_TABLE needs to be transferred to user BOB. User Mike is a user who has select on JIM.JIMS_TABLE. The grant on DBA_TABLES to JIM is only to facilitate the creation of a test table. It has no other relevance to the test.

create user jim identified by N0t_Jim#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to jim;
GRANT UNLIMITED TABLESPACE TO jim;
grant create table to jim;
grant select on dba_tables to jim;
 
 
create user bob identified by N0t_Bob#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to bob;
GRANT UNLIMITED TABLESPACE TO bob;
grant create table to bob;
grant select on dba_tables to bob;
 
 
create user mike identified by N0t_Mike#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to mike;

As user Jim, create a table, grant select to user MIKE.

create table jim.jims_table
tablespace PRODTSPACE
as
select * from dba_tables
;
 
GRANT SELECT ON JIM.JIMS_TABLE TO MIKE;

The table now exists

col owner format a5;
col table_name format a10;
select
owner, table_name, tablespace_name, partitioned, row_movement
from dba_tables
where owner in ('JIM','BOB')
;
 
OWNER TABLE_NAME TABLESPACE_NAME                PAR ROW_MOVE
----- ---------- ------------------------------ --- --------
JIM   JIMS_TABLE PRODTSPACE                     NO  DISABLED

With the following grants:

set pages 99;
col grantee  format a10;
col owner format a10;
col table_name format a10;
col grantor  format a10;
col PRIVILEGE format a17;
 
SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE  owner in ('JIM','BOB')
order by grantee
;
 
GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- -----------------
BOB        JIM        JIMS_TABLE JIM        ALTER
BOB        JIM        JIMS_TABLE JIM        DELETE
BOB        JIM        JIMS_TABLE JIM        INDEX
BOB        JIM        JIMS_TABLE JIM        INSERT
BOB        JIM        JIMS_TABLE JIM        SELECT
BOB        JIM        JIMS_TABLE JIM        UPDATE
BOB        JIM        JIMS_TABLE JIM        FLASHBACK
BOB        JIM        JIMS_TABLE JIM        REFERENCES
BOB        JIM        JIMS_TABLE JIM        READ
BOB        JIM        JIMS_TABLE JIM        ON COMMIT REFRESH
BOB        JIM        JIMS_TABLE JIM        QUERY REWRITE
BOB        JIM        JIMS_TABLE JIM        DEBUG
MIKE       JIM        JIMS_TABLE JIM        SELECT

In preparation to move the table, user JIM grants all on the table to the future new owner BOB.

GRANT ALL ON JIM.JIMS_TABLE TO BOB;

Next, JIM generates the DDL of the table

set long 9999999 pages 0 heading off pagesize 0;
SELECT dbms_metadata.get_ddl('TABLE','JIMS_TABLE') FROM dual;

Give the DDL to user BOB, change the table owner and add a partitioning clause (see the last line of DDL). In order to reduce the size of the DDL, I have deleted many of the columns.

  CREATE TABLE "BOB"."JIMS_TABLE"
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,
        "TABLE_NAME" VARCHAR2(128) NOT NULL ENABLE,
        "TABLESPACE_NAME" VARCHAR2(30),
        .
        .
        .
        Columns deleted
        .
        .
        .
        "DATA_LINK_DML_ENABLED" VARCHAR2(3),
        "LOGICAL_REPLICATION" VARCHAR2(8)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PRODTSPACE"
  partition BY hash (OWNER) (partition p1);

We now have two tables

col owner format a5;
col table_name format a10;
select
owner, table_name, tablespace_name, partitioned, row_movement
from dba_tables
where owner in ('JIM','BOB')
;
 
OWNER TABLE_NAME TABLESPACE_NAME                PAR ROW_MOVE
----- ---------- ------------------------------ --- --------
JIM   JIMS_TABLE PRODTSPACE                     NO  DISABLED
BOB   JIMS_TABLE                                YES DISABLED

Now to swap the data from JIM’S table to the new BOB table

ALTER TABLE BOB.JIMS_TABLE exchange partition p1 WITH TABLE JIM.JIMS_TABLE;

The data is now in the new table

SELECT COUNT(*) FROM JIM.JIMS_TABLE;
SELECT COUNT(*) FROM BOB.JIMS_TABLE;
 
SQL> SELECT COUNT(*) FROM JIM.JIMS_TABLE;
 
  COUNT(*)
----------
         0
 
SQL> SELECT COUNT(*) FROM BOB.JIMS_TABLE;
 
  COUNT(*)
----------
      2228

The grants will have to be re-granted on the new table named BOB.JIMS_TABLE;

GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- -----------------
BOB        JIM        JIMS_TABLE JIM        ALTER
BOB        JIM        JIMS_TABLE JIM        DELETE
BOB        JIM        JIMS_TABLE JIM        INDEX
BOB        JIM        JIMS_TABLE JIM        INSERT
BOB        JIM        JIMS_TABLE JIM        SELECT
BOB        JIM        JIMS_TABLE JIM        UPDATE
BOB        JIM        JIMS_TABLE JIM        REFERENCES
BOB        JIM        JIMS_TABLE JIM        READ
BOB        JIM        JIMS_TABLE JIM        ON COMMIT REFRESH
BOB        JIM        JIMS_TABLE JIM        QUERY REWRITE
BOB        JIM        JIMS_TABLE JIM        DEBUG
BOB        JIM        JIMS_TABLE JIM        FLASHBACK
MIKE       JIM        JIMS_TABLE JIM        SELECT

I was asked to look into a nightly script that had significantly increased in elapsed time. The script was rather simple in that it deleted rows older than (today’s date – 3 months) from multiple tables. I ran an ADDM report on the database (12.1.0.2.0 – 64bit Production), and one of the deletes popped up in the report:

   Action
      Run SQL Tuning Advisor on the DELETE statement with SQL_ID
      "75csungpxhvv6".
      Related Object
         SQL statement with SQL_ID 75csungpxhvv6.
         delete from PROD_SCHEMA.PROD_TABLE where ORDER_DT<20201212
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for INDEX "PROD_SCHEMA.PROD_TABLE_ORDER_DT" with object ID
      322364 consumed 100% of the database time spent on this SQL statement.

The table in question was not large; a few 100 thousand rows with reasonable row length; no large varchars etc. The obvious issues were quickly eliminated; the table has current stats, there was an index to exactly match the predicate, and the optimizer was choosing the index. However, I did notice that the table was occupying 28 GB of space. A quick calculation of # of rows multiplied by row length indicated that it should be occupying less than 1 GB of space approx.

I ran the below query and confirmed that most of the space was empty

set line 200;
set pages 9999;
col owner                format a15               heading "Owner"
col table_name           format a30               heading "Table Name"
col avg_row_len          format 999,999,999       heading "Avg Row Len"
col num_rows             format 999,999,999,999   heading "Number of Rows"
col TOTAL_SIZE_MB        format 999,999,999       heading "Total|Size|(MB)"
col ACTUAL_SIZE_MB       format 999,999,999       heading "Actual|Size|(MB)"
col FRAGMENTED_SPACE_MB  format 999,999,999       heading "Frag|Size|(MB)"
col percentage           format 999.99            heading "Per|Cent|Frag"
SELECT   owner
        ,table_name
        ,avg_row_len
        ,num_rows
        ,Round((( blocks * 8 / 1024 )), 2) "TOTAL_SIZE_MB"
        ,Round(( num_rows * avg_row_len / 1024 / 1024 ), 2)  "ACTUAL_SIZE_MB"
        ,Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024 / 1024 ) ), 2) "FRAGMENTED_SPACE_MB"
        ,Round(( Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024
                                                  / 1024
                                                ) ), 2
                     )
                     / Round((( blocks * 8 / 1024 )), 2) ) * 100, 2)  "percentage"
FROM     dba_tables
WHERE    num_rows > 0
  and    table_name = 'PROD_TABLE'
  and    owner = ' PROD_SCHEMA '
order by FRAGMENTED_SPACE_MB desc 
;

We were able to ask the application for a small outage to export (data pump), truncate, and import that data from the table. After this, the query that used to take almost 8 minutes started executing in less than a second.

We ran into an interesting situation recently when MRP failed on a standby with the error:

ORA-38882: Cannot drop tablespace UNDOTBS2 on standby database due to guaranteed restore points.

The background was that we had to remove a node from our primary RAC to upgrade the server. We did not remove any nodes from the standby. After the node was removed from the primary, MRP failed on the standby with the above error. As far as we could remember or check, we did not have a guaranteed restore point on the database. A google search led us to Mike Dietrich’s Blog, where he had responded to a similar error in a different context. The link is

Mike Dietrich’s Blog About Oracle Database

We were not using a PDB, but since we did not have any other ideas, we decided to try Mike Dietrich’s solution and issued the following alter:

alter system set "_allow_drop_ts_with_grp"=true;

After the above alter, the database was bounced, and MRP started successfully.

While performing an import data pump operation into an Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production we encountered the below error:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_15" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_15":  "/******** AS SYSDBA" directory=exp_dir dumpfile=resolvecode.dmp remap_table=myuser.accounting_codes:accounting_codes_prod 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS [] 
ORA-31604: invalid transform NAME parameter "MODIFY" for object type PROCACT_INSTANCE in function ADD_TRANSFORM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 9710

This issue is documented on Oracle support at

Impdp Fails With ORA-39126 ORA-31604 Iinvalid Transform NAME Parameter "MODIFY" For Object Type PROCACT_INSTANCE in function ADD_TRANSFORM (Doc ID 1596495.1)

The solution is to change the import data pump command from:

impdp \'/ as sysdba \' dumpfile=resolvecode.dmp directory=exp_dir remap_table=myuser.accounting_codes:accounting_codes_prod 

to:

impdp \'/ as sysdba \' dumpfile=resolvecode.dmp directory=exp_dir remap_table=myuser.accounting_codes:accounting_codes_prod exclude=PROCACT_INSTANCE

The use of the exclude=PROCACT_SYSTEM will exclude the resource manager objects such as resource plans and groups.

There is also a patch available, but the addition of exclude=PROCACT_INSTANCE solved my issue faster than patching.