Tags: Oracle, tech tips
Hello, Oraclites. Have you taken a look at the new (well, relatively new) MERGE command that is now part of ANSI SQL? In many cases, incorporating the MERGE command into your application can replace writing a 10 to 20 line program in PL/SQL. And that will lead to significantly improved performance. This is definitely one of those cases where “less is more.”
How does MERGE work?
The MERGE command functions as either an INSERT command or an UPDATE command all in one, depending upon a condition that you specify. And just like INSERT and UPDATE, the new MERGE command is a DML command. If you totally hose your data trying out the new MERGE command, you can do a ROLLBACK. No harm, no foul.
Here’s an example that everyone can probably relate to. WIDGETS Incorporated has 10,000 active and inactive employees, and thus 10,000 rows in the emp table. The column status contains an I for inactive employees, and an A for active employees.
Here is the description of the emp table:
id NUMBER(5) PRIMARY KEY name VARCHAR2(20) NOT NULL dept VARCHAR2(10) NOT NULL salary NUMBER(10,2) hire_date DATE status CHAR(1)
There’s also a new_emp table created by HR and used nightly to update the emp table. HR assigns a new unique ID to new employees who have never worked for WIDGET before, but those who have are reassigned their previous ID. The number of rows in new_emp varies night by night, but averages around 20 rows. Usually one or two out of the 20 represents a rehire. The structure of the new_emp table is exactly the same as the emp table; only the data is different.
What HR wants to do, and the MERGE command allows them to accomplish, is to add the personnel data for a new hire who has never before worked for the company, and assign that employee a status of A (for active). In the same pass through the data, the command should update the emp table by locating inactive employees who have been rehired. When those records are found, the status should be changed from Inactive to Active, and the starting salary when this person is rehired should be updated in his or her row in the emp table. We’ll take care of updating the other columns later.
MERGE INTO emp E USING (SELECT id, name, dept, salary FROM new_emp) N ON (e.id = n.id) WHEN MATCHED THEN UPDATE SET E.dept = N.dept, E.salary = N.salary, E.hire_date = sysdate, E.status = ‘A’ WHEN NOT MATCHED THEN INSERT VALUES (N.id, N.name, N.dept, N.salary, SYSDATE, ‘A’);
This should solve their problem! The merge (either Inserts or Updates) are going to happen to the emp table. We are going to use the data returned by the SELECT to compare each returned row with each row in emp to determine if the values of id are equal. If they are, we’ll do the update in the “WHEN MATCHED” clause. If they aren’t equal, we’ll do the INSERT in the “WHEN NOT MATCHED” clause.
That’s pretty neat, and what’s really great is we don’t need to write a program to accomplish it. Furthermore, we can deal with all the rows in a single pass of the data.
If you like this, take a look at multitable INSERTS, another great feature for developers and DBAs.
–Bob the OrclTestGuy
Tags: Java 7, Oracle, virtual developer day, virtual education, webinar, WebLogic Server 12c
As a technology professional, it is not uncommon to find yourself attending product training and demonstrations. Luckily, many of these events no longer require expensive face-to-face time. Unfortunately, the term webinar leaves a bad taste in many people’s mouths. That’s because often these events boil down to a poorly organized sales meeting led by a person that likes the sound of their own voice. Don’t judge….you know what I’m talking about.
So, when I attended the Oracle Virtual Developer Days for WebLogic Server 12c and Java 7, I was not expecting anything beyond the standard webinar. I could not have been more wrong! Their functional presentation and depth of information blew me away. Besides the requisite keynote session, there were breakout tracks that included a focused Q/A and hands-on labs and even a networking lounge to chat with other attendees. Overall, the experience was engaging, easy-to-navigate and best of all, educational.
I had only minor hiccups with the video streaming, and those were quickly addressed using the chat, where moderators helped to resolve this (among many, I’m sure) issue. All of the presentations were pre-recorded, so I could pause and rewind as needed. The hands-on-labs required me to download and install software prior, but this was a lot less buggy than working in virtual cloud.
Having used this dynamic Oracle Developer Day platform, I am now spoiled. We can only hope other vendors take a page from Oracle and up their virtual education game to this level. I’ll keep you posted on any other webinar gems, and if you’ve attended one that you loved be sure to share the new here.
Tags: Oracle, oracle partitioning data management optimizer range list hash
One of the best features of the Oracle RDBMS over the last ten years has been partitioning. If your shop needs to handle large volumes of data, if you are running OLAP applications, if you often need to move datasets around, if you are responsible for staging data for a rolling twelve-month period, or if you want to improve the performance of you SQL statements, then this is the tool for you!
The basic idea behind partitioning is “divide and conquer”. We’re going to take a very large object (such as a table with six million rows), divide it into logical piece parts, and then manage the smaller pieces. Oracle provides us with a number of different strategies on how we can divide our data. If your data represented orders from your customers, and that was a stored column in the table, you could partition your data by order date. You could break the data into twelve partitions, one for order data for each month over the past year. You could also break your table data into partitions by a selected range. For example, all customers whose last name begins with A – C could go in partition 1, D – H into partition 2, etc.
Another way to “divide and conquer” is to partition by a list when you have discrete values. For example, customers who live in Illinois and Indiana go into partition 1; Pennsylvania, Ohio, and Kentucky go into partition 2; and so on.
Now that you’ve got the idea, let’s talk about the benefits of this tactic. If you can divide your data into 10 partitions, each partition can go into its own tablespace. Radical, huh? That means if you need to move the customers whose last names begin with A – C, you only need to move that single partition, not the entire table. If the data is divided into ten partitions of approximately the same number of row, you will only need to move 10% of the data, instead of 100%. The same is true if you only need to back up one specific partition; your typical O/S tasks would only take 10% of the time they used to take.
The optimizer is also aware of partitioning. If you query your customer table looking for a customer whose last name is Baker, the optimizer knows that it only needs to search in Partition1, since this partition contains customers whose last names range from A – C. You don’t have to spend any time searching through the other nine partitions, because Oracle knows it will never find what you’re looking for in any of the remaining partitions. You can also choose to create an index on just that partition.
There’s still a lot more to know about partitioning, and I encourage you to take a look at it, especially if the size of your Oracle datafiles is starting to become unmanageable, and you’re seeing a degradation of performance at the same time.
Start with the following resources:
I hope that you can learn whether this technique is something that could benefit you and your organization.
Tags: cloud, Larry Ellison, OpenWorld, Oracle, oracle certification, Salesforce
Well, Oracle OpenWorld 2011 has come to a close. For one week in October, this year’s conference brought (approx) 45,000 Oracle customers, employees, and partners to the city of San Francisco, but now the crowds have been replaced with fork lifts in a frantic attempt to break down all of the vendor booths as quickly as possible.
In typical fashion, Larry Ellison made some major announcements in his keynote presentation on Wednesday afternoon! Effective immediately, Oracle announced that every one of the 150+ modules that comprise the new Fusion ERP application are now in production. Fusion is the next generation of applications, which Oracle completely wrote from the ground up and developed with 100% open standards for the past 6 years. It’s the upgrade path for customers running Oracle’s e-business suite, and it’s an option for customers on Peoplesoft, JD Edwards, and Siebel as well.
Ellison also announced that Oracle was creating a public cloud using Oracle’s new Exadata and Exalogic machines, designed explicitly for maximum performance when paired with the Oracle Database. Ellison referred to these machines as “parallel everything” with a performance up to 10 times faster than current servers in the marketplace at a cost of 1/10th of those same machines. Everything is built on standards, making it very easy to port your application from your server to the cloud, or from the cloud back to your server, or from the cloud to your server and then back to the cloud again. It’s all up to you. At the same time, Ellison took a number of shots at competitors, keeping alive the banter between Salesforce.com CEO Marc Benioff which had already provided plenty o’ drama for most of the week!
The other interesting revelation is that social networking is actually built into the new Fusion applications. Facebook is used for identifying yourself and your colleagues who interact with these systems and need to constantly share information. You can extend this social networking to include your customers, your prospects, your partners, your suppliers, etc. This improves overall workflow and communication, but at the same time, you have complete control of the level of access for each player. It definitely had a look and feel that differs from your typical IT applications.
Many excellent small session presentations were offered during the last half of the conference. If you’re not aware, there are some major enhancements in the database in 11g R2. Some of my personal favorites include the Workload Repository and the potential for multiple execution plans, the new SQL Results cache and PL/SQL results cache in the SGA, all the new automatic monitoring features, enhancements to Security, and invisible indexes. I’ll discuss these and other topics featured at OpenWorld 2011 in future blog posts.
Logging off for now; take care!
Tags: Cisco, CIW, Come Together, CompTIA, IT industry, ITCC, LPI, Microsoft, Novell, Oracle, PMI, TechCertRegistry
Since the onslaught of the Great Recession, highlighting your skills for employers has become an important, if not critical, activity. In the IT industry, one of the best ways to prove your skills is to earn certification in the relevant fields and technologies. Thanks to Transcender, and to your own hard work and diligence, you probably have a few certifications under your belt, or are seriously working toward earning one.
If you have a really diversified skill set, you probably have certifications from more than one vendor. Each vendor has their own certification system. CompTIA and Cisco issue physical wallet cards to certified individuals. Microsoft phased out their printed certifications in 2010, then launched their Virtual Business Card site (although wallet cards may be coming back, as per this July post on Born To Learn). All of these vendors, including Oracle, also support a public, online transcript system. The problem is that none of these certification systems are integrated. So you might find yourself fumbling through cards in a high-stakes interview or dealing with an ever-expanding resume to accommodate the boatload of transcript IDs and vendor-specific links.
The (proposed) solution? To make available one central repository of all your certifications, regardless of the vendor. An organization named the ITCC (Information Technology Certification Council) is trying to do exactly that with its TechCertRegistry. Using a single account, you can link certifications from multiple vendors and combine them into one report. Continue Reading Come Together, Right Now…under one certification registry?…
Tags: auditing, authentication, credentials, Oracle, passwords, privileges, Security+
As I sit here next to my distinguished colleagues who are working on Microsoft and Cisco practice exams for Transcender, I feel a little bit like an outsider, since I’m the only Oracle expert on the team. Many of their blogs lately have concerned security in the Microsoft and Cisco environments, so not wanting to feel left out, I thought it was appropriate to let you know that Oracle is no slouch when it comes to security.
A small but important subset of the security features that Oracle provides is the security surrounding user access to the database and subsequent access to different objects within the database. Since space and time are limited, I’ve chosen to discuss the top five security features as decided by yours truly. Ladies and Gentlemen (drum roll please), here are:
My 5 favorite security features in Oracle 11g
Number 5: DBA superpowers. In the past few years Oracle has given the DBA a number of additional capabilities for securing database accounts. Accounts can now be locked, passwords can be set to expire (password aging), minimum password length and password complexity can be established, and passwords containing mixed case characters can be enforced. A maximum number of failed login attempts can also be established, and if a user is unable to login after that number of successive attempts, the account will be locked. Password history and the reuse of the same password can also be controlled. You can choose whether passwords should be case sensitive.
You can even query a view called DBA_USERS_WITH_DEFPWD to find if you have any of those default accounts in your database still set with the default password. Do you really want a hacker breaking into your database because she knows that the password for scott is tiger (doesn’t everybody know that?). And, although not a security topic per se, the DBA can insure that users don’t consume an inappropriate amount of CPU resources, both at the individual command level and the session level.
Number 4: The Oracle password file. When properly protected, the password file is a much better way to authenticate remote DBAs than setting the old REMOTE_OS_AUTHENT init.ora parameter equal to TRUE. Relying on a remote client to be responsible for authenticating DBAs, in my mind, is like giving away the keys to the kingdom. The password file is a far superior method because it provides a single repository for authenticating DBAs. This file exists where it should be located, on the database server, and thus prohibits a hacker from impersonating a legitimate DBA and establishing a DBA connection from a client machine.
Side note: There is some confusion regarding the concept of the password file. It exists solely to store the credentials of a person with “super” DBA privileges (called SYSDBA or SYSOPER) who may need to access the database for the purpose of starting it up if it’s down, or shutting it down if it’s up. This is an alternative method for DBAs who may not have been given the traditional O/S authentication privilege to authenticate themselves. It has nothing to do with authenticating ordinary users. Users have only one way to authenticate themselves: by providing a username and password that matches those stored in the data dictionary of the database. And if the database is down – well, they are just out of luck until the DBA brings the database back up.
Number 3: Auditing improvements. Auditing is a feature of Oracle than has been around for quite a while. However, you may be unaware of some upgrades since the days when AUDIT_TRAIL was set to either TRUE or FALSE. Many shops these days, in addition to the traditional use of auditing to check for suspicious activity, are using Oracle’s auditing feature to insure that privacy requirements are being met (HIPAA, for example), that compliance requirements are being met (Sarbanes-Oxley, for example), as well as to gather statistics which are used as the basis for a proposal on a new business opportunity. New options for the initialization parameter AUDIT_TRAIL allow you to do a variety of tasks such as determining where the audit trail is written as well as the level of detail you would like to capture when auditing. And if you want to take the auditing concept to the next level of sophistication, I suggest you brush up on Oracle’s fine-grained auditing (not to be confused with fine-grained access control).
Number 2: Database-side security. When building applications, Oracle 11g provides a lot of flexibility in terms of how you are going to implement security. One option is to handle all of the security in the code of the application, and pretty much ignore the security set up in the database. When that application needs to finally access the database, it makes a connection as a highly privileged user who can do almost anything in the database. But since any user would have to go through the application to get to the database, the application restricts that user to just those database activities that the application deemed appropriate for that type of user.
On the other hand, you could take advantage of all the features of the database to rely on securing the data. This, IMHO, is the better way to go because you aren’t recreating all of the logic that the database already has built in through privileges, roles, virtual private database, and so on. Also, if you don’t rely on the database to perform your security checking, every new application is going to have to be built with all of that logic repeated each time. It’s also very helpful to the developer to use application roles. These can either be default roles that the user acquires at logon, or roles that the user (or the code the user is running) must explicitly request.
For an extra layer of security, you can attach a password with those roles. The user can be prompted for that password as part of the running application. You also have the ability to turn the role on at one point in your code, and then turn it off at another point in your code. The net effect is that the user will only have the privileges contained within that role for the short period of time in the code while the role is turned on. This provides the developer with a whole slew of options in terms of developing highly secure applications.
Number 1: Password customization. My favorite security feature in Oracle 11g is the ability to create your own logic to determine what is and what is not considered to be an acceptable password. If the DBA knows a little PL/SQL, she or he can create some pretty creative rules for users who are attempting to change their password. This feature is implemented by a function you create and name and then assign to a profile. The return value for your function will be a Boolean. If FunctionA is the password function associated with the profile DEFAULT, then any time a user with the DEFAULT profile tries to create or modify a password (for themselves or for others), then FunctionA will automatically run. If that function returns a TRUE, the new password is considered acceptable by Oracle (assuming it’s not violating some other requirement). If that function returns a FALSE, the new password is considered unacceptable by Oracle and the password is NOT changed.
There are three input arguments to the function and their values are automatically populated by the Oracle software. They are the username, new password, and old password of the user whose password is being assigned with the creation of a new account, or whose password is being changed with an existing account. What the function does with those three values, and whether the function returns a TRUE or FALSE, is strictly determined by the logic implemented by the author of the function (you!). For example, since you have access to the values of both new and the old passwords in your code, you could very easily compare them, and based upon whether they were identical, return either a true or false. You could also very easily look at the number of characters in the new password, and if it’s not long enough in your opinion, you could prevent the user from changing it. You can access SYSDATE and prohibit password changes on certain dates or during certain time frames. All of the examples I’ve given could be done with less than a half dozen lines of PL/SQL code. If you want to go crazy, your rule regarding acceptable passwords is only limited by your creativity as a programmer!
Until next time, be secure!
Tags: Access, DB2, DDL, DML, Oracle, RDBMS, SQL, SQL Server
If you are one of the many IT pros who are familiar with relational databases, then you’re probably at least somewhat knowledgeable about SQL. Your experience may come from SQL Server, Oracle, DB2, or even Microsoft Access. You might think of these as different “brands” of relational databases, but in fact all of these applications use SQL as their tool for manipulating the stored data.
One set of SQL statements is referred to as DML, or Data Manipulation Language. These are the SQL statements such as INSERT, UPDATE, DELETE, and SELECT. These statements perform the actions you would expect on the rows stored in the tables of a Relational Database Management System (RDBMS). Tables, like spreadsheets, contain rows and columns of data. You can also think of a table like a physical flat file, except the records in the file are called rows in the table, and the fields in the file are called columns in the table. Relational databases, however, are much more powerful than flat files or spreadsheets when it comes to manipulating and extracting data.
Another set of SQL statements is referred to as DDL, or Data Definition Language. These are the SQL statements such as CREATE, ALTER, and DROP. Before you can store rows in a table, you must first CREATE a table. Then you can INSERT rows into that table. For example, if the table was named CUSTOMER, you would likely have one row in the CUSTOMER table for each customer of your company. Each row may contain columns to store specific data about that row, such as Customer ID, Customer Name, or Customer Address. If your business had 575 customers, then there would be 575 rows in the CUSTOMER table. If the value of a row changes, such as when a customer’s address changes, you would perform an UPDATE. If a new company became a customer, you would perform an INSERT. If a customer went out of business and consequently was no longer a customer of yours, you would perform a DELETE for that row. If the entire CUSTOMER table, along with all of its rows was no longer needed, you would DROP the CUSTOMER table.
I hope this gives you a little insight into relational databases and how the SQL language plays a very crucial role in controlling and manipulating the actual content in the database. Drop me a line in the blog if I can answer any specific questions you might have.
Tags: Oracle, survey says
Are you a certified Oracle expert? If so, please bop on over to the Oracle Certification Program’s 2009 Salary Survey and let your voice, and experience, be heard.
This survey seeks input from all people who hold Oracle certifications, from all countries, regardless of current employment status or the type of certification held. Plus, it’s short!
Click here to take the survey: http://education.oracle.com/pls/web_prod-plq-dad/ou_online_display_survey.display_survey?p_survey_no=319&p_preview=N
Click here to read the new issue of Oracle Certification e-Magazine: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=300
Tags: Oracle, survey says
Are you Oracle certified? Do you want a voice in the development of future certifications? If so, bop on over to the Oracle website and take this survey:
Oracle is considering whether to develop an Oracle Business Intelligence Enterprise Edition (OBIEE) certification. Let them know who you are, what you do, and whether you think this certification should go forward.