The 'Create Archive Scripts' Action

The 'Create Archive Scripts' action provided by Valuemation Console can be used for Oracle, SQL Server and Maria DB. For DB2 see 'Archive Creation - DB2'.

If possible, always create the archive schema using the 'Create Archive Scripts' action. This takes care of everything needed, including necessary privileges for the database user. Using other methods (such as exporting data structure from SQL Developer or using console templates) should be avoided as these ways tend to be too sensitive to user error and they do not handle accompanying aspects such as privileges, tablespaces etc.

The archive is created in two steps:

1. Run the 'Create Archive Scripts' action
2. Execute the resulting DDL scripts.

Note that the Console has to be specifically enabled before it can be used, see the 'Console' section for details.

Archive creation steps:

1. Open the Valuemation Console.

   In Valuemation, go to the 'Customize' menu and select 'Console'.

2. In the Console, go to the 'SQL' menu and select 'Create Archive Scripts'. (Note that the Console GUI is always in English.)
3. The 'Create Archive Scripts' dialog opens. The content of the dialog will vary according to used database platform. For Oracle, the dialog may look like this:

   

   Specify name for the archive schema and type database access password. Other parameters in the dialog will be prefilled.

4. Click 'Ok'.
   • The dialog entries are subject to validation, an error message 'Some input parameters are not specified' gets displayed if anything is missing.
   • If the specified database schema already exists, a warning dialog 'The given schema name already exists. Do you want to drop it?' gets displayed. In testing environments, this may often be the case and it will probably be safe to drop the schema. In production environments, make sure that deleting the schema is the correct way to proceed.
5. Script creation

   Upon clicking 'Ok' in the previous step, a new tab 'ARCHIVE tables/views' opens and the script generation starts. The results are continuously written to the new tab.

   After that another tab 'ARCHIVE indexes' opens and the script generation continues while the results are continuously written to the new tab.

   Each of the two script generation processes may take a few minutes. When the processes are finished, the cursor is on the first line of each script.

   Note that although the tables must not contain foreign key constraints, there will be a section at the end of the script containing some foreign key constraints referencing the same table. This is a correct result as these foreign key constraints are needed for correct deletion of object hierarchy.

   On Oracle, each table should contain the table options (e.g. tablespace) as prefilled in the input dialog. The same applies to indexes.

   Possible output:

   

6. Script execution

   For performance reasons, the script in tab 'ARCHIVE indexes' should be executed after the archive creation. At this point, just save the script as file.

   Execute the script in tab 'ARCHIVE tables/views'. With the cursor at the beginning of the script, use action 'Execute SQL statements from here' (keyboard shortcut 'Ctrl+F9').

   The execution may take some time. The cursor in the 'ARCHIVE tables/views' follows the execution process and the status line at the bottom of the window displays information about the progress.

   When the execution is finished, a new archive scheme is created and filled with data.

   As the very last step, configure the ARCHIVE Valuemation session (if not configured already). Make sure the session is connected to the newly created archive schema.