Manage Replay capture and schema

Find out how DSIDE_REPLAY package can help you stopping, monitoring, managing, upgrading or uninstalling a Replay capture or schema.

Caution: to avoid facing locking situations, all running capture jobs must be stopped before performing any maintenance on Replay schema or on Oracle dictionary.

Stopping a Replay capture job #

While running and gathering data, there are three ways to interrupt the Replay capture job:

• – The job encounters an unexpected Oracle error.
  This error is displayed, and the GATHER job is immediately stopped.
• – The maximum number of running hours, given by the “maxrun” parameter at start time, has been reached.
• – The user cancels the gathering job by calling the DSIDE_REPLAY.REQUEST_STOP procedure.

Monitoring a Replay capture job #

DSIDE_REPLAY package provides procedures and functions helping monitoring if a GATHER job is already running and, if relevant, what this job is currently being performing.

Summary of DSIDE_REPLAY monitoring procedures and functions:

Name	Type	Description
GET_CURRENT_MESSAGE	Function	If a capture is running, display the activity currently being processed by this job.
IS_JOB_ALREADY_RUNNING	Function	Indicates if a Gather job is already running. Used to prevent a new job to be launched when another one is already running.
REQUEST_STOP	Procedure	Request the currently running job to stop.
SHOW_RUNNING_JOBS	Procedure	Give the list of every sessions running as a Gather job, and their current status.

GET_CURRENT_MESSAGE function #

This function displays the command that is currently being performed by the Gather job if a job is running.

Syntax:

DSIDE_REPLAY.GET_CURRENT_MESSAGE
RETURN VARCHAR2;

IS_JOB_ALREADY_RUNNING function #

This function indicates if one or more Gather jobs are currently running. If no job has been executed yet, the function returns 0. Otherwise the function returns 1 or a higher value.

Syntax:

DSIDE_REPLAY.IS_JOB_ALREADY_RUNNING
RETURN NUMBER;

REQUEST_STOP procedure #

This procedure requests any running Gather job to stop, before performing the next snapshot.

Syntax:

DSIDE_REPLAY.REQUEST_STOP(
stop_requested IN BOOLEAN);

Parameters:

Parameter	Description
stop_requested	True: requests the Gather job to stop. False: allows subsequent jobs to run without stopping. Default value = true.

Notes:

The sent STOP message will be taken into account by the capture job at the end of the snapshot being processed or at the end of the sleep time being waited for. That means the STOP signal can take until “interval” time before being actually efficient and the job actually stopped.

After having used the REQUEST_STOP(true) procedure, a REQUEST_STOP(false) must be issued in order to allow subsequent executions of the DSIDE_REPLAY.GATHER procedure.

SHOW_RUNNING_JOBS procedure #

This procedure gives the list of every running Gather, and the associated status.

Syntax:

DSIDE_REPLAY.SHOW_RUNNING_JOBS;

Note:

Before running this procedure, the sqlplus “serveroutput” option must be turned ON.

Replay Schema administration #

DSIDE_REPLAY package also provides administration facilities.

Summary of DSIDE_REPLAY administration procedures and functions:

Name	Type	Description
ADD_DAYS	Procedure	Add empty days to the current Replay schema
DROP_DAY	Procedure	Remove an existing day from the current Replay schema
EXPORT_DAYS	Procedure	Export one or several days for sharing or retaining purpose
GET_LIST_OF_DAYS	Function	Return the list of days that have been collected
GET_LIST_OF_DAYS	Procedure	Display the list of already allocated days
TRUNCATE_DAY	Procedure	Truncate the tables content of an existing Replay day

ADD_DAYS procedure #

This procedure creates new tables that will be used and filled when gathering data with more retention.

Syntax:

DSIDE_REPLAY.ADD_DAYS(
number_of_days IN NUMBER);

Parameters:

Parameter	Description
number_of_days	The number of days to be added to the current Replay schema. Default value = 1, meaning one new day to be created.

DROP_DAY procedure #

This procedure drops tables associated to a day number.

Syntax:

DSIDE_REPLAY.DROP_DAY(
day_num IN NUMBER);

Parameters:

Parameter	Description
day_num	The number of the day to be dropped.

EXPORT_DAYS procedure #

This procedure allows to export one or several days into a dumpfile for sharing or retaining purposes.

Syntax:

DSIDE_REPLAY.EXPORT_DAYS(
days                   IN LIST_OF_DAYS,
directory_name_or_path IN VARCHAR2,
return_message         OUT VARCHAR2);

Parameters:

Parameter	Description
days	The list of days to be exported. This list can contain only one day or several days.
directory_name_or_path	The name or path of the Oracle directory where the dumpfile will be created. The user must have privileges on this directory.
return_message	At the end of the export, this message informs about the way the procedure ran, in case of success or error.

Usage sample:

set serveroutput on
variable return_message varchar2(2048);
begin
DSIDE_REPLAY.EXPORT_DAYS(
days=>list_of_days(2,5),
directory_name_or_path=>'DATA_PUMP_DIR',
return_message=>:return_message);
end;
/
select :return_message from dual;

This PL/SQL block requests the export of Replay days ’02’ and ’05’, by creating a dumpfile into DATA_PUMP_DIR Oracle directory.
For more comfort, this procedure is also encapsulated in d.side Console, to allow the user to export days in a few clicks.

Important:
The dumpfile created by this procedure must be processed by the import_replay_day.sql script.
Please, refer to « d.side Support Import Replay Day » guide for more details.

Required privileges:

To be able to run export or import Replay day feature, the Replay schema owner must be granted the following privileges:

grant read, write on directory directory_name to replay_user ;

GET_LIST_OF_DAYS function #

This function returns the list of tables that already contain collected data.

Syntax:

DSIDE_REPLAY.GET_LIST_OF_DAYS
RETURN LIST_OF_GATHER_DAYS;

Usage sample:

SELECT day_num, to_char(day_date, 'YYYY-MM-DD - DAY', 'nls_date_language=english') DAY
FROM TABLE(DSIDE_REPLAY.GET_LIST_OF_DAYS) order by 2 desc;

Output sample:

---

02 2026-01-07 - WEDNESDAY
01 2026-01-01 - THURSDAY

GET_LIST_OF_DAYS procedure #

This procedure displays the list of existing tables and if they already contain a collected day.

Syntax:

DSIDE_REPLAY.GET_LIST_OF_DAYS;

Output sample:

DY DATE
-- ----------
01 2017-06-12
02 -           # Means "empty". No data has been collected yet in tables number 02.
03 2017-06-10
04 2017-06-11

Note:

Before running this procedure, the sqlplus “serveroutput” option must be set to ON.

TRUNCATE_DAY procedure #

This procedure removes tables content for a given tables number.

Syntax:

DSIDE_REPLAY.TRUNCATE_DAY(
day_num IN NUMBER,
reuse_storage IN BOOLEAN);

Parameters:

Parameter	Description
day_num	The number of the tables that must be truncated.
reuse_storage	Do the TRUNCATE TABLE statements use DROP or REUSE STORAGE clause? Default value = false, a DROP STORAGE is performed.

Upgrading a Replay schema #

When downloading a new version of dsreplay.sql script, DSIDE_REPLAY package can easily be upgraded, following these few steps:

• 1. STOP Replay capture potentially running on the Replay schema to be upgraded.
• 2. Wait until capture is actually stopped.
• 3. Run @dsreplay.sql script, connected as Replay user, to upgrade DSIDE_REPLAY package.
• 4. Restart Replay capture if previously stopped.

Note: for more comfort, upgrade is available from d.side Console.
For more details about d.side Console, please refer to « d.side Console User Guide ».

Uninstalling a Replay schema #

Once the Replay gathering job is interrupted, removing Replay feature from your database can be performed by executing the following statement:

drop user REPLAY_USER_NAME cascade ;

REPLAY_USER_NAME must match the user initially created with dscreate.sql script.
You need to be connected with an Oracle user that has been granted the privilege to drop users.

Replay schema support #

DSIDE_REPLAY package provides functions that could be useful when talking with d.side software support teams.

Summary of DSIDE_REPLAY support functions:

Function	Description
GET_ORACLE_VERSION	Return the Oracle version on which DSIDE_REPLAY package is running.
GET_PACKAGE_VERSION	Return DSIDE_REPLAY PL/SQL package version.
GET_SCHEMA_VERSION	Return the Replay schema version.

GET_ORACLE_VERSION function #

This function gives the Oracle version on which DSIDE_REPLAY PL/SQL package is running. This helps preventing the Replay Schema to be out of date when upgrading Oracle dictionary.

Syntax:

DSIDE_REPLAY.GET_ORACLE_VERSION
RETURN VARCHAR2;

GET_PACKAGE_VERSION function #

This function gives the DSIDE_REPLAY PL/SQL package version.

Syntax:

DSIDE_REPLAY.GET_PACKAGE_VERSION
RETURN VARCHAR2;

GET_SCHEMA_VERSION function #

This function gives the Replay schema version that has been created with the dscreate.sql script.

Syntax:

DSIDE_REPLAY.GET_SCHEMA_VERSION
RETURN VARCHAR2;