Fixing the ORA-00600: [ktfbtgex-7] error

It was a normal day at the office, when I noticed an ORA-00600 on a production database. Upon investigation, I found the following details in the alert.log file:

ORA-00600: internal error code, arguments: [ktfbtgex-7], [2031625], [128], [2031624], [], [], [], []

This error was related to extent management bitmap for locally managed tablespaces. A new extent has not been created because it could not fit into its datafile. As I found more details about the ktfbtgex-7 error code, here’s how to interpret the error parameters:

First argument of the error is the first block adress of the extent to create. Second argument is the length of the extent to create. Third argument is the last block adress in the datafile.

As we can see in my example above:

2031625 + 128 = 2031753

2031753 - 2031624 = 129

So the error comes from this: 2031625 + 128 > 2031624

As we can see that the extent is 129 bytes longer, and therefore it’s not created by Oracle to avoid any data corruption. To fix this, you need to rebuild the extent management bitmap associated to the tablespace.

In this example, 2031625 is already greater than 2031624. Which definitively point a bitmap issue.

With the database in startup restrict mode. You can deduce the tablespace name from the trace file which provides the SQL statements.

And finally, here is how to rebuild the TEST tablespace bitmap (to be done as SYS user):

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('TEST');

Tags:

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read