Skip to main content

Key Manager Database

Symptom: SSHMGR_REPORTINGDATA table entries consume too much space, which Oracle does not release after deleting​

If using Oracle as the Key Manager Database, you will need to manually release the reserved space after deleting any table entries. To do this:

  1. Stop all Key Manager services, to prevent database modifications during the operation.

  2. Gain root terminal access to any Key Manager Server, then open Key Manager manage shell:

    # su - sshmgr
    $ . /opt/sshmgr/.runtime/bin/activate
    $ /opt/sshmgr/bin/manage shell
      from django.db import connection

  3. Verify data sizes and allocation of the LOB.

  • To display the actual size of the data inside the LOB:

    with connection.cursor() as cursor:
      sql = "select SUM(dbms_lob.getlength(context)) from SSHMGR_REPORTINGDATA;"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Command completed successfully.")
        rows = cursor.fetchall()
      except Exception as e:
        print(f"ERROR: Command failed: {e}")

    for row in rows:
      print(row)

  • To show the allocated space for the LOB (replace SYS_LOB* with the name of the LOB): with connection.cursor() as cursor:

      sql = "SELECT segment_name, bytes FROM user_segments WHERE segment_name = 'SYS_LOB*';"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Command completed successfully.")
        rows = cursor.fetchall()
      except Exception as e:
        print(f"ERROR: Command failed: {e}")

    for row in rows:
      print(row)

  1. Copy the actual reporting data to a temporary table, in this example we use temp_reportingdata: with connection.cursor() as cursor:

      sql = "create table temp_reportingdata as (select * from SSHMGR_REPORTINGDATA);"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Created reporting temp data table completed successfully.")
      except Exception as e:
        print(f"ERROR: Creating reporting data temp table: {e}")

  2. Truncate the original SSHMGR_REPORTINGDATA table:

    with connection.cursor() as cursor:
      sql = "TRUNCATE TABLE SSHMGR_REPORTINGDATA;"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Truncated SSHMGR_REPORTINGDATA table successfully.")
      except Exception as e:
        print(f"ERROR: Truncating SSHMGR_REPORTINGDATA table: {e}")

  3. Copy the original reporting data back to the SSHMGR_REPORTINGDATA table:

    with connection.cursor() as cursor:
      sql = "insert into SSHMGR_REPORTINGDATA (select * from temp_reportingdata);"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Adding data back to SSHMGR_REPORTINGDATA table was successful.")
      except Exception as e:
        print(f"ERROR: Adding data into SSHMGR_REPORINGDATA table: {e}")

  4. Commit changes:

    with connection.cursor() as cursor:
      sql = "commit;"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Commit completed successfully.")
      except Exception as e:
        print(f"ERROR: running commit: {e}")

  5. If everything was performed successfully to this point, you may delete the temporary table: with connection.cursor() as cursor:

      sql = "drop table temp_reportingdata;"
      try:
        cursor.execute(sql)
        connection.commit()  # Commit if manual commit is needed
        print("Dropped reporting temp data table successfully.")
      except Exception as e:
        print(f"ERROR: In dropping reporting data temp table: {e}")

  6. You may verify the size of the data and the allocation by rerunning the commands in step 3.

  7. Restart Key Manager services.