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

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

 

 

 

How To Deinstall SQLTRACE

In this post I like show the correct way to remove the sqltrace schemas and procedures. First of all move to <location_for_sqltrace_packages>/install and connect / as sysdba to the database and execute @sqdrop.sql;

output for the command;

 

 
shone-1/cloud/prod/server102/ADM/SQLT/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jan 29 12:17:22 2018

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

... uninstalling SQLT, please wait

TADOBJ completed.

PL/SQL procedure successfully completed.

SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    my_count INTEGER;
  3
  4  BEGIN
  5    SELECT COUNT(*)
  6      INTO my_count
  7      FROM sys.dba_users
  8     WHERE username = 'TRCADMIN';
  9
 10    IF my_count = 0 THEN
 11      BEGIN
 12        EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
 13      EXCEPTION
 14        WHEN OTHERS THEN
 15          DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
 16      END;
 17
 18      FOR i IN (SELECT directory_name
 19                  FROM sys.dba_directories
 20                 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
 21      LOOP
 22        BEGIN
 23          EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
 24          DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
 25        EXCEPTION
 26          WHEN OTHERS THEN
 27            DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
 28        END;
 29      END LOOP;
 30    END IF;
 31  END;
 32  /
Dropped directory SQLT$STAGE.
Dropped directory TRCA$STAGE.
Dropped directory SQLT$UDUMP.
Dropped directory SQLT$BDUMP.
Dropped directory TRCA$INPUT1.
Dropped directory TRCA$INPUT2.

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER SQLTXADMIN CASCADE

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER SQLTXPLAIN CASCADE

User dropped.

SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE SQLT_USER_ROLE

Role dropped.

SQL>
SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.
SQL>
SQL>

 

HTH – Antonio NAVARRO

ORA-32004 And ORA-27102 After Increase Memory

Today I was increasing the Memory_target for a database when I get the next error;

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 12851347456

The problem looks clear, Not enough space, of course, in my case (using Solaris 11.3 with ZFS) this is a well know problem, if ZFS is on default it is very easy use all physical memory;

You can see the memory consume from root user executin the next command

echo ::memstat | mdb -k

The output is below, in this case the 56% of memroy is used by ZFS

Page Summary                 Pages             Bytes  %Tot
----------------- ----------------  ----------------  ----
Kernel                     1551129             11.8G   18%
Defdump prealloc            182104              1.3G    2%
ZFS Metadata                258844              1.9G    3%
ZFS File Data              4657165             35.5G   56%
Anon                        637853              4.8G    8%
Exec and libs                97157            759.0M    1%
Page cache                   57244            447.2M    1%
OSM                         469504              3.5G    6%
Free (cachelist)             14532            113.5M    0%
Free (freelist)             530445              4.0G    6%
Total                      8388608               64G

From ZFS Doc;

zfs_arc_max

Description
Determines the maximum size of the ZFS Adaptive Replacement Cache (ARC). However, see user_reserve_hint_pct.

Default
75% of memory on systems with less than 4 GB of memory

physmem minus 1 GB on systems with greater than 4 GB of memory

Free the memory in used by ZFS and limit it solve the problem.

 

HTH – Antonio NAVARRO

 

How To Purge A SQL In 10.2.0.4 And 10.2.0.5

Today I was purge a SQL in an old 10.2.0.5 database, I used the DBMS_SHARED_POOL, but it don’t work. Look for notes in ML/MOS I find in this version there is a bug, you can workaround it by set the event 5614566.

I posted a entry some time ago about how to purge simple sql from buffer cache, you can see it following the nex link;

https://sql1.wordpress.com/2014/03/20/get-out-of-here/

Below I show an example using this event;

 
SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         1

1 fila seleccionada.

SBBD*ANTO> alter session set events '5614566 trace name context forever';

SesiĆ³n modificada.

SBBD*ANTO> exec SYS.DBMS_SHARED_POOL.PURGE ('0000000507C52778,720914062', 'S');

Procedimiento PL/SQL terminado correctamente.

SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         0

 

HTH – Antonio NAVARRO