It serves as a central purpose in that it does not contains extraneous or superfluous data. A phone book is a good example of database.

Database management system: A Database Management System (DBMS) is the software that manages a database.

The DBMS has a data dictionary, some time referred to as system catalog, which stores data about everything it holds, such as names, structures, locations, and types.

This data is also referred to as metadata, meaning data about data. The life span of a piece of data, from its creation to its deletion, is recorded in the data dictionary, as is all logical and physical information about that piece of data. A database Administrator (DBA) should become intimate with the data dictionary of the DBMS, which serves him or her over the life of the database.

This data is also referred to as metadata, meaning data about data. The life span of a piece of data, from its creation to its deletion, is recorded in the data dictionary, as is all logical and physical information about that piece of data. A database Administrator (DBA) should become intimate with the data dictionary of the DBMS, which serves him or her over the life of the database.

Role Of Database Management System

Securing data: security is always a concern in a production database, and often in a development or test database too. It is usually not a question of whether or not to have any security, but rather how much to have. A DBMS typically offers several layers of security, in addition to the operating system (OS) and network security facilities. Most often, a DBMS holds user account with passwords requiring the user to login, Or be authenticated, in order to access the database.

DBMSs also offer other mechanisms, such as groups, roles, privileges, and profiles, which all offer a further refinement of security. These security levels not only provide for enforcement, but also for the establishment of business security policies. For example, only an authenticated user who belongs to an aviation group may access the aviation data. Or, only an authenticated user who has the role of operator may back up the database.

Integrity: the integrity of data refers to its consistency and correctness. For data to be consistent, it must be modeled and implemented the same way in all of its occurrences. For data to be correct, it must be right, accurate, and meaningful. One way a DBMS maintains integrity is by locking a data item in the process of being changed. A database usually locks at the database page level or at the row level. Incidentally, locking also permits concurrency.

Introduction to PL/SQL

What is PL/SQL?PL/SQL is the procedural language extensions to SQL Procedural programming language Uses detailed instructions Processes statements sequentially Combines SQL commands with procedural instructionsUsed to perform sequential processing using an Oracle database PL/SQL Procedural Language Constructs Variables and types Control Structures Sequence Selection: IF-THEN-ELSE Iteration: LOOP Procedures and Functions Object types and methods

PL/SQL Variables

Variable names are symbolic memory addresses (locations) that your program can use to store values. Variable names must follow the Oracle naming standard Can use reserved words (BEGIN, NUMBER) and table names for variable names, but is not a good practice

Make variable names descriptive Use lower-case letters, and separate words with

underscores Example: current_s_id

Declaring PL/SQL Variables

PL/SQL is a strongly-typed language All variables must be declared prior to use Syntax for declaring a variable:

variable_name data_type_declaration;

Example:

current_s_id NUMBER(6);

Variable Declaration Includes A data type that identifies the type of data that the variable will store An identifier that is the variable’s name An operational assigned (initial) value

An ending semicolon

PL/SQL Data Types (pp. 42-53)ScalarReferences a single valueCompositeReferences a data structureReferenceReferences a specific database itemLOBReferences a large binary objectScalar Data TypesDatabase scalar data types:

• VARCHAR2
• CHAR
• DATE
• LONG
• NUMBER

Non-database scalar data types:

Integers: BINARY_INTEGER, INTEGER, INT, SMALLINT

Decimal numbers: DEC, DECIMAL, DOUBLE, PRECISION,

NUMERIC, REAL,BOOLEAN

Composite Data Types

Reference multiple data elements, such as a record

Types:

RECORD

TABLE

VARRAY

Tabular structure that can expand or contract as needed

Reference Data Types

Reference a database item Assume data type of item

%TYPE: assumes data type of field

%ROWTYPE: assumes data type of entire row

PL/SQL Programs

Made up of blocks

Blocks can be nested within each other

Each block is a logical unit of work in a program

PL/SQL Block Structure

DECLARE

/* Declaration section: PL/SQL variables, types,

cursors, and local subprograms */

BEGIN

/* Executable section: procedural and SQL statements.

The only required section */

EXCEPTION

/* Exception handling section: error-handling

statemnets */

END;

Nested PL/SQL Program Blocks

An inner program block can be nested within an outer program block PL/SQL Program Lines

May span multiple text editor lines Each line ends with a semicolon Text is not case sensitive

Comment Statements

Add Comments to Your Program

/* Developer: <your name>

* Date: <current date>

* Program: <file name, chapter number>

* <brief description of the program

*/

Arithmetic Operators Assignment Statements Displaying PL/SQL Output in SQL*Plus

Displaying PL/SQL Program Output in SQL*Plus

First set serveroutput on

SET SERVEROUTPUT ON

Command to output data from a PL/SQL program in

SQL*Plus:

DBMS_OUTPUT.PUT_LINE(‘output string’);

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Current Output:’);

END;

/

Executing a PL/SQL Program in SQL*Plus

Copy program code from Notepad to SQL*Plus

Type / to execute

PL/SQL Data Type Conversion Functions

TO_DATE: character string to DATE

TO_DATE(‘07/14/01’, ‘MM/DD/YY’);

TO_NUMBER: character string to NUMBER

TO_NUMBER(‘2’);

TO_CHAR: NUMBER or DATE to character string

TO_CHAR(2);

TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH:MI’);

Character String Functions

Concatenating strings: joining 2 or more character strings into a single string

Concatenation operator: || s_first_name := ‘Sarah’

s_last_name := ‘Miller’

s_full_name := s_first_name || ‘ ’ || s_last_name

Write your first PL/SQL program

DECLARE

todaysDate DATE;

BEGIN

todaysDate := SYSDATE;

DBMS_OUTPUT.PUT_LINE('Today''s date is '||

to_char(todaysDate, 'MM/DD/YYYY'));

END;

/

PL/SQL Character String Functions

RTRIM: removes blank trailing spaces

cust_address := RTRIM(cust_address);

LENGTH: returns string length (number of characters)

address_length := LENGTH(cust_address);

UPPER, LOWER: changes characters to all upper or lower case

s_name := UPPER(s_name);

s_name := LOWER(s_name);

PL/SQL Character String Functions

INSTR: searches a string and looks for a matching

substring and returns its starting position

starting_position := INSTR(string_being_searched, <search_string>);

blank_position := INSTR(‘Sarah Miller’, ‘ ’);

PL/SQL Character String Functions

SUBSTR: extracts a specific number of characters from

a string, starting at a given point

extracted_string := SUBSTR(string_being_searched, starting_point, number_of_characters_to_extract);

s_first_name := SUBSTR(‘Sarah Miller’, 1,5);

Debugging PL/SQL Programs Syntax error

Does not follow language guidelines Causes a PLS- compile error

Examples:

• misspelling a reserved word,
• using afunction incorrectly
• Logic error
• Program compiles correctly, but does not give correct output

Locating and Correcting Syntax Errors

Isolate the line that is causing the error This may be before or after the line that is flagged

by the compiler Comment out lines as necessary until program runs One error may cause several cascading errors, so re-run program after fixing each error

Locating and Fixing Logic Errors

Identify the output variable(s) that have the error.

Identify the inputs and calculations that contribute

to the error.

Display the values of the inputs using DBMS_OUTPUT commands.

Take a break and look at it again later.

Ask a fellow student for help.

Ask your instructor for help.

NULL Values in Assignment Statements Until a value is assigned to a variable, the

variable’s value is NULL Performing an arithmetic value on a NULL value always

results in a NULL value

Advice: Always initialize variable values

PL/SQL Selection Structures(pp. 63-67, Urman)

IF/END IF:

IF condition THEN

program statements

END IF;

IF/ELSE/END IF:

IF condition THEN

program statements

ELSE

alternate program statements

END IF;

More on PL/SQL Selection Structures

IF/ELSIF:

IF condition1 THEN

program statements;

ELSIF condition2 THEN

alternate program statements;

ELSIF condition3 THEN

alternate program statements;

. . .

ELSE

alternate program statements;

END IF;

PL/SQL Comparison Operators

Evaluating NULL Conditions in IF/THEN Structures

If a condition evaluates as NULL, then it is FALSE

How can a condition evaluate as NULL?

It uses a BOOLEAN variable that has not been

initialized

It uses any other variable that has not been

initialized

PL/SQL Loops

Loop: repeats one or more program statements multiple

times until an exit condition is reached

Pretest loop: exit condition is tested before program

statements are executed

Posttest loop: exit condition is tested after program

statements are executed

LOOP … EXIT Loop

LOOP

program statements

IF condition THEN

EXIT;

END IF;

more program statements

END LOOP;

LOOP … EXIT WHEN Loop

WHILE Loop

Numeric FOR Loop

Cursors (chapter 6, Urman)

Pointer to a server memory location

Contains information about a SQL command in a PL/SQL programCalled the command’s context area

Types of Cursors

Implicit Cursors

Created automatically every time you use an INSERT,

UPDATE, DELETE, or SELECT command

Doesn’t need to be declared Can be used to assign the output of a SELECT command to one or more PL/SQL variables Can only be used if query returns one and only one

record

Implicit Cursor Syntax

SELECT field1, field2, …

INTO variable1, variable2, …

FROM tablename

WHERE search_condition_that_will_

return_a_single_record;

Explicit Cursors

Must be declared in program DECLARE section Can be used to assign the output of a SELECT command to one or more PL/SQL variables Can be used if query returns multiple records or no records

Using an Explicit Cursor

Declare the cursor

Open the cursor

Fetch the cursor result into PL/SQL program variables

Close the cursor

1.Declaring an Explicit Cursor

DECLARE

CURSOR cursor_name IS SELECT_statement;

2.Opening an Explicit Cursor

OPEN cursor_name;

3.Fetching Explicit Cursor Records

FETCH cursor_name INTO variable_name(s);

4.Closing an Explicit Cursor

CLOSE cursor_name;

5.Processing an Explicit Cursor

LOOP ..EXIT WHEN approach:

OPEN cursor_name;

LOOP

FETCH cursor_name INTO variable_name(s);

EXIT WHEN cursor_name%NOTFOUND:

END LOOP;

CLOSE cursor_name;

Processing an Explicit Cursor

Cursor FOR Loop approach:

FOR variable_name(s) in cursor_name LOOP

additional processing statements;

END LOOP;

Using Reference Data Types in Explicit Cursor Processing

Declaring a ROWTYPE reference variable:

DECLARE

reference_variable_name cursor_name%ROWTYPE;

Referencing a ROWTYPE reference variable:

reference_variable_name.database_field_name

PL/SQL Table of Records

PL/SQL table that can store multiple values that are referenced by a key Usually used to store database records that need to be processed by a PL/SQL program Improves performance by limiting number of database retrievals

PL/SQL Exception Handling

All error handling statements are placed in the EXCEPTION program block

Exception handler: program command that provides information about an error, and suggest correction actions Predefined Exceptions

Exception Handler Syntax

For Predefined Exceptions

WHEN exception1_name THEN

exception handling statements;

WHEN exception2_name THEN

exception handling statements;

…

WHEN OTHERS THEN

exception handling statements;

Undefined Exceptions

User-Defined Exceptions

Errors that will not cause a run-time error, but will violate business rules Programmer creates a custom error message

Exception Handling in Nest Program Blocks

If an exception is raised and handled in an inner block, program execution resumes in the outer block Exception Handling in Nested Program Blocks

DECLARE

variable declarations

BEGIN

program statements

additional program statements

EXCEPTION

error handling statements

END;

Exception Handling in Nested Program Blocks Exceptions raised in inner blocks can be handled by exception handlers in outer blocks

SQL*DBA

The purpose of this chapter is to familiarize you with the basic, mid-level, and many advanced functions of SQL*DBA. It should also help you to put that knowledge to use in your current and future environments. Although this chapter's coverage is not exhaustive, it should certainly give you all the requirements you need to forge ahead in understanding the more advanced concepts of database management with SQL*DBA.

The following screen captures and sample programs come from a DG/UX Aviion 9500, 8-processor UNIX system running a DG/UX 5.4.3.10 MU02 operating system with Oracle RDBMS Version 7.1.4. The output or code could run differently on your system.

Introduction to Oracle SQL*DBA

To assist in the setup, administration, and day-to-day operations of your Oracle databases, Oracle Corporation provided a tool called SQL*DBA. Although SQL*DBA has many functions, its most obvious use is to start up and shut down local databases.

If you have SQL*Net installed, SQL*DBA uses its features to start up and shut down remote databases as well. This versatility gives the database administrator great flexibility in database management.

Secondary functions of SQL*DBA include altering database and system statistics, modifying the characteristics of a database, administering users and security, restoring a database, and manipulating data files belonging to the databases. With the added capability to monitor various aspects of the database as it is running, SQL*DBA is an important and multifaceted tool.

SQL*DBA has three modes of operation:

•  
• Line mode

•  
• Command mode

•  
• Menu mode

Line mode is a non-graphical interface that enables the user to interactively enter commands. The output is scrolled across the user's screen. This mode is very useful for managing portions of the database that do not require the ease of screen mode.

Command mode is identical to line mode with the exception of how it is used. Although line mode is interactive by nature, command mode is intended to run in batch mode. Generally, command mode is used to run a script, or collection of commands, created by the user.

Menu mode, also referred to as screen mode, is a graphical interface that you can use on supported ASCII terminals, including X terminals. It provides the user with a menu-driven interface from which they can issue most SQL*DBA commands.

Please be aware that some versions of SQL*DBA (such as those supplied with Oracle Version 6 and Personal Oracle) might not support the menu (screen) mode, as well as shortcut keys. Although the menu interface is quite powerful, a handful of commands can only be executed from the command line. Table 7.1 contains a list of those commands. Screen mode supports all the commands that line mode supports in addition to a feature called monitor, which I discuss later in this chapter.

Command Purpose

DESCRIBE Describes tables and views

EXECUTE Executes PL/SQL blocks

PRINT Prints the value of a variable defined with the VARIABLE command

REMARK Denotes a comment or remark and prevents the interpreter from executing the line

SET Sets or modifies the characteristics of the current SQL*DBA session

SHOW Shows characteristics of the current SQL*DBA session

VARIABLE Defines a variable to be used within the current SQL*DBA session

Table 7.1. SQL*DBA commands available only through the command line.

Each of the modes is covered in greater detail in later sections. First, it is important to cover a few topics before proceeding to the commands.

The SQL*DBA Command Set

SQL*DBA accepts all standard SQL, PL/SQL, and SQL*DBA commands with few exceptions. Exceptions to this rule include SQL*Plus formatting commands such as set heading or set linesize. You can find a complete list of SQL*DBA specific

commands in the command reference at the end of this chapter.

Before You Start SQL*DBA

There are several requirements to meet before you can run SQL*DBA. First, the user must either own the executable or be a member of the group associated with it. You can find the SQL*DBA executable in the bin directory of your ORACLE_HOME environment variable; it's usually named sqldba (DOS-based systems might include an EXE or COM extension). If you are running Oracle on a DOS machine, the executable is located in the directory pointed to by your XBIN environment variable.

Once you can execute SQL*DBA, you must also have privileges to execute the specific commands you want to use. For instance, if you want to add a data file to a tablespace, you must have the ALTER TABLESPACE privilege.

Next, it is important to know how to connect to a database. Nearly all the commands supported by SQL*DBA require a connection to a database (especially startup and shutdown options).

Connecting to a Database

All SQL*DBA modes support the connect commands. With a username/password specification, you can connect to your

default database. SQL*Net supports special connect strings to connect to remote databases on your network. You can use this option to eliminate the need for logging into every system where a database is running.

connect internal is the most typical connection using SQL*DBA. Internal, a special username viable only through SQL*DBA, is

actually a alias for logging into the SYS account. Intended strictly for use with special operations such as startup and shutdown,

the internal username is limited to users with the correct access to SQL*DBA (ownership and group access to the executable).

Terminating SQL*DBA Commands

SQL*DBA, like SQL*Plus, enables you to execute multiple-line commands. By default, you must terminate all commands with a semicolon (;) or forward slash (/), regardless of which mode you are using. These characters tell SQL*DBA to execute the command. Some commands might not require the terminator. If you press return while entering a command where SQL*DBA expects further input, SQL*DBA provides a continuation line where you can enter the next part of the command line. Entering the termination character ends the command and begins execution.

SQL*DBA Operation Modes

I mentioned earlier the three modes of operation for SQL*DBA; only one major difference distinguishes the three modes.

Screen mode is the only mode capable of running monitor programs that help you monitor various aspects of your instance.

Apart from this difference, all three modes function identically. Commands that you can issue in line mode work identically when issued in either screen or command modes.

SQL*DBA in Line Mode

Line mode places the user into a line-driven, interactive interface. This mode does not support menus or additional input devices other than the keyboard. You usually use line mode for quick access to SQL*DBA commands or for automating different aspects of its functionality.

Because line and screen mode are identical in nature except for monitor commands, I limit the scope of this discussion on line mode to starting SQL*DBA in line mode, starting up and shutting down the database, and automating the startup and shutdown

processes. The following section on screen mode goes into much greater depth about the commands available in SQL*DBA.

Line mode is provided strictly for backward compatibility with older versions of Oracle. Oracle announced that it will not support SQL*DBA in future releases. At press time, SQL*DBA is still a supported product.

Starting SQL*DBA in Line Mode

To start SQL*DBA in line mode, you can enter one of the following commands: sqldba mode=line or sqldba lmode=y. After starting SQL*DBA, you should see a prompt that resembles SQLDBA>.

At this point, you can connect to the database using connect internal or connect username/password. Once you are finished with your SQL*DBA session, you can issue the exit command to leave.

SQL*DBA is not case sensitive unless you are dealing with the selection of data from the database. Uppercase or lowercase letters are perfectly legitimate.

Startup and Shutdown with SQL*DBA in Line Mode

As stated before, the primary use for SQL*DBA is the startup and shutdown of your database. You can do this from line or screen mode, but it seems the most common way is from line mode.

You cannot issue startup and shutdown commands from a connection via the Multi-Threaded Server. You must have a dedicated session to issue these commands. If running the Multi-Threaded Server, you must first disable the TWO_TASK environment variable before starting SQL*DBA.

To start up a database, use the following set of commands.

1.Start SQL*DBA by issuing the sqldba command.

2.Issue the CONNECT INTERNAL command to connect to the database.

3.Start the database using any of the STARTUP commands.

4.Exit SQL*DBA by typing EXIT.

You can substitute any one of the startup options for the STARTUP command listed in step 3, such as STARTUP MOUNT, or STARTUP FORCE. Later on, I discuss the startup and shutdown options in greater depth.

To shut down the database from line mode, use the following steps:

1.Start SQL*DBA by issuing the sqldba command.

2.Issue the CONNECT INTERNAL command to connect to the database.

3.Shut down the database using any of the SHUTDOWN commands.

4.Exit SQL*DBA by typing EXIT.

You can substitute other shutdown options for the SHUTDOWN option listed in step 3, depending on your site's needs. An example is using the SHUTDOWN IMMEDIATE command if you want to log out all processes connected to the database.

SQL*DBA in Command Mode

Command mode enables you to place a group of commands in the same file to be executed together. Various uses of command mode include starting up or shutting down an instance and collecting data for a custom report.

The format and execution of a command file for use in SQL*DBA is identical to the format of command files used in SQL*Plus.

The following is a list of guidelines you can use for formatting a command file.

Although the SQL extension is not required, using it could help you identify the file in the future.

If any commands in your command file require you to be connected to the database, the first line should be CONNECT INTERNAL.

You should terminate all single-line or multiple-line commands with ; or /. If the command is longer than a single line, only the last line of the command must have a ; or / terminating it.

You can separate command lines with a return for readability.

Scripts can call other scripts, but the depth of the nesting is operating-system dependent.

The command mode does not require an EXIT command.

To call a command script while executing SQL*DBA, you can use the following command format:

sqldba command="@filename.sql"

SQL*DBA requires the quotes and the @ to execute the script properly. The following is a sample startup script:

REM

REM startup.sql to be used to automate the startup

REM of a database through SQL*DBA command mode

REM

connect internal;

startup;

With the preceding script, you can start up your database with the following command:

sqldba command="@startup.sql"

The following is a sample shutdown script:

REM

REM shutdown.sql to be used to automate the shutdown

REM of a database through SQL*DBA command mode

connect internal;

shutdown;

You can then execute this script in same way you executed startup.sql:

sqldba command="@shutdown.sql"

Make sure that startup.sql and shutdown.sql reside in your current directory or somewhere within your path. If SQL*DBA cannot find your script, you are given an error and returned to a SQLDBA> prompt instead of your operating system prompt. It is a good idea to explicitly name the directory and file that you want to execute.

You can further customize these scripts by adding startup and shutdown options (such as shutdown immediate) or SQL statements that show who is currently logged into the Oracle instance.