1.7 Managing Schema Objects


1. Overview


Purpose

In this tutorial, you learn how to use SQL Devevloper to manage tables, indexes, and views. You also learn how to manage program code that is stored in the database.

Time to Complete

Approximately 1 hour

Introduction

A schema is a collection of database objects. A schema is owned by a database user and shares the same name as the user. Schema objects are logical structures created by users. Some objects, such as tables or indexes, hold data. Other objects, such as views or synonyms, consist of a definition only.

Software Requirements

The following is a list of hardware and software requirements:
  • Oracle Database 12c
Prerequisites

Before starting this tutorial, you should:
  • Install the Sample Schemas
  • Complete the Administering User Accounts and Security tutorial


2. Creating a Database Connection in SQL Developer


A database connection is a SQL Developer object that specifies the necessary information for connecting to a specific database, as a specific user of that database. You must have at least one database connection (existing, created, or imported) to use SQL Developer. To create a database connection , perform the following steps:

1. In a terminal window, set the environment variable. Change directories to the sqldeveloper directory under $ORACLE_HOME. Invoke SQL Developer by executing the sh sqldeveloper.sh command.

Managing Schema Objects

2. In the Connections navigator, right-click the Connections node and select New Connection.

Managing Schema Objects

3. Enter a connection name of your choice, username of system and password for the SYSTEM user. Select "Save Password" if you want to save your password for future connections as this user. Accept the default connection type and role. Enter the hostname, port, and SID. You can click Test to ensure that the connection works correctly.

Managing Schema Objects

4. Click Connect.

Managing Schema Objects

5. Your connection is displayed in the Connections tab on the left side and a SQL worksheet is opened automatically.

Managing Schema Objects


3. Managing Tables


In this topic, you view table definitions and table data. You also create a new table and modify it.
  • Viewing Tables
1. Expand the system node in Oracle SQL Developer.

Managing Schema Objects

2. Expand the Other Users node and then expand the HR node.

Managing Schema Objects

3. Expand the Tables (Filtered) node.

Managing Schema Objects

4. Select the EMPLOYEES table. Detailed information about the table is displayed in the object pane. The Columns tab displays the column names and definitions.

Managing Schema Objects
  • Viewing Table Data
1. Select the EMPLOYEES table, click the Data tab to view the data stored in the table.

Managing Schema Objects

2. The Data tab shows the rows stored in the EMPLOYEES table.

Managing Schema Objects

3. To sort the rows by last name, right-click the LAST_NAME column name and select Sort in the menu.

Managing Schema Objects

4. Select the LAST_NAME column and click the right-arrow to move it to the Selected Columns list. Click OK.

Managing Schema Objects

5.The data is now displayed in sorted order.

Managing Schema Objects

  • Creating a Table
1. In this topic you create a new table in the APPUSER schema. You created the APPUSER schema in the Administering User Accounts and Security User tutorial. Expand the APPUSER node in Oracle SQL Developer.

Managing Schema Objects

2. Right-click the Tables node and select New Table.

Managing Schema Objects

3. Enter purchase_orders in the Name field. Enter po_number in the Column Name field. Select NUMBER as the Type. Select "Not Null" and "Primary Key." Click Add Column.

Managing Schema Objects

4. Enter po_description in the Column Name field. Select VARCHAR2 as the type. Enter 200 in the Size field. Click Add Column.

Managing Schema Objects

5. Enter po_date in the Column Name field. Select DATE for the type and select the Not Null column. Click Add Column.

Managing Schema Objects

6. Enter po_vendor in the Column Name field. Select NUMBER as the type and select the Not Null column. Click OK.

Managing Schema Objects

7. The PURCHASE_ORDERS table appears in the Tables list for the APPUSER user.

Managing Schema Objects

8. Select the PURCHASE_ORDERS table. Click the Columns tab to view the column definitions.

Managing Schema Objects

  • Adding Columns to a Table
1. Right-click the PURCHASE_ORDERS table and select Edit.

Managing Schema Objects

2. The Edit Table dialog box appears. Click the green plus sign to add a column.

Managing Schema Objects

3. Enter po_date_received in the Name field. Select DATE in the Type menu. Click the green plus sign again.

Managing Schema Objects

4. Enter po_requestor_name in the Name field. Select VARCHAR2 in the Type menu. Enter 40 in the Size field. Click OK.

Managing Schema Objects

5. The Columns tab shows the new columns.

Managing Schema Objects

  • Loading Data Into a Table

1. Right-click the APPUSER user and select Edit User.

Managing Schema Objects

2. On the Create/Edit User window, click Quotas.

Managing Schema Objects

3. Select Unlimited for the APPTS tablespace and click Apply.

Managing Schema Objects

4. Click Close

Managing Schema Objects

5. Expand Tables. Right-click the PURCHASE_ORDERS table and select Import Data.

Managing Schema Objects

6. Select the load_po.csv file. Click Open.

Managing Schema Objects

7. Deselect Header. Ensure that Format is set to csv. Select UTF8 in the Encoding menu. Select none for Left Enclosure. Click Next.

Managing Schema Objects

8. Ensure that Import Method is set to Insert. Click Next.

Managing Schema Objects

9. Click Next.

Managing Schema Objects

10. Verify each column in the Source Data Columns list and its value in the Name field BEFORE clicking Next. After verifying all five columns, click Next.

Managing Schema Objects

11. Click Finish.

Managing Schema Objects

12. The data was successfully imported. Click OK.

Managing Schema Objects

14. Select the PURCHASE_ORDERS table and click the Data tab to see the new rows.

Managing Schema Objects


4. Managing Indexes


In this topic you view index definitions. You also create a new index.
  • Viewing Indexes
1. Expand the HR user in the Other Users node. Expand Indexes.

Managing Schema Objects

2. Select EMP_DEPARTMENT_IX.

Managing Schema Objects

3. Information about the index definition is displayed in the Columns tab. Click the Details tab to view additional information.

Managing Schema Objects

4. The Details tab shows additional information about the index definition.

Managing Schema Objects

  • Creating an Index
1. Select the SH user in the Other Users list and expand the Tables entry.

Managing Schema Objects

2. Right-click the Products table. Select Index in the menu and then select Create Index

Managing Schema Objects

3. Enter prod_desc_idx in the Name field. Select PROD_DESC in the "Column Name or Expression" field. Click OK.

Managing Schema Objects

4. Ensure that PRODUCTS is selected in the left hand navigator pane.

Managing Schema Objects

5. Select the Indexes tab on the right hand side. Your new PROD_DESC_IDX index is listed.

Managing Schema Objects


5. Managing Views


In this topic, you display view definitions. You also create a new view.
  • Displaying Views
1. Expand HR under Other Users in the left pane. Expand Views.

Managing Schema Objects

2. Select the EMP_DETAILS_VIEW view.

Managing Schema Objects

3. The Columns tab in the right pane displays the columns that are part of this view.

Managing Schema Objects

  • Creating a View
1. Ensure the HR user is selected. Right click Views and select New View.

Managing Schema Objects

2. Enter king_view in the Name field. Enter SELECT * FROM hr.employees WHERE manager_id = 100 in the SQL Query box. Click OK.

Managing Schema Objects

3. The new view is listed under Views in the left pane.

Managing Schema Objects

4. Select the KING_VIEW view.

Managing Schema Objects

5. The columns that are included in the view are displayed in the Columns tab. Click the Data tab.

Managing Schema Objects

6. The rows that are retrieved when the view is queried are displayed in the Data tab.

Managing Schema Objects

6. Managing Program Code Stored in the Database


As a database administrator (DBA), you may be asked to revalidate schema objects that have become invalid. Schema objects (such as triggers, procedures, or views) might be invalidated when changes are made to objects on which they depend. In this section you will create a new PL/SQL procedure. You will make a change to the table that is referenced in the procedure. This change will invalidate the procedure. You will then recompile the procedure.
  • Validating (Compiling) Invalid Schema Objects
1. Log in to SQL*Plus as the SYSTEM user.

Managing Schema Objects

2. Execute the cr_add_po.sql script to create a new PL/SQL procedure named APPUSER.ADD_PO_HISTORY. Exit from SQL*Plus.

Managing Schema Objects

3. Return to SQL Developer. Expand Procedures for the APPUSER user. Select your new ADD_PO_HISTORY procedure to view it in SQL Developer.

Managing Schema Objects

4. Expand Tables for the APPUSER user. Right-click the PURCHASE_ORDERS table and select Edit.

Managing Schema Objects

5. Select the PO_DESCRIPTION column. Change the value of Size to 250. Click OK.

Managing Schema Objects

6. Select the Reports tab. Expand Data Dictionary Reports. Expand All Objects and select Invalid Objects.

Managing Schema Objects

7. In the Select Connection dialog box, click the green plus sign to create a new connection.

Managing Schema Objects

8. Enter appuser in the Connection Name field. Enter appuser in the Username field. Enter the password for appuser. Enter orcl in the SID field. Click Connect.

Managing Schema Objects

9. Click OK.

Managing Schema Objects

10. Click Apply in the Enter Bind Values window.

Managing Schema Objects

11. The Invalid Objects tab appears in the object pane. Note that the ADD_PO_HISTORY procedure is now invalid.

Managing Schema Objects

12. Select the ADD_PO_HISTORY procedure. Right-click and select Compile.

Managing Schema Objects

13. Click Apply.

Managing Schema Objects

14. Click OK.

Managing Schema Objects

15. Close the Invalid Objects tab. Select Invalid Objects again in the left pane. Connect as the APPUSER and reselect Invalid Objects. Because you recompiled ADD_PO_HISTORY, there are no longer any invalid objects.

Managing Schema Objects