Use the show sql command to view the SQL statement being written by the Post process to post data to the target database. If the post queue is empty, or if Post is not processing a statement, the show sql command shows the most recent SQL statement processed.
Use this command when you think replication is taking too long, or when Post stops on an error. Knowing which SQL statement is at fault can help you determine what is wrong and whether or not further action is required.
The following is an example of the show sql display:
sp_ctrl (tustin:8852)> show sql
Last SQL statement of queue tustin and instance o.ora920-0.ora920 on tustin
insert into “KWONG”.”KCWTAB2” (“C1”,”C2”) values (:V001,:V002)
Issue this command for the target system.
| Supported sources: | Oracle |
| Supported targets: | All |
| Authorization level: | Viewer (3) |
| Issued for: | target system |
| Related commands: | none |
| Component | Description |
|---|---|
| show sql | Without options, shows all SQL statements. |
| queue queuename |
Use this option to show the SQL statement for a specific named queue.
Queue names are case-sensitive on all platforms. Example: sp_ctrl(sysA)> show sql queue q1 |
| for datasource-datadest |
Use this option to show the SQL for a specific replication stream when you are replicating to or from more than one Oracle instance.
Example: sp_ctrl(sysA)> show sql for o.oraA-o.oraB |
| thread=n |
Use this option to show the SQL for a post processing thread.
Leave no spaces between the components. This option can appear in any order with other options. Example: sp_ctrl(sysB)> show sql thread=1234 |
| session=n |
Use this option to show the SQL for a user session. • session= is a required part of the syntax. • n is a session number displayed with the show post threads command. Leave no space between the components. This option can appear in any order with other options. Example: sp_ctrl(sysB)> show sql session=1234 |
These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.
| Option | Description |
|---|---|
| on host |
Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA |
| on host:portnumber |
Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA:8304 |
| on login/password@host |
Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA |
| on login/password@host:portnumber |
Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304 |
Use the show statusdb command to view the Status Database. Each machine involved in replication has its own Status Database that contains records of key replication events, including those that did not generate an error message or warning at the user interface. This information can alert you to potential problems and help you resolve existing ones.
The show statusdb display includes the following information:
When appropriate, the Status Database refers you to the Event Log if there is more information about an entry.
| Supported sources: | Oracle |
| Supported targets: | All |
| Authorization level: | Viewer (3) |
| Issued for: | source or target system |
| Related commands: | status, lstatus |
| Basic command | Command options | Remote options |
|---|---|---|
| show statusdb | detail |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
| Component | Description |
|---|---|
| detail |
This option displays a higher level of detail for the Status Database. Example: sp_ctrl(sysB)> show statusdb detail |
These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.
| Option | Description |
|---|---|
| on host |
Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA |
| on host:portnumber |
Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA:8304 |
| on login/password@host |
Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA |
| on login/password@host:portnumber |
Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304 |
Use the show scn command to view the Oracle SCN of the last transaction that a Post process applied to the target. The command shows all of the last SCNs when using multiple post queues.
NOTE: This command stops all Post processes in order to obtain the SCN information.
sp_ctrl (sysB)> show scn
For resume replication from ora112
On source activate to scn=4550108289
reconcile queue spdl13 for o.ora112-o.ora112 scn 4550108290
reconcile queue spdl14 for o.ora112-o.ora112 scn 4574108174
reconcile queue spdl15 for o.ora112-o.ora112 scn 5374667318
| Supported sources: | Oracle |
| Supported targets: | Oracle |
| Authorization level: | Viewer (3) |
| Issued for: | target system |
| Related commands: |
activate config |
| Basic command | Remote options |
|---|---|
| show scn |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.
| Option | Description |
|---|---|
| on host |
Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA |
| on host:portnumber |
Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA:8304 |
| on login/password@host |
Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA |
| on login/password@host:portnumber |
Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304 |
The show sync command shows any out-of-sync issues that Post may have encountered. The information is extracted from the Status Database. For each out-of-sync object, the command displays:
To see which SQL statement caused the error, view the SID_errlog.sql log file. The Event Log also will contain a record of the problem.
If nothing is out of synchronization when you issue the show sync command, you will be returned to the sp_crtl prompt.
Issue this command for the target system.
| Supported sources: | Oracle |
| Supported targets: | All |
| Authorization level: | Viewer (3) |
| Issued for: | target system |
| Related commands: | show statusdb |
| Basic command | Remote options |
|---|---|
| show sync |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.
| Option | Description |
|---|---|
| on host |
Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA |
| on host:portnumber |
Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA:8304 |
| on login/password@host |
Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA |
| on login/password@host:portnumber |
Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304 |
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
