Oracle 11g pdf download free

In particular, this guide is for the following groups of Oracle users: - Developers who want to acquire basic database administrator DBA skills - Anyone managing database servers - Database administrators managing an Oracle database for a small or medium-sized business This book is equally useful for enterprise DBAs. It recommends best practices and describes efficient ways of performing administrative tasks with Oracle Enterprise Manager as the primary interface. The OBE steps through all tasks in the chapter and includes annotated screenshots.

In some cases, the OBE provides additional information to help you complete the task. Note: If you're looking for a free download links of High Availability of Oracle Database 11g R2 Chinese Edition Pdf, epub, docx and torrent then this site is not for you.

Realize the full potential of Oracle Database 11g with help from the experts. Written by Robert G. Oracle 11g is an Oracle geodatabase After the download is finished After you download , install, Download the application Anniversary Edition and Windows Mac. Windows Users' choice Download oracle 11g express edition Download oracle 11g express edition Most people looking for Oracle 11g express edition downloaded: Oracle Database 11g Express Edition. Oracle Client Express Edition.

Ignore case-sensitivity. Open a new worksheet. You also learn about your user account that you use in this course. Finally, you access and bookmark the Oracle Database 11g documentation and other useful Web sites that you can use in this course.

Connection Name: myconnection. Username: oraxx Ask your instructor to assign you one ora account out of the m y ora21—ora40 range of accounts. Password: oraxx c a d.

Port: c l r a f. Then click the Data tab. Then, click OK. Then, press F5. The following is the expected result. The character pattern-matching operation is also referred to as: Wildcard search 3. Group functions give one result per group of rows.

Single-row functions operate on single rows only and return one result per row. Which function converts mixed-case or lowercase character strings to uppercase? UPPER 5. TRUNC m y c. O ly What is the default sorting order of rows?

I n You cannot specify a column position as the sort expression. You can specify an expression, an alias, or a column position as the sort r a condition. Single-row subquery The outer query takes the results of the subquery r a null and uses these results in its WHERE clause.

The outer query finds no employee O ly with a job ID equal to null, and so returns no rows. Is this a system or an object privilege? Create a role containing the system privileges and grant the role to the users.

Which user can User1 only c l r a 7. What command do you use? Then, verify whether that user can use the privilege. Note: For this exercise, team up with another group. For example, if you are user ora21, team up with another user ora Include an option for this user to further grant this privilege to other users. Team 2 executes this statement. Team 1 executes this statement.

Team 2 should add Human Resources as department number l a e Team 1 creates a synonym named team2. But, you see the ora error with the second row insertion. Query the data dictionary to see information about the tables you own.

Query the dictionary view to see information about all the tables that you can access. Then, find the names of all synonyms that are in your schema.

You need to determine the names and definitions of all the views in your schema. Create a report that dictionary view. Write a query in a script to display the following information about your sequences: sequence name, maximum value, increment size, and last number.

Query the data dictionary to find out the constraint names and types for both the tables. Query the data dictionary to display the object names and types for both the tables. Then query the data dictionary view to find the index name, table name, and whether the index is unique. You want to store the names and email IDs of all employees past and present in a table.

Next, change the department for m Kochhar to 50 and commit your change. Track the changes to Kochhar using the Row Versions feature. If the year hired was , display Needs Review for the review status; otherwise, display not this year! If the employee has been employed five or more years, print 5 years of service. If the employee has been employed 10 or more years, print 10 years of service.

If the employee has been employed 15 or more years, print 15 years of service. Write a query to display the last name, department number, and salary of any employee whose department number and salary match the department number and salary of any employee who earns a commission. Display the last name, department name, and salary of any employee whose salary and commission match the salary and commission of any employee located in location ID Write a query to find all employees who earn more than the average salary in their A departments.

Display last name, salary, department ID, and the average salary for the e shown in the sample output. Sort by average salary. The subquery picks up a NULL value, so the entire query returns no rows. Write a query to display the last names of employees who have one or more coworkers in their departments with later hire dates but higher salaries. Display only those rows, which are affected. Ignore case- sensitivity. In this course, you have access to the objects in all the schemas.

However, the emphasis of the examples, demonstrations, and practices is on the Human Resources HR schema. Human Resources HR This is the schema that is used in this course. In the Human Resource HR records, each employee has an identification number, email address, job identification code, salary, and manager.

Some employees earn commissions in addition to their salary. The company also tracks information about jobs within the organization. Each job has an identification code, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different positions within the company.

When an employee resigns, the duration the employee was working for, the job identification number, and the department are recorded. Each employee is assigned to a department, and each department is identified either by c a a unique department number or a short name.

Each department is associated with one location, and the country code. You learn how r a to use SQL Developer for your database development tasks. With just a few clicks, you can easily create and debug stored procedures, test SQL statements, and view optimizer plans.

When connected, you can perform operations on objects in the database. Therefore, it is a cross-platform tool. SQL Developer does not require an installer t e r U s and you need to simply unzip the downloaded file. To install SQL Developer, you need an unzip tool. Unzip the downloaded SQL Developer kit into the folder created in step 1. The installation kit for l e SQL Developer 1.

You may use either version of SQL c Developer in this course. Instructions for installing SQL Developer version 1. You can customize many aspects of the appearance and behavior of SQL Developer by setting preferences.

To use SQL Developer, you must have at least one database connection, which may be existing, created, or imported. O ly You can create and test connections for multiple databases and for multiple schemas. You can export connections to an XML file so that you can reuse it later. On the Connections tabbed page, right-click Connections and select New Connection.

Enter the O ly username and password of the schema that you want to connect to. You can select the connection type as: n - t e r U s Basic: In this type, enter hostname and SID for the database you want to connect to. Port is already set to Or you can also choose to enter the Service - I n name directly if you use a remote database connection.

TNS: You can select any one of the database aliases imported from the c l - e tnsnames. Click Test to ensure that the connection has been set correctly. Click Connect. So, after you close the SQL Developer connection and open it again, you are not prompted for the password. The connection gets added in the Connections Navigator. You can expand the connection to view the database objects and view object definitions, for example, dependencies, details, statistics, and so on.

However, these connections are read-only connections that enable you to browse objects and data in that data source. You can customize many aspects of the appearance of SQL Developer by setting preferences.

For example, if you select a table in the Navigator, the details about columns, t U constraints, grants, statistics, triggers, and so on are displayed on an easy-to-read tabbed page.

Expand the Connections node in the Connections Navigator. Expand Tables. By default, the Columns tab is selected. It shows the column O description of the table. Using the Data tab, you can view the table data and also enter new rows, update data, and commit these changes to the database.

Alternatively, you can create objects using the context menus. When created, you can edit the objects using an edit dialog box or one of the many context-sensitive menus.

O ly As new objects are created or existing objects are edited, the DDL for those adjustments is l one or more objects in the schema. To open a dialog box for creating e r s a new table, right-click Tables and select New Table. The dialog boxes to create and edit t U database objects have multiple tabs, each reflecting a logical grouping of properties for that type of object.

If you select the Advanced check box, the Create Table dialog box changes to one with multiple O ly options, in which you can specify an extended set of features while you create the table. In the Connections Navigator, right-click Tables. In the Create Table dialog box, select Advanced. Specify column information. Click OK. Sometimes, you may want to edit the table that you have created; to do so, right-click the table in the Connections Navigator and select Edit.

You can use bind variables in the SQL statements, but not substitution a e 2. You can use substitution variables in the SQL statements, but not bind variables. Commit: Writes any changes to the database and ends the transaction I n 4.

Rollback: Discards any changes to the database, without writing them to the database, and ends the transaction l e 5. Cancel: Stops the execution of any statements currently being executed c 6. Execute Explain Plan: Generates the execution plan, which you can see by clicking the Explain tab 8.

Autotrace: Generates trace information for the statement 9. Enter SQL statements. Results are shown here. Commands not n a e supported by the SQL Worksheet are ignored and are not sent to the Oracle database. View the results on the Script Output tabbed page. For a single statement, the semicolon at the end is optional. When you enter the statement, the SQL keywords are automatically highlighted. Alternatively, you can press the F9 key. Alternatively, e r s In the example in the slide, because there are multiple SQL statements, the first statement is t U I n terminated with a semicolon.

The cursor is in the first statement, and therefore, when the statement is executed, results corresponding to the first statement are displayed in the Results box. The contents of the saved file are visible and editable in your SQL Worksheet window. In the Windows Save dialog box, enter a file name and the location where you want the file saved. Click Save. You can have multiple files open at the same time. Each file displays as a Script Pathing I n l e You can select a default path to look for scripts and to save scripts.

To run the code, click the Run Script F5 icon. Select or navigate to the script file that you want to open. Click Open. The Open O ly 2. In the Open dialog box, select or navigate to the script file that you want to open. The code of the script file is displayed in the SQL Worksheet area. The output from the script is displayed on the Script Output tabbed page. Use the command, followed by the location, and name of the file you want to run, in the 2.

Click the Run Script icon. You can also save the script output by clicking the Save icon on the Script Output tabbed page. The Windows n a e File Save dialog box appears and you can identify a name and location for your file.

After formatting, the SQL code is beautified with the l a e keywords capitalized and the statement properly indented. When you place your cursor here, it shows the Snippets window.

From the drop-down list, you can select the functions category that you want. SQL Developer has the feature called Snippets. You can drag snippets into the Editor window. You can use the drop-down list to select a group.

A Snippets button is placed in the right window margin, so that you can display the e r s Snippets window if it becomes hidden. Then you can edit the syntax so that the SQL function is valid in the current context.

To see a brief description O ly of a SQL function in a tool tip, place the cursor over the function name. Provide the location of the sqlplus. You cannot resume execution from this point; instead, to start running or debugging from the beginning of the function or procedure, l e click the Run or Debug icon in the Source tab toolbar. O These options are also available as icons in the debugging toolbar. Individual reports are r a displayed in tabbed panes at the right side of the window; and for each report, you can select O using a drop-down list the database connection for which to display the report.

For reports about objects, the objects shown are only those visible to the database user associated with the selected database connection, and the rows are usually ordered by Owner.

You can also create your own user-defined reports. Organize reports in folders. To create a user-defined report, perform the following steps: r a 1. Then, click Apply. You can also include an optional tool tip to be displayed I n when the cursor stays briefly over the report name in the Reports navigator display. To create a folder for user-defined reports, right-click the User Defined Reports node r a or any folder name under that node and select Add Folder.

Information about user-defined reports, including any folders for these reports, is stored in a file named UserReports. Also, you have shortcut icons to some of the frequently used tools such as Notepad, Microsoft Word, and Dreamweaver, available to you.

To do so, perform the following: n a e 1. From the Tools menu, select External Tools. In the External Tools dialog box, select New to add new tools. Therefore, users will no longer be able to connect to an Oracle 8. Create a folder. Note: As with version 1. These are the same options that are found in the Debug menu in version 1. Using SQL r a Developer, you can browse, create, and edit database objects. SQL Developer enables you to create and save your own special set of reports for repeated use.

Version 1. Oracle SQL contains many extensions. It contains its own command language. To log in from a Windows environment: r a O ly 1. Enter the username, password, and database name. To log in from a command-line environment: 1. Log on to your machine. Enter the sqlplus command shown in the slide. I n username Your database username password Your database password Your password is visible if you enter it here.

Instead, enter only your username. Enter your password at the password prompt. The result of c contain data. In c the result: r a Null?

The SQL prompt then appears. The new current line is displayed. The newly edited line is displayed. In this case, replace the employees table with the departments table. The default extension is.

GET filename [. The default extension for the file name is. OFF closes the spool file. OUT closes the spool file and sends the file results to the printer. In this way, you can store frequently used scripts for use in the future. You can also, alternatively, use the symbol l to run a script. This opens an editor with the script file in it. The delimiter must be the first character of a new line immediately following the statement. You can now append to, or replace an existing file, where previously you could only use SPOOL to create and replace a file.

O ly To spool output generated by commands in a script without displaying the output on the screen, use interactively. SET parameters. You can then use c r a group functions to return summary information for each group. The Oracle server applies the group O ly functions to each group of rows and returns a single result row for each group.

COUNT returns the number of non-null rows for the given expression. To substitute a value for null O values, use the NVL function. COUNT returns either a number or zero. The total a e salary and the number of employees are calculated for each job ID within each department.

It groups rows. You can use the CUBE operator to produce cross-tabulation rows. The cumulative aggregates can be used in reports, charts, and graphs. It then applies the aggregate function to these groupings. CUBE produces totals as well but O effectively rolls up in each possible direction, producing cross-tabular data. First, it calculates the standard c l e aggregate values for the groups specified in the GROUP BY clause in the example, the sum of salaries grouped on each job within a department.

Then it creates progressively higher-level r a subtotals, moving from right to left through the list of grouping columns. In the example, the sum of salaries for each department is calculated, followed by the sum of salaries for all O departments. It is used to produce result sets that are typically used for cross-tabular reports. The aggregate function specified in the select list is applied to these groups to produce summary I n values for the additional superaggregate rows.

Mathematically, these combinations form O an n-dimensional cube, which is how the operator got its name. By using application or programming tools, these superaggregate values can then be fed into charts and graphs that convey results and relationships visually and effectively.

Further, the CUBE operator displays the total salary for every job irrespective of the department. The function returns a value of 0 or 1. You can observe similar output in the fifth row. This is the total salary for those departments whose department ID is less than 50 and all job titles.

Doing so facilitates efficient aggregation and, therefore, facilitates analysis of data across multiple dimensions. A multiquery approach is inefficient because it requires multiple scans of the same data. The result set displays the average n a e In the output, the group marked as 1 can be interpreted as the following: 3, This could be very inefficient. It is generally used for generating grand totals. This is illustrated in the example in r a the slide. Therefore, the use of composite columns is recommended.

The result is a cross-product of groupings from each r a grouping set. A relational database does not store records in a hierarchical way. However, where a hierarchical relationship exists between the rows of a single table, a process O ly called tree walking enables the hierarchy to be constructed.

Imagine a family tree with the eldest members of the family found close to the base or trunk of n a e the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on. Note: Hierarchical trees are used in various fields such as human genealogy family trees , r a livestock breeding purposes , corporate management management hierarchies , manufacturing product assembly , evolutionary research species development , and scientific research.

This relationship can be exploited by joining the O ly table to itself. FROM table n a e Specifies the table, view, or snapshot containing the columns. This clause is required for a hierarchical query. To find the child rows of a parent row, the Oracle server evaluates the O ly PRIOR expression for the parent row and the other expressions for each row in the table.

Use Example r a employee King as the starting point. Print only one column. This will make your report more readable. The forks where one or more branches split away from a larger branch are called nodes, and the very end of a branch is called O ly a leaf or leaf node.

The diagram in the slide shows the nodes of the inverted tree with their Davies is a child and a leaf. Note: A root node is the highest node within an inverted tree.

A child node is any nonroot node. A parent node is any node that has children. A leaf node is any node without children. The number of levels returned by a hierarchical query may be limited by available user memory. The argument n is the total length of the return value as it is displayed on your terminal screen. The predicate you use acts as a Boolean condition. O ly Starting at the root, walk from the top down, and eliminate employee Higgins in the result, but process the child rows. The n e r data dictionary is a collection of tables and views that contain information about the database.

All data dictionary tables are t U owned by the SYS user. Information stored in the data dictionary includes names of Oracle I n server users, privileges granted to users, database object names, table constraints, and audit l e information. There are four categories of data dictionary views. Each category has a distinct prefix that reflects its intended use. The next step is to enhance the report to automate the process. You must also plan to clean up the output that is generated and make sure that you suppress elements such as headings, feedback messages, top titles, and so on.

The dropem. This file can now be O ly started from SQL Developer by locating the script file, loading it, and executing it.

A set of r a four single quotation marks produces one single quotation mark in the final statement. Also remember that character and date values must be surrounded by quotation marks. For example, in the fifth example in the slide, the surrounding quotation marks are for the entire string. The second quotation mark acts as a prefix to display the third quotation n a e mark.

Thus, the result is one single quotation mark followed by the parenthesis, followed by the semicolon. If you do not enter l n a e any department number, the department number is treated as null by the DECODE function, and the user is then prompted for the hire date. The variable name cannot contain a pound sign. These scripts can be used to avoid repetitive coding, contain run-time parameters. You learn about the functions. The purpose of a database is to store and retrieve related information.

This is accomplished while delivering high for failure recovery. Every time O ly an instance is started, the SGA is allocated and the background processes are started. Because the physical and logical structures are separate, the physical storage of data can be managed a e without affecting access to logical storage structures. The physical storage structures n include: t e r U s - The control files where the database configuration is stored I n - The redo log files that have information required for database recovery - The data files where all data is stored l e An Oracle instance uses memory structures and processes to manage and access the database c r a storage structures.

All memory structures exist in the main memory of the computers that constitute the database server. Processes are jobs that work in the memory of these computers. After the user establishes connection, a session is created for the user. Connection and session are closely related to user process but are very different in meaning. A communication pathway is established using available interprocess communication mechanisms or network software when different computers run the database application and n a e Oracle Database, and communicate through a network.

A session lasts from the time the user connects until the time the user c l e disconnects or exits the database application. In the case of a dedicated connection, the session is serviced by a permanent dedicated process. O Multiple sessions can be created and exist concurrently for a single Oracle Database user using the same username, but through different applications, or multiple invocations of the same application.

It r a illustrates an Oracle Database configuration where the user and associated server process are on separate computers, connected through a network.

An instance has started on a node where Oracle Database is installed, often called the host or database server. A user starts an application spawning a user process. The application attempts to establish a a e connection to the server. The connection may be local, client server, or a three-tier n t e r U s connection from a middle tier. The server runs a listener that has the appropriate Oracle Net Services handler. The server I n detects the connection request from the application and creates a dedicated server process on behalf of the user process.

For example, the c user changes the address of a customer in a table and commits the change. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.