Top-n Query is one of the more advanced SQL problems. How does one retrieve N first (or least) rows from a record set? For example, how does one find the top five highest-paid employees in a given department?
10gR1 revamped Oracle clustered database management and features. 10gR2 builds on this success with a long list of improvements and enhancements. Oracle has streamlined the installation process and provided more filesystem options, made some performance and monitoring improvements, and improved manageability with a half-dozen administration enhancements. This article will take a look at the major changes.
This article shows how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHed. As tools, the DBMS_MVIEW.explain_mview procedure and the MV_CAPABILITIES_TABLE are used. In this particular case, refresh time was reduced from more than 14 hours to less than 2!
The aim of this article is to describe the process of creating a user-defined aggregate function. Oracle 9i was used to prepare and test the function; some parts of the code may not work with Oracle versions older than 9i. This article gathers information that is needed to write the aggregate function in one place, and presents a clear step-by-step descripion of the process.
If there is a task in Oracle for which the wheel has been reinvented many times, it is that of generating database object DDL. There are numerous scripts floating in different forums doing the same thing. Some of them work great, while others work only until a specific version. Sometimes the DBAs prefer to create the scripts themselves. Apart from the testing overhead, these scripts require substantial insight into the data dictionary. As new versions of the database are released, the scripts need to be modified to fit the new requirements.
Starting from Oracle 9i Release 1, the DBMS_METADATA package has put an official end to all such scripting effort. This article provides a tour of the reverse engineering features of the above package, with a focus on generating the creation DDL of existing database objects. The article also has a section covering the issue of finding object dependencies.
Just about every DBA has had to deal with ora-1000 errors, "Maximum open cursors exceeded." This article will discuss initialization parameters that affect open cursors, the difference between open and cached cursors, closing cursors, and monitoring open and cached cursors.
Last month we talked about basic Oracle security, and set out principles for a top notch secure system. These included passwords, the principle of least privilege, and roles.
This month we journey into the fascinating world of Oracle Network Security. The topics covered will not involve the Oracle Advanced Security option: it's too big to cover here, and it is an added expense that many companies do not want. Instead, we will go over basic network security that can be implemented by anyone who uses Oracle. It is built in and so is already part of your system.
Everyone knows the basic features of sql*plus, but one underused feature that can make your scripts an order of magnitude more useful is the ability to store and reuse values, including values read from the database, in variables. This lets you use user-defined and database values not just in subsequent queries, but also in calls to other scripts and SQL*Plus's other functionality.
This article introduces Oracle XML DB features to the DBAs and Developers who are not actively working with XML. It offers a quick start to those who finds quite a lot of Oracle XML literature around, and who is not sure where to begin.
In the rapidly shifting world of database technology, one fact has always been, and will always remain, true: a great database is no good if it can easily be broken into. A faulty security plan is not just vulnerable to hackers; it opens your company to data theft, corruption, or even legal action.