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

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:
      ---------------- --------
      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)

      ---------------- --------
      DEPT 65,536
      EMP 65,536

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:
  • For example, consider a SQL statement that inserts data into a table named EMP. With extended auditing turned on, configure auditing on the EMP:
  • 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)
      DBMS_OUTPUT.PUT_LINE('Name is: '||pi_name);
  • Then invoke the procedure in an anonymous block such as:
      l_var varchar2(100):= Q'$Pinnacle's p's and q's text$';
  • 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
  • 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 SQLPATH=%CD:~0,3%scripts\sql
  • 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:
  • Oracle stores the audit trail output in a file with an aud extension that looks like this:
  • 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:
      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:
      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:
      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 (, 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
      * 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
        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


Home | Company Info | Pricing | Contacts | Client Directory | Computer Tips | NewsTestimonials |
Disclaimer | Our Privacy Policy | Terms of Use | Site Map

Business Website Links, LLC ē 8041 Via Hacienda ē Palm Beach Gardens ē Florida ē 33418

Copyright ©2005 all rights reserved by Business Website Links, LLC
Web Host and Design by Business Website Links, LLC