book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein

October 2005

Intermediate to advanced

454 pages

14h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

1.2.1.1. Sections of the block1.2.1.2. Anonymous blocks
1.2.3.1. NULLs
1.2.4.1. String literals1.2.4.2. Numeric literals1.2.4.3. Boolean literals
1.2.6.1. Single-line comment syntax1.2.6.2. Multi-line comment syntax
1.3.1.1. Character data1.3.1.2. Numbers1.3.1.3. Dates, timestamps, and intervals1.3.1.4. Booleans1.3.1.5. Binary data1.3.1.6. ROWIDs1.3.1.7. REF CURSOR datatype1.3.1.8. Internet datatypes1.3.1.9. “Any” datatypes
1.3.2.1. Declaring a variable1.3.2.2. Declaring constants1.3.2.3. Anchored declarations
1.4.2.1. Simple CASE statement1.4.2.2. Searched CASE statement
1.6.2.1. RAISE statement1.6.2.2. Using RAISE_APPLICATION_ERROR
1.6.3.1. Built-in error functions1.6.3.2. Unhandled exceptions1.6.3.3. Propagation of an unhandled exception
1.7.2.1. Record-level operations1.7.2.2. Field-level operations
1.8.2.1. Using an associative array1.8.2.2. Using a nested table1.8.2.3. Using a VARRAY
1.9.1.1. Structure of a procedure1.9.1.2. Calling a procedure
1.9.2.1. Structure of a function1.9.2.2. Calling a function
1.9.3.1. Defining parameters1.9.3.2. Actual and formal parameters1.9.3.3. Parameter modes
1.9.4.1. Rules for building packages1.9.4.2. Rules for calling packaged elements1.9.4.3. Package data
1.10.3.1. Error handling with implicit cursors1.10.3.2. Implicit SQL cursor attributes
1.10.5.1. Limiting rows retrieved with BULK COLLECT
1.10.6.1. Declaring REF CURSOR types1.10.6.2. Declaring cursor variables1.10.6.3. Opening cursor variables1.10.6.4. Fetching from cursor variables
1.11.6.1. Syntax of the FORALL Statement1.11.6.2. FORALL Examples
1.13.1.1. Transaction participation1.13.1.2. Creating a DML trigger1.13.1.3. The WHEN clause1.13.1.4. Working with NEW and OLD pseudo -records1.13.1.5. Determining the DML action within a trigger
1.13.2.1. Creating a DDL Trigger
1.13.3.1. Creating a database event trigger
2.4.2.1. Strong vs. weak REF cursors2.4.2.2. REF cursor attributes2.4.2.3. Dynamic data access
3.3.2.1. Random partitioning (PARTITION BY ANY)3.3.2.2. Range partitioning (PARTITION BY RANGE)3.3.2.3. Order streaming (ORDER)3.3.2.4. Hash partitioning (PARTITION BY HASH)3.3.2.5. Cluster streaming (CLUSTER)
4.2.7.1. Using DES3GETKEY4.2.7.2. Using the key in encryption
4.3.3.1. Specifying the encryption type4.3.3.2. Specifying chaining4.3.3.3. Specifying padding4.3.3.4. Combining options in the typ parameter4.3.3.5. Handling and converting RAW data4.3.3.6. Specifying the encryption algorithm4.3.3.7. Putting it together
5.2.2.1. Problems with static policies5.2.2.2. Using a pragma
5.3.2.1. Shared static policy5.3.2.2. Context-sensitive policy5.3.2.3. Shared context-sensitive policy
5.4.3.1. Data dictionary view5.4.3.2. Event-based tracing
6.2.3.1. Turning off bind variable capture
6.2.4.1. Drawbacks with the default FGA approach6.2.4.2. Creating a user-defined audit facility
6.3.2.1. The ADD_POLICY procedure6.3.2.2. The DROP_POLICY procedure6.3.2.3. The DISABLE_POLICY procedure6.3.2.4. The ENABLE_POLICY procedure
6.4.2.1. The DBA_FGA_AUDIT_TRAIL view6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view
6.5.1.1. The case for FGA6.5.1.2. The case for triggers
7.1.1.1. Controlling the precision7.1.1.2. Controlling the range
7.1.2.1. The RANDOM function
8.2.1.1. Running OS executables and anonymous blocks8.2.1.2. DBA_SCHEDULER_JOBS view
8.2.2.1. Enabling and disabling jobs8.2.2.2. Stopping running jobs8.2.2.3. Running a job8.2.2.4. Dropping a job
8.3.1.1. Examples of calendar strings8.3.1.2. Determining future calendar strings
8.7.1.1. DBA_SCHEDULER_JOB_LOG8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILS8.7.1.3. Pruning the job log8.7.1.4. Log levels8.7.1.5. Setting the retention period
8.7.2.1. DBA_SCHEDULER_WINDOW_LOG8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILS

Content preview from Oracle PL/SQL for DBAs

FGA Compared with Regular Auditing

Starting with Oracle Database 10g Release 1, Oracle’s regular auditing (using the AUDIT command) has been enhanced so that it captures more information than it did in previous releases—for example, the text of the SQL statement issued, the bind variables, and more. In many respects it looks identical to FGA. Does it, therefore, obviate the need for FGA? Not at all. Let’s look at the differences between regular and fine-grained auditing.

Types of statements: Regular auditing can track many different types of statements—DML, DDL, session control statements, privilege management statements, and so on. FGA, on the other hand, can track only one statement (SELECT) in Oracle9i Database and four (SELECT, INSERT, UPDATE, DELETE) in Oracle Database 10g.
Special parameters: FGA runs out of the box without requiring any special parameters. The FGA_LOG$ table, the repository for FGA audit entries, is already present in the SYS schema. In contrast, regular auditing must first be enabled at the database level before individual objects can be audited. You do this by setting the initialization parameter AUDIT_TRAIL. Because this parameter is not dynamic, you must also restart the database for it to take effect.
Success or failure: Regular auditing can be set up so that auditing is done regardless of the success or failure of the user’s action. With FGA, tracking is done only if the action is successful.
Disable/enable: FGA can be temporarily disabled and enabled ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Oracle Database 12c PL/SQL Programming

Michael McLaughlin

Oracle PL/SQL Best Practices

Steven Feuerstein

Expert PL/SQL Practices for Oracle Developers and DBAs

John Beresniewicz, Adrian Billington, Martin Büchi, Melanie Caffrey, Ron Crisco, Lewis Cunningham, Dominic Delmolino, Sue Harper, Torben Holm, Connor McDonald, Arup Nanda, Stephan Petit, Michael Rosenblum, Robyn Sands, Riyaj Shamsudeen

Oracle Advanced PL/SQL Developer Professional Guide

Saurabh K. Gupta

Publisher Resources

ISBN: 0596005873Supplemental Content Errata Page