AUTHID CURRENT (USER - DEFINER)

AUTHID CURRENT_USER

Specify CURRENT_USER to indicate that the package executes with the privileges of CURRENT_USER. This clause creates an invoker's rights package.
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the package resides.

AUTHID DEFINER

Specify DEFINER to indicate that the package executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the package resides. This is the default and creates a definer's rights package.


WARNING: Writing PL/SQL code with the default authid definer, can facilitate SQL injection attacks, because an intruder would get privileges that they would not get if they used authid current_user.

PL/SQL Function Result Cache

On the PL/SQL Function Result Cache

By Steven Feuerstein  

Best practices—and preparation—for PL/SQL in Oracle Database 11g

I have been reading that Oracle is launching the 11th release of its database. Very exciting! But here's the problem: I don't think I'll be able to use it for another two years. So why should I even care about the new PL/SQL features of this future (for me) release?

Ah, yes, the real world. Oracle comes out with a new database release, and by the time it does so, its developers are already focused on the next new release. People like me start writing about, demonstrating, and even training on the newer release. And then there's almost everybody else: still on older releases, hoping and praying that someday maybe their management will see fit to catch up.

I feel your pain.

Having said that, I do think it makes an awful lot of sense to learn now about what Oracle Database 11g will have to offer you and your company in the future. The reason is very simple: once you see what is going to be available in Oracle Database 11g, you will probably change the way you write your code now !

I would say that the single most important new PL/SQL feature in Oracle Database 11g is thePL/SQL function result cache . Quite a mouthful, but then it is quite a feature.

I offer in this answer a quick overview of this feature, and I conclude by discussing how knowing about this feature should affect the way you write PL/SQL programs for earlier Oracle Database releases.

Suppose I am on a team that is building a human resources application. The employees table is one of the key structures, holding all the data for all the employees. Hundreds of users execute numerous programs in the application that read from this table—and read from it very often. Yet the table changes relatively infrequently, perhaps once or twice an hour. As a result, the application code repeatedly retrieves from the block buffer cache what is mostly static data, enduring the overhead of checking to see if the particular query has already been parsed, finding the data in the buffer, and returning it.

The team needs to improve the performance of querying data from the employees table. Currently, we use the following function to return a row from the employees table: 

FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
    l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
      INTO l_employee
      FROM employees
    WHERE employee_id = employee_id_in;

    RETURN l_employee;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
       /* Return an empty record. */
       RETURN l_employee;
END one_employee;

In Oracle Database 11g, however, we can add a line to the header of this function as follows:  

FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
   RESULT_CACHE RELIES_ON (employees)
IS
    l_employee   employees%ROWTYPE;
BEGIN
.
.
.
 

This RESULT_CACHE clause tells Oracle Database that it should remember (store in a special in-memory result cache) each record retrieved for a specific employee ID number. And when a session executes this function and passes in an employee ID that was previously stored, the PL/SQL runtime engine will not execute the function body, which includes that query.

Instead, it will simply retrieve the record from the cache and return that data immediately. The result is much faster retrieval.

In addition, by specifying RELIES_ON (employees), we inform Oracle Database that if any session commits changes to that table, any data in the result cache drawn from the table must be invalidated. The next call to the one_employee function would then have to execute the query and retrieve the data fresh from the table.

Because the cache is a part of the System Global Area (SGA), its contents are available to all sessions connected to the instance. Furthermore, Oracle Database will apply its "least recently used algorithm" to the cache, to ensure that the most recently accessed data will be preserved in the cache.

Prior to Oracle Database 11g, a similar kind of caching was possible with package-level collections, but this cache is session-specific and located in the Process Global Area (PGA). This means that if I have 1,000 different sessions running the application, I could use up an enormous amount of memory in addition to that consumed by the SGA.

The PL/SQL function result cache minimizes the amount of memory needed to cache and share this data across all sessions. This low memory profile, plus the automatic purge of cached results whenever changes are committed, makes this feature of Oracle Database 11g very practical for optimizing performance in PL/SQL applications.

Analyze Performance and PGA Memory Impact

To test the improvement in performance and the impact on PGA memory over repeated queries of the data, I put together a set of scripts, available at oracle.com/technetwork/oramag/oracle/07-sep/o57plsql.zip, that compares three different ways to retrieve a row of employee data:

1. Execute the query repeatedly
2. Cache all the rows of the employees table in a packaged collection and then retrieve the data from that cache
3. Use the PL/SQL function result cache to avoid repetitive querying

To try this out yourself, unzip the o57plsql.zip file and run the 11g_emplu.tst script. It should take about five or six seconds to complete, and then you should see results like this: 

PGA before tests are run:
session PGA:  910860 bytes

Execute query each time 
Elapsed: 4.5 seconds. 
session PGA:  910860 bytes

Cache table in PGA memory 
Elapsed: .11 seconds. 
session PGA: 1041932 bytes

Oracle Database 11
                               g result cache 
Elapsed: .27 seconds. 
session PGA: 1041932 bytes
                            

Here are my conclusions from this admittedly incomplete analysis: 

• The Oracle Database 11g PL/SQL function result cache is, indeed, much faster than repetitive querying. In this test, it was over an order of magnitude faster.  
• A packaged collection cache is even faster, most likely because the PL/SQL runtime engine can access the data from PGA memory rather than SGA memory. Unfortunately, this also means that the consumption of memory occurs on a per-session basis, which is not very scalable. 
• The packaged collection approach consumed additional PGA memory, but the Oracle Database 11g function result cache did not.

And then, of course, there are the other key advantages of the function result cache: automatic invalidation of cache contents when a dependent table is changed, the fact that the cache is shared across sessions, and the application of the least recently used algorithm to the memory in the cache.

So Why Should You Care Now?

"All right," you may be saying to yourself, "It's cool. Super cool. But I still can't use it for two years or more, so what good does that do me now?"

You may not be able to use the PL/SQL function result cache yet, but you can write your code now so that when you eventually upgrade to Oracle Database 11g, you will be able to quickly and easily use this cache in your application code.

In other words, you can and should prepare now for this future feature.

How do you do that? By placing all your queries (at least those against tables that change infrequently but are queried often) inside functions, so that you can easily add the RESULT_CACHE clause.

Think about it: today you probably don't do that. Instead, whenever you need data from the database, you write the required query, right there in the application logic you are writing (whether that logic resides in the back end—other PL/SQL programs—or the front end—languages such as Java).

Next Steps READ more about  PL/SQL function result cache  oracle.com/technnetwork/database/documentation READ more  Best Practice PL/SQL  PL/SQL Practices DOWNLOAD  Oracle Database 11g  test scripts for comparing row retrieval speed

And that same query (or some minor variation on it) will likely appear in multiple places in your application code. Why not? It is so easy to write those SQL statements; that's one of the beauties of PL/SQL. But that ease of use in executing SQL inside PL/SQL makes us all take SQL for granted, and when you upgrade to Oracle Database 11g, you will pay the price.

If after upgrading, you want to take advantage of RESULT_CACHE, you will have to find every affected SQL statement and either put the RESULT_CACHE hint inside that query (that's right, this feature is available natively within SQL as well as for functions) or construct the function, put the query inside it, find each of the applicable queries, and replace the query with the function call.

Certainly both of these approaches are eminently doable, but they are also very unlikely to happen. IT managers are loathe to go into existing, working production code and upset the applecart by making lots of changes.

If, conversely, you start right now , in Oracle9i Database or Oracle Database 10g, to place your queries inside functions, you will almost instantly be able to upgrade your code to use this fantastic new feature when you upgrade to Oracle Database 11g.

And, best of all, the application code that calls the function will not have to be changed at all! Your manager will be very impressed.

And that is why you should learn about the new features of Oracle Database 11g. Today.

PL/SQL White Lists

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1 (12.1)

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 (12.1) to allow you to add an extra layer of security to your PL/SQL objects.

• Setup
• Basic Usage
• Cross-Schema Usage
• Unit Type
• Package White Lists
• Package White Lists (12.2 Update)

Setup

The following examples will use the two database users defined below.

CONN sys/password@pdb1 AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CREATE SESSION, CREATE PROCEDURE, CREATE TABLE TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CREATE SESSION, CREATE PROCEDURE TO test2;

Basic Usage

The procedure below includes an ACCESSIBLE BY clause, indicating it can only be called by an object called calling_proc.

CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
  ACCESSIBLE BY (calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>

Notice we have not created the calling_proc procedure yet, but no error is produced. This is because the objects referenced by the ACCESSIBLE BY clause are not checked at compile time. Only the syntax of the clause is checked.
We can create the calling_proc procedure and use it to call the protected_proc procedure.

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : calling_proc');
  protected_proc;
END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST1 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>

If we attempt to call the protected_proc procedure directly, we get an error.

SQL> EXEC protected_proc;
BEGIN protected_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECTED_PROC
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

If we try to create a new object that references the protected_proc procedure and the new object is not in the white list, we get a compilation error.

CREATE OR REPLACE PROCEDURE another_calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : another_calling_proc');
  protected_proc;
END;
/
Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANOTHER_CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3  PL/SQL: Statement ignored
4/3  PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>

Cross-Schema Usage

If no schema is explicitly mentioned in the white list, it is assumed the object listed is in the same schema as the object with the ACCESSIBLE BY clause. For example, if we switch to another user and create a procedure called calling_proc that accesses test1.protected_proc, it will not work.

CONN test1/test1@pdb1
GRANT EXECUTE ON protected_proc TO test2;

Grant succeeded.

SQL> 


CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3  PL/SQL: Statement ignored
4/3  PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>

For this to be successful, we must add the reference into the white list using the fully qualified object name. The example below includes an object reference without a schema prefix, signifying current schema (TEST1) and one with an explicit schema reference.

CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
  ACCESSIBLE BY (calling_proc, test2.calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>

Now, we can reference and execute the protected_proc procedure from the other user.

CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Procedure created.

SQL>


SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST2 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>

Unit Type

The examples so far have not specified the unit type (object type) when defining the white list references, which means any compatible object type with the correct name in the correct schema will pass the white list test. If we want to make the test more stringent, we can specify not just the name, but the unit type also. Some examples are shown below.

ACCESSIBLE BY (PACKAGE calling_pkg)
ACCESSIBLE BY (PROCEDURE calling_proc)
ACCESSIBLE BY (FUNCTION calling_func)
ACCESSIBLE BY (TYPE calling_type)
ACCESSIBLE BY (TRIGGER calling_trg)

There seem to be some discrepancies about which unit types can access PL/SQL objects that use the ACCESSIBLE BY clause. The New Features Guide suggests tables, indexes and views can also be referenced in the white list, but the unit types TABLE, INDEX and VIEW are not allowed. The following example shows that function-based indexes and views do not work against a function using the ACCESSIBLE BY clause.

CONN test1/test1@pdb1

CREATE OR REPLACE FUNCTION protected_func (id IN NUMBER)
  RETURN NUMBER
  ACCESSIBLE BY (t1_fbi, t1_vw)
AS
BEGIN
  RETURN id;
END;
/

CREATE TABLE t1 (
  id NUMBER
);


SQL> CREATE INDEX t1_fbi ON t1(protected_func(id));
CREATE INDEX t1_fbi ON t1(protected_func(id))
                          *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>


-- Views are created normally, but fail when run.
CREATE OR REPLACE VIEW t1_vw AS
SELECT protected_func(id) AS id_vw
FROM   t1;

View created.

SQL> SELECT * FROM t1_vw;
SELECT * FROM t1_vw
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>

Package White Lists

In Oracle 12.1 the ACCESSIBLE BY clause is only valid at the top-level of the package specification. It can not be applied to individual packaged procedures, functions or types within the package. The following example shows a white list applied to a package specification.

CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>

Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error.

CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);
END;
/

Warning: Package created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0  PLS-00157: Only schema-level programs allow ACCESSIBLE BY
SQL>

The ACCESSIBLE CLAUSE is not valid in the package body. It can only be defined in the package specification.

CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>


CREATE OR REPLACE PACKAGE BODY protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc AS
  BEGIN
    NULL;
  END;
END;
/

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3  PLS-00103: Encountered the symbol "ACCESSIBLE" when expecting one
  of the following:
  is as compress compiled wrapped

SQL>

Package White Lists (12.2 Update)

In Oracle database 12.1 it was only possible to use a white list for a whole package. In Oracle 12.2 this limitation is no longer present, making the concept of white lists for packages much more granular. It is now possible to white list individual subprograms or a package.

CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func);
END;
/

CREATE OR REPLACE PACKAGE BODY protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc)
  AS
  BEGIN
    NULL;
  END;

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func)
  AS
  BEGIN
    RETURN NULL;
  END;
END;
/

sys_refcursor

You can do this:

PROCEDURE abc( p_cursor IN SYS_REFCURSOR) IS
   v_sol_id blt.sol_id%TYPE;
   v_bill_id blt.bill_id%TYPE;
   v_bank_id blt.bank_id%TYPE;
BEGIN
   LOOP
      FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;
      EXIT WHEN p_cursor%NOTFOUND;
      ...
   END LOOP;
END;

Then use it:

DECLARE
   v_cursor SYS_REFCURSOR;
BEGIN
   OPEN v_cursor FOR
      SELECT  BLT.sol_id,
              BLT.bill_id,
              BLT.bank_id
      FROM BLT;
   abc (v_cursor);
   CLOSE v_cursor;
END;

CREATE OR REPLACE FUNCTION func_refcur(

    ip_dept_id NUMBER)

  RETURN sys_refcursor

IS

  l_rc_var1 sys_refcursor;

BEGIN

  OPEN l_rc_var1 FOR SELECT * FROM employees WHERE department_id=ip_dept_id;

  RETURN l_rc_var1;

END;




Un cursor ref Strong siempre devuelve un tipo conocido, normalmente de un objeto TYPE declarado. El compilador puede encontrar problemas en un bloque PL / SQL comparando los tipos devueltos a cómo se utilizan.






Un cursor de referencia debil tiene un tipo de retorno que depende de la instrucción SQL que ejecuta, es decir, sólo una vez que se abre el cursor es el tipo conocido (en tiempo de ejecución). El compilador no puede determinar los tipos hasta que se ejecuta, por lo que se debe tener cuidado para asegurarse de que el conjunto de resultados del cursor se gestiona correctamente para evitar errores de tiempo de ejecución.

ESTADISTICAS SCRIPT DBMS_APPLICATION_INFO

CREATE OR REPLACE PROCEDURE SP_ESTADISTICAS_TABLAS(esquema varchar2) AS
sentencia VARCHAR2(500);
modulo VARCHAR2(64);
accion VARCHAR2(64);
--Cursor con todas las tablas del esquema
CURSOR get_Tablas IS
  SELECT owner,table_name
  FROM dba_tables
  WHERE owner=esquema;
BEGIN
modulo := 'Administracion DBA';
accion := 'Estadisticas Tablas';
dbms_application_info.set_module(modulo, accion);
  --se recorren las tablas del esquema
  FOR i IN get_tablas loop
    --se prepara la sentencia SQL en la cual se hace el conteo de los registros y se inserta en la tabla de auditoria(aud_registros)
    sentencia:=' BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || esquema || ''', TABNAME => ''' || i.table_name || ''' ); END;';
    EXECUTE IMMEDIATE(sentencia);
    dbms_application_info.read_module(modulo, accion);
  END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE SP_ESTADISTICAS_INDICES(esquema varchar2) AS
sentencia VARCHAR2(500);
modulo VARCHAR2(64);
accion VARCHAR2(64);
--Cursor con todas las tablas del esquema
CURSOR get_indices IS
  SELECT owner,index_name
  FROM dba_indexes
  WHERE owner=esquema;
BEGIN
modulo := 'Administracion DBA';
accion := 'Estadisticas Indices';
dbms_application_info.set_module(modulo, accion);
  --se recorren los indices del esquema
  FOR i IN get_indices loop
    sentencia:=' BEGIN DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => ''' || esquema || ''', INDNAME => ''' || i.index_name || ''' ); END;';
    EXECUTE IMMEDIATE(sentencia);
    dbms_application_info.read_module(modulo, accion);
  END LOOP;
END;
/

Como Ver Set de Caracteres Oracle Database

How to check character set in Oracle

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

SELECT * FROM NLS_DATABASE_PARAMETERS

DBMS_SERVER_ALERT

Tablespace Thresholds and Alerts (DBMS_SERVER_ALERT)

This article describes how to set tablespace thresholds using the DBMS_SERVER_ALERTpackage as an early warning mechanism for space issues.

• Introduction
• Setting Thresholds
• Displaying Thresholds
• Scripts

Introduction

Oracle allows you to set tablespace thresholds using the DBMS_SERVER_ALERT package as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by the Enterprise Manager (DB Control, Grid Control or Cloud Control).

Setting the OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAMEparameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting.

There are two types of tablespace thresholds that can be set.

• TABLESPACE_PCT_FULL : Percent full. When the warning or critical threshold based on percent full is crossed a notification occurs.
• TABLESPACE_BYT_FREE : Free Space Remaining (KB). The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example Cloud Control displays and sets these values in MB.

The thresholds are set using a value and an operator.

• OPERATOR_LE : Less than or equal.
• OPERATOR_GE : Greater than or equal.

Setting Thresholds

 Make a note of your existing thresholds before changing them, so you know what to set them back to.

The following examples show how to set the different types of alerts.

BEGIN
  -- Database-wide KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
    
  -- Database-wide percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '85',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '97',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);

  -- Tablespace-specific KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
    
  -- Tablespace-specific percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '90',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '98',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
END;
/

Setting the warning and critical levels to '0' disables the notification.

Displaying Thresholds

The threshold settings can be displayed using the DBA_THRESHOLDS view.

SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;

TABLESPACE_NAME                METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ ------------------------------ ------------ ------------------------------ ------------ ---------------
TEMP                           Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
UNDOTBS1                       Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
UNDOTBS2                       Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
USERS                          Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          Tablespace Space Usage         GE           90                             GE           98
                               Tablespace Space Usage         GE           85                             GE           97
                               Tablespace Bytes Space Usage   LE           1024000                        LE           102400

7 rows selected.

SQL>

Oracle 11g Release 2 introduced the DBA_TABLESPACE_THRESHOLDS view, which displays the settings for all tablespaces, showing the default where no tablespace-specific threshold is set.

SET LINESIZE 200

COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT tablespace_name,
       contents,
       extent_management,
       threshold_type,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_tablespace_thresholds
ORDER BY tablespace_name;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN THRESHOL METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ --------- ---------- -------- ------------------------------ ------------ ------------------------------ ------------ ---------------
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
TEMP                           TEMPORARY LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK    DO NOT CHECK 0
UNDOTBS1                       UNDO      LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK    DO NOT CHECK 0
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Space Usage         GE           90                             GE           98

10 rows selected.

SQL>