Business Website Links, LLC Computer Tips on Oracle DBMS

Business Website Links
Website Design, PR, and Surveys
Your One-Stop Shop for the
Internet & Beyond

Home

Company Info

Pricing

Contact

Client Directory

Computer Tips

News

Testimonials

Computer Tips

Oracle DBMS

Visit us often. Computer tips updated daily. Click here to--> "Tell a friend" so they can get updated computer tips, too. Please visit our clients, as they support the computer tips page.

If you would like to submit a tip send us an email with your tip to info@businesswebsitelinks.com.
______________________________________________________________

Avoid over-dependence on SQL*Plus with the Group By and Rollup SQL commands (8i+)

While the SQL*Plus language is an invaluable tool, it’s important not to get so dependent on it that you forget what to do if it isn’t available. For example, following is a nifty script that computes the amount of storage space (in bytes) taken up by your own database objects, such as tables or indexes. The script is composed of two parts. The first three lines are SQL*Plus commands. The next two lines make up the SQL query.

column sum(bytes) heading BYTES format 999,999
compute sum of bytes on report
break on report skip 1
select segment_name,bytes
from user_segments

If you run these lines in SQL*Plus or iSQL*Plus, the output appears like this:

SEGMENT_NAME BYTES
---------------- --------
DEPT 65,536
EMP 65,536
--------
sum 131,072

But if you type this script in HTML DB, JDeveloper’s SQL Worksheet, Enterprise Manager’s SQL Scratchpad, or some non-Oracle application, you’ll probably get the error message ORA-00900: invalid SQL statement because of the three SQL*Plus commands at the top. Fortunately, SQL is more powerful today than it was when SQL*Plus was originally designed. You can produce approximately the same report with straight SQL, as shown here:

select segment_name,
to_char(sum(bytes),'999G999') as BYTES
from user_segments
group by rollup (segment_name)

SEGMENT_NAME BYTES
---------------- --------
DEPT 65,536
EMP 65,536
131,072

Detect SQL and bind variables with extended auditing (10g)

Oracle introduced a useful auditing capability in Oracle 10g called extended auditing. With this feature enabled, Oracle writes all statement information to the audit row, including the text of the SQL statement and any bind variables used. This is a great help if you’re trying to diagnose a security breach or application problem.

To configure extended auditing, set the AUDIT_TRAIL parameter to DB_EXTENDED. For instance, with a server parameter file, you can change this so it takes effect after restarting the database, like so:

ALTER SYSTEM SET AUDIT_TRAIL = DB_EXTENDED SCOPE=SPFILE;

For example, consider a SQL statement that inserts data into a table named EMP. With extended auditing turned on, configure auditing on the EMP:

AUDIT INSERT on SAMPLE.EMP BY ACCESS;

Now, suppose someone issues the following INSERT statement:

INSERT INTO sample.emp VALUES(:id, :lname, :fname);

If the bind values are 1, 'SMITH', and 'KENN', this will store both the SQL and bind variables into the audit trail. To see this, issue the following SELECT statement:

SELECT sqltext, sqlbind FROM sys.aud$;

Oracle returns a row containing two values similar to the following:

INSERT INTO sample.emp VALUES(:id, :lname, :fname)
#1(1):1 #2(5):Smith #3(5):Kenny

Avoid the hassle of escaping quotes with user-defined quotes

You’re probably quite familiar with the practice of escaping quotes and other special characters in code. But it can sure be a pain if you or your program has to insert lengthy segments of text. Fortunately, Oracle10g eliminated the necessity of escaping quotes in SQL statements by introducing the ability to have user-defined quote characters.
Prior to Oracle10g, if you wanted to include quotes in text, you had to escape the quote with another quote such as:

SELECT 'Pinnacle''s p''s and q''s text' from dual;

Now with Oracle10g, you can rewrite this as:

SELECT Q'$Pinnacle's p's and q's text$' from dual;

Note that the quoted strings starts with the letter Q, followed by a single quote and the new quote character. It ends with the new quote character and a single quote. We used a dollar sign ($) as our quote character, but you can use other characters if you’d like. Now you can put any quoted text in between your quote characters.

CREATE or REPLACE PROCEDURE proc_test (pi_name VARCHAR2)
IS
begin
DBMS_OUTPUT.PUT_LINE('Name is: '||pi_name);
end;
/

Then invoke the procedure in an anonymous block such as:

DECLARE
l_var varchar2(100):= Q'$Pinnacle's p's and q's text$';
BEGIN
proc_test(l_var);
END;
/

The output is:

Name is: Pinnacle's p's and q's text

Simplify client installs with Instant Client (10g)

If you need to put an Oracle client on several workstations--or if you simply want the Oracle client on a flash drive--Oracle now has a solution to fit your needs. With 10g, Oracle has introduced Instant Client. It’s the barebones software for making a connection to Oracle.

In addition to the platform downloads, there are several Instant Client packages you can download from Oracle, such as Basic, Basic Light, JDBC, and SQL*Plus, among others (installing Instant Client requires at least the Basic or Basic Light package). Each package is a small ZIP file and doesn’t require installation itself. Instant Client and the packages are available at www.oracle.com/technology/tech/oci/instantclient/instantclient.html.

You can use Instant Client for a variety of purposes, such as on Linux to allow Tora to connect to a database, or on Microsoft Windows as a quick way to use SQL*Plus from a flash drive.

To use Instant Client:

1. Unzip the files

2. Add the location of Instant Client to your path, along with TNS_ADMIN, LD_LIBRARY_PATH, SQL_PATH and NLS_LANG.

To utilize Instant Client on a flash drive, set up a quick batch file for Windows in order to set the variables:

SET PATH=%CD%;%PATH
SET TNS_ADMIN=%CD%
SET LD_LIBRARY_PATH=%CD%
SET SQLPATH=%CD:~0,3%scripts\sql
SET NLS_LANG=AMERICAN_AMERICA.UTF8

This is necessary because you typically don’t know which drive letter it will map to from system to system.

You should also modify the SQLPATH and NLS_LANG variables to fit your environment. Then, simply open up a command prompt (cmd), cd to the client directory, and run your batch file.

Tighten security by customizing the software owner account

For new database installations, consider picking a user other than oracle to own the software. Making the switch for existing installations can be a major pain and probably isn’t worth the trouble if the rest of the security strategy is sound. Discourage routine use of the account that owns the Oracle software.

Grant permissions without putting your database in jeopardy

If you're not careful, some workarounds for granting privileges on schema objects you don't own can create dangerous security holes. In the more recent Oracle releases, you don't have to use the old workarounds, so you can grant privileges on other users' objects without compromising security.

To help you get the most out of your database's new security features, we'll:

Discuss why old-fashioned permission-granting workarounds cause security problems.

Present an example showing you how to grant privileges properly without risky workarounds.

Show you how to grant this special privilege to someone else.

Read More: Oracle Tips

Decipher audit trail files more easily by creating them in XML format (10.2)

Oracle can write audit information to a dictionary table or to a file. When it writes standard audit information to a file, the output is difficult to read and decipher.

For example, suppose you want to audit any updates to the SCOTT.EMP table. To do so:

1. Change the database parameter named audit_trail and bounce (restart) the database. The syntax is as follows:

alter system set
audit_trail = OS scope = spfile;

2. Configure the database to audit the update statements made on the table:

AUDIT UPDATE ON SCOTT.EMP;

Oracle stores the audit trail output in a file with an aud extension that looks like this:

Audit trail: SESSIONID: "859" ENTRYID: "2" STATEMENT: "7" USERID: "SCOTT" USERHOST: "CNETICS\KENNY-DESK" TERMINAL: "KENNY-DESK" ACTION: "103" RETURNCODE: "0" OBJ$CREATOR: "SCOTT" OBJ$NAME: "EMP" SES$ACTIONS: "------S---------" SES$TID: "52556" OS$USERID: "CNETICS\ksmith"

To change the operating system audit files to an XML format, change the audit_trail parameter as before like this:

alter system set
audit_trail = XML scope = spfile;

Beginning in Oracle 10g Release 2, Oracle now writes the audit files in an XML format that various tools can help you read and manage much more easily.

Fine-tune private memory areas with PGA_AGGREGATE_TARGET (9i+)

As you may have heard some experienced DBAs mention, one of the most important tuning parameters available for the database is SORT_AREA_SIZE. This is how you can specify an area in memory for a particular user process for sorting, updating bitmap indexes and other functions. As you might imagine, this becomes particularly significant in data warehouse environments. Unless you're using the shared server configuration, formerly known at MTS (multi-threaded server), this memory is allocated in an area called the PGA. This acronym may raise the pulse of you golf fans out there, but this PGA stands for Program Global Area. In the dedicated server configuration, the PGA is separate from the SGA, which is the memory space Oracle uses for the shared pool and database buffer cache.

Read More: Oracle Tips

Save time by using Show Parameter for just one parameter (SQL*Plus)

To view an initialization parameter, it's easy to get in the habit of issuing show parameters (or show parameter) in SQL*Plus and then hunting through the whole list to find the one parameter you want.

But there isn't really need for all that extra effort. To save yourself from looking through all those parameters, just follow the command with the parameter you're interested in, like so:

show parameter USER_DUMP_DEST

SAMBA for Windows! Share files between a Windows PC and Linux or UNIX machines

It may be frustrating if you have to access from Windows any files on the Linux or UNIX machine hosting your Oracle database. Fortunately, Microsoft has offered a simple solution through the program suite called Services For UNIX (SFU).
To help you provide file-sharing capabilities between Windows, Linux, and UNIX machines, we'll:

Explore the different file sharing systems used by Linux/UNIX and Windows.

Perform a custom install of SFU that focuses strictly on gaining file-sharing access from a Windows computer to a Linux or UNIX system.

Connect to a UNIX file system from a Windows computer using SFU.

Discuss file and folder permissions.

Read More: Oracle Tips

Tip: Secure the infrastructure housing your database

Make sure the Oracle host is in a locked room with limited access, redundant power, and fire protection systems. Don't just assume that this is the case. Check it out yourself or confirm it with the administrator responsible for that machine.

Also take a look at the hardware strategy involving the database. Security isn't only about hackers; it's also about availability of the database. If you aren't using RAID or some other disk-mirroring solution, you should do so. Look at disaster recovery and determine what would happen if the Oracle host crashed and was offline for several days.

Tip: Keep unauthorized users from viewing stored database code

To tighten security on your Oracle database, you may want to prevent users from seeing the stored source code in the database. You can do this with the REVOKE command. By default, the ability to select from the ALL_SOURCE dictionary view is granted to PUBLIC. To remove this privilege, issue the following command:

REVOKE SELECT ON ALL_SOURCE
from PUBLIC;

If you revoke SELECT on this view but want to grant some users the ability to see database-stored code, grant those users SELECT capability to some role name of your choice, using the GRANT command:

CREATE ROLE VIEW_CODE_ROLE;
GRANT SELECT ON ALL_SOURCE
to VIEW_CODE_ROLE;

The users with VIEW_CODE_ROLE will be able to see any code that they or the VIEW_CODE_ROLE can execute. To provide the ability to execute all code, you can grant the EXECUTE ANY PROCEDURE system privilege:

GRANT EXECUTE ANY PROCEDURE
to VIEW_CODE_ROLE;

Since EXECUTE ANY PROCEDURE is so powerful, you may want to place a password on our VIEW_CODE_ROLE role and not make it available by default to a user upon connection.

Tip: Keep your database safe from intrusions at all network levels

You’ve probably often heard that security is a multi-layered or multi-level concept, and that protection at all layers is essential for keeping your database secure. But beyond the database itself, you may wonder what the specific layers are that you and the other IT staff you work with should be concerned about.

Moving from the most abstract to the most specific, we define the major levels of network security as:

Policy level. This is all about planning, strategy, and decision-making. At this level, organizations define their security needs and make rules designed to meet those needs.

Physical level. This pertains to the physical security of your network’s components. It includes the obvious—physically controlling access to workstations, servers, and network devices such as firewalls, routers, and switches. It also includes preventing unauthorized persons from obtaining physical possession of or access to the network cable (or, in the case of wireless networking, airwaves). Finally, it involves protecting network information that’s in hard copy form, such as written password lists and network diagrams, as well as smart cards, tokens, keys to removable hard disks, backup tapes, CDs, and anything else that pertains to your network.

Perimeter level. This involves stopping unauthorized persons or data from entering your local network from across the internet, through the phone lines, or other outside locations. The most obvious manifestation of perimeter security is the firewall, but it also includes network access controls in the form of logon authentication, intrusion detection systems, network-based anti-virus systems, network-based spam filtering, remote access quarantine controls and similar methods of keeping undesirable packets out of the network.

Host level. This includes security mechanisms designed to protect individual systems, such as host-based firewalls, host-based IDS and anti-virus programs, operating system hardening and security patches, and securing individual applications.

Data level. This pertains to security of the data stored in your database, the computer’s file system, or data sent across the network, and includes encryption technologies, third-party disk/partition encryption, and IPSec encryption to protect data in transit. It also includes folder and file level access controls.

By implementing security at multiple levels (preferably at all of the levels listed), you avoid the dangers of a single point of failure when it comes to protecting your data.

Tip: Don’t let hackers figure out passwords from their hash values

Your database is in deep trouble if a hacker can simply reconstruct the passwords based on their hash values. That’s why you should read Joshua Wright and Carlo Cid’s October 2005 paper “An Assessment of the Oracle Password Hashing Algorithm,” which they presented at the SANS Network Security 2005 conference.

This paper details weaknesses and vulnerabilities in Oracle’s hashing algorithm for storing database user passwords. It also describes what you can do to guard your database’s password values. On Metalink (https://metalink.oracle.com/), you can read a note where Oracle responds to the issues the paper raises.

To learn about weaknesses in Oracle database password hashing algorithms and find out what you can do to prevent the compromising of your databases:

* Read the SANS paper at www.sans.org/rr/special/index.php?id=oracle_pass
* Read Oracle’s response on Metalink (Note:340240.1).

Here’s a summary of Oracle’s recommendations and other actions you should take right away to protect your database:

1. Force password complexity using a password verification function and profiles.

2. Require users to change passwords regularly via profile limits.

3. Limit access to dictionary tables that display password hash values, such as SYS.USER$ and SYS.USER_HISTORY$.

4. Consider Oracle’s Advanced Security Option or tunneling software for Oracle Net traffic encryption.

5. Limit privileges for application server accounts.

6. Turn on auditing for access to the DBA_USERS view.

7. Consider Oracle’s Advanced Security Option for alternative user authentication.

Tip: Quickly speed up huge, joined table queries by letting Oracle swap in a materialized view

Large data warehouse applications often require queries against huge tables joined together. One way to speed up these operations is to denormalize the data using a materialized view. Like a table, a materialized view actually stores data (not just the SQL code to produce the data, which is what a normal view contains). But unlike a table, Oracle automatically manages the rebuilding of a materialized view (based on parameters you set) so the data is up to date.

Of course, the SQL may come from an application that didn’t consider the use of materialized views. Since Oracle 8i, you can remedy that situation with the query rewrite feature, which lets the optimizer decide to run your improved version of the query (i.e., with the materialized view) instead of the original SQL.

The following steps show an example of creating a materialized view for query rewrite.

1. Ensure that the database parameter QUERY_REWRITE_ENABLED is set to True. Restart the database if necessary.

2. Make sure the user creating the materialized view has the Query Rewrite system privilege.

3. Create materialized view logs on the tables that constitute the materialized view:

Create Materialized View Log
on Sales with Rowid;
Create Materialized View Log
on Region with Rowid;

4. Create the materialized view. In the code below, we create the view in the Region tablespace, and specify reasonable storage parameters. Note that we’ve included the Rowid values for the two tables that form the view. We need this to invoke the Fast (i.e., incremental) refresh method, which makes Oracle only add changed rows when it updates the materialized view.

Create Materialized View Emp_Sales_Region
Tablespace Region
Storage (Initial 1m next 5m pctincrease 0)
Build immediate
Refresh Fast on Demand
Enable query rewrite
As
Select Region_Name, Sales_Amt, S.Rowid, R.Rowid
>From Sales S, Region R
Where S.Region_id = R.Region_id
And S.Trans_Date > Sysdate -1;

5. Finally, analyze the tables that form the materialized view.

Tip: One more use for DataGuard: Hassle-free database cloning for reporting (9i+)

You’re probably familiar with Data Guard (i.e., Standby Databases) as a database recovery tool. The concept is simple: Oracle applies redo data from the production database to one or more standby databases, keeping them synchronized so they can take over if the main database goes down.

However, another potential use for Data Guard is to capture data from a production database for use in a secondary database for reporting, testing, or data warehousing. This approach eliminates the need to create time-consuming scripts or programs to transport the data from one database to another.

Tip: Four ways to get the most out of materialized views

While materialized views--especially with query rewrite--are a powerful way to speed up big queries, don’t assume that you’re getting all the performance you could be getting just because you’ve set them up. In some cases, you may not get any performance gain for your efforts! Here are four easily overlooked points to make sure that your investment in materialized views is paying off:

Don’t forget to create indexes on large materialized views. These indexes are created in exactly the same way as for tables.

For very large materialized views, consider partitioning; you do this the same way as for tables.

When initially trying the query rewrite feature, consider using a query with the identical SQL that’s used to define the materialized view. This ensures that query rewrite won’t be rejected because of some minor difference in the SQL code.

Consider using the initialization parameter QUERY_REWRITE_INTEGRITY. If you set this parameter to STALE_TOLERATED instead, the optimizer may invoke query rewrite without considering whether the data in the materialized view is up to date. Alternatively, setting the parameter to ENFORCED prevents query rewrite if the materialized view has stale data.

Tip: Build a better case for clustering by analyzing hardware upgrade costs

It’s understandable that management may not want to invest in a lot of changes when it perceives that the window ain’t broke. Unfortunately, this attitude may cause your organization to miss out on high availability and other advantages of a clustering solution, such as RAC.

One consideration that may make RAC more attractive to your business is if you look at machine upgrade costs over time. With a cluster, you may be able to stay up to the latest hardware standards by upgrading one or two boxes a year. Compare that with a mainframe solution where the cost of upgrading happens all at once. Showing the benefits for the hardware budget can sweeten the case for clustering.

Tip: Two powerful tools make queries on past data available now (9i+)

If you recently upgraded to 9i or 10g, you have powerful new tools for taking snapshots of data from the past. The UNDO tablespace and its associated UNDO_RETENTION setting give you and your users:
* The ability to run reports from a certain point in time.
* The ability to use a SQL script to re-create accidentally changed or deleted data.

The syntax for performing a flashback query is AS OF. You use this clause together with a data mask,
as in SELECT deptno, sum(Sal) FROM emp GROUP BY deptno AS OF TO_DATE('13-Jul-05' 09:00, date HH:MM);.

To enable this feature:

* Set the parameter UNDO_MANAGEMENT=AUTO.

* Set the UNDO_RETENTION parameter to tell Oracle how many days back you need it to store old data.

* Create an UNDO tablespace with ample room to store the past data.

* Grant the FLASHBACK privilege on specific tables, or FLASHBACK ANY TABLE privilege to users and roles who need to use this feature.

Note that if the undo data is no longer available, you’ll receive the dreaded ORA-01555: Snapshot too old error. To prevent this from happening, increase the setting of UNDO_RETENTION.

Tip: Getting weird errors when attempting standby database recovery? Try this shortcut (8i)

When you’re trying to perform a standby database recovery, the last thing you need is the peculiar errors shown here:

ORA-00283: recovery session canceled due to errors

ORA-01670: new datafile 901 needed for standby database recovery

ORA-01122: database file 901 failed verification check

ORA-01110: data file 901:

'/oracle/data/file1.dbf'

ORA-01203: wrong incarnation of this file - wrong creation SCN

Unfortunately, such errors can occur occasionally because the tablespace was dropped somehow and re-created using the datafile.

Although Metalink (in note 213864.1) reports that you should refresh the standby database, we found an easier solution. Just follow these steps:

1. Put the affected tablespace into backup mode.

2. Copy the most current tablespace files from the primary database instance (overwriting the standby).

3. Take the primary tablespace out of backup mode.

4. Push the logs to the standby.

In our case, this solved our problem. You might need a refresh of the control file as well.

Tip: Easily connect to Oracle from .NET with the OracleClient namespace (VB .NET/.NET Framework 1.1)

Visual Basic 6 developers generally relied on the Microsoft OLE DB provider for Oracle (MSDAORA) or Open Database Connectivity (ODBC) to retrieve Oracle data using ADO. Well, .NET developers have another option: the OracleClient namespace.

The System.Data.OracleClient namespace is the .NET Framework Data Provider for Oracle. However, you should know that VB .NET doesn’t automatically expose the library when you create a new project.

Note: Although you can manually create a reference to the OracleClient library, you can also have VS .NET create it for you. To do so, open the Toolbox, select the OracleDataAdapter from the Data tab and add it to your form. If you don’t want to use the component, simply click the Cancel button on the first form of the dialog wizard and delete the component from your system tray. The reference remains intact.

To manually reference the System.Data.OracleClient:

1. Select Project | Add Reference from the main menu.

2. In the Reference dialog box, click on the .NET tab.

3. In the component list, locate System.Data.OracleClient.dll, click on it, click the Select button, and click OK.

Once you create the reference, you can use the OracleConnection, OracleCommand, and OracleDataAdapter classes to access your Oracle data. The code below shows one way to accomplish this task:

Dim con As System.Data.OracleClient.OracleConnection = _

New System.Data.OracleClient.OracleConnection( _

"User ID = scott; Password = " & _

"tiger; Data Source = oracle1;")

Dim myCMD As New System.Data.OracleClient.OracleCommand( _

"SELECT * FROM Emp", con)

Dim MyDA As New System.Data. _

OracleClient.OracleDataAdapter(myCMD)

Dim ds As New DataSet

MyDA.Fill(ds)

DataGrid1.DataSource = ds.Tables(0)

This code retrieves all the records from the Emp table and loads them into a grid named DataGrid1. If you use this code, be sure to modify the connection information so that it’s valid for your system.

Tip: Speed up your PL/SQL just by upgrading (10g)

If you’ve inherited a lot of slow PL/SQL code, you may be at a loss to figure out how you’re going to speed it all up. One thing that can help is Oracle Database 10g’s optimizing compiler. In 10g, Oracle’s PL/SQL compiler automatically performs a degree of optimization.

So if you’re seeking another reason to upgrade to Oracle Database 10g, the need to deal with that slow PL/SQL may be the answer.

The compiler has three optimization levels, as shown in here:

Level Meaning

0 No optimization

1 Medium optimization and compile times

2 Best optimization; slowest compile times (default)

Level 2, which gives the most optimization, is the default. If you need to set the optimization down for any reason (such as to compile faster), you can use code similar to the following:

alter session

set PLSQL_OPTIMIZE_LEVEL = 1;

TO VISIT BUSINESS WEBSITE LINKS' INTERNET DIRECTORY
CLICK HERE---->INTERNET DIRECTORY ONLINE.COM