Chapter 7

Loading Data into Your Database

In This Chapter

Making tablespaces

Understanding users and schemas

Understanding database object types

It’s no secret that databases hold data: typically, lots of it. However, data isn’t just loose in the database; data lives in structures, which are owned by users. Furthermore, this isn’t a random collection of data and objects; it supports a specific application.

In this chapter, we focus less on the actual data itself and more on the structures that hold the data and control access to that data. We explain tablespaces and their role in object storage. Objects must have an owner, and we explain how users have schemas that contain objects. Database objects that a user can own include — but are not limited to — tables, indexes, and views.

A database application includes the tables, indexes, PL/SQL code, and other objects executing the program logic inside the database. Depending on the application’s size and nature, building an application structure within a database can be complex.

Here is the general order of operations for building an application environment:

1. Create the tablespaces that will contain the tables and indexes for the application.

2. Create the database account for who will own the database objects for the application.

3. Create the objects (tables, indexes, packages, and so on) in the application owner’s schema.

4. Create any synonyms for object names to simplify access.

5. Create database roles to control access to the application schema owner’s objects.

6. Load the data into the tables and generate indexes.

7. Create the application users and grant those users access roles so they can access the application objects.

The exact build instructions for an application environment should come from the vendor, or you should create them with the application developers. The requirements should be defined before the production environment is built — although in the “real world,” the requirements are often subject to change.

In this chapter, we give you knowledge and tools to perform the steps for building an application environment.

Making Tablespaces

Database objects are logically contained within tablespaces. A tablespace is a logical storage container that houses physical data files in which database tables and indexes are stored.

In a database, tablespaces are created in two ways:

By default for internal database structures

By the database administrator (DBA) to store user objects

For example, a data tablespace has one or more database files on the OS’s file system. Within that tablespace, one or more data table is created, and the data is stored in the tablespace’s corresponding data files. In Figure 7-1, you can see a graphical example of a tablespace and its contents.

Figure 7-1: The data tablespace hierarchy.

Figure 7-1 includes the following:

Logical tablespace: This stores data tables for the user.

Physical data file: You can add data files as necessary.

Database objects: Here you can see objects from different users.

Multiple users can store their objects in the same tablespace. Tablespaces are available to any user with objects in the database although organizing different users in different tablespaces is better for performance and manageability. Also, try to separate data and index objects into separate tablespaces (and thus database files) to reduce disk contention as index and table segments for the same object are accessed.

As you add objects and tables grow, Oracle manages the size of these things:

Segments are any objects requiring storage.

Extents are the unit of storage Oracle uses to allocate space for segments.

Oracle tracks the growth of segments and extents and knows where each object is stored. This segment management is covered in greater detail in Chapter 10.

These standard tablespaces are listed with their corresponding data files:

SYS@dev12c> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES

  2  ORDER BY TABLESPACE_NAME;

 

TABLESPACE_NAME  FILE_NAME

---------------- ---------------------------------------------

EXAMPLE          /u01/app/oracle/oradata/dev12c/example01.dbf

MY_DATA          /u01/app/oracle/oradata/dev12c/my_data01.dbf

SYSAUX           /u01/app/oracle/oradata/dev12c/sysaux01.dbf

SYSTEM           /u01/app/oracle/oradata/dev12c/system01.dbf

UNDOTBS1         /u01/app/oracle/oradata/dev12c/undotbs01.dbf

USERS            /u01/app/oracle/oradata/dev12c/users01.dbf

 

6 rows selected.

The EXAMPLE tablespace is for Oracle demo objects, and MY_DATA contains a demo table. SYSAUX and SYSTEM are for internal database objects. UNDOTBS1 is for undo (rollback) objects. USERS is the default tablespace for objects created by users who didn’t specify a tablespace when they created objects.

To see each tablespace, space available, type, and extent management, go to Enterprise Manager Database Express and choose Storage⇒Tablespaces. Figure 7-2 shows this data.

Figure 7-2: Tablespace management.

From the Tablespaces management screen, you can choose Actions⇒Create to create a tablespace for storing application data tables, as shown in Figure 7-3.

Figure 7-3: The Create Tablespace General options.

Then follow these steps:

1. On the General options screen, type a tablespace name.

In this example, the tablespace name is MY_DATA.

2. Select the tablespace type:

• Permanent for normal objects, such as tables and indexes.

• Temporary for temp storage typically used for processing data. Data disappears from objects either after a commit or a session logs out.

• Undo for storing undo segments.

Do not select the Set as Default check box for this example unless you want this tablespace to be the default tablespace for all users.

3. Select the Smallfile radio button (for data files less than 32GB).

These files are easier to manage than bigfiles.

4. Select the Online radio button so the tablespace is available immediately.

5. Click the right arrow to go to the next screen.

6. On the Add Datafiles option screen, enter this name for your file:

/u01/app/oracle/oradata/dev12c/my_data01.dbf

Set the remaining options as follows:

• File Size: Leave File Size set at its default 100M.

• Reuse Existing File: You should only check this box, for example, if you are re-creating the tablespace over an old one that was dropped.

• Auto Extend: Allows the data file to grow if more space is needed. Leave this check box selected.

• Increment: Tells Auto Extend how much to grow at a time. Leave this set at default 100M.

• Maximum File Size: This allows the data file to grow to a specified size up to 32GB. Change that to 8G.

Figure 7-4 shows an added data file and filename for my_data01.dbf.

7. Click the right arrow to go to the next screen.

8. On the Space options screen (see Figure 7-5):

a. Leave Block Size set as Database Default.

b. For Extent Allocation, select the Automatic radio button. This is best for databases that have normal, regular growth.

Figure 7-4: Adding the MY_DATA data file.

Figure 7-5: MY_DATA storage options.

9. Click the right arrow to go to the next screen.

From the Logging options screen, you choose whether you want operations logged on the tablespace. You almost always want to choose logging. The only time you may not want logging is if this tablespace is going to store objects that are part of a load process, where every night they are batch-loaded, and then the data is moved to other tablespaces for long-term storage. Without logging, you compromise recoverability. The Force Logging check box sets that even if someone tries to skip logging on an operation in this tablespace, Oracle will not allow the logging to be skipped.

10. Leave Logging selected and Force Logging unselected.

11. Click the right arrow to go to the next screen.

12. On the Segments option screen, choose a Segment Space Management and Compression option:

• Automatic: For ease of management, let Oracle manage the extent and segment growth.

• Manual: Manually specify the size of each unit of allocation.

• Compression: Choose from None (no compression), Basic (SELECT friendly compression, no so DML friendly), and OLTP (DML friendly, extra, licensed feature).

13. Click the Show SQL button to see the actual SQL being executed:

CREATE SMALLFILE TABLESPACE "MY_DATA" DATAFILE

'/u01/app/oracle/oradata/dev12c/my_data01.dbf'

SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 8G

LOGGING DEFAULT NOCOMPRESS ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO;

14. When you’re satisfied with your options, click OK.

The tablespace is created.

15. Repeat Steps 1 through 14 to create the index tablespace.

If you name the second tablespace ACME_INDEX, you have these options when it’s created:

TABLESPACE_NAME FILE_NAME

--------------- ----------------------------------------------

SYSTEM          /u01/app/oracle/oradata/dev12c/system01.dbf

SYSAUX          /u01/app/oracle/oradata/dev12c/sysaux01.dbf

UNDOTBS1        /u01/app/oracle/oradata/dev12c/undotbs01.dbf

USERS           /u02/app/oracle/oradata/dev12c/users01.dbf

MY_DATA         /u01/app/oracle/oradata/dev12c/my_data01.dbf

MY_INDEX        /u01/app/oracle/oradata/dev12c/my_index01.dbf

6 rows selected.

Now you have tablespaces and are ready to start creating users and objects.

Understanding Users and Schemas

Users not only access data in a database, but they own the objects that contain the data. The set of objects owned by a user is its schema. Not all users own objects, so schemas may be empty.

Other users can access or execute objects within a user’s schema after the schema owner grants privileges. It’s common practice to have one user own all of an application’s objects (tables, indexes, views, and so on) and then provide access to those objects to all the application users within the database. This is done via database grants, roles, and synonyms.

For example, assume you have the ACME application. You’d create a user called ACME_OWN and create all objects as ACME_OWN. Then you’d create a database role called ACME_USER and grant SELECT, UPDATE, EXECUTE for the objects in ACME_OWN’s schema to that role. Application users would be granted the ACME_USER role so they could access the ACME_OWN’s objects. This way, one user owns the objects, but the actual database or application users access the data. This separation improves both security and manageability.

Users fall into one of two categories:

Application owners whose schemas contain multiple objects

Application users with few or no objects

The syntax for each user creation is the same, but grants and privileges for each are what separate the two categories.

Here’s the simple syntax for creating a user:

CREATE USER <USERNAME>

IDENTIFIED BY "<PASSWORD>"

TEMPORARY TABLESPACE <TEMPORARY TABLESPACE>

DEFAULT TABLESPACE <DEFAULT TABLSPACE>;

For username, use something descriptive (such as DATABASE TITLE_OWN) for the owner of objects for the application. If a connection pooled web user (as explained in Chapter 3) is going to access the application, a name appended with _WEB is appropriate. Normal application users should be descriptive, such as first name, last initial; an example is VICKYB.

The password for the user should have the following characteristics:

Be more than eight characters

Include numbers and special characters

Not be based on dictionary words

Use uppercase and lowercase characters

Placing the password in double quotation marks (" ") allows special characters without disrupting the SQL syntax.

Two tablespaces need to be identified when creating a user: temporary and default:

The TEMPORARY tablespace is where temporary segments are created. TEMP is the standard.

The DEFAULT tablespace is where tablespace objects (such as tables or indexes) are created if you omit the TABLESPACE storage clause during the object create statement. Ideally, every table or index creation statement lists a tablespace. If a tablespace is missing, these objects go to the tablespace defined as DEFAULT. Generally, the USERS tablespace is defined as DEFAULT.

A user needs system privileges to be able to connect to the database and create objects. Granting the CREATE SESSION privilege or CONNECT role allows a user to log in to the database. Giving a user the RESOURCE role enables the user to create database objects. Roles and privileges are explained in greater detail in Chapter 9.

In the following steps, you create a user with SQL*Plus and grant the necessary roles and privileges to connect to the database:

1. In SQL*Plus, type the following to create a user:

SYS@dev12c> create user acme_own

  2  identified by "acme_own2013!"

  3  temporary tablespace temp

  4  default tablespace users;

 

User created.

In this example, the user is schema owner ACME_OWN. The default tablespace is defined as USERS although the TABLESPACE storage clause is expected to specify ACME_DATA when objects are created. We also create a role to hold the necessary privileges that this user will need.

2. Grant the user CONNECT and RESOURCE roles so that the user can log in to the database and create objects:

SYS@dev12c> grant connect to acme_own;

 

Grant succeeded.

 

SYS@dev12c> grant resource to acme_own;

 

Grant succeeded.

3. Create a new role:

SYS@dev12c> create role acme_user;

 

Role created.

 

SYS@dev12c> grant create session to acme_user;

 

Grant succeeded.

In this example, ACME_USER is created. That user will receive object grants from the ACME_OWN account as objects are created.

4. Grant the appropriate INSERT, UPDATE, DELETE, and EXECUTE privileges for each object to the second role.

This lets you grant the role that has the grants to each application user. Each application user then has access to the ACME_OWN objects. This saves you from having to individually grant each user access to each object.

5. Grant CREATE SESSION to the first role.

When users receive the role, they can log in to the database.

You can create individual application users by using SQL*Plus. Use Enterprise Manager Database Express to create users:

1. Choose Security⇒Users to get to the Create User screen shown in Figure 7-6.

2. Enter the username, profile, and password.

VICKYB has DEFAULT profile and password authentication. The password you type appears as asterisks; you have to enter it twice to ensure you don’t mistype it.

Figure 7-6: The Create User screen.

3. Click the right arrow to go to the Tablespaces screen. (See Figure 7-7.)

Choose the new MY_DATA tablespace as VICKYB’s default tablespace.

Accept the Temporary Tablespace — TEMP tablespace default.

Figure 7-7: The user creation tablespace selection.

4. Click the right arrow to go to the next screen.

The Privilege screen appears. On the left are system privileges or roles. Roles are denoted by a check mark. Figure 7-8 shows the new user with the CONNECT role.

Figure 7-8: The user creation privilege screen.

5. When you finish choosing roles and privileges, click OK.

The user is created.

If you need more application users, follow these steps:

1. From the main Security screen, select the user you want to use as a template.

2. Click the CREATE LIKE button.

This will take you back into the Create User wizard with the options already selected to reflect the user you are copying.

3. Create a new user with the same roles and privileges but with a different username and password.

At this point, you have an application schema owner account and a database role; grant object privileges to this role as you create objects. You also have an application user with a role. After the application objects are built and access has been granted to that role, the application user can access the objects.

Creating Database Objects

Multiple object types exist in a database, and it’s important to know what’s available. Periodically, Oracle adds new object types to extend functionality. New options within each object type are regularly added as well.

The multitude of objects in Oracle grows with every release, and the options available for each object grow even faster. To get the most up-to-date listing of syntax and options, visit the Oracle Database SQL Language Reference 12c documentation at

http://docs.oracle.com/cd/E16655_01/server.121/e17209/toc.htm

After you decide what type of objects to create, you need to know how to create them. The most common object creation methods are

Vis SQL*Plus with scripts or command line statements

Via a GUI tool, such as Enterprise Manager Cloud Control

Via Oracle-supplied SQL*Developer

Object types

The guts of a database are its objects; and tables are at the core because they contain the rows of data. However, other objects within the database are important. The following objects are common in an Oracle database.

Table

A table contains rows of data and is the core of the database. Tables are composed of column names, each with a defined data type. Data is loaded into the table as rows.

Create specific constraints on each column of data to restrict data. Create primary keys on one or more columns to enforce uniqueness for each row. Foreign keys generate relationships between rows in one table and rows in other tables.

Tables are contained within a tablespace and may be split between multiple tablespaces (partitioning) to improve performance and manageability.

View

A view is a SQL statement that joins one or more tables to form a logical representation of data. Rather than the user or program unit issuing a complex statement on multiple tables, the view allows that data to already be joined. That way, the user can select from the view to achieve the same result. Views provide the benefits of reduced complexity and improved performance when created as materialized views, in which data is already selected and stored.

Index

An index is an internal mechanism that allows fast access to selected rows within a table. Just as you look in a book’s index to find a topic, a database index is a pointer to selected data within data tables.

You can use multiple types of indexes depending on the nature of the table and data:

B*Tree indexes are the default and most common

Bitmap indexes are used for data with low cardinality or low levels of uniqueness, such as a YES/NO column.

Function-based indexes exist on functions on SQL statements. For example, if you want to search for LAST_NAME in uppercase, you might create an uppercase function-based index.

Indexes are key to fast data access, but they come at a cost. The index must be updated every time data is inserted, updated, or deleted — and those are all performance hits. Indexes also consume disk space and are commonly stored in tablespaces separate from the corresponding data tablespaces. Bottom line: Index enough to speed up common searches, but, not so much as to slow down data modifications.

Like rows of data, you may partition indexes into multiple tablespaces to improve performance and manageability.

Procedure

A procedure is a PL/SQL program unit that executes program code to process rows of data. Application and business logic exist as procedures within a database.

A procedure can

Stand alone within a schema

Be part of a package

Be an anonymous PL/SQL block

Function

A function function is a PL/SQL program unit that executes and returns a value to the program unit that called it. Conceptually, an Oracle function isn’t unlike functions in other programming languages. Functions typically accept input parameters from the calling program, perform some type of processing on that input, and return a value to the calling program unit.

Functions come in two ways:

Oracle provides many useful built-in functions: for example, time, date, and mathematical functions.

The user can write customized functions.

A functions can exist in the following ways:

Stand alone within a schema

As part of a package

As an anonymous Pl/SQL block

Package

A package is a group of related PL/SQL procedures and functions that form a larger program unit. A package typically has procedures and functions related to a specific business purpose; that way, the functionality is contained to that package. A package contains two things:

A package spec, or header, which lists the publicly exposed program units

The package body, which holds the actual PL/SQL program code for each contained procedure or function

Trigger

A trigger is a PL/SQL program unit that is executed when a table is updated, inserted, or deleted, or when other database events occur. Here’s a common trigger example:

Assume an insert on the sales table. Delete the appropriate amount on the inventory table; if it drops to a certain level, then order new inventory.

Database link

A connection from one database to another is a database link. It allows a user or program unit to select or modify data from another Oracle database. The link specifies a Transport Network Substrate (TNS; described in Chapter 5) alias to connect to a remote database. For example, if you execute

SELECT * FROM CUSTOMER@ROLLING_MEADOWS_DB;

You select all the data from the CUSTOMER table in the ROLLING_MEADOWS_DB database.

Synonym

A synonym in a database is just what it is in everyday life: a different name for the same thing. Synonyms can be

Private: The name is available only to the owner of that synonym.

Public: The name is more common and provides a short name for all users within a database so they don't have to list the schema owner for each object in their queries.

By default, objects are accessed by SCHEMA_OWNER.OBJECT_NAME. For example, ACME_OWN.CUSTOMER is the customer table for ACME_OWN and is how any other application user must access that table: for instance, SELECT * FROM ACME_OWN.CUSTOMER. A public synonym allows you to drop the ACME_OWN from the query.

Object creation methods

As a DBA, you’re expected to create objects, but you seldom create them from scratch. Typically, the application developer or software vendor provides SQL scripts with the DDL and DML for the objects to be created. You simply log in via SQL*Plus and run the scripts provided.

SQL scripts are the recommended method for these reasons:

A script isn't subject to typos.

A script can be versioned, controlled, and re-executed as necessary.

We cover many of the fine points of SQL and SQL*Plus in Chapter 6.

Odds are that if you’re creating multiple objects by hand, typing directly into SQL*Plus, something is wrong with your overall development process. Rarely is it okay to create ad hoc objects.

The easiest way to create objects is with a tool, such as SQL*Developer. SQL*Developer enables you to hand-code the SQL to create objects or use various wizards if you’re not yet comfortable with the SQL language. The nice thing is that if you use a wizard, you can always see the SQL it created to help you learn.

The next section runs through some examples using SQL*Developer on Linux. First, launch SQL*Developer and get connected to your database with the HR demo schema. This example uses the Oracle software installed on our database server, and we are connecting to our local database, dev12c.

1. Open a terminal window and navigate to sqldeveloper under your ORACLE_HOME directory.

2. Log in as your Oracle software owner, oracle.

3. From this point, type

cd $ORACLE_HOME/sqldeveloper

4. Launch the SQL*Developer tool by typing the following.

./sqldeveloper.sh

5. Connect to your database by clicking the (+) sign under the Connections tab on the right side of the screen.

6. In the wizard that appears, give your connection a name and then fill in the username and password.

7. Select the Save Password check box.

8. Change the SID to dev12c, as shown in Figure 7-9.

Figure 7-9: Creating a SQL*Developer Connection.

9. Click the Test button. If everything works, click Save.

10. Click the Connect button to open a connection to the database.

To create a database table in a schema, follow these steps:

1. Open the drop-down menu from the plus sign (+) next to your connected username.

2. Right-click Tables and choose New Table from the context menu.

The Create Table screen appears.

3. Fill in your column information.

Figure 7-10 shows a DEPT table with two columns.

4. (Optional) Click the DDL tab to see the code generated in the background.

5. Click OK to create the table.

6. From the Connections panel, right-click Indexes and choose New Index from the context menu.

The Create Index screen appears, as shown in Figure 7-11.

Figure 7-10: Creating the DEPT table.

Figure 7-11: Creating an index on the DEPT table.

7. Fill in the appropriate values:

• Name: Enter <DEPT_NAME_IDX>.

• Table: Choose DEPT from the drop-down list.

• Type: Select the Normal and the Unique options.

The DEPT_NAME column is automatically selected because it’s the only un-indexed column in the table. If that’s not what you want, you can select DEPT_NAME and then click the red X button to remove it from the column list. You can then add the appropriate columns.

8. (Optional) Click the DDL tab to see the code being generated.

9. Click OK to create the new index.