Feed aggregator

 SELECT

   distinct(os_username),username,userhost,timestamp

FROM

    sys.dba_audit_session where username in ('<UserName>')

 /tmp File system hit 100% after running some extraction that failed in half-way. The extracted files were deleted but the Space is not released.

Filesystem                                                               Size  Used Avail Use% Mounted on

/dev/mapper/rhel-tmp                                                      20G   20G   20K 100% /tmp

Find the runaway  os process that is not releasing the space.

************************************************

use the below command 

ll -d /proc/[1-9]*/fd/* | grep /tmp/

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/2200/fd/1 -> /tmp/OracleExtracts/nohup.out (deleted)

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/2200/fd/2 -> /tmp/OracleExtracts/nohup.out (deleted)

lr-x------ 1 apps apps 64 Sep 16 05:20 /proc/2200/fd/9 -> /tmp/OracleExtracts/Oracle_Extract_Scripts_ALL.sql (deleted)

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/624/fd/1 -> /tmp/OracleExtracts/nohup.out (deleted)

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/624/fd/2 -> /tmp/OracleExtracts/nohup.out (deleted)

lr-x------ 1 apps apps 64 Sep 16 06:20 /proc/624/fd/9 -> /tmp/OracleExtracts/Oracle_Extract_Scripts_ALL.sql (deleted)

Kill the os process identified in above  output

************************************

kill -9 624 

kill -9 2200

After killing the os processes the system FS "/tmp" will be back to normal

Filesystem                                                               Size  Used Avail Use% Mounted on

/dev/mapper/rhel-tmp                                                      20G      34M  20G   1% /tmp

What could make a “create index” statement fail with an error about a table having too many columns (ORA-01792)? And why would this suddenly happen when the code that raised the error had been dropping and recreating the index every night for the best part of 3 years with no problems?

This is a question that came up on the Oracle developer forum recently, and was finally answered by the original poster after getting an important clue from another participant in the thread.

The clue (supplied by Solomon Yakobson) was that when you create a “function-based” index Oracle will create a hidden virtual column on the table but if the table has already reached the limit on the number of columns (1,000 until very recently) the attempt to create the column will fail with error ORA-01792, leading to the create index statement failing.

But this drop/create has been working perfectly for years – and a query for the table definition reported only 40 columns – so how did an extra 960 columns sneak in to break things? It’s a bug that can appear when you mix virtual columns with function-based indexes – Oracle bypasses a section of the code path that is supposed to clean up after the “drop index”. Here’s a very simple example to demonstrate.

rem
rem     Script:         fbi_puzzle_4.sql
rem     Author:         Jonathan Lewis / Marco Antonio Romito
rem     Dated:          Sep 2024
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0

drop table t1;

create table t1(
        n1 number(6,0),
        n2 number(6,0) as (n1 + 1) virtual,
        n3 number(6,0)
)
/

prompt  =============================
prompt  An index that causes problems
prompt  =============================

create index t1_i1 on t1(n1, nvl(n3,0), n2);

column column_name format a32
column data_default format a32

select
        column_id, column_name, 
        hidden_column, virtual_column, user_generated, internal_column_id,
        data_default
from 
        user_tab_cols 
where 
        table_name = 'T1'
order by 
        internal_column_id
/

I’ve created a table of three columns, where the second column is a virtual column. Then I’ve created an index on the table which includes references to all three columns, but one of those references includes a call to the nvl() function – which means Oracle will create a fourth (hidden, virtual) column for that column expression. Here are the results of the subsequent query against user_tab_cols:

 COLUMN_ID COLUMN_NAME                      HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- -------------------------------- --- --- --- ------------------ --------------------------------
         1 N1                               NO  NO  YES                  1
         2 N2                               NO  YES YES                  2 "N1"+1
         3 N3                               NO  NO  YES                  3
           SYS_NC00004$                     YES YES NO                   4 NVL("N3",0)

4 rows selected.

As you can see n2 is flagged as virtual (but not hidden) generated as “N1” + 1; and there’s a fourth, system-generated, hidden, virtual column generated as NVL(“N3”,0).

As ever, things are more likely to break when you mix features. Here are a few more lines of code to run after creating the index and reporting the columns:

drop index t1_i1;
create index t1_i1 on t1(n1, nvl(n3,0), n2);

drop index t1_i1;
create index t1_i1 on t1(n1, nvl(n3,0), n2);

drop index t1_i1;
create index t1_i1 on t1(n1, nvl(n3,0), n2);

drop index t1_i1;
create index t1_i1 on t1(n1, nvl(n3,0), n2);

drop index t1_i1;
create index t1_i1 on t1(n1, nvl(n3,0), n2);

select
        column_id, column_name, 
        hidden_column, virtual_column, user_generated, internal_column_id,
        data_default
from 
        user_tab_cols 
where 
        table_name = 'T1'
order by 
        internal_column_id
/

You might expect Oracle to drop the system-generated column as it drops the index – but here are the results we now get from querying user_tab_cols:

 COLUMN_ID COLUMN_NAME                      HID VIR USE INTERNAL_COLUMN_ID DATA_DEFAULT
---------- -------------------------------- --- --- --- ------------------ --------------------------------
         1 N1                               NO  NO  YES                  1
         2 N2                               NO  YES YES                  2 "N1"+1
         3 N3                               NO  NO  YES                  3
           SYS_NC00004$                     YES YES NO                   4 NVL("N3",0)
           SYS_NC00005$                     YES YES NO                   5 NVL("N3",0)
           SYS_NC00006$                     YES YES NO                   6 NVL("N3",0)
           SYS_NC00007$                     YES YES NO                   7 NVL("N3",0)
           SYS_NC00008$                     YES YES NO                   8 NVL("N3",0)
           SYS_NC00009$                     YES YES NO                   9 NVL("N3",0)

9 rows selected.

Oracle “forgets” to drop the system-generated virtual column, and generates a new virtual column every time the index is recreated. Since the columns are hidden columns you won’t notice that something has gone wrong if you query the (more commonly used) view user_tab_columns – so there may well be a number of sites which have tables with huge numbers of hidden virtual columns, all quietly working their way towards unexpected ORA-01792 errors.

This “failure to drop” isn’t consistent behaviour – if the index had been defined as (n1, n2, nvl(n3,0)) then the system-generated column would have been dropped every time the index was dropped. I haven’t tried to work out exactly what conditions have to be met for the error to appear but I think it may be something to do with a user-defined virtual column appearing in an index after an expression that has to be supported by a system-generated virtual column.

Bear in mind that if I create two indexes (n1, nvl(n3,0)) and (n2, nvl(n3,0)) then Oracle will generate just one virtual column to support the expression nvl(n3,0), so when I drop one of these indexes Oracle will have to say: “does dropping this index mean I should drop a virtual column?” and I’m guessing that somewhere in that area of code Oracle is bypassing some of the checks it should be doing.

If you have any indexes that contain both virtual columns and column expressions, and if you drop and recreate those indexes from time to time, check user_tab_cols to make sure that you don’t have multiple identical system-generated, hidden, virtual columns that support the expression(s) in those indexes.

In my case I found the following steps were sufficient to clear up the excess occurrences:

• drop the problem index (n1, nvl(n3,0), n2)
• create an index on just the problem expression (nvl(n3,0))
• drop that index (which got rid of all the copies of the generated virtual columns)
• recreate the problem index.

In more complicated cases (e.g. several indexes following this pattern, multiple indexes that include the same expression etc.) you may have to develop a more sophisticated approach.

Oracle has released VirtualBox 7.1. The downloads and changelog are in the usual places. I’ve done an install on a Windows 11 PC and it worked fine. Working with Vagrant? The current version of Vagrant is 2.4.1, which doesn’t support VirtualBox 7.1. You can make it work by doing the following. Edit the “meta.rb” file, … Continue reading "Oracle VirtualBox 7.1"

This is a summary of some of the points covered in Juan Loaiza’s keynote at Oracle CloudWorld 2024, with links to relevant posts to help you get started using them. AI Application Development The first section of the presentation discussed generative data and application development. These are some of the subjects he touched on. These … Continue reading "Generative Data Dev and App Dev"