db_securefile PREFERRED results in ORA-60019 with small uniform extents

Last 19c upgrade issue. Working on our new 19c database, several things died off with errors like this:

SQL> execute DBMS_STATS.CREATE_STAT_TABLE ('MYSCHEMA','MYSTATTAB','MYTS');
BEGIN DBMS_STATS.CREATE_STAT_TABLE ('MYSCHEMA','MYSTATTAB','MYTS'); END;

*
ERROR at line 1:
ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8
ORA-06512: at "SYS.DBMS_STATS", line 20827
ORA-06512: at "SYS.DBMS_STATS", line 20770
ORA-06512: at "SYS.DBMS_STATS", line 20765
ORA-06512: at line 1

Our tablespaces had small uniform extents and our 19c database had defaulted the parameter db_securefile to PREFERRED. We bumped our uniform extent sizes up to 1 megabyte and the problem went away. Setting db_securefile to PERMITTED also resolved the issue.

Oracle’s support site has a bunch of good information about this. This might be a relevant bug:

Bug 9477178 : ORA-60019: CREATING INITIAL EXTENT OF SIZE X IN TABLESPACE FOR SECUREFILES

Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to db_securefile PREFERRED results in ORA-60019 with small uniform extents

  1. True, but not really a 19c issue per se. We switched to preferred in 12.1 🙂

    • Bobby says:

      Thanks Connor. That sounds familiar. I probably learned that when I did the 12.1 certification but it really hasn’t mattered to us until this upgrade.

  2. Dan Grotjan says:

    Hi Bobby. First, thanks for posting this. We just upgraded to 19c and are encountering ORA-60019 when utilizing the dbms_obfuscation_toolkit.md5 function. Interestingly, altering the session to set db_securefile to “PERMITTED” does not resolve our issue.

    When you say you “bumped our uniform extent sizes up to 1 megabyte”, what exactly do you mean? Did you create a new tablespace, or alter the existing tablespace? If the latter, which value did you increase – MIN_EXTLEN?

    Thanks, Dan

    • Bobby says:

      Sorry that I did not notice this comment sooner. I dropped and recreated the tablespaces with 1 megabyte uniform extents.

      Bobby

Leave a Reply to Bobby Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.