Creating an Oracle 12c Database Using the Database Configuration Assistant

1. Run . Press the Windows button on your keyboard, you will be taken to the start screen ( start screen), at the bottom of the screen there is a button to call the applications screen ( Apps screen). Click it.

2. On screen Apps select icon Database Configuration Assistant.

3. Window Database Configuration Assistant - Database Operation. Select Create a Database. Click Next.

4. Window Database Configuration Assistant—Creation Mode. Select advanced mode. Click Next.

5. Window Database Configuration Assistant - Database Template. Select Custom Database. Click Next.

6. Window Database Configuration Assistant - Database Identification. Specify a base name. In field Global Database Name Enter your name DB. The name can be arbitrary, no more than six characters, start with a letter and not contain special characters, underscores and spaces, for example test or rp34. In field SID enter a unique database ID. Make it the same as the DB name. Click Next.

7. Window Database Configuration Assistant—Management Options. Check the box Configure Enterprise Manager (EM) Database Express. In field EM Database ExpressPort enter the port number (the default port number is 5500 - leave it). Click Next.

8. Window Database Configuration Assistant - Database Credential. Set passwords for system users (in this example, the same password is set for all system users, it is not necessary to do this on an industrial database). Check Use the Same Administrative Password for All Accounts, enter password. Enter password for Oracle Home User Password– the owner of Oracle Home, on behalf of which Oracle services are launched (this user you create or specify when installing Oracle-). Click Next.

9. Window Database Configuration Assistant—Network Configuration. Select a listener process or create a new one. Click Next.

10. Window Database Configuration Assistant - Storage Locations. Specify the storage mechanism for database files (in this example, the file system is specified). Select fileSystem. Specify the location of the base files (in this example, the default paths will be used). Check usedatabasefileLocationsfromTemplate.

Specify the database recovery options (in this example, the recovery options are not used). Check the box SpecifyFastRecoveryarea. This is the default folder where RMAN is backed up and archived copies of redo log files are stored. By default, the folder is located at (ORACLE_BASE)\fast_recovery_area. In field FastRecoveryarea you can change this path and explicitly set the folder for Reserve copy. In parameter FastRecoveryarea size a limit is set on the size of this folder (it is better to make it equal to the volume of the entire hard drive). Parameter enableArchiving turns on the mode for archiving log files. If you press the button EditArchivemodeParameters then you can change the template for the names of redo archives, as well as additional paths for multiplexing archived copies. Leave everything as default.

ATTENTION: For the industrial base, it is necessary to enable the mode of archiving log files. However, when the limit of the Fast Recovery Area folder is reached (i.e. when it is full), the database will stop and wait for free space. Therefore, if you cannot monitor the filling of this folder - DO NOT turn on the mode of archiving log files, i.e. do not check the box enableArchiving.

Click Next.

11. Window Database Configuration Assistant - Database Options. Select the required base components. Click Next.

12. Window. Bookmark Memory. Set the allocation method and size of the memory. Select Typical. In field Percentage set quantity physical memory, which will be allocated to Oracle. Usually this 70-80% . If other resource-intensive processes are running on the server besides Oracle, choose a number less than 70% .

13. Click the button ... Window All Initialization Parameters. Correct the values ​​(field value) the following parameters:

Required parameters (required to change!).

; For a large number of active users (more than 70-80).

; Count REAL USERS,

; not the number of computers on the network.

; If there are less than 70-80 users, do not touch these parameters.

; If there are more than 100-200 users, perhaps more efficiently

; use Shared Server Mode.

processes = number of users*2

sessions = 1.1*processes +5

Click close. Window Database Configuration Assistant - Initialization Parameters.

14. Window Database Configuration Assistant - Initialization Parameters. Bookmark Sizing. Select the size of the database block. The database block size should be at least 8Kb. If the server has a good disk subsystem ( SCSI discs or RAID) can choose 16Kb and higher (block size over 8Kb only makes sense for large bases). In field block size enter the desired value ( 8192 or 16384 ).

15. Window Database Configuration Assistant - Initialization Parameters. Bookmark characterSets. Select the encoding for the database. The encoding must be CL8MSWIN1251. Selected by default use the default. If the correct encoding is specified, then nothing needs to be changed. If the specified encoding is not correct. In this case, choose Choose from the list of character sets and select the correct encoding from the list CL8MSWIN1251. In field Defaultlanguage select the value from the drop-down list Russian» and in the next field Default Territory- meaning " Russia».

14. Window Database Configuration Assistant - Initialization Parameters. Bookmark connection mode. Select Dedicate Server Mode. Click Next.

15. Window Database Configuration Assistant - Creation Option. Select Create Database. Click Next.

16. Window Database Configuration Assistant—Summary. Check if the settings are correct. Click OK.

17. Window Database Configuration Assistant - Progress Page. The database creation process is in progress. Wait for completion.

18. Window Database Configuration Assistant. Database creation completed. Make a note of the Web address for the Database Control. Click close.

19. The database has been created and is already running. To connect to a database from other ORACLE_HOMEs, you need to set up an Oracle network environment for them.

20. After creating the database, you can change some settings for the database. To do this, you need to execute a few sql commands in sqlplus (or TOAD) under SYS and restart the database.

Run sqlplus /nolog.

C:\> sqlplus /nolog

connect to the database as user sys as sysdba

SQL> conn sys/sys as sysdba

disable the use of the recycle bin in the db

SQL> alter system set recyclebin=off scope=spfile;

disable password case sensitivity

SQL> alter system set sec_case_sensitive_logon=FALSE scope=both;

fix issue with ORA-29471: DBMS_SQL access denied

SQL> alter system set "_dbms_sql_security_level"=384 scope=spfile;

(optional) not to change password for users every 60 days

instead of unlimited, you can put number = number of days

SQL> alter profile DEFAULT limit password_life_time unlimited;

DB restart

SQL> shutdown immediate

SQL> startup

21. Old versions of clients 8, 9, 10, 11 cannot connect to Oracle 12c with errors:

ORA-28040: No matching authentication protocol - for forms 6i

ORA-01031: insufficient privileges - for 11g client

To solve the problem, you need to add to sqlnet.ora on the server

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

22. Some oracle 10 and 11 clients crash with ORA-01031: insufficient privileges when trying to execute

SELECT u.NAME
FROM sys. USER$u
WHERE u. TYPE# = 1
ORDER BY 1

Starting 12c, the SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS. This change increases the default security of the database by not allowing access to a subset of data dictionary tables through the SELECT ANY DICTIONARY privilege.

Detour maneuver:

GRANT SELECT ON sys. USER$ TO PUBLIC;

Creating an Oracle database manually involves several steps. Some of them depend on operating system. For example, in a Windows environment, before creating a database, you must first run the oracle program used to create the database service. Steps to create a database manually:

1. Write a database creation script. An example of such a scenario is provided in step 6.
2. Create a directory structure that will host the new database. Follow the instructions for creating the optimal flexible architecture.
3. Modify the existing sample init.ora file maintained by Oracle to reflect the settings for the new database.
4. Describe the SID name for Oracle. On the Windows platform, at the operating system prompt, enter: set ORACLE_SID = mydb

   In UNIX, enter:

   Export ORACLE_SID = mydb

1. Establish a database connection via SQL* Plus as SYSTEM / MANAGER as sysdba or how / as sysdba and enter the following command to start the database in nomount mode: startup nomount pfile= D:/oracleadmin/mydbscripts/initMYDB.ora; Substitute your initialization parameters in place of the pfile parameter values ​​given here.
2. After starting the database, use the script you wrote to create the Oracle database. Here is a sample: create database MYNEW maxinstances 1 maxloghistory 1 maxlogfiles 5 maxlogmembers 5 maxdatafiles 100 datafile d:/oracle/oradata/mydb/system01.dbf size 325M reuse autoextend on next 10240K maxsize unlimited character set WE8MSWIN1252 national character set AL16UTF16 Logfile group 1 (d :/oracle/oradata/mydb/edo01.log) size 100M, group 2 (d:/oracle/oradata/mydb/edo02.log) size 100M, group 3 (d:/oracle/oradata/mydb/edo03.log) size 100M default temporary tablespace TEMP tempfile d:/oracle/oradata/mydbemp01.dbf extent management local uniform size 1M undo tablespace UND0_TS datafile d:/oracle/oradata/mydb/emp0.dbf size 150M reuse autextend on next 10240K maxsize unlimited;

• After creating the database, run the catalog.sql , catproc.sql , catexp.sql scripts, and any new scripts needed to support the products you have installed. On a UNIX system, the scripts are located in the $ORACLE_HOME\rdbms\admin directory, and on a Windows environment, in $ORACLE_HOME/rdbms/admin . Review the scripts before you run them, as many catalog scripts call other scripts.
• For added security, enter at least some other passwords for SYS and SYSTEM , rather than leaving the default MANAGER and CHANGE_ON_INSTALL passwords. The sample script in step 6 creates an UNDO tablespace. The initialization options for it are: undo_management=AUTO undo_tablespce=UNDOTBS The only option you can't change after the database is created is the database block size, which you described in the init.ora file before it was created. The DB_BLOCK_SIZE parameter is used to set this value. For example, the following line sets the default database block size to 8 KB. DB_BL0CK_SIZE=8k To see the parameters in effect in your database, request a V$PARAMETER dynamic lookup: select Name, Value, IsDefault from V$PARAMETER;

Lab #1

Oracle Database 11g Express Edition database. Designing SQL queries

Objective

Studying the user interface (DB) of Oracle Database 11g Express Edition and constructing SQL queries.

Tasks

Creating a database connection to Oracle Database 11g Express Edition. Exploring HR schema objects and compiling a data model. Designing SQL queries to select data in the Query Editor and Query Builder modes.

Theoretical part

3.1. General information about DB

Oracle Database 11g Express Edition (Oracle Database XE) is a free (free) version of the most powerful relational database in the world. It is ideal for installation in educational institutions for the purpose of teaching students modern methods working with Oracle relational databases and developing applications for displaying user data and manipulating data.

Oracle Database XE is easy to install and easy to manage. You can download installation files for various platforms on the official Oracle website http://www. /technetwork/database/database-technologies/express-edition/downloads/index. html.

Oracle Database XE can be installed on a computer (host machine) with any number of processors (one database per computer), but Oracle Database XE will only store up to 11 GB of user data, use up to 1 GB of memory, and use only one processor per host -car.

Oracle Database 11g Express Edition is a relational database that stores and retrieves collections of related information. In a relational database, information storage objects are organized into structures called tables. Each table contains rows (records) that are made up of columns (fields). Tables are stored in the database in structures called schemas. Schemas are logical data structures in which database users store their tables and other objects.

Oracle Database XE is accessed using the database home page, which has an intuitive graphical interface. Access to the home page is organized on the basis of a web service. The home page provides the user with web tools for database administration, creating tables, views and other schema objects, importing, exporting, viewing, editing table data. These tools allow you to run SQL queries and SQL scripts, create and debug PL/SQL programs, develop database applications, and generate reports.

Oracle Database XE contains an embedded HR (Human Resource) schema, which is an example of a linked table schema. The HR schema has tables to store fictitious information about employees and departments. Tables contain common columns that allow data from one table to be related to data from other tables. The HR schema is owned by a user named HR.

3.2. Accessing Oracle Database XE

Users access Oracle Database 11g Express Edition via account database user. When installing the database, the SYS and SYSTEM user accounts are automatically created - these are users with database administration privileges. An HR user account that does not have administrative privileges is also automatically created.

However, for security reasons, the HR user account has been locked out. You must unlock this account before you can start working with HR schema objects. You can use the SQL*Plus command line to do this.

To unlock a user account using command line SQL*Plus, you need:

Start button -> All Programs -> Oracle Database 11g Express Edition -> Run SQL Command Line. Connect as the SYSTEM user:

Type connect Enter a name to connect to: SYSTEM Enter a password:<пароль-для-SYSTEM >

After a successful connection (message connected), enter the following SQL statement:

SQL> ALTER USER HR ACCOUNT UNLOCK;

Enter the password for user HR using the following SQL statement:

SQL> ALTER USER HR IDENTIFIED BY HR;

To exit the editor, enter the SQL statement:

The SQL*Plus Command Line Editor window is shown in Figure 1. 1.1.

Rice. 1.1. SQL*Plus Command Line Editor Window

Connecting to an Oracle Database XE database is through the Home Page, a browser-based web interface for performing various database administration operations, including the following:

Storage base monitoring (Storage); Monitoring of database sessions (Sessions); View database initialization parameters (Parameters); Getting started with Oracle Application Express (Application Express).

Home page access: Start button -> All Programs -> Oracle Database 11g Express Edition -> Get Started.

The Oracle Database XE home page appears in the web browser window (Figure 1.2).

Rice. 1.2. Oracle Database XE Homepage

Click the Application Express button. When prompted for login information, enter: username - SYSTEM, password -<пароль-для-SYSTEM >, as in the SQL*Plus command line editor. Click the Login button (Fig. 1.3).

Rice. 1.3. Connecting a User with the Database Administrator Role to Oracle Database XE

The next step is to create an Oracle Application Express workspace for the HR user. This workspace will contain all database applications that the HR user is allowed to work with. The workspace creation form is shown in fig. 1.4.

Rice. 1.4. Creating an Oracle Application Express Workspace

Application Express Username is the name of the workspace. You can use either a username (HR) or any other (for example, hr_apex). Confirm the password. Click Create Workspace. On the next page, click Click here to enter the workspace. The first time you try to access the workspace, you will be prompted to reset the password for the workspace (you can use the same password or a different one).

Creating an Oracle Application Express workspace only needs to be done once. For the next database connections, use the Already have in account? Login Here. To enter the workspace, a form for Oracle Application Express will be shown, fig. 1.5.

In order to skip the steps of authorization of the SYSTEM user and transition to the form fig. 1.5, copy and save the URL of this form (for example, http://127.0.0.1:8080/apex/f?p=4550:1:494885012264286) and use it in a web browser to directly start working with the authorization form.

After logging into the Oracle Application Express workspace, the home page opens, on which the main controls are located - Application Builder, SQL Workshop, Team Development, Administration icons (Fig. 1.6).

Figure 1.6. Oracle Application Express Workspace Home Page Control Icons

Clicking on the image of the icon causes a transition to a new page with icons of allowed operations. Clicking on the SQL Workshop icon allows you to access tools for exploring database objects (Object Browser, Utilites) and working with SQL queries Commands, SQL Scripts, Query Builder (Fig. 1.7).

Rice. 1.7. Tools for exploring database objects and working with SQL queries

3.3. Accessing data with SQL

SQL is a non-procedural language for database access. SQL statements are designed to perform various tasks, such as retrieving data from tables in Oracle Database XE. All database operations are performed using SQL statements. You can perform the following operations using SQL statements:

querying, inserting and updating data in tables; formatting data, performing calculations based on data, storing and printing query results; studying the structure of tables and defining database objects.

Creating Data Fetch Queries

SELECT statement syntax for selecting all table records

You can specify an alias after the column name in the select list, using space as a delimiter. If the alias contains spaces or special characters such as the number sign # or the dollar sign $, or if it is case sensitive, enclose the alias in quotation marks "". Example:

SELECT employee_id "Employee ID number",

last_name "Employee last name",

first_name "Employee first name"

Row sampling limit

You can limit the number of rows that are retrieved from the database using the WHERE clause in the SQL statement. By adding a WHERE clause, you can specify a condition that must be met, and only those rows that match the condition will be returned.

When using the WHERE clause:

the WHERE clause immediately follows the FROM clause in the SQL statement syntax; the WHERE clause consists of the WHERE keyword and a condition (or multiple conditions); the condition of the WHERE clause specifies a comparison of values ​​that limits the number of rows returned by the query.

Merging tables

Sometimes it becomes necessary to display data from several tables. To do this, the FROM clause of the SELECT statement specifies a list of table names from which to retrieve data. If the information comes from more than one table, the tables are merged.

For example, in the EMPLOYEES table, the DEPARTMENT_ID column represents the employee's department number. The DEPARTMENTS table has a DEPARTMENT_ID column as well as a DEPARTMENT_NAME column. You can combine data from the EMPLOYEES and DEPARTMENTS tables using the DEPARTMENT_ID column and generate a report that will show employee names and department names.

Internal table joins

An inner join joins columns of related tables based on a common field or combination of common fields. With this join, the result set does not include rows from any of the tables that do not have a match in another table. In inner joins, the join condition must be strictly observed.

natural union

A natural join allows you to display data from two tables when a value in one column in one table directly matches a value in another column in the second table. If two tables include one or more columns that have the same names and data types, a natural join returns all rows from the two tables that have the same values ​​in all matching columns. Often this type of join includes a primary key and foreign key columns.

Syntax

Example:

SELECT employee_id, last_name, first_name, department_id,

department_name, manager_id

FROM employees NATURAL JOIN departments

Joining two tables with the USING statement

The USING statement allows you to specify the columns that will be used to join between two tables. Column names must be the same for both tables and must be of compatible data types. Use the USING statement if your tables contain more than one column whose names match and you need to clearly define the name of the column on which you want to join the tables.

Syntax

Join tables with column identification. ON statement

The ON statement is used to specify a join condition for two tables or a self-join condition for a table. The ON statement allows you to write a join condition for different column names, but the data types of those columns must match.

Syntax

FROM employees JOIN job_history

Applying Additional Join Conditions

It is often necessary to combine data from two tables so that some additional conditions are met. For example, let's say you want to show the result of joining the EMPLOYEES and DEPARTMENTS tables only for the employee whose ID is 149. To add more conditions to the ON operator, you can add an AND operator. You can also use the WHERE clause to apply additional conditions to preselect table rows.

Using the AND operator

and e. manager_id = 149

Using the WHERE clause

SELECT e. employee_id e. last_name e. department_id,

d. department_id, d. location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

WHERE e. manager_id = 149

Table aliases

The examples show how aliases are used to identify table names. In the FROM clause, the abbreviation appears after the table name. This abbreviated name is called an alias. Once an alias has been created in the FROM clause, it can be used in any part of the SELECT clause.

Joining Multiple Tables

Joining multiple tables is used when the required information is spread across more than two tables. An example would be a three-way join - joining three tables. Suppose it is necessary to find employees, their subordinates and the names of departments for employees. This requires access to three tables: - EMPLOYEES, DEPENDENTS and DEPARTMENTS. It is possible to join as many tables as needed to obtain coherent information.

Syntax

The FROM clause must specify the tables to be joined.

Example

SELECT e. last_name, d. first_name, w. department_name
FROM employees e

JOIN dependents d

ON d. relative_id = e. employee_id

JOIN departments w

ON w. department_id = e. department_id

Self join tables

The ON statement can also be used to join columns that have different names (in the same table or in another table). If columns of different names to be joined are located in the same table, then the tables are self-joining. For example, you can self-join the EMPLOYEES table based on the EMPLOYEE_ID and MANAGER_ID columns.

Outer joins of tables

An outer join also combines the rows of related tables based on a common field or combination of common fields. However, the result set includes rows from one of the tables even if they do not have a match in another table. With outer joins, the join condition is not strictly observed. One of the tables is mandatory - the result set will contain all its rows.

Syntax

LEFT is a left outer join. Specifies that table1 is a required table (the result set will contain all of its rows) and table2 is optional. For those rows in table1 that do not match in table2, NULL is returned.

RIGHT is a right outer join. Mandatory table table2, table1 optional.

FULL is a full outer join. It is a bidirectional outer join. The resulting set includes:

all rows of inner table joins; rows in table1 that do not match in table2; rows in table2 that do not match in table1.

OUTER is an optional keyword. Indicates that an outer join is in progress.

SELECT employees.*, job_history.*

FROM employees LEFT JOIN job_history
ON employees. hire_date = job_history. start_date

Group operations

Group operations process multiple rows and return one generalized result. In fact, they allow you to combine in one form or another similar items of information returned from the database. To perform group operations, Oracle provides generic functions and grouping instructions (GROUP BY, HAVING, and others).

The syntax of most generic functions is:

generic_function( expression)

Generalizing_function - specifies the name of the generalizing function: COUNT (count), AVG (average value), MAX (maximum value), MIN (minimum value), STDDEV (standard standard deviation), SUM (sum), VARIANCE (statistical).

DISTINCT - indicates that the generalization function should consider only non-repeating values ​​of the expression.

ALL - indicates that the generalizing function should take into account all values ​​of the expression, including duplicates. The default is to use ALL.

Expression - Specifies the column or any other expression on which to summarize.

NULL values ​​for expression by generic functions are ignored and not included in the result.

Select MAX(salary) from Employees - Find the maximum value for the salary column in the entire EMPLOYEES table.

Select count(*) from Regions - Calculates the number of records in the REGIONS table.

GROUP BY statement

Used in conjunction with summary functions, splits the result set into multiple groups, and then returns one line of summary information for each group. If there is a mixture of generics and non-generics in the SELECT list, SQL considers a Group by operation to be performed, so any non-generic expressions must also be specified in the Group by clause. If this is not done, Oracle will issue an error message.

It is not allowed to use a group (generalize) function in a GROUP BY clause.

When grouping by a column that contains NULL values ​​in some rows, all rows with NULL values ​​are placed in one group and presented in the output as one summary row.

To obtain summary results using the GROUP BY clause, it is possible to filter table records using the WHERE clause. When executing an SQL statement that contains WHERE and GROUP BY clauses, Oracle first applies the WHERE clause and weeds out rows that do not satisfy the WHERE clause. The rows that satisfy the WHERE clause are then grouped according to the GROUP BY clause. The SQL syntax requires the WHERE clause to precede the GROUP BY clause.

HAVING statement

Used to apply a filter to groups created by the GROUP BY clause. If the query contains GROUP BY and HAVING, the result set will contain only those groups that satisfy the condition specified in the HAVING statement. The syntax for the HAVING clause is similar to the syntax for the WHERE clause. But there is one limitation for the HAVING statement. This condition (specified in the HAVING clause) can only apply to SELECT list expressions or the GROUP BY clause. If HAVING contains something that is not in SELECT or GROUP BY, an error message will be issued.

The order of the GROUP BY and HAVING statements in the SELECT statement does not matter.

You can use the WHERE and HAVING statements in the same query. It is important to understand how one instruction affects another. The WHERE clause is executed first, and the rows that satisfy the WHERE clause are passed to the GROUP BY clause. The GROUP BY clause collapses the filtered data into groups, and then the HAVING clause is applied to the groups to eliminate the groups that do not satisfy the HAVING clause.

Syntax for queries with generalization functions and grouping

3.4. Tools for creating and executing SQL queries

In Oracle Database XE, you can write and execute SQL statements using SQL tool Commands (SQL query editor), or you can use the Query Builder tool (query designer) to build queries with a graphical interface (Fig. 1.7).

Launching the SQL Query Editor: Click on the SQL Commands icon.

Entering and executing a SQL query: on the SQL Commands page, write query texts -> select the query you need to execute -> click on the Run button. The result of the query execution is at the bottom of the window (Fig. 1.8).

Rice. 1.8. Entering and executing SQL queries on the SQL Commands page

Oracle Database XE has a handy graphical tool for creating SQL queries - the Query Builder. Launching the Query Builder: Clicking on the Query Builder icon (Fig. 1.7) or on the same item in the SQL Workshop drop-down list -> a browser page with the Query Builder form opens. On the left panel is a list of available tables. Click on the table name -> the table form with the list of available columns is placed on the top right panel. On the form of the table, mark those columns for which data should be included in the resulting set (Fig. 1.9).

Rice. 1.9. Building SQL queries on the Query Builder page

The tabs on the bottom right panel are:

Conditions - conditions imposed on the columns of the table. Allows you to set column aliases, enter conditions for selecting rows according to the column data, determine the type and sort order, the visibility of the result, the function applied to the column, the need for grouping.

SQL - the text of the generated SQL query.

Results - the result of executing the SQL query. To get it, click on the Run button.

Saved SQL - saved SQL queries. To save the query, click on the Save button.

The query builder allows you to visually join tables. To merge two tables, you need to: select two tables -> mark columns for the result on their forms -> place the cursor over the linked column of the child table (COUNTRIES) -> press the right mouse button and drag the link image with the button pressed onto the linked column of the parent table (REGIONS) ) (Fig. 1.10).

Rice. 1.10. Designing SQL queries to join tables

If you hover over the link image, a tooltip appears with the link condition. Clicking on a link brings up a pop-up menu - remove link, set left or right outer join. By default, a relationship defines an inner join of tables.

3.5. Information about database objects

Oracle stores all information about database objects in special dictionary data (data dictionary). The dictionary contains descriptions of how real data is organized. A dictionary is made up of tables and views that can be queried just like any other tables and views in the database. These views are owned by an Oracle user named SYS. Views of type user_* contain information about the objects that the current user is. You can use the following views to get information about user objects:

User_tables - relational tables owned by the current user; User_views - views owned by the current user; User_tab_comments - comments for tables belonging to the current user; User_tab_columns - columns of all tables owned by the current user; User_col_comments - comments for columns of tables and views owned by the current user; User_indexes - indexes of tables of the current user; User_cons_columns - columns in the current user's constraints; User_constraints - restrictions on the tables of the current user; User_triggers - Database triggers owned by the current user.

Oracle Database XE has tools for exploring and reporting on database objects.

Access to the database object exploration tool: the Object Browser icon (Fig. 1.7) or the corresponding item in the SQL Workshop drop-down list. By default, the transition to the list of tables takes place (another type of database objects for research can be selected from the drop-down list). Then, after selecting a specific object, its properties are displayed. The view of the page for displaying the properties of the DEPARTMENTS table (Table tab - shows the list of columns and their properties) is shown in fig. 1.11.

Rice. 1.11. Table Properties Display: List of Columns

If you go to the Model tab, you can see the data model - a visual representation of the links of the table under study with other database tables (Fig. 1.12).

Figure 1.12. Data Model Mapping: Table Relationships

The data model shown in the figure shows that the LOCATIONS and EMPLOYEES tables are the parent of the DEPARTMENTS table in question. They are shown in the model above the table under study. The DEPARTMENTS table itself is the parent of the JOB_HISTORY and EMPLOYEES tables (located below the DEPARTMENTS table). Similar information about the parent (in relation to the studied) tables can be seen in the Dependencies tab (References list).

Access to reports: Utilites icon (Fig. 1.7) or the corresponding element of the SQL Workshop drop-down list -> list element (or icon) Object Reports -> list element by the parameter of interest. For example, for the Table Reports list - columns, comments, restrictions, etc. (Fig. 1.13).

Rice. 1.13. Object Reports

Using these tools, you can get fairly complete information about the main objects available in the database.

Security measures

At runtime you need:

observe the rules for turning on and off computer equipment; do not connect cables, connectors and other equipment to the computer that are not related to the laboratory installation; when the mains voltage is on, do not disconnect, connect or touch the cables connecting various devices computer; in case of a malfunction in the operation of the equipment or violation of the rules, inform the head of the laboratory work; do not try to fix malfunctions in the operation of the equipment on your own; upon completion of work, tidy up the workplace.

ATTENTION! When working at a computer, you must remember: life-threatening voltage is connected to each workplace. Therefore, during operation, you must be extremely careful and comply with all safety requirements!

The task
Launch an internet browser, for example, Google Chrome. Launch the Oracle Application Express home page at http://127.0.0.1:8080/apex/ . Log in to the database as an HR user. Using the SQL Query Editor, create and execute a query to select data from the EMPLOYEES table using the sort instruction by employee names. Using the SQL query builder, create and execute a query to select data from the DEPARTMENTS table using the sort instruction by department name. Using the SQL query editor, create and execute a query to select data from the related tables DEPARTMENTS and EMPLOYEES using Russian column aliases and sorting. The query should return the names of all departments, full names of managers of these departments, their email addresses /text/category/zarabotnaya_plata/" rel="bookmark">salary by department, average salary by department. Get complete information about database objects − tables, columns, constraints, views, including all comments and data types Based on this information, build a data model on which to show all tables and relationships between them.

Directions:

obtain general information about relationships between tables using the Object Browser tool; detailed information get about relationships between tables using a query

select uc. table_name "Table",

uc. constraint_name "External key constraint",

ucc1.column_name "External key field",

ucc2.table_name "Spawns a table",

uc. r_constraint_name "Ogre in RT",

ucc2.column_name "Key field in RT"

from user_constraints uc

join user_cons_columns ucc1 on ucc1.constraint_name = uc. constraint_name

join user_cons_columns ucc2 on ucc2.constraint_name = uc. r_constraint_name

where uc. constraint_type="R";

present the data model in a graphical form using MS Word, MS Visio or ERWin Data Modeller.

Requirements for the content and design of the report

The report must be completed in text editor MS word. The report must contain:

Brief theoretical information, Graphical data model, Texts of all SQL queries with comments on each instruction used in the query, Resulting tables with data for all executed queries, Conclusions on the work done.

test questions

7.1. What is the purpose of Oracle Database XE?

7.2. How is a user connected to an Oracle Database XE database?

7.3. What tools does user interface Oracle Database XE database for working with SQL queries?

7.4. What tools does the Oracle Database XE database user interface provide for exploring database objects?

7.5. What is the syntax of the SELECT statement?

7.6. What is the purpose of the WHERE clause?

7.7. What are the types of table joins?

7.8. What is the difference between inner and outer table joins?

7.9. What are group operations used for?

7.10. How should the GROUP BY and HAVING statements be used?

This is the second part of the tutorial on creating a database. The lesson is practical, during the lesson the Oracle server, which was installed in the first lesson, will be used. So let's get started:

The Database Configuration Assistant (dbca) is used to create the database. It, like most utilities, is located in the bin directory. Oracle Server. In the console we execute:

[email protected]: cd /u01/app/oracle/product/11.1.0/db_1/bin
[email protected]:./dbca

There will be a launch graphic application in which to work. Oracle allows you to create a database "by hand", without using various utilities, but we'll talk about that later.

The first screen simply reports on the launch of the utility, its purpose. We immediately proceed to the second step by clicking next.

The second step is to select the action to be taken. If there are no other databases on the computer, then some options will be unavailable. For example, such as deleting a database or setting up. Now we are interested in creation, so we select the "Create Database" option and move on.

Now we are asked to choose a database template that will be used during creation. General is a common template for most databases. Data Warehouse - a data warehouse designed to store data and a small number of transactions. Custom Database - fully customizable database for your needs, depending on the situation.
Select General Purpose of Transactional Processing and move on.

This step prompts you to specify the Global Database Name (Global Database Name) and SID (System Identifier). Typically, the global name is a SID with a domain name. The system identifier uniquely identifies the database. Its length must not exceed 8 characters and cannot start with a number. I chose the name "testdb" and the global name "testdb.all-oracle.ru" for my database. Let's move on.

Specify the options to be included in the database being created. Specified "Configure Enterprise Manager", the option "Configure Database Control for local management" is automatically checked. If you have Grid configured, then the configuration will be different. Grid technology will be discussed later.

In addition, if required, we enable notification by e-mail and automatic backup of the Recovery Area. I left these options unchecked because the test machine does not have Internet access to send messages and there is no need for a backup. If you wish, you can include these options in your database. To do this, mark and indicate the necessary information. For email notification SMTP server and address to which to send. For backup time and credentials for authorization at the operating system level. Let's move on.

This step prompts you to provide passwords for built-in Oracle accounts. You can specify individual passwords for each account, or you can select the "Use a single password for all accounts" option. It should be noted that prior to Oracle 11g, everything was converted to upper case, and password logins were not case sensitive. Starting with version 11g, the register began to differ in terms of security requirements. For compatibility with previous versions It is possible to set the security level.

Here we select storage. Offered File System (File system), Automatic Storage Management - ASM (Automatic Storage Management) or Raw Devices (literally raw devices). In our case, we choose file system and move on. We will talk about other storage methods later.

Specify the location of the database files. You can choose from a template provided by Oracle, a common location for all database files, or use Oracle-managed files.

Specify the location of the Flash Recovery Area and its size. Previously, we created a directory for FRA and now we specify the path to it, the size is left unchanged.

Flash Recovery Area is a new option available since version 10g and is the basis of a feature called Automated Disk-Based recovery. FRA - part of the disk space for storing and managing files. It is completely separate from other database components such as data files, redo logs, and control files. The Flash Recovery Area is used by the Oracle Recovery Manager (RMAN) utility to ensure that the database is recoverable based on the files saved in the Flash Recovery Area. Flash Recovery Area will be discussed in more detail in the database recovery classes.

On the same screen, you can click the File Location Variables... button to view a summary report of Oracle file locations, including the current ORACLE_BASE setting.

At this step, it is proposed to install schemes with examples, and run any of your scripts, if any. Specify to install examples and move on. You will need them in later lessons.

Now it is proposed to specify the memory settings, the type of connection to the server, the encoding. Since we are now creating just a sample database and do not set ourselves the task of fine-tuning, we leave the default values. On the Connection Mode tab, select "Dedicated server Mode" - the dedicated server mode. By clicking on the "All Initialization Parameters…" button, you can view the initialization parameters. They will be displayed in a table:

Here we can change the parameters as required. If nothing is required, then close the window and move on.

Security Settings. You are prompted to select settings prior to version 11g or new ones that have been introduced since version 11g. The main difference for the user is case sensitivity. We indicate to use the new settings and proceed further.

At this stage, it is proposed to activate automatic maintenance tasks. For example, collecting statistics. Disable and move on.

Specify the location of data files, log files, control files. If everything suits, then move on.

The final step is to complete the creation of the database. Also, if you can save the created database as a template. This is relevant if you created a specific database with a bunch of settings for a specific case and in the future you will often create databases according to a template.

Click the "Finish" button and the database creation process will begin. This will take some time, depending on the settings of the database being created and the power of the computer, from ten minutes to an hour.

If the database creation is successfully completed, the following window will appear:

Here are the main parameters of the database, a link to work with Enterprise Manager. In addition, by clicking the "Password Management ..." button, you can set passwords for built-in accounts, set or remove user locks.

After changing the passwords, if any, click "Exit", this completes the creation of the database.

This concludes the lesson. In the fourth lesson, we will learn how to stop and start a database instance, learn what a listener is, learn how to connect to a database from sqlplus.

If you're a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can run in your environment.

In this case, it is important to understand some of the basic DBA activities of Oracle. In this tutorial, we'll show you how to create an Oracle database from the command line.

When you install the Oracle software, it will give you the option to create a new database from the user interface.

If you decide not to create a new database, but install only software Oracle, then you can then create the database separately.

In order to create Oracle database , you have two options:

1. Use the Database Configuration Assistant (DBCA) and create a new database using the GUI. It's pretty straight forward.
2. Use the "Create Database" command to create a new Oracle database from the command line. This method is useful when you do not have access to the server console to run DBCA. Or, if your server doesn't have the correct Xterm settings, use this method.

1. Setting the appropriate Oracle environment variables

First, you must set the appropriate environment variable on the server correctly.

This example assumes that oracle is installed in the directory /u01/app/oracle/product. Change this value to suit your environment.

export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0

Most important parameter is the ORACLE_SID which will have the name new Oracle database the one you want to create. In this example, the name of the new database is set to "dev" as shown below.

Export ORACLE_SID=dev

2. Create the ini-initdev.ora file

Then create an ora.ini file for the new database. This is the initialization file for the new database.

Depending on your Oracle version, you may see a sample init.ora file in $ORACLE_HOME. If you have, use it as a base and edit the values ​​accordingly.

cd $ORACLE_HOME/dbs cp init.ora initdev.ora

Note:

As shown above, the initialization file for the new database should be of the following format: INIT (ORACLE_SID) .ora - So, in this case, the file name will be: initdev.ora

If you don't see the default init.ora template in your $ORACLE_HOME/DBS, use the following sample.

*.db_name="dev" *.db_domain="" *.audit_file_dest="/u01/app/oracle/admin/dev/adump" *.audit_trail="db" *.compatible="11.2.0.0.0" * .memory_target=1G *.control_files="/u01/app/oracle/oradata/dev/control01.ctl","/home/oracle/u02/oradata/dev/control02.ctl" *.db_block_size=8192 *.diagnostic_dest= "/u01/app/oracle/admin/dev" *.open_cursors=250 *.processes=100 *.remote_login_passwordfile="EXCLUSIVE" *.undo_tablespace="UNDOTS"

A few points need to be considered in said file:

• In the specified file, make sure you set db_name to the name of the ORACLE_SID set in the previous step
• Whatever it's called undo_tablespace, we indicate that we must use the exact name in the CREATE DATABASE command.
• Change the location of directories appropriately based on your system. Don't forget to change "dev" in the above directory to your ORACLE_SID name.

3. Create Serve Parameter file (spfile)

SP file stands for server parameter file. Unlike the sp file, the file is initialized in binary and you cannot edit the SPFILE manually.

The sp file is created from the ini file. The advantage of the sp file is that you can change the value of the initialization parameters after the database has started using the ALTER SYSTEM command.

In other words, when using the "ALTER SYSTEM" command to change any parameter value, it saves them in the sp file.

Then, when starting the Oracle database, it first looks for the sp file for the parameter value. If it cannot find the sp file, then it will use the text based on the ini file.

To create an sp file for our new database, use the following command.

First, use sqlplus command and get a SYSDBA Oracle prompt from where we will create a new database.

$ sqlplus / as sysdba Connected to an idle instance. SQL>

If you notice in the output above, it says "Connected to an idle instance.". This is because our current ORACLE_SID is set to dev, which is a new database that we haven't created yet.

So the first step is to create a new sp file based on the ini file. spfile means ini file. The following command will create a new spfile.

SQL> CREATE SPFILE FROM PFILE; File created.

As you can see below, the command has automatically created spfiledev.ora.

$ ls -1 $ORACLE_HOME/dbs/ initdev.ora spfiledev.ora

4. Run Idle Instance

Before creating the database, we must start an instance for the "dev" database with the STARTUP NOMOUNT command. As you may have guessed, this command will not connect to the database. It's simple to start a new empty simple ORACLE_SID instance named "dev".

SQL> STARTUP NOMOUNT; ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 1261564 bytes Variable Size 520093700 bytes Database Buffers 721420288 bytes Redo Buffers 15515648 bytes

In the command above, it will read the default spfile named spfile(ORACLE_SID).ora from the default spfile location $ORACLE_HOME/dbs. If spfile does not exist, it will default to init(ORACLE_SID).ora file initialization

For some reason, if you want to specify the location of your spfile, you can do so by passing the spfile parameter, as shown below.

SQL> STARTUP NOMOUNT PFILE=/tmp/initdev.ora

Also, you may get the following ORA-01078 and LRM-00109 if the spfile or initialization file is not in the default location.

SQL> STARTUP NOMOUNT ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file "/u01/app/oracle/product/11.2.0/dbs/initdev.ora"

5. Create a New Oracle Database

Use the following CREATE DATABASE command to create an empty database.

SQL> CREATE DATABASE dev USER SYS IDENTIFIED BY DevSysPass USER SYSTEM IDENTIFIED BY DevSystemPass LOGFILE GROUP 1 ("/home/oracle/u02/oradata/dev/redomed_01.log") SIZE 50M, GROUP 2 ("/home/oracle/u02/ oradata/dev/redomed_02.log") SIZE 50M, MAXLOGFILES 5 MAXLOGHISTORY 10 MAXDATAFILES 50 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE "/home/oracle/u02/oradata/dev/system01.dbf" SIZE 100M REUSE SYSAUX DATAFILE "/home /oracle/u02/oradata/dev/sysaux01.dbf" SIZE 100M REUSE DEFAULT TABLESPACE USERS DATAFILE "/home/oracle/u02/oradata/dev/users01.dbf" SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE TEMPTS TEMPFILE "/ home/oracle/u02/oradata/dev/tempts01.dbf" SIZE 30M REUSE UNDO TABLESPACE UNDOTS DATAFILE "/home/oracle/u02/oradata/dev/undots01.dbf" SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

In the above command:

• The Create database command will create an Oracle database named "dev"
• The password specified in the 2nd line will be assigned to the user SYS
• The password specified in the 3rd line will be assigned to the SYSTEM user
• We create two repeated logs with a size of 100MB each.
• MAXLOGFILES - The maximum number of log files is set to 5
• MAXDATAFILES - This indicates the maximum number of Oracle data files that can be created for this database.
• DATAFILE - Points to the data file to be used in the table SYSTEM
• SYSAUX DATAFILE - This indicates that the data file will be used in the SYSAUX tablespace
• The default tablespace for this database is set to USERS
• The temporary tablespace is set to TEMPTS by default
• Undo tablespace is set to UNDOTS

Note:

If you set DB_CREATE_FILE_DEST in the initialization file to a directory location, then you don't need to specify the exact location and filenames for all data files, Oracle takes care of that for you.

For example, if you specified this in the initdev.ora file.

# vi initdev.ora DB_CREATE_FILE_DEST="/home/oracle/u02/oradata/dev"

In this case, you can simplify your CREATE DATBASE command as shown below.

SQL> CREATE DATABASE dev USER SYS IDENTIFIED BY DevSysPass USER SYSTEM IDENTIFIED BY DevSystemPass MAXLOGFILES 5 MAXLOGHISTORY 10 MAXDATAFILES 50 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DEFAULT TABLESPACE USERS DEFAULT TEMPORARY TABLESPACE TEMPACE UNDOTS

The commands above will create the appropriate data files needed for all tablespaces (undo, temporary, etc.) according to the location specified in the DB_CREATE_FILE_DEST directory.

6. Build a Dictionary of Views Data

As a final step, run catalog.sql and catproc.sql. directory script will create all vocabulary tables, performance-related views, necessary public synonyms. It will also provide appropriate access to all synonyms that have been created. The catproc script executes all scripts that are required for PL/SQL functionality.

SQL>@?/rdbms/admin/catalog.sql; SQL>@?/rdbms/admin/catproc.sql

Partial output from the above commands.

SQL>@?/rdbms/admin/catalog.sql; DOC>################################################ ###################### DOC>########################## ########################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error and terminate the SQLPLUS session if the user is not SYS. DOC> Disconnect and reconnect with AS SYSDBA. DOC>################################################ ###################### DOC>########################## ########################################### DOC># no rows selected package created. package body created. .. .. Synonym created. Grant succeeded. PL/SQL procedure successfully completed. SQL> @?/rdbms/admin/catproc.sql .. .. Comment created. Synonym created. Grant succeeded. PL/SQL procedure successfully completed.

If you're interested, you can look at the catproc directory and script to see exactly what it does.

Vi $ORACLE_HOME/rdbms/admin/catalog.sql; vi $ORACLE_HOME/rdbms/admin/catproc.sql

7. Inspection - Completion and commissioning

And finally, perform regular shutdowns and startups to make sure everything works as expected on this new database.

$ sqlplus / as sysdba SQL> SHUTDOWN IMMEDIATE; database closed. database dismounted. ORACLE instance shut down. SQL> STARTUP; ORACLE instance started. Total System Global Area 1234563200 bytes Fixed Size 1262454 bytes Variable Size 522935700 bytes Database Buffers 720583588 bytes Redo Buffers 12946358 bytes Database mounted.