Oracle PL/SQL Programming: A Developer's Workbook

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 ...