3 Replies Latest reply on Mar 12, 2020 6:13 PM by L. Fernigrini

    XE 18c full database, how to clean up - and compression does not impact?

    Tailor K.

      Hello.

       

      I received a small classic EXP file from a client, with 7 GB to simulate a situation with our ERP. Then I tried to put the data on a XE 18c database, created as a non PDB database to emulate best the origin database (a simple instance 11.2 standard edition one).

       

      The IMP stopped at the 'middle' of the file with error "ORA-12954: A solicitacao excede o tamanho maximo para o banco de dados que e 12 GB"; there are two datafiles separating data and index, and the final database has growed up to this (it is a Windows installation):

      10/03/2020  13:37        10.600.448 CONTROL01.CTL

      10/03/2020  13:37        10.600.448 CONTROL02.CTL

      10/03/2020  13:25       209.715.712 REDO01.LOG

      10/03/2020  13:37       209.715.712 REDO02.LOG

      10/03/2020  13:21       209.715.712 REDO03.LOG

      10/03/2020  13:37       492.838.912 SYSAUX01.DBF

      10/03/2020  13:37       891.297.792 SYSTEM01.DBF

      10/03/2020  13:30       757.080.064 TEMP01.DBF

      10/03/2020  13:37     2.049.974.272 UNDOTBS01.DBF

      10/03/2020  13:30         5.251.072 USERS01.DBF

      10/03/2020  13:32     8.589.942.784 WSI_DADOS.DBF

      10/03/2020  13:30     6.119.497.728 WSI_INDEX.DBF

       

      So how should we calculate the 12 GB limit ? The database went to a 'read only' state after ORA-12954, not even dropping my tablespaces is allowed - I had to drop the entire database and recreate it.

       

      The second question is about compression - I tried this to see how far could it go on a new try:

       

      ALTER TABLESPACE WSI_DADOS DEFAULT ROW STORE COMPRESS ADVANCED;

      ALTER TABLESPACE WSI_INDEX DEFAULT ROW STORE COMPRESS ADVANCED;

       

      Nothing changed, it stopped at the same point - does compression really work on XE 18? Or it won´t work with imported tables, or perhaps with the classic IMP format ?

       

      Tips welcome ... Thanks!

        • 1. Re: XE 18c full database, how to clean up - and compression does not impact?
          L. Fernigrini

          Hi, the size of the EXP files does not necessarily match the size on the database. The main thing to consider is that indexes "data" is not included in the export file, just the definition.

           

          I would do the following

           

          1) Manually create the tables, including the COMPRESS FOR OLTP clause or whatever compression suits yo better. You can get all table definition from the exp file using the SHOW=Y option (see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:559256199914 )

          2) Run the import with the IGNORE=Y and INDEXES=N options, to avoid the import to fail with the tables that already exists and creating indexes...

          IGNORE

          Default: n

          Specifies how object creation errors should be handled. If you accept the default, IGNORE=n, Import logs or displays object creation errors before continuing.

          If you specify IGNORE=y, Import overlooks object creation errors when it attempts to create database objects, and continues without reporting the errors.

          Note that only object creation errors are ignored; other errors, such as operating system, database, and SQL errors, are not ignored and may cause processing to stop.

          INDEXES

          Default: y

          Specifies whether or not to import indexes. System-generated indexes such as LOB indexes, OID indexes, or unique constraint indexes are re-created by Import regardless of the setting of this parameter.

          You can postpone all user-generated index creation until after Import completes, by specifying INDEXES=n.

          3) Create the indexes with the appropriate compression option, consider skipping some of them if you are short of space...

          • 2. Re: XE 18c full database, how to clean up - and compression does not impact?
            Tailor K.

            Hello,

             

            I got it ... in fact, an exported DMP does not point a fair database size, just some notion.

             

            So when I change my local tablespaces default store parameters to compress everything, the original tables on the DMP file (without any compression instruction) will be imported as it was originally, ignoring my new 'default', right ?

            I supose it´s different from manually creating a new table that will 'inherit' compression and other storage characteristics from tablespace defaults if not explicitally specified...

             

            And what about 'cleanning' a full database that has reached its limits, is there any option, or I do need to export my data and put it on a new database?

             

            Thanks for your attention.

            • 3. Re: XE 18c full database, how to clean up - and compression does not impact?
              L. Fernigrini
              I got it ... in fact, an exported DMP does not point a fair database size, just some notion.

              I would not take the size of the EXP file as an indicator of the disk space required at all. It depends on block size, pct_free, compression, additional indexes, etc etc etc.

               

              So when I change my local tablespaces default store parameters to compress everything, the original tables on the DMP file (without any compression instruction) will be imported as it was originally, ignoring my new 'default', right ?

              I supose it´s different from manually creating a new table that will 'inherit' compression and other storage characteristics from tablespace defaults if not explicitally specified...

               

              If you create them "manually" then you are sure they are compressed. You may make a test by importing a single table once you have set the tablespace compression and verify if the IMP process uses the tablespace default, or uses compress information from the dmp file. My guess is that the dump may already contain the compression type on the stored DDL so it will overwrite the tablespace settings, but it is worth a try.