Records 19 - 30 Oracle9i Database Administration. Fundamentals I. Volume 1 • Student Guide. DGC Production September D New Oracle9i features such as Oracle Managed Files, Automatic segment Oracle9i DBA Fundamentals I. Understanding the Oracle9i Server Architecture. 1 Records 16 - 32 Introduction. Course Objectives I Oracle 9i Enterprise Edition I Database Administrator Tasks I 1 Oracle Architectural Components.
|Language:||English, French, Portuguese|
|Genre:||Business & Career|
|ePub File Size:||27.56 MB|
|PDF File Size:||8.79 MB|
|Distribution:||Free* [*Registration Required]|
Fundamentals II subparagraph (c)(1)(ii) of DFARS , Rights in Technical Data and Explain solutions included with Oracle9i for. Each pdf file contains a unique serial number associated with your particular name Chapter 1: Oracle9i Database Administration and Management Features . Oracle is a registered trademark and Oracle9i is a trademark or registered trademark of Oracle .. of on the upper floors of #1 World Trade Center in New York City. .. comprehend, it fundamentally changes the nature of the relationship.
Where do you train the Oracle course? How much does the Oracle course cost? The Oracle course fee is quoted in the currency of your country. Please contact us for the latest prices and special offers.
The exam fees are not included in the course fee unless specifically stated. If you are from out of town, we can arrange accommodation close to our centre. Accommodation rates vary depending on the time of year. Included in your course fees: Tuition Breakfast and lunch on course days Teas and coffees Training Material Attendance certificate. What other branches do you have in SA or in the world? We only train in Cape Town.
What books do I get with this course? You will receive a course text book which you can take away with you to keep after the course. What are the dates for the course? See our schedule page: No, the exam is not included in the price. Exam prices vary from centre to centre. The exam is a multiple test, on computer at an authorised testy centre. The results are made known immediately. ATL database Process failures and instance failures are both what types of failure?
Media failure B. User failure C. Non-media failure D. Statement failure Which command-line utility is used to start and stop the listener? User-managed backup and recovery best defines which statement? Custom backup and recovery performed with OS commands and database commands B.
Automated RMAN-based backup What Oracle background process has the responsibility of performing the roll forward in instance recovery? PMON B. SMON C. RECO D.
Oracle9 DBA Fundamentals II: Student Guide • Volume 1
DBWR Which of the following commands would you use to make a backup set unavailable? MAKE C. What are some of the issues of network complexity that the database administrator should consider? How much time it will take to configure a client B. What type of work clients will be performing C. What type of protocols are being used D.
The size and number of transactions that will be done E. Complete recovery is best defined by which of the following statements? Most transactions are recovered. All transactions are recovered except the last archived log. All committed transactions are recovered. There is no data lost whatsoever. Direct-path load B. Conventional load C. Default-path load D. External-path load What are the three primary network configurations?
N-tier architecture B. Single-tier architecture C. Multi-tier architecture D. Two-tier architecture Only incomplete recovery B.
Only complete recovery C. Only partial recovery D. Complete recovery and incomplete recovery What is the primary purpose of using checkpoints? To decrease free memory buffers in the SGA B. To write non-modified database buffers to the database files and to synchronize the physical structures of the database accordingly C. To record modified database buffers that are written to the database files and to synchronize the physical structures of the database accordingly D. What command would you use to retain a backup past the retention date?
HOLD B. KEEP D. STORE Which view can be used to identify clean-up issues after a failed hot or online backup? How does Oracle Shared Server differ from a dedicated server? Clients use dispatchers instead of dedicated connections.
The System Global Area contains request and response queues. Shared server processes execute client requests. What is the disadvantage of the hostnaming option? It cannot use bequeath connections. It cannot use Oracle Shared Server connections. It cannot use client load balancing. Incremental backup B. Full backup C. Image copy D. Current control file backup E. Backup set creation What does IIOP stand for? Internet Interactive Objects Protocol B. Internet Instance Objects Protocol C.
Internet Inter-Orb Protocol D. Internet Inter-Objects Protocol E. User process B. Media failure C. Instance failure D. Change-based recovery is based upon a unique SCN number that each transaction uniquely contains. See Chapter 11 for more information. The recovery catalog is an optional feature of RMAN. One major benefit of the recovery catalog is that it stores metadata about backups in a database that can be reported or queried.
See Chapter 8 for more information. The data file or control file both can be used to store load data. The control file should only store small data loads for one-time use or test purposes.
See Chapter 15 for more information. Control files and parameter files make up two of the physical structures of the Oracle database. Data files and redo logs make up the other physical structures. See Chapter 6 for more information. See Chapter 12 for more information. The control file can be backed up in two ways: See Chapter 9 for more information. Oracle Net is responsible for handling client-to-server and server- to-server communications in an Oracle environment.
It manages the flow of information in the Oracle network infrastructure. Oracle Net is used to establish the initial connection to the Oracle server and then it acts as the messenger, which passes requests from the client back to the server or between two Oracle servers. See Chapter 1 for more information.
B, C, D. See Chapter 13 for more information. Oracle Shared Server allows Oracle servers to manage a greater number of connections utilizing the same amount or less memory and process resources. If an Oracle server is constrained by these resources, Oracle Shared Server can be an alternative configuration that can provide relief.
See Chapter 4 for more information. The loss of inactive or active online redo logs will require an incomplete recovery because the backup will not have all the required logs to apply to the database. The loss of an archived log since the last current backup will also not allow a complete recovery for the same reason as a missing redo log.
Dispatchers take the place of the dedicated server processes. The dispatchers are responsible for responding to client requests by placing the requests on a request queue not a response queue in the SGA; they also retrieve completed requests that were placed on a response queue by the shared server and pass them back to the client.
This also allows you to script exports more efficiently. See Chapter 14 for more information. Cancel-based recovery requires the DBA to manually cancel the recovery process at the command line. The spfile. This is the binary initializa- tion file that is the default when Oracle is started. This file contains persistent parameters. The init.
The main characteristic of the localnaming method is that it uses the tnsnames. In fact, this method is sometimes called the tnsnames. The file contains information about the service name and connect descriptors for each service name that a client can contact.
See Chapter 3 for more information. A, B, D. The session must be enabled to run parallel DML. And the appropriate hints must be entered in the DML statements to allow direct-load insert. Dynamic Registration allows an Oracle server to automatically register with a listener. This reduces the amount of maintenance work the DBA has to do to maintain the listener. See Chapter 2 for more information. Client access control is a feature of Connection Manager that makes Connection Manager function in a manner similar to that of a firewall.
Connections can be accepted or rejected on the basis of the client location, the destination server, and the Oracle service that the client is attempting to connect to. This gives the DBA flexibility they need to configure access control to the Oracle environment. The resynch command should be used when you make physical changes to the target database, such as adding new data files or control files. See Chapter 10 for more information.
Logging records significant events, such as starting and stopping the listener, along with certain kinds of network errors. Tracing records all events that occur, even when an error does not happen.
The trace file provides a great deal of information that logs do not. See Chapter 7 for more information. This parameter is an integer value between 0 and The tnsping utility can be used to check to see if a client can con- tact a listener. The command format is tnsping. This utility also provides information on how long it takes to contact the listener.
Archive logging can be enabled and disabled. In earlier versions of RMAN, files were restored upon request even if it was unnecessary. The shared server processes are responsible for executing the client requests. They retrieve the requests from a request queue and place the completed request in the appropriate dispatcher response queue.
All of these are part of the Oracle Net Stack. Internal Naming is not one of the methods used to resolve a net service name, but localnaming, hostnaming, and Oracle Internet Directory are. For more information, see Chapter Choice B, read-only backup and read-write recovery, will require the restoration and recovery of the data files because changes have been made to the database since the backup.
Choice D will also require restoration of the data file and recovery up to the point when the tablespace was made read-only. In choice A, no changes are made because the tablespace is read-only throughout. Flashback Query allows you to query old deleted data by rebuilding the necessary data elements in the undo tablespaces.
See Chapter 5 for more information. The two methods of exporting data are conventional and direct export. Conventional is the default method, which uses the standard SQL command processing, and direct export bypasses certain aspects of the SQL evaluation layer to improve performance. The listener would not be running on the client. This would be a server-side check that would be performed. Remember that OS commands, such as cp in Unix, are necessary to copy the file to the new location.
Oracle Database 9i SQL Fundamentals Practice Solutions - A...
The Oracle Net Manager is a graphical tool that provides a way to configure most of the critical network files for the Oracle server. The listener. This file contains information about the listening locations, the service names that the listener is listening for, and a section for optional listener parameters, such as logging and tracing parameters. There should be only one listener. If multiple listeners are used, each listener should have its own entry in the listener.
The RMAN repository is the control file that stores the backup information if the recovery catalog is not used. Process failures and instance failures are both types of non-media failure.
These types of failure are usually less critical. The lsnrctl command-line utility is used to start and stop the listener. You can also use this utility to get information about the status of the listener and make modifications to the listener. User-managed backup is the term used to describe the standard backups that have been used from the inception of Oracle.
These backups are usually custom written through the use of OS and database commands. The system monitor SMON process is responsible for applying all of the committed or uncommitted changes in the online redo logs. The DBA needs to consider such items as the number of clients the network will need to support, the type of work the clients will be doing, the locations of the clients in the network, and the size of transactions that will be done in the network.
Complete recovery means that all transactions are recovered. No data is lost and none must be reentered when the database is recovered. The conventional load is the default load that performs normal SQL command processing.
The direct-path load performs an expedited processing that bypasses the buffer and writes directly to data files. The external-path load is a load used for processing external files. The three primary network configurations are single-tier, two-tier, and n-tier architecture. Single-tier was the predominant architecture for many years when the mainframe dominated the corporate environment.
Two-tier architecture came into vogue with the introduction of the PC and has been a dominant architecture ever since. With the inception of the Internet, more organizations are turning towards n-tier architecture as a means to leverage many computers and enhance flexibility and performance of their applications. The main purpose of the database checkpoint is to record that the modified buffers have been written to the data files and to establish data consistency, which enables faster recovery in the event of a failure.
The KEEP command causes a backup to be kept past the retention setting in the database. Oracle Shared Server uses a shared model. Clients share processes called dispatchers that handle their requests. Clients also share processes called shared servers that execute their requests.
The sharing is done through modifications to the SGA. The disadvantage is that certain functionality, such as client load balancing and failover, is not available when you use the hostnaming method. A, B, D, E. A media failure would most likely cause the DBA to get actively involved in the recovery of the database by entering recovery commands if this was a user-managed recovery.
The other failures mentioned are usually handled by Oracle automatically. Modern networks can be comprised of many computers on multiple operating systems using a wide variety of protocols and communicating across wide geographic areas. One need look no further than the explosion of the Internet to see how networking has matured and what a profound impact networks are having on the way we work and communicate with one another.
While networks have become increasingly complex, they also have become easier to use and manage. For instance, we all take advantage of the Internet without knowing or caring about the components that make this communication possible because the complexity of this huge network is completely hidden from us. The experienced Oracle database administrator has seen this maturation process in the Oracle network architecture as well.
This chapter highlights the areas that database administrators DBAs need to consider when implementing an Oracle network strategy.
It also looks at the responsibilities the database administrator has when managing an Oracle network. The chapter then explores the most common types of network configurations and introduces the features of Oracle Net—the connectivity management software that is the backbone of the Oracle network architecture. It will also explore the Oracle network architecture and summarize the Oracle network infrastructure. First and foremost is the design of the Oracle network architecture itself.
It is flexible and configurable, and it has the scalability to accommodate a range of network sizes. Also, when you are working with an Oracle network, there are a variety of network configurations to choose from. The sections that follow summarize the areas that the DBA needs to consider when designing the Oracle network infrastructure. Network Complexity Issues The complexity of the network plays an important role in many of your network design decisions.
Consider the following questions to determine network complexity: In complex networks, clients may be geographically dispersed over a wide area. Will these be PCbased clients or terminal-based clients? Will these be thin clients that will do little processing or fat clients that will do the majority of the application processing?
Will there be any middle-tier servers involved, such as an application server or transaction server? This is especially important to consider when you are dealing with third-party applications. Consider the following questions to determine the security of a network: What kinds of information will be sent across the Oracle network?
This may involve sending the data in a format that makes it tamperproof and also ensures that the data cannot be captured and read by parties other than the client and the intended Oracle server. In large organizations with many Oracle services, this can be a management and administration issue. Interfacing Existing Systems with New Systems The following issues should be considered when existing computer systems must communicate with Oracle server networks: If so, what is the frequency and what transport mechanisms should be used?
Will the Oracle server need to send information back to the existing system? Here are some of the key responsibilities of the DBA in the Oracle network implementation process: Network Configurations T here are three basic types of network configurations to select from when you are designing an Oracle infrastructure.
The simplest type is the singletier architecture. This has been around for years and is characterized by the use of terminals for serial connections to the Oracle server.
Applications utilizing single-tier architecture are sometimes referred to as green-screen applications because most of the terminals using them, such as the IBM terminal, have green screens. Single-tier architecture is commonly associated with mainframe-type applications.
This architecture is still in use today for many mission-critical applications, such as Order Processing and Fulfillment and Inventory Control, because it is the simplest architecture to configure and administer.
When a single-tier architecture is being used, users interact with the database using terminals. These terminals are non-graphical, character-based devices. Figure 1. In this type of architecture, client terminals are directly connected to larger server systems such as mainframes. All of the intelligence exists on the mainframe, and all processing takes place there.
Simple serial connections also exist on the mainframe. Although no complex network architecture is necessary, a single-tier architecture is somewhat limiting in terms of scalability and flexibility.
Because all of the processing must take place on the server, the server can become the bottleneck to increasing performance. Also, two-tier architecture allows the client computer to share the application processing load. To a certain degree, this reduces the processing requirements of the server. The two-tier architecture does have some faults, even though at one time, it was thought to be the panacea of all networking architectures.
Unfortunately, the main problem, that of scalability, persists. The slash represents the invisible component of the two-tier architecture and the one that is often overlooked: When prototyping projects, many developers fail to consider the network component and soon find out that what worked well in a small environment may not scale effectively to larger, more complex systems. There was a great deal of redundancy in the two-tier architecture model because application software was required on every desktop.
As a result of this scenario, many companies end up with bloated PCs and large servers that still do not provide adequate performance. What is needed is a more scalable model for network communications. That is what n-tier architecture provides. Instead of dividing application processing work between a client and a server, you divide the work up among three or more machines. The n-tier architecture introduces middleware components, one or more computers that are situated between the client and the Oracle server, which can be used for a variety of tasks.
Some of those tasks include the following: This common interface means less training of staff and also increases the potential reuse of client-side application components. N-tier architecture makes it possible to take advantage of technologies such as networked computers. Such computers can make for economical, low-maintenance alternatives to the personal computer.
Student Guide Volume 1
Because much of the application processing can be done by application servers, the client computing requirements for these networked computers are greatly reduced. In addition, the processing of transactions can also be offloaded to transaction servers, which reduces the burden on the database servers.
The n-tier model is very scalable and divides the tasks of presentation, business logic and routing, and database processing among many machines, which means that this model accommodates large applications. In addition, the reduction of processing load on the database servers means that the servers can do more work with the same amount of resources.
Also, the transaction servers can balance the flow of network transactions intelligently, and application servers can reduce the processing and memory requirements of the client see Figure 1. It is responsible for handling client-to-server and server-to-server communications, and it can be configured on the client, the middle-tier application, web servers, and the Oracle server.
Oracle Net also manages the flow of information in the Oracle network infrastructure. First, it is used to establish the initial connection to the Oracle server, and then it acts as the messenger, passing requests from the client back to the server or passing them between two Oracle servers. Basically, Oracle Net handles all negotiations between the client and server during the client connection. These products allow n-tier architectures to be used in the enterprise, which increases the flexibility and performance of application designs.
To learn more about these products and some of the features of Oracle Net, read the following sections, which mirror the five categories of networking solutions that Oracle Net provides: This support is handled transparently and allows Oracle Net to establish connectivity to a wide range of computers and a wide range of operating environments.
SANs are special configurations of hardware that are used for situations in which multiple servers need high-speed communications between them.
The new Virtual Interface VI protocol is lightweight and works with a specific hardware configuration to relieve network activity responsibility from the CPUs and place it on special network adapters. A for details on the use, configuration, and restrictions on the VI protocol. This guide may be obtained from the Oracle Technology Network website at technet. This range allows users to bridge existing systems to other Unix or PC-based systems, which increases the data access flexibility of the organization without making wholesale changes to the existing systems.
These features allow clients to connect to applications interfacing with an Oracle database via a web browser. By utilizing features such as Secured Sockets Layer SSL , client connections can obtain a greater degree of security across the Internet.
Directory Services: Directory Naming Directory Naming allows for network names to be resolved through a centralized naming repository. It provides a simplified way to manage directories of information, whether this information is about users in an organization or Oracle instances connected to a network. By utilizing Oracle Internet Directory, you ensure security and reliability of the directory information because information is stored in the Oracle database.
As of Oracle9i, Directory Naming has become the preferred method of centralized naming within an Oracle environment, replacing the Oracle Names Server. The Oracle Names Server can still be utilized in Oracle8i and earlier versions, however. OID can be used with both Oracle8i and 9i databases. In Oracle9i, the OID runs as an application. The OID is a scalable architecture, and it provides mechanisms for replicating service information among other Oracle servers.
OID also provides security in a number of ways. Also, an administrator can maintain policies that grant or deny access to services. These policies are defined for entities within the Oracle Internet Directory tree structure.
Oracle Shared Server Oracle Shared Server formerly known as Multithreaded Server is an optional configuration of the Oracle server that allows support for a larger number of concurrent connections without increasing physical resource requirements. This is accomplished by sharing resources among groups of users.
Connection Manager Oracle Connection Manager is a middleware solution that provides three additional scalability features: Multiplexing Connection Manager can group together many client connections and send them as a single multiplexed network connection to the Oracle server. This reduces the total number of network connections the server has to manage.
Network access Connection Manager can be configured with rules that restrict access by IP address. This rules-based configuration can be set up to accept or reject client connection requests. Also, connections can be restricted by point of origin, destination server, or Oracle server. Cross-protocol connectivity This feature allows clients and servers that use different network protocols to communicate.
Connection Manager acts as a translator, providing two-way protocol conversion. Oracle Connection Manager is controlled by a set of background processes that manage the communications between clients and servers. This option is not configured using the graphical Oracle Net Manager tool. Advanced Security The threat of data tampering is becoming an issue of increasing concern to many organizations as network systems continue to grow in number and complexity and as users gain increasing access to systems.
Sensitive business transactions are being conducted with greater frequency and, in many cases, are not protected from unauthorized tampering or message interception. Oracle Advanced Security, formerly known as the Advanced Security Option and the Advanced Networking Option, not only provides the tools necessary to ensure secure transmissions of sensitive information, but it also provides mechanisms to confidently identify and authenticate users in the Oracle enterprise.
When configured on the client and the Oracle server, Oracle Advanced Security supports secured data transactions by encrypting and optionally checksumming the transmission of information that is sent in a transaction. These security features ensure that data transmitted from the client has not been altered during transmission to the Oracle server.
Oracle Advanced Security also gives the database administrator the ability to authenticate users connecting to the Oracle servers. In fact, there are a number of authentication features for ensuring that users are really who they claim to be. These are offered in the form of token cards, which use a physical card and a user identifying PIN number to gain access to the system; the biometrics option, which uses fingerprint technology to authenticate user connection requests; Public Key; and certificate-based authentication.
Another feature of Oracle Advanced Security is the ability to have a single sign-on mechanism for clients. Single sign-on is accomplished with a centralized security server that allows the user to connect to any of the Oracle services in the enterprise using a single user ID and password. Oracle leverages the industrystandard features of Kerberos to enable these capabilities. Kerberos is an authentication mechanism based on the sharing of secrets between two systems.
This greatly simplifies the privilege matrix that administrators must manage when they are dealing with large numbers of users and systems. Firewall Support Firewalls have become an important security mechanism in corporate networks.
Firewalls fall into two broad categories: IP-filtering firewalls IP-filtering firewalls monitor the network packet traffic on IP networks and filter out packets that either originated or did not originate from specific groups of machines.
The information contained in the IP packet header is interrogated to obtain this information. Vendors of this type of firewall include Network Associates and Axent Communications. Proxy-based firewalls Proxy-based firewalls prevent information from outside the firewall from flowing directly into the corporate network.
Instead, the firewall acts as a gatekeeper, inspecting packets and sending only the appropriate information through to the corporate network. This prevents any direct communication between clients outside the firewall and applications inside the firewall. Check Point Software Technologies and Cisco are examples of vendors that market proxy-based firewalls. Oracle works closely with the vendors of both types of product to ensure support of database traffic through these types of mechanism. This product can be incorporated into the firewall architecture to allow database packets to pass through the firewall and still maintain a high degree of security.
Know Thy Firewall It is important to understand your network infrastructure, the network routes you are using to obtain database connections, and the type of firewall products you are using. I have had more than one situation in which firewalls have caused connectivity issues between a client and an Oracle server. For instance, I remember what happened after a small patch was applied to a firewall when I was working as a DBA for one of my former employers. In this case, employees started experiencing intermittent disconnects from the Oracle database.
It took many days of investigation and network tracing before we pinned down the exact problem. When we did, we contacted the firewall vendor and they sent us a new patch to apply that corrected the problem.
It turns out that the networking routes for the development staff had been modified to have connections routed through a new firewall. This firewall was configured to have a connection timeout after 20 minutes of inactivity, which was too short an amount of time for this department.
These are examples of the types of network changes that a DBA needs to be aware of to avoid unnecessary downtime and to avoid wasting staff time and resources. Heterogeneous Services Heterogeneous Services provide the ability to communicate with non-Oracle databases and services.
These services allow organizations to leverage and interact with their existing data stores without having to necessarily move the data to an Oracle server. These products allow Oracle to communicate with non-Oracle data sources in a seamless configuration. Heterogeneous Services also integrate existing systems with the Oracle environment, which allows you to leverage your investment in those systems.
These services also allow for two-way communication and replication from Oracle data sources to non-Oracle data sources. Transparent Gateway The Transparent Gateway product seamlessly extends the reach of Oracle to non-Oracle data stores, which allows you to treat non-Oracle data sources as if they were part of the Oracle environment.
In fact, the user is not even aware that the data being accessed is coming from a non-Oracle source. This can significantly reduce the time and investment necessary to transition from existing systems to the Oracle environment. Transparent Gateway fully supports SQL and the Oracle transaction control features, and it currently supports access to more than 30 non-Oracle data sources. Generic Connectivity Generic Connectivity provides a set of agents, which contain basic connectivity capabilities.
You can also use ODBC as a stand-alone connection solution. These drivers can be obtained from Oracle or third-party vendors. Because these drivers are generic in nature, they do not provide as robust an interface to external services as does the Transparent Gateway. External Procedures In some development efforts, it may be necessary to interface with procedures that reside outside of the database.
These procedures are typically written in a third-generation language, such as C. When a call is made, a process will be started that acts as an interface between Oracle and the external procedure. This callout process defaults to the name extproc. The listener is then responsible for supplying information, such as a library or procedure name and any parameters, to the called procedure.
These programs are then loaded and executed under the control of the extproc process. The Oracle Net Stack Architecture T he Oracle Net software is comprised of a series of programs that form a type of stack architecture.
Each of these programs is responsible for handling various aspects of network communications, and each functions as a layer of the stack. This section discusses the architecture of the Oracle Net stack and defines the responsibilities of each portion. Then create a query that displays the name, job, department name, salary, and grade for all employees. The HR department wants to determine the names of all employees who were hired after Davies.
Create a query to display the name and hire date of any employee hired after e employee Davies. Using Subqueries to Solve Queries 1. The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply excluding that employee.
For example, if the user enters Zlotkey, find all employees who work with Zlotkey excluding Zlotkey. Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is Using Subqueries to Solve Queries continued 5.
Create a report for HR that displays the last name and salary of every employee who reports to King. Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department.
Using the Set Operators 1. Use the set operators to create this report. The HR department needs a list of countries that have no departments located in them. Display the country ID and the name of the countries. Produce a list of jobs for departments 10, 50, and 20, in that order. Using the Set Operators continued 4. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the company that is, they changed jobs but have now gone back to doing their original job.
The HR department needs a report with the following specifications: This way you get to see the feedback messages on the Script Output tabbed page. From File menu, select Open. In the Open dialog box, navigate to D: You get a Create Table succeeeded message on the Script Output tabbed page.
Manipulating Data continued 4. Populate the table with the next two rows of sample data listed in step 3 by running the INSERT statement in the script that you created. Manipulating Data continued To confirm that the table was created and to view its structure, issue the following command: Include only those columns that you need. To confirm that the table was created and to view its structure: Hence, you will not be allowed to insert any row into the table because it is assigned a read only status.
Now try to insert the same row again. Creating Other Schema Objects Part 1 1. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all m y e employees in department They have requested that you label the view columns d reassigned to another department through the view. Creating Other Schema Objects continued Part 2 7. You need a sequence that can be used with the primary key column of the DEPT table. Oracle Join Syntax 1.
Write a query to display the last name, department number, and department name for all employees. Order the results by the employee number. Oracle Join Syntax continued 6.
The HR department wants to determine the names of all employees hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies. The company has three divisions: Tracks business statistics to facilitate business decisions Each of these divisions is represented by a schema. 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. A join is used to view information c table.
Therefore, you can join tables together to view information from more than one O ly Note: In the example in the slide, the report c displays data from two separate tables: In other words, all rows in the first table are joined to all rows in O ly A Cartesian product tends to generate a large number of rows and the result is rarely useful.
Before the Oracle9i release, the join syntax was proprietary. The SQL: Rows in one c r a table can be joined to rows in another table according to common values that exist in the corresponding columns that is, usually primary and foreign key columns. For example, to join four O tables, a minimum of three joins is required.
This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row. Therefore, it is necessary O ly to add the table prefix to execute your query.
However, using a table prefix improves performance, because you tell the Oracle server exactly where to find the columns. Therefore, you can use table aliases instead of table names. Just as a column alias gives a column another name, a table alias gives a table another name. The table name is specified in full, followed by a space and then the table alias.
Equijoins are also called simple joins or inner joins. Other columns that are not present in both the tables need not be qualified r a by a table alias, but it is recommended for better performance. Table aliases are used to qualify the columns and avoid ambiguity. The example in the slide limits the rows of output to those with a department ID equal to 20 or For example, to join three tables, a minimum of two joins is required.
The relationship is obtained using an operator other than the n a e t e r U s I n c l e r a O Oracle Database 11g: The salary c r a must be between any pair of the low and high salary ranges. It is important to note that all employees appear exactly once when this query is executed. No O ly employee is repeated in the list. There are two reasons for this: That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salary grade table.
I n Note: O Table aliases have been specified in the example in the slide for performance reasons, not because of possible ambiguity. To return the department record that does not have any employees, or to return the employee record that does not belong to any department, you can use the outer join. Syntax e A l Missing rows can be returned if an outer join operator is used in the join condition. This operator has the effect of creating one or more null rows, to which one or more O ly rows from the nondeficient table can be joined.
The c Outer Join Restrictions r a Contracting department does not have any employees. The empty value is shown in the output. Overview e A l This practice is intended to give you practical experience in extracting data from more than one table c using the Oracle join syntax. Create a report for the HR department that displays employee last names, department numbers, and all employees who work in the same department as a given employee.
Give each column an l appropriate label. Oracle SQL contains many extensions. It contains its own command language. To log in from a Windows environment: Enter the username, password, and database name. To log in from a command-line environment: 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.
To ensure the integrity of your password, do not enter it at the operating system prompt. Instead, enter only your username. Enter your password at the password prompt.
The result of c contain data. In c the result: O ly Type: The SQL prompt then appears. The new current line is displayed. The newly edited line is displayed. U I n c l e r a O Oracle Database 11g: In this case, replace the employees table with the departments table. You can use the commands described in the following 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 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.
O ly SQL Developer, the visual tool for database development, simplifies the following tasks: When connected, you can perform operations on objects in the database. The SQL Developer 1. 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. I n SQL Developer can be downloaded with the following packaging options: To install SQL Developer, you need an unzip tool.
Download the SQL Developer kit from http: Unzip the downloaded SQL Developer kit into the folder created in step 1. SQL Developer 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. By using this tab, you can run predefined reports or create and add your own reports. You can customize many aspects of the appearance n e r and behavior of SQL Developer by setting preferences. The following menus contain standard s entries, plus entries for features specific to SQL Developer: 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.
On Windows, if the tnsnames. 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: 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 name I n directly if you use a remote database connection. 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 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.
Example e A c l In the Create Table dialog box, if you do not select the Advanced check box, you can create a r a table quickly by specifying columns and some frequently used features. 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. 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 the SQL Worksheet toolbar that contains icons to perform the following tasks: Execute Statement: Executes the statement where the cursor is located in the Enter SQL variables.
You can use bind variables in the SQL statements, but not substitution a e 2. Run Script: You can use substitution variables in the SQL statements, but not bind variables. Writes any changes to the database and ends the transaction I n 4. Discards any changes to the database, without writing them to the database, and ends the transaction l e 5. Stops the execution of any statements currently being executed c 6. SQL History: Displays a dialog box with information about SQL statements that you have r a executed O 7.
Execute Explain Plan: Generates the execution plan, which you can see by clicking the Explain tab 8. Generates trace information for the statement 9. Enter SQL statements.
Oracle9 DBA Fundamentals II: Student Guide • Volume 1
Results are shown here. Commands not n a e supported by the SQL Worksheet are ignored and are not sent to the Oracle database. You can display a SQL Worksheet by using any of the following two options: 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.Heterogeneous Services provide the ability to communicate with non-Oracle databases and services.
You must pass both sections to pass the exam. By using Oracle Net Manager, you can ensure that the files are created in a consistent format, which will reduce the potential for connection problems. As of Oracle9i, Directory Naming has become the preferred method of centralized naming within an Oracle environment, replacing the Oracle Names Server. The Oracle Names Server can still be utilized in Oracle8i and earlier versions, however.