DBMS_LOCK Must Be Declared In Swingbench

Today I was simulating workload with Swingbench (Version 2.5). I fan of this free tool it is a workload simulator for Oracle databases, of course, HammerDB is a worderful tool too. When executed the first workload I received lots of errors from event log into the Console. It says me that SOE.ORDERENTRY package body was invalid.

When I tryed compile it get the next error;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Advertencia: Cuerpo del paquete modificado con errores de compilación.

Transcurrido: 00:00:00.34
XXXXX>  show err
Errores para PACKAGE BODY SOE.ORDERENTRY:

LINE/COL ERROR
________ _________________________________________________________________
79/13    PL/SQL: Statement ignored
79/13    PLS-00201: identifier 'DBMS_LOCK' must be declared
82/13    PL/SQL: Statement ignored
82/13    PLS-00201: identifier 'DBMS_LOCK' must be declared

Of course, I usually never use the sys user to install anything, in my case I installed Swingbench with system user. The solution is so easy like

 

 
os11> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 17 16:06:38 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>
SQL> grant execute on dbms_lock to soe;

Grant succeeded.

 

And recompile the package body again;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Cuerpo del paquete modificado.

Transcurrido: 00:00:00.42
XXXXX>  show err
No hay errores.

HTH – Antonio NAVARRO

 

Advertisements

How To Cancel A SQL Statement In 18c

Until now is you want to cancel a sql you must kill (in the worst of the cases) or disconnect (in the best) the session who execute the statement. In the new release of Oracle (18c) you can “kill” a statement without kill the session. There are a new clause “cancel sql” into the “alter system” to perform this action.

You need the sqlid and the sid and serial# for session executing the sql_id which you want to kill. The format is show below;

alter system cancel sql ‘sid, serial#, sql_id’;

and an example;

alter system cancel sql ‘403, 35122, 77jhfmw06g05u’;

HTH – Antonio NAVARRO

 

Error ORA-00933 Creating DB Link With Special Characters

Today I get the next error executing a create database link commandd;

ORA-00933: SQL command not properly ended

The syntax is show below

create database link unodos connect to Stevy identified by Pa**word using ‘remotedb.worl.es’;

In this case, the pass have special characters (two asterisks) and Oracle thinks it is sql syntax, you need specify it like string, you can this by user quotes like in the next sample;

create database link unodos connect to Stevy identified by “Pa**word” using ‘remotedb.worl.es’;

 

HTH – Antonio NAVARRO

crsctl stop has vs crsctl stop crs

Last week talking with a coworker, there was the issue of which command to use to stop a clutch, I personally have seen many people use the crsctl stop has, but we really did not know the difference to use stop has or use stop crs, a look at the documentation, always remember, RTFM (Read The Fine Manual) we clarified. Like show below;

use crsctl stop has command for stop a restart (one node OR single-instance)

use crsctl stop crs command for stop a cluster (two or more nodes)

HTH – Antonio NAVARRO

 

Get Stats Preferences

In a previous post I showed the way to get the stats preferences for all database. Today I post the method for all levels (Table, Schema and Database);

TABLE Level:

 
-- Replace  OWNER_USER  by owner user
-- Replace  TABLE_NAME by table name
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'INCREMENTAL'),         
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'GRANULARITY'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'STALE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'ESTIMATE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'CASCADE')
FROM DUAL
/

SCHEMA Level:

   
-- Replace OWNER_USER  by owner user             
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'INCREMENTAL'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'GRANULARITY'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'STALE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'ESTIMATE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'CASCADE') 
FROM DUAL
/

DATABASE Level:

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'INCREMENTAL'),
  DBMS_STATS.get_prefs(pname=>'GRANULARITY'),
  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'CASCADE')
FROM DUAL
/

For all cases you can see histograms like show below;

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt   
FROM DUAL
/

HTH – Antonio NAVARRO

 

List Essential Performance Reports Scritps

Today I like list (and remember in some case) the basics performance reports for the more powerfull utilities in Oracle, ASH, ADDM and AWR. I put where it is and if is for RAC or single instance.

AWR Single instance

STA @$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR RAC

STA @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

 

ADDM Single instance

@STA $ORACLE_HOME/rdbms/admin/addmrpt.sql

ADDM RAC

@STA $ORACLE_HOME/rdbms/admin/addmrpti.sql

 

ASH Single instance

@STA $ORACLE_HOME/rdbms/admin/ashrpt.sql

ASH RAC

@STA $ORACLE_HOME/rdbms/admin/ashrpti.sql

HTH – Antonio NAVARRO

How To Install SQL Trace

In this post I want show how to install SQLT, this tool was designed by Carlos Sierra (https://carlos-sierra.net/) and Mauro Pagano (https://mauro-pagano.com/). It is a powerfull to optimize and analyze SQL.

 

The first of all you need download it from Oracle. Remember SQLT is free but you can need Tuning and/or Diagnostic license. Of course Mauro Pagano has developed other similar tool, and Carlos Sierra has implemented several tools with similar functions, but I will tald about it in other post.

After download and unzip the file connect as sysdba and execute the sqcreate.sql file

 
SQL> connect / as sysdba
SQL> @/users/anr/sqlt/install/sqcreate.sql

This is the output;

       
Ignore errors from here until @@@@@ marker as this is to test for NATIVE PLSQL Code Type
@@@@ marker . You may ignore prior errors about NATIVE PLSQL Code Type
old   1: ALTER SESSION SET PLSQL_CODE_TYPE = &&plsql_code_type
new   1: ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE

Session altered.

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD):                           

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN:
Re-enter password:

PL/SQL procedure successfully completed.

The next step is to choose the tablespaces to be used by SQLTXPLAIN

The Tablespace name is case sensitive.

Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?

Type YES or NO [Default NO]:                       
... please wait

TABLESPACE                     FREE_SPACE_MB
------------------------------ -------------

 * * * * * * * * * * * * 
    R E C O R T ED 
 * * * * * * * * * * * * 

Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]:

PL/SQL procedure successfully completed.

... please wait

TABLESPACE
------------------------------
TEMP_TBS

Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP_TBS

PL/SQL procedure successfully completed.

The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: CLOUD

PL/SQL procedure successfully completed.

SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: T

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

TADOBJ completed.

SQDOLD completed. Ignore errors from this script
  adding: 180216132555_01_sqcreate.log (deflated 96%)

SQCUSR completed. Some errors are expected.

Procedure created.

No errors.
  adding: 180216132956_02_sqcusr.log (deflated 95%)

TAUTLTEST completed.
  adding: 180216132959_09_tautltest.log (deflated 89%)

SQUTLTEST completed.
  adding: 180216132959_10_squtltest.log (deflated 90%)

no rows selected

TACOBJ completed.
  adding: 180216132959_03_tacobj.log (deflated 86%)

SQL> PRO Dropping Libraries for TRCA
Dropping Libraries for TRCA
SQL> SET TERM OFF;
tool_repository_schema: "SQLTXPLAIN"
tool_administer_schema: "SQLTXADMIN"
role_name: "SQLT_USER_ROLE"
Creating Procedures
Creating Package Specs TRCA$G
No errors.
Creating Package Specs TRCA$P
No errors.
Creating Package Specs TRCA$T
No errors.
Creating Package Specs TRCA$I
No errors.
Creating Package Specs TRCA$E
No errors.
Creating Package Specs TRCA$R
No errors.
Creating Package Specs TRCA$X
No errors.
Creating Views
Creating Package Body TRCA$G
No errors.
Creating Package Body TRCA$P
No errors.
Creating Package Body TRCA$T
No errors.
Creating Package Body TRCA$I
No errors.
Creating Package Body TRCA$E
No errors.
Creating Package Body TRCA$R

No errors.
Creating Package Body TRCA$X
No errors.
Creating Grants on Libraries

Tool Version
----------------
12.2.171004

Install Date
----------------
20180216

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID)      /XXXXXXXXXXXXXXX/CLOUD/trace
TRCA$INPUT2(VALID)      /XXXXXXXXXXXXXXX/CLOUD/trace
TRCA$STAGE(VALID)       /XXXXXXXXXXXXXXX/CLOUD/trace
user_dump_dest
background_dump_dest

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 12.1.14 2015/12/06 carlos.sierra mauro.pagano abel.macias@oracle.com
$ */

VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 12.1.14 2015/12/06 carlos.sierra mauro.pagano abel.macias@oracle.com
$ */

VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.5.8 2013/05/10 carlos.sierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 12.1.160429 2016/04/29 carlos.sierra abel.macias@oracle.com$ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
TACPKG completed.

PL/SQL procedure successfully completed.

  adding: 180216133013_04_tacpkg.log (deflated 80%)

SQCOBJ completed. Some errors are expected.
  adding: 180216133042_05_sqcobj.log (deflated 93%)

SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring, Automatic Workload Repository
(AWR) and SQL Tuning Sets (STS).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

pack_license: "T"
enable_tuning_pack_access

PL/SQL procedure successfully completed.

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key

connect_identifier: ""

PL/SQL procedure successfully completed.

Table truncated.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Procedure created.

No errors.

Table truncated.

PL/SQL procedure successfully completed.

Procedure dropped.

Commit complete.

SQSEED completed.
  adding: 180216133243_07_sqseed.log (deflated 80%)

... dropping packages for SQLT
... creating package specs for SQLT$A
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$a;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$C
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$c;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$D
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$d;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$E
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$e;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$H
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$h;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$I
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$i;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$M
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$m;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$R
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$r;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$S
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$s;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$T
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$t;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating views
... creating package body for SQLT$A
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$a;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$C
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$c;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$D
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$d;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$E
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$e;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$H
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$h;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$I
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$i;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$M

SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$m;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$R
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$r;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$S
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$s;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$T
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$t;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;

Creating Grants on Packages ...

LIBRARIES
----------------------------------------------------------------
VALID   PACKAGE      12.2.171004  OSQLT$A
VALID   PACKAGE      12.1.10       SQLT$C
VALID   PACKAGE      12.1.11       SQLT$D
VALID   PACKAGE      12.1.10       SQLT$E
VALID   PACKAGE      12.1.10       SQLT$H
VALID   PACKAGE      12.1.10       SQLT$I
VALID   PACKAGE      12.1.10       SQLT$M
VALID   PACKAGE      12.1.160429   SQLT$R
VALID   PACKAGE      12.1.10       SQLT$S
VALID   PACKAGE      12.1.10       SQLT$T
VALID   PACKAGE      11.4.5.0      TRCA$E
VALID   PACKAGE      11.4.5.0      TRCA$G
VALID   PACKAGE      11.4.5.0      TRCA$I
VALID   PACKAGE      11.4.5.0      TRCA$P
VALID   PACKAGE      11.4.5.0      TRCA$R
VALID   PACKAGE      11.4.5.0      TRCA$T
VALID   PACKAGE      11.4.5.0      TRCA$X
VALID   PACKAGE BODY 12.2.171004  OSQLT$A
VALID   PACKAGE BODY 12.1.14       SQLT$C
VALID   PACKAGE BODY 12.2.171004  OSQLT$D
VALID   PACKAGE BODY 12.1.160429   SQLT$E

LIBRARIES
----------------------------------------------------------------
VALID   PACKAGE BODY 12.1.160429   SQLT$H
VALID   PACKAGE BODY 12.2.171004  OSQLT$I
VALID   PACKAGE BODY 12.2.171004  OSQLT$M
VALID   PACKAGE BODY 12.2.171004  OSQLT$R
VALID   PACKAGE BODY 12.1.10       SQLT$S
VALID   PACKAGE BODY 12.2.171004  OSQLT$T
VALID   PACKAGE BODY 11.4.5.0      TRCA$E
VALID   PACKAGE BODY 12.1.14       TRCA$G
VALID   PACKAGE BODY 12.1.14       TRCA$I
VALID   PACKAGE BODY 11.4.5.8      TRCA$P
VALID   PACKAGE BODY 11.4.5.0      TRCA$R
VALID   PACKAGE BODY 12.1.160429   TRCA$T
VALID   PACKAGE BODY 11.4.5.0      TRCA$X

Deleting CBO statistics for SQLTXPLAIN objects ...                 

13:33:53    0 sqlt$a: -> delete_sqltxplain_stats
13:33:58    5 sqlt$a: 
SQL>

 

Ready for service.

HTH – Antonio NAVARRO