Chapter 30. PL/SQL for DBAs

Beginner

Q:

30-1.

You can examine database parameters with the GET_PARAMETER_VALUE function of the DBMS_UTILITY package (Oracle 8.0.4 only).

Q:

30-2.

Here’s one possible implementation:

CREATE OR REPLACE FUNCTION get_parameter (
    p_parameter v$sysstat.name%TYPE )
RETURN v$sysstat.value%TYPE IS

  v_intval    BINARY_INTEGER;
  v_who_cares BINARY_INTEGER;
  v_ret_val   v$sysstat.value%TYPE;

BEGIN
  v_who_cares := DBMS_UTILITY.GET_PARAMETER_VALUE(p_parameter,
                                                  v_intval,
                                                  v_ret_val);
  RETURN(v_ret_val);
END get_parameter;

Q:

30-3.

The built-in is DBMS_UTILITY.DB_VERSION, and an example follows:

DECLARE
  v_db_version v$version.banner%TYPE;
  v_db_compatibility v$version.banner%TYPE;

BEGIN
  DBMS_UTILITY.DB_VERSION(v_db_version,v_db_compatibility);
  DBMS_OUTPUT.PUT_LINE(v_db_version);
END;
/

Q:

30-4.

Here’s a suggested procedure:

CREATE OR REPLACE PROCEDURE display_db_version
  IS
  CURSOR version_cur IS
  SELECT banner
    FROM v$version
   WHERE banner LIKE 'Oracle%'
  ;
  v_db_version v$version.banner%TYPE;

BEGIN
  OPEN version_cur;
  FETCH version_cur INTO v_db_version;
  CLOSE version_cur;
  v_db_version := SUBSTR(v_db_version,INSTR(v_db_version,'Release ')+8);
  v_db_version := SUBSTR(v_db_version,1,INSTR(v_db_version,' ')-1);
  dbms_output.put_line(v_db_version);
END;

Q:

30-5.

DBMS_SHARED_POOL maintains the SHARED_POOL.

Q:

30-6.

Here’s the command:

BEGIN
  DBMS_SHARED_POOL.KEEP('DBMS_SHARED_POOL','P');
END;

Q:

30-7.

Packages run within the privileges of their owner, not those of the user calling them. In Oracle 8.1, this default ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.