Oracle DBA Tutorial

Oracle DBA Tutorial gives you an overview and talks about the fundamentals of Oracle DBA.

This section highlights the concepts and a general overview of the players that deliver this proprietary database management system. These oracle dba tutorial players fall into three main categories:

Shared memory is a section of the host server’s memory through which all the data passes and the applications’ code is stored and executed

System support infrastructure is a mix of background and foreground processes that perform the tasks required to facilitate the application interaction with the 12c database

The operating system files a suite of no less than ten files that play individual roles as the database runs.

The next three sections address the oracle dba tutorial of these players and provide a bird’s-eye view of what they do.

If you would like to Enrich your career with a Database Administrator(DBA) and get Oracle certified professional, then Enrol Our “Oracle DBA Training”  Course.  This course will help you to achieve excellence in this domain.

Oracle Database Architecture

Shared Memory

Shared memory is nothing more than a newfangled name for what was and is sometimes still referred to as RAM—random access memory. As the 12c database is started, a handful of entries in its system parameter file contribute to the size of memory allocated to the instance. Many adopters of the Oracle technology use the words “database” and “instance” synonymously. There is a fundamental difference between the two.

• A database is an assortment of files that store data plus a handful of worker files that facilitate application access.
• An instance is a segment of shared memory and support processes that provide the capability for applications to work with the data stored in the database. Once the instance is started, the following areas of shared memory play a role in database management activities:
• The system global area, or SGA, contains data and control information from a single database instance.

The program global area, or PGA, is part of the memory allocated to a 12c instance as it is started. Unlike the memory in the SGA, PGA memory is not shared. It contains data and control information specific to server processes, not the instance as a whole.

• The user global area, or UGA, is a memory associated with each user session.
• Even though it is allocated from PGA memory, the UGA is discussed as one of the four main memory components.
• Software code areas are where SQL code is prepared for execution and sits in memory until used.
• It would be impossible to get into the details of each of these components; as you encounter the memory structures that support a running Oracle instance, the terminology will not be brand new. Below Figure is a graphical representation of the bullet points just discussed with minimal drill-down.

Related Article: Oracle Transportable Tablespace Step by Step

Overview of shared memory components for a 12c database

Oracle Database 12c offers two approaches for memory management-manual or auto:

Auto memory management a maximum amount of memory that can be used is defined, and the instance self-manages the size of the assortment of SG components. This approach is recommended by Oracle and first appeared in Oracle around the turn of the century; it is referred to as automatically shared memory management (ASMM).

Manual memory management The administrator specifies fixed sizes for the components that make up the SGA; each component size is specified in the system parameter file as the 12c instance starts.

Interaction with the database through the instance is brokered by system support processes introduced next.

Go through the Oracle DBA Certification Training in Hyderabad to get a clear understanding of Oracle DBA!

System Support Processes

These processes are initiated automatically as an Oracle instance is started. Each one plays a role in the management of application interaction with the data. A full set of 12c processes from a running instance appears in the below figure.

Related Article: Control File in Oracle

Background processes that support the Database:

The below diagram presents some detail on the role played by each one of the highlighted processes.

Operating System Files

The below Figure illustrated the makeup of these operating system files, the barebones minimum as discussed in the above table.

Work with Objects in the Oracle Database

Let's discuss the most common objects you will encounter from day one working with Oracle. Objects are used to perform a number of functions, including:

Storing data to be used by applications—tables
Presenting a subset of stored information to different users based on who they are—views
Allowing users of the database to refer to objects that exist somewhere else in the database-synonyms
Executing portions of code repetitively to initiate common tasks within the application—stored objects

Related Article: Oracle DBA Interview Questions and Answers

Tables

Tables are the most common object used in the database—notables equate to no database. A table is represented in two dimensions somewhat like a matrix, forms of which we have all seen. Picture the PERSON table as mentioned a while back. The next listing shows some of the columns in this table:

Once created, data is stored in the table and it must conform to the rules laid out as the data types are specified for each column in each row. A row in this table belongs to one and only one individual. If you look at the salary bracket for each person, you are inspecting the series of values in each row’s column of the table. In other words, rows are horizontal and Columns are vertical. A handful of records in the PERSON table could resemble those shown in Table.

Once we discuss data types in the next section, we will pull the preceding table and listing together and create the table in the Oracle database.

Views

Suppose human resources wanted to share details of employees with another organization in the company, but only part of the information was trapped in PERSON. This is where the view comes into play. This subset of the full table may look as follows:

• V_Person
• PID
• DID
• PGIVEN
• PSURNAME
• DID
• LID

Notice there are a few columns missing—SID and PHDATE. We have just hit on two fundamental characteristics of a database:

• Views built on a subset of table columns can be used to enforce a flavor of security. Users outside personnel are not able to see anyone’s salary bracket or hire date. Igor in shipping knows that boxes destined for Mel Wood are sent to procurement (department 12) in the Wichita office (location o2). Norma in human resources (HR) can put the final touches on a salary increase for Brittany Cohen, knowing the upper limit of allowable remuneration is $74,000 per annum as defined for salary bracket 10.
• Users inside and outside HR can use the same table for different applications. This conjures up a vast assortment of opportunities for consolidation as organizations move to mature database processing engines.

Synonyms

These handy objects are created and used as alternative names for objects, a pointer to the real table. Suppose as mentioned in the previous section, HR owned the PERSON table and shipping needed to reference their table using the name PERSLOC. Rather than duplicate the PERSON table for the purpose of shipping, a synonym would be created called PERSLOC pointing at the V_PERSON view. Once these pointers are set up, their uses are endless. In the non-database world, we have used synonyms all our lives—what North Americans call a delivery van, the British call a lorry. Figure 1-6 illustrates how tables/views/synonyms interact with one another.

In Figure 1-6, there is one and only one copy of the data:

The PART table contains the data in capitals, underlined.
The Inventory view looks at a subset of the columns in the table in mixed case, italics.
The applications that use the view use the synonym PART in capitals, bold to reference the Inventory view.
For decades, relational database vendors have been inventing ways to enhance the throughput of application code that works with data stored in their repositories. The next section discusses some ground-breaking inventions that first appeared more than 20 years ago.

Related Article: How to View Tablespace in Oracle

Stored Objects

This involves encapsulating code segments in an assortment of database objects, which are then run implicitly or explicitly to perform pre-determined tasks. Code that is used over and over again in applications is often contained in stored objects and then called into action as required. This ensures consistent processing of data and can assist the performance of applications at the same time. Stored objects first appeared with the release of Oracle7 in 1992. The most common stored objects fall into one of the following four categories:

• Procedures can accept input parameters and perform processing based on the nature of the code they contain. They are invoked manually. Suppose some commodity needs replenishing in an inventory application. The procedure designed to perform this iterative task accepts a stock number and quantity to order and runs off and does its thing. Procedures can service a handful of applications as long as the expected results are the same.
• Functions are invoked implicitly and return a single value. The returned value can be a wide assortment of data types.
• Packages group similar and connected processing units into a single object. They can contain an assortment of procedures and functions. They are made up of two parts:
• The package specification defines how it interfaces with applications, specifying information such as the number of input and output parameters as well as sub-programs it contains.

The package body contains the programming units defined in the specification section.

• Triggers are program units aligned with one, and only one, table. The jargon used in database processing refers to how these code segments fire when certain activities are carried out—hence their name.

Stored objects are the backbone of Oracle’s relational database technology implementation. They run on the database server, thereby

• Centralizing significant parts of your database applications for increased maintainability and security
• Enabling you to achieve a significant reduction of network overhead in client/server applications
• Contributing to the reusability of code since Oracle checks and compiles the codes when it is first executed and can be used over and over again

Procedures, packages, and functions can declare variables, constants, and cursors, as well as sophisticated exception handling. A cursor is simply a SELECT statement defined once in the stored object and then used repetitively, Packages can contain global variables and cursors that are available to all procedures and functions in the same package.

Work with Data Types in an Oracle Database

This is a big topic; for the purposes of this book, we will just scratch the surface with the basics without delving into the full suite of data types. We will cover those that you will run across from day one-variable length character, number, date, club, and blob.

Visit here to learn Oracle DBA Online Training in Bangalore

Variable Length Character-varchar2

This data type can be from 1 to 32,768 bytes. In multibyte character sets, it is possible that this 4,000 limit may not equate to the same number of characters. Oracle stores only relevant characters, and using proprietary compression, actually occupies significantly less database space than it appears to need when seen on the screen. Oracle has a few different ways to specify a variable-length character field but the most common is VARCHAR2. As a table is created, one specifies the characteristics of variable length data as follows:

Oracle stores from 1 to 3 characters for the COUNTRY_CODE field and 1 to 3o for the COUNTRY_NAME. In the preceding simple statement used to create the COUNTRY table, column names can be as many as 30 characters. Though not as common as the VARCHAR2 data type, you may encounter and work with applications that use a few of the following:

• VARCHAR Same as VARCHAR2, although its use is not recommended
• CHAR Fixed-length character data up to 2,000 bytes; there is a relationship between characters and bytes with this data type. If a field value occupies less than the 2,000-byte maximum, trailing spaces are added at the end. In addition, if a field is null, 2,000 spaces are stored in this zero-length field value up to this maximum.
• NCHAR Fixed-length character data that takes into consideration the bytes required to represent a single character in some character sets is significantly stronger than the familiar US7ASCII. The storage of data in this field is treated exactly the same as CHAR if it is less than 0,000 bytes long or is null.

NOTE All user-defined names used for just about anything in the Oracle database are limited to no more than 30 characters.

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

Number

Fields specified using this data type can store positive or negative numbers within a wide range that most applications do not even come close to reaching the upper and lower boundaries of. You can store integers with or without decimal digits included. The format for defining number fields is made up of two parts:

• The precision, which is the total length of the field; the decimal point, if present, does not occupy one of these positions. The highest precision that can be specified is 38.
• The scale is the number of decimal digits.

Column definitions for fields that will contain numeric data are summarized in the table below.

NOTE: You need not specify the ability for a number field to also contain a sign; numbers can be stored in these fields whether they are positive or negative.

Related Article: Oracle Rename Tablespace

Datetime

Oracle provides a rich set of options for storing data and timestamp information.

The following four offerings are available with Oracle Database 12c in the DATETIME data type:

• DATE The most familiar data type that contains CENTURY, YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
• TIMESTAMP The same as DATE except for fractions of seconds are stored if specified as the field is defined.
• TIMESTAMP WITH TIME ZONE. The same as TIMESTAMP except for the information of the locale where the data is created is stored in the field as well. In the multinational application, this is how a row created at 10:03:12.87 in New York City, USA can be equated to one created at 15:03:12.87 in London, England. It takes into consideration the five-hour time difference between the two cities.
• TIMESTAMP WITH LOCAL TIME ZONE The same as TIMESTAMP except it does not store the time zone information internally. Hence, there would be no way of determining that information created at 10:03:12.87 in New York City, USA was indeed created in the eastern time zone.

A column defined using this data type is subject to a handful of rules to ensure its value is valid. The default display format for date-time fields is DD-MON-YY. It may seem odd that unless specified, Oracle displays a two-character year by default. The date December 15, 2014, is displayed as 15-DEC-14 by default; Oracle does store information to ensure the two-century digits, as well as a timestamp, are preserved. Special output formatting has to be applied to date fields to get the century digits as well as the timestamp embedded in the database, as shown in the following listing:

CLOB/BLOB

This large object type can hold up to 128terabytes of data, commonly used to store complex semi-structured and unstructured data. Oracle handled data of this type for many releases in the LONG datatype; a few releases ago, administrators were told to wean themselves off the LONG data type in favor of these two large objects. In a nutshell

CLOB is best suited for storing semi-structured data such as XML documents or files created by popular word processors. With semi-structured data, the database may not decompose the data into a format usable by applications; it simply stores the data and makes it available to application components for viewing and/or processing.

BLOB is best suited for binary-based data such as photos or videos. The most common examples of unstructured data are photos and videos stored in the database as binary objects.

BLOB is used anywhere and everywhere when CLOB is not a solution.