Oracle Schema User SQL scripts

This topic describes how to run the built-in SQL scripts for Oracle Schema Users.

StarTeam Server SQL Scripts for Oracle Schema Users

This section lists the available scripts and the recommendations of how often they should be run.

The SQL scripts for Oracle schema users that you may run are located in the Micro Focus\StarTeam Server <version>\DBScripts\Oracle_Scripts folder.

The following table lists the SQL scripts that you are most likely to need. Some should be run manually on a regular basis. The table recommends the frequency for running these scripts. You may adjust the frequency depending on the StarTeam Server usage at your facility. Run scripts at times when the server is least used, such as overnight or on weekends.

In addition to running these scripts, you should also run a Purge option from the Server Administration tool to remove deleted views from the database. We recommend purging the database after you have deleted one or more views from a project. See the Server Administration Tool Help for information on the Purge option.

Caution: Be sure to backup your StarTeam Server schema user, as necessary, and verify these backups periodically. You should restore and test backups of your StarTeam Server project data on a test system. Restoring and testing your backups helps to ensure that your data is being backed up correctly.
starteam_oracle_compute_stats.sql

Run: weekly

This script updates the statistics in the database distribution page for all tables in the database. This data enables the query optimizer to choose the right index for a given query.

starteam_oracle_dropall.sql Run: only if necessary
Caution: Running this script deletes all StarTeam Server tables and the data they contain from the database. This script can be executed from either Sql*Plus or SQL*Worksheet. Use this script with extreme caution.

One use case example is if you migrate a StarTeam Server configuration to another database, you might use starteam_oracle_dropall.sql to remove tables from the original database. Another example is if you mistakenly add the StarTeam Server tables to a tablespace other than the StarTeam Server tablespace, use the starteam_oracle_dropall.sql script to remove them.

starteam_oracle_rebuild_indexes.sql

Run: weekly

This script rebuilds the database indexes and configures the storage parameters for the index tablespace. The script assumes that the indexes are located in a tablespace named INDX. If your index tablespace uses a different name, edit starteam_oracle_rebuild_indexes.sql to reflect the correct tablespace name. Run the starteam_oracle_rebuild_indexes.sql script weekly to enhance database data retrieval.

Back to top

Running SQL Scripts for Oracle Schema Users

StarTeam Server comes with some SQL scripts written specifically for use with your database. These scripts help you maintain and tune StarTeam Server databases. You run some SQL scripts after installation, some on a weekly basis for database performance maintenance, and some scripts are run for you automatically by StarTeam Server.

Note: The Oracle_Scripts folder contains several subfolders: Create_Stored_Procedures, Drop_Stored_Procedures, Install, Preinstall, Generic, and DW (for Data Warehouse). The scripts in these subfolders are run by StarTeam Server as needed. Never execute any of them directly from an external database user interface, such as SQL*Plus or SQL *Worksheet.

 

To run a SQL script for Oracle schema users:

  1. Go to the command prompt.
  2. Change directories to the directory containing the StarTeam Server SQL scripts for Oracle schema users.
  3. At the command prompt, type: sqlplus username/password@servicename
    username The StarTeam Server Oracle Schema User Name.
    password The StarTeam Server Oracle Schema Password.
    servicename The Net Service Name created using Oracle Net 8 Easy Config.

  4. Execute the script.

    For example, to execute the starteam_oracle_compute_stats.sql script, type @starteam_oracle_compute_stats.sql and press Enter.

Back to top